3 Creating and Using Database Objects

Creating a Table

To implement the employee evaluation process, you will need to establish three tables, performance_parts, evaluations, and scores.

• The performance_parts table lists the categories of performance measurements, and the relative weight for each item.

• The evaluations table will contain the employee's information, evaluation date, and the job, manager and department at the time of evaluation. You must preserve this information in this table because at any point in the future, the employee may change job designation, manager, or department.

• The scores table contains the scores assigned to each performance category for each evaluation.

To create a table using SQL Developer interface:

You will create the performance_parts table using the SQL Developer graphical interface.

1. In the Connections navigation hierarchy, click the plus sign (+) next to hr_conn to expand the list of schema objects.

2. Right-click Tables.

3. Select New Table.

   
   Description of the illustration table_create_1.gif
   
   

4. In the Create Table window, enter the following information:

   • For Schema, select HR.

   • For Name, enter PERFORMANCE_PARTS.

   
   Description of the illustration table_create_2.gif
   
   

5. Click the default column that was created with the table.

6. Enter the information for the first column in the table as follows:

   • For Column Name, enter PERFORMANCE_ID.

   • For Type, enter VARCHAR2.

   • For Size, enter 2.

   Leave the value of Not Null and Primary Key properties. You will come back to this later, in "Ensuring Data Integrity".

   
   Description of the illustration table_create_3.gif
   
   

7. Enter information for the second column as follows:

   Click Add Column.

   • For Column Name, enter NAME.

   • For Type, enter VARCHAR2.

   • For Size, enter 80.

8. Enter information for the third column as follows:

   Click Add Column.

   • For Column Name, enter WEIGHT.

   • For Type, enter NUMBER.

9. Click OK.

   SQL Developer generates the new table, performance_parts.

10. In the Connections navigation hierarchy, click the plus sign (+) next to Tables to expand the list of tables.

    performance_parts is a new table in the hr schema, listed between locations and regions.

You just created a new table, performance_parts. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table.

In Example 3-1, you will create the evaluations table by entering the information directly in the SQL Worksheet pane.

CREATE TABLE evaluations (
  evaluation_id    NUMBER(8,0), 
  employee_id      NUMBER(6,0), 
  evaluation_date  DATE, 
  job_id           VARCHAR2(10), 
  manager_id       NUMBER(6,0), 
  department_id    NUMBER(4,0),
  total_score      NUMBER(3,0)
)

CREATE TABLE succeeed.

You created a new table, evaluations. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

In Example 3-2, you will create another table, scores, by entering the information in the SQL Worksheet pane.

CREATE TABLE scores (
  evaluation_id   NUMBER(8,0), 
  performance_id  VARCHAR2(2), 
  score           NUMBER(1,0)
);

CREATE TABLE succeed.

You created a new table, scores. If you click the table, the table will appear on the right side of the SQL Developer window, showing its new columns. If you click the SQL tab, you will see the script that created this table. You may need to click the Refresh icon.

Ensuring Data Integrity

The data in the table must satisfy the business rules that are modeled in the application. Many of these rules can be implemented through integrity constraints that use the SQL language to explicitly state what type of data values are valid for each column.

When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule, so when your application includes a SQL statement that inserts or modifies data in the table, Oracle Database automatically ensures that the constraint is satisfied. If you attempt to insert, update, or remove a row that violates a constraint, the system generates an error, and the statement is rolled back. If you attempt to apply a new constraint to a populated table, the system may generate an error if any existing row violates the new constraint.

Because Oracle Database checks that all the data in a table obeys an integrity constraint much faster than an application can, you can enforce the business rules defined by integrity constraints more reliably than by including this type of checking in your application logic.

"NAME" VARCHAR2(80) NOT NULL ENABLE

ALTER TABLE performance_parts
MODIFY weight NOT NULL;

ALTER TABLE performance_parts succeeded. 

"WEIGHT" NUMBER NOT NULL ENABLE

CONSTRAINT "PERF_PERF_ID_PK" PRIMARY KEY ("PERFORMANCE_ID")

ALTER TABLE evaluations
ADD CONSTRAINT eval_eval_id_pk PRIMARY KEY (evaluation_id);

ALTER TABLE evaluations succeeded.

CONSTRAINT "EVAL_EVAL_ID_PK" PRIMARY KEY ("EVALUATION_ID")

CONSTRAINT "SCORES_EVAL_FK" FOREIGN KEY ("EVALUATION_ID")
 REFERENCES "HR"."EVALUATIONS" ("EVALUATION_ID") ENABLE
CONSTRAINT "SCORES_PERF_FK" FOREIGN KEY ("PERFORMANCE_ID")
 REFERENCES "HR"."PERFORMANCE_PARTS" ("PERFORMANCE_ID") ENABLE

ALTER TABLE evaluations
    ADD CONSTRAINT eval_emp_id_fk FOREIGN KEY (employee_id) 
    REFERENCES employees(employee_id);

ALTER TABLE evaluations succeeded

CONSTRAINT "EVAL_EMP_ID_FK" FOREIGN KEY ("EMPLOYEE_ID")
 REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

CONSTRAINT "SCORE_VALID" CHECK (score >=0 and score <=9) ENABLE

Adding Data to a Table, Modifying, and Deleting

You can use SQL Developer to enter data into tables, to edit, and to delete existing data.The following tasks will show these processes for the performance_parts table.

to add data to a table using the SQL Developer interface:

Follow these steps to add rows of data to the performance_parts table

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_parts table display.

3. In the Data pane, click the New Record icon.

   
   Description of the illustration data_add_1.gif
   
   

4. In the new row, add the following information; you can click directly into the column, or tab between columns:

   • Set PERFORMANCE_ID to 'WM'

   • Set NAME to 'Workload Management'

   • Set WEIGHT to 0.2

   Press the Enter key.

   
   Description of the illustration data_add_2.gif
   
   

5. Add a second row with the following information: set PERFORMANCE_ID to 'BR, set NAME to 'Building Relationships, and set WEIGHT to 0.2.

   Press the Enter key.

6. Add a third row with the following information: set PERFORMANCE_ID to 'CF', set NAME to 'Customer Focus', and set WEIGHT to 0.2.

   Press the Enter key.

7. Add a fourth row with the following information: set PERFORMANCE_ID to 'CM', set NAME to 'Communication', and set WEIGHT to 0.2.

   Press the Enter key.

8. Add a fifth row with the following information: set PERFORMANCE_ID to 'TW', set NAME to 'Teamwork', and set WEIGHT to 0.2.

   Press the Enter key.

9. Add a sixth row with the following information: set PERFORMANCE_ID to 'RD', set NAME to 'Results Orientation', and set WEIGHT to 0.2.

   Press the Enter key.

10. Click the Commit Changes icon.

11. Review and close the Data Editor Log window.

    
    Description of the illustration data_add_3.gif
    
    

12. Review the new data in the table performance_parts.

    
    Description of the illustration data_add_4.gif
    
    

You have added 6 rows to the performance_parts table.

To modify table data using the SQL Developer interface:

Follow these steps to change data to the performance_parts table.

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_parts table display.

3. In the Data pane, in the 'Workload Management' row, click the weight value, and enter a new value for '0.3'.

   In the 'Building Relationships' row, click the weight value, and enter a new value for '0.15'.

   In the 'Customer Focus' row, click the weight value, and enter a new value for '0.15'.

   
   Description of the illustration data_add_5.gif
   
   

4. Press the Enter key.

5. Click the Commit Changes icon.

6. Review and close the Data Editor Log window.

   
   Description of the illustration data_add_6.gif
   
   

You have now changed values in three rows of the performance_parts table.

To delete table data using the SQL Developer interface:

Imagine that in the company modeled by the hr schema, management decided that the categories Workload Management and Results Orientation had too much overlap. You will now remove the row 'Results Orientation' from the performance_parts table.

1. In the Connections navigation hierarchy, double-click the performance_parts table.

2. Click the Data tab in the performance_parts table display.

3. In the Data pane, click the 'Results Orientation' row.

4. Click the Delete Selected Row(s) icon.

   
   Description of the illustration data_add_7.gif
   
   

5. Click the Commit Changes icon.

6. Review and close the Data Editor Log window.

   
   Description of the illustration data_add_8.gif
   
   

You have now removed a row from the performance_parts table.

Indexing Tables

When you define a primary key on a table, Oracle Database implicitly creates an index on the column that contains the primary key. For example, you can confirm that an index was created for the evaluations table on its primary key, by looking at its Indexes pane.

In this section, you will learn how to add different types of indexes to the tables you created earlier.

To create an index using the SQL Developer interface:

Follow these steps to create a new index for the evaluations table.

1. In the Connections navigation hierarchy, right-click the evaluations table.

2. Select Index and then select Create Index.

   Alternatively, in the Connections navigation hierarchy, you can right-click Indexes and select New Index.

   
   Description of the illustration index_2.gif
   
   

3. In the Create Index window, enter the following parameters:

   • Ensure that the Schema is set to HR.

   • Set the Name to EVAL_JOB_IX.

   Click the Add Column Expression icon, which looks like a 'plus' sign.

   • Set the Column Name or Expression to JOB_ID.

   • Set the Order to ASC.

   Click OK.

   
   Description of the illustration index_3.gif
   
   

You have now created a new index EVAL_JOB_IX on the column JOB_ID in the evaluations table. You can see this index by finding it in the list of Indexes in the Connections navigation hierarchy, or by opening the evaluations table and browsing to the Indexes tab. The following script is the equivalent SQL statement for creating this index.

CREATE INDEX eval_job_ix ON evaluations (job_id ASC) NOPARALLEL;

To modify an index using SQL Developer interface:

Follow these steps to reverse the sort order of the EVAL_JOB_ID index.

1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

2. Right-click EVAL_JOB_IX, and select Edit.

   
   Description of the illustration index_4.gif
   
   

3. In the Edit Index window, change Order to DESC.

   Click OK.

You changed the index. The following script is the equivalent SQL statement for creating this index:

DROP INDEX eval_job_id;
CREATE INDEX eval_job_ix ON evaluations (job_id DESC) NOPARALLEL;

To delete an index using SQL Developer interface:

Following steps to delete the EVAL_JOB_IX index.

1. In the Connections navigation hierarchy, the plus sign (+) to expand Indexes.

2. Right-click EVAL_JOB_IX, and select Drop.

   
   Description of the illustration index_5.gif
   
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the index EVAL_JOB_IX. The following script is the equivalent SQL statement for dropping this index.

DROP INDEX "HR"."EVAL_JOB_ID";

Dropping Tables

Sometimes it becomes necessary to delete a table and all its contents from your schema using. To accomplish this, you must use the SQL statement DROP TABLE. You will use the tables that you already created to learn other concepts, so create a simple table that you can subsequently delete by running the following script in the SQL Statement window:

CREATE TABLE temp_table(
  id NUMBER(1,0),
  name VARCHAR2(10)
);

To delete a table using the SQL Developer interface:

Follow these steps to delete TEMP_TABLE from the hr schema.

1. In the Connections navigation hierarchy, right-click TEMP_TABLE.

2. Select Table, and then select Drop.

   
   Description of the illustration index_6.gif
   
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the table TEMP_TABLE. The following script is the equivalent SQL statement for dropping this table.

DROP TABLE "HR"."TEMP_TABLE";

Creating a View

The standard syntax for creating a view follows:

CREATE VIEW view_name AS query;

To create a view using the SQL Developer interface:

Follow these steps to delete create a new view from the hr schema.

1. In the Connections navigation hierarchy, right-click Views.

2. Select New View.

   
   Description of the illustration view_1.gif
   
   

3. In the Create View window, enter the following parameters:

   • Ensure that Schema is set to HR.

   • Set Name to SALESFORCE.

   • Set the SQL Query to the following:

     SELECT first_name || ' ' || last_name "Name", salary*12 "Annual Salary"
     FROM employees
     WHERE department_id = 80
     

4. In SQL Parse Results, click Test Syntax.

   
   Description of the illustration view_2.gif
   
   

5. Click OK.

You created a new view. The equivalent SQL statement for creating this view follows:

CREATE VIEW salesforce AS 
SELECT first_name || ' ' || last_name "Name", 
 salary*12 "Annual Salary"
FROM employees
WHERE department_id = 80;

In Example 3-6, you will create a view of all employees in the company and their work location, similar to the query you used in "Using Character Functions".

CREATE VIEW emp_locations AS
SELECT e.employee_id,
  e.last_name || ', ' || e.first_name name,
  d.department_name department,
  l.city city, 
  c.country_name country
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
 d.location_id=l.location_id AND
 l.country_id=c.country_id
ORDER BY last_name;

CREATE VIEW succeeded.

You have now created new view that relies on information in 4 separate tables, or a 4-way JOIN. In the Connections navigation hierarchy, if you click the 'plus' sign next to Views, you will see emp_locations.

Updating a View

To change the properties of a view in SQL Developer interface:

You will change the salesforce view by adding to it the employees in the Marketing department, and then rename the view to sales_marketing.

1. In the Connections navigation hierarchy, right-click the salesforce view.

2. Select Edit.

   
   Description of the illustration view_3.gif
   
   

3. In the Edit View window, change the SQL Query by adding the following to the last line: 'OR department_id = 20'.

   Click Test Syntax.

   Click OK.

   
   Description of the illustration view_4.gif
   
   

4. To rename the view, right-click salesforce and select Rename.

   
   Description of the illustration view_5.gif
   
   

5. In the Rename window, set New View Name to sales_marketing.

   Click Apply.

   
   Description of the illustration view_6.gif
   
   

6. In the Confirmation window, click OK.

You changed the view. The equivalent SQL statements for changing and renaming the view are:

CREATE OR REPLACE VIEW salesforce AS query;
RENAME "SALESFORCE" to SALES_MARKETING;

Dropping a View

To drop a view using the SQL Developer interface:

You will use the DROP VIEW statement to delete the sales_marketing view.

1. In the Connections navigation hierarchy, right-click the sales_marketing view.

2. Select Drop.

   
   Description of the illustration view_7.gif
   
   

3. In the Drop window, click Apply.

4. In the Confirmation window, click OK.

You deleted the view. The equivalent SQL statement for dropping the view is:

DROP VIEW sales_marketing;

Creating a Sequence

You can create a sequence in the SQL Developer Interface, or using the SQL Statement window.

To create a sequence using the SQL Developer interface:

The following steps will create a sequence, evaluations_seq, that you can use for the primary key of the evaluations table.

1. In the Connections navigation hierarchy, right-click Sequences.

2. Select New Sequence.

   
   Description of the illustration sequence_1.gif
   
   

3. In the New Sequence window, enter the following parameters:

   • Ensure that Schema is set to HR.

   • Set Name to EVALUATIONS_SEQ.

   In the Properties tab:

   • Set Increment to 1.

   • Set Start with to 1.

   • Check Order.

   Click OK.

   
   Description of the illustration sequence_2.gif
   
   

You have now created a sequence that can be used for the primary key of the evaluations table. If you click '+' to expand the Sequence tree, you can see new sequence. The equivalent SQL statement is:

CREATE SEQUENCE evaluations_seq INCREMENT BY 1 START WITH 1 ORDER;

In Example 3-7, you will create another sequence by entering the required information directly in the SQL Statement window.

CREATE SEQUENCE test_seq INCREMENT BY 5 START WITH 5 ORDER;

CREATE SEQUENCE succeeded.

Dropping a Sequence

To delete a sequence, you must use the SQL statement DROP SEQUENCE. To see how a sequence can be deleted in SQL Developer, you can use the test_seq sequence you created earlier. If the new sequence does not appear in the Connections hierarchy navigator, click the refresh icon.

To drop a sequence:

Follow these steps to drop a sequence.

1. In the Connections navigator, right-click the test_seq sequence.

   
   Description of the illustration sequence_3.gif
   
   

2. In the Drop window, click Apply.

   
   Description of the illustration sequence_4.gif
   
   

3. In the Confirmation window, click OK.

You have now deleted the sequence test_seq. The equivalent SQL statement follows:

DROP SEQUENCE "HR"."TEST_SEQ";