Lab 13 Exercises

1. Create a sequence to be used with the DEPARTMENT table’s primary key column. The sequence should start at 60 and have a maximum value of 200. Have your sequence increment by ten numbers. Name the sequence DEPT_ID_SEQ.
2. Write a script to display the following information about your sequences: sequence name, maximum value, increment size, and last number. Name the script p13q2.sql. Execute your script.

   SEQUENCE_NAME MAX_VALUE INCREMENT_BY LAST_NUMBER
   ------------- --------- ------------ ------------
   CUSTID        1.000E+27            1          109
   DEPT_ID_SEQ         200            1           60
   ORDID         1.000E+27            1          622
   PRODID        1.000E+27            1       200381

3. Write an interactive script to insert a row into the DEPARTMENT table. Name your script p13q3.sql. Be sure to use the sequence that you created for the ID column. Create a customized prompt to enter the department name. Execute your script. Add two departments named Education and Administration. Confirm your additions.
4. Create a non-unique index on the FOREIGN KEY column in the EMPLOYEE table.
5. Display the indexes and uniqueness that exist in the data dictionary for the EMPLOYEE table. Save the statement into a script named p13q5.sql.

   INDEX_NAME           TABLE_NAME   UNIQUENES
   -------------------- ------------ ---------
   EMPLOYEE_DEPT_ID_IDX EMPLOYEE     NONUNIQUE
   EMPLOYEE_ID_PK       EMPLOYEE     UNIQUE

6. Create a PRIMARY KEY constraint on the DEPARTMENT table. Confirm the constraint in the data dictionary by executing p11_q3.sql. Modify and Confirm the unique index in the data dictionary by executing p13q5.sql.

   CONSTRAINT_NAME      C
   -------------------- --
   DEPARTMENT_ID_PK     P
   EMPLOYEE_ID_PK       P
   EMPLOYEE_DEPT_ID_FK  R
   
   INDEX_NAME              TABLE_NAME   UNIQUENES
   ---------------------   ------------ ---------
   DEPARTMENT_ID_PK        DEPARTMENT   UNIQUE
   EMPLOYEE_DEPT_ID_IDX    EMPLOYEE     NONUNIQUE
   EMPLOYEE_ID_PK          EMPLOYEE     UNIQUE