Database Tutorial

Course Tutorial Site

Site Admin

CIT225: Lab 3 Instructions

without comments

Lab #3: Oracle Assignment

Objectives

The lab is designed to teach you how to work with the INSERT and UPDATE statements, disable and enable database constraints, and use scalar subqueries. The anonymous block PL/SQL program should achieve these objectives:

  • Learn how to write a positional and named INSERT statement.
  • Learn how to disable and enable constraints.
  • Learn how to use the .NEXTVAL pseudo column in an INSERT statement.
  • Learn how to write a scalar query.
  • Learn how to use a subquery in the SET clause of an UPDATE statement.
  • Learn how to write an INSERT statement with embedded scalar subqueries.

Business Scenario

Building data-centric application software often requires that you pre-seed data in certain database tables. The process of pre-seeding involves inserting rows of data. Sometimes the Entity Relationship Diagram (ERD) can create conflicts, which require you to temporarily disable constraints to insert data.

The lab is designed to teach you how to write INSERT statements. The INSERT statements use:

  • Literal values
  • Pseudo columns for sequence values
  • Subqueries that retrieve surrogate primary key values from tables by using the natural keys of the tables

You will disable foreign key constraints and remove not null constraints to pre-seed some of the tables with data. After inserting the new data, you will add re-enable foreign key constraints and re-add not null constraints. You should use the preseed_oracle_store.sql script as a reference model for SQL syntax.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

You should check the CREATE statement of a table to discover the foreign key constraints. Alternatively, you can use the following diagnostic script to find the foreign key constraints of the SYSTEM_USER_LAB table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'SYSTEM_USER_LAB'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It should print:

Constraint Name:                           References:
 Table.Column                               Table.Column
------------------------------------------ ----------------------------------------
FK_SYSTEM_USER_LAB_1                       REFERENCES
(SYSTEM_USER_LAB.CREATED_BY)               (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SYSTEM_USER_LAB_2                       REFERENCES
(SYSTEM_USER_LAB.LAST_UPDATED_BY)          (SYSTEM_USER_LAB.SYSTEM_USER_LAB_ID)
 
FK_SYSTEM_USER_LAB_3                       REFERENCES
(SYSTEM_USER_LAB.SYSTEM_USER_GROUP_ID)     (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)
 
FK_SYSTEM_USER_LAB_4                       REFERENCES
(SYSTEM_USER_LAB.SYSTEM_USER_TYPE)         (COMMON_LOOKUP_LAB.COMMON_LOOKUP_LAB_ID)

You should check the CREATE statement of a table to discover the not null constraints. Alternatively, you can use the following diagnostic script to find and list the not null constraints of the SYSTEM_USER table:

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN constraint_name   FORMAT A22
COLUMN search_condition  FORMAT A36
COLUMN constraint_type   FORMAT A1
SELECT   uc.constraint_name
,        uc.search_condition
,        uc.constraint_type
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.table_name = ucc.table_name
AND      uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = UPPER('system_user_lab')
AND      uc.constraint_type = UPPER('c')
ORDER BY uc.constraint_name;

It should print:

CONSTRAINT_NAME        SEARCH_CONDITION                     C
---------------------- ------------------------------------ -
NN_SYSTEM_USER_LAB_1   "SYSTEM_USER_NAME" IS NOT NULL       C
NN_SYSTEM_USER_LAB_2   "SYSTEM_USER_GROUP_ID" IS NOT NULL   C
NN_SYSTEM_USER_LAB_3   "SYSTEM_USER_TYPE" IS NOT NULL       C
NN_SYSTEM_USER_LAB_4   "CREATED_BY" IS NOT NULL             C
NN_SYSTEM_USER_LAB_5   "CREATION_DATE" IS NOT NULL          C
NN_SYSTEM_USER_LAB_6   "LAST_UPDATED_BY" IS NOT NULL        C
NN_SYSTEM_USER_LAB_7   "LAST_UPDATE_DATE" IS NOT NULL       C

You can disable a constraint to work around its limitation with the following syntax: 

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

You can enable a constraint to work around its limitation with the following syntax: 

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

You can remove a not null constraint by redefining the column as null, like this: 

ALTER TABLE table_name
MODIFY (column_name  VARCHAR2(10) NULL);

You can enable a not null constraint by redefining the column as null, like this: 

ALTER TABLE table_name
MODIFY (column_name  VARCHAR2(10) CONSTRAINT constraint_name NOT NULL);

The lab has twelve parts. The test cases should insert two rows in the SYSTEM_USER table and twenty rows in the COMMON_LOOKUP table.

Lab Description

[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.

The lab requires that you run four preparation scripts. The cleanup_oracle.sql removes all but the target environment tables and views from the instance. The create_oracle_store2.sql script creates all the tables and sequences. The apply_oracle_lab2.sql script inserts pre-seeded data for subsequent labs. The apply_oracle_lab2.sql should create tables like the create_oracle_store2.sql script with an _LAB suffix added to the table names.

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@/home/student/Data/cit225/oracle/lib2/preseed_oracle_store.sql
 
-- insert calls to other SQL script files here ...
 
SPOOL apply_oracle_lab3.txt
 
-- insert your SQL statements here ... 
-- start with the preseed_oracle_store.sql script
-- edit the preseed_oracle_store.sql script to work with the _lab tables. 
 
SPOOL OFF

Click the Lab Instructions link to display the lab steps:

Lab Instructions →

You should complete the following twelve steps:

  1. You need to disable the following foreign key constraints from the SYSTEM_USER_LAB table:

    • Disable the fk_system_user_3 foreign key constraint.
    • Disable the fk_system_user_4 foreign key constraint.

    After you run the two UPDATE statements, you should query the results from the SYSTEM_USER_LAB table, like this:

    COL table_name       FORMAT A18  HEADING "Table Name"
COL constraint_name  FORMAT A28  HEADING "Constraint Name"
COL constraint_type  FORMAT A12  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show:

    Table Name     Constraint Name                  Constraint Type Status
-------------- -------------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_2         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_3         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     DISABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     DISABLED

  2. You need to remove the not null constraints from the following columns of the SYSTEM_USER table, which you can do with the MODIFY subclause:

    • Remove not null constraint from the system_user_group_id column.
    • Remove not null constraint from the system_user_type column.

    COL table_name       FORMAT A18  HEADING "Table Name"
COL constraint_name  FORMAT A28  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names, like:

    Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     DISABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     DISABLED

  3. After running your apply_oracle_lab2.sql script, you will insert one row into the SYSTEM_USER_LAB table. This single row will use a literal values as qualified below.

    Table Name: SYSTEM_USER_LAB
    Column Value #1
    SYSTEM_USER_LAB_ID 1
    SYSTEM_USER_NAME SYSADMIN
    SYSTEM_USER_GROUP_ID null
    SYSTEM_USER_TYPE null
    FIRST_NAME  
    MIDDLE_NAME  
    LAST_NAME  
    CREATED_BY 1
    CREATION_DATE SYSDATE
    LAST_UPDATED_BY 1
    LAST_UPDATE_DATE SYSDATE

  4. You need to disable the following foreign key constraints from the COMMON_LOOKUP table:

    • Disable the fk_clookup_1 foreign key constraint.
    • Disable the fk_clookup_2 foreign key constraint.

    COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    It should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names, like:

    Table Name         Constraint Name          Constraint Type Status
------------------ ------------------------ --------------- --------
COMMON_LOOKUP_LAB  PK_CLOOKUP_LAB_1         PRIMARY KEY     ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_1         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_2         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_3         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_4         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_5         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_6         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_7         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_1         FOREIGN KEY     DISABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_2         FOREIGN KEY     DISABLED

  5. Insert 20 rows into the COMMON_LOOKUP_LAB table. They should contain the following data. Rows 6 through 20 will use the COMMON_LOOKUP_LAB_S1 sequence value.

    Table Name: COMMON_LOOKUP_LAB
    ID # Context Type Meaning
    1 SYSTEM_USER_LAB SYSTEM_ADMIN System Administrator
    2 SYSTEM_USER_LAB DBA Database Administrator
    3 SYSTEM_USER_LAB SYSTEM_GROUP Database Administrator
    4 SYSTEM_USER_LAB COST_CENTER Database Administrator
    5 SYSTEM_USER_LAB INDIVIDUAL Database Administrator
    1001 CONTACT_LAB EMPLOYEE Employee
    1002 CONTACT_LAB CUSTOMER Customer
    1003 MEMBER_LAB INDIVIDUAL Individual Membership
    1004 MEMBER_LAB GROUP Group Membership
    1005 MEMBER_LAB DISCOVER_CARD Discover Card
    1006 MEMBER_LAB MASTER_CARD Master Card
    1007 MEMBER_LAB VISA_CARD VISA Card
    1008 MULTIPLE HOME Home
    1009 MULTIPLE WORK Work
    1010 ITEM_LAB DVD_FULL_SCREEN DVD: Full Screen
    1011 ITEM_LAB DVD_WIDE_SCREEN DVD: Wide Screen
    1012 ITEM_LAB NINTENDO_GAMECUBE Nintendo Gamecube
    1013 ITEM_LAB PLAYSTATION2 PlayStation2
    1014 ITEM_LAB XBOX XBox
    1015 ITEM_LAB BLU-RAY Blu-ray

  6. Query the result of inserting 20 rows in the COMMON_LOOKUP_LAB table with the following query: 

    1
2
3
4
5
6
7

    COL common_lookup_id       FORMAT  9999
COL common_lookup_context  FORMAT A22
COL common_lookup_type     FORMAT A18
SELECT   common_lookup_lab_id
,        common_lookup_context
,        common_lookup_type
FROM     common_lookup_lab;

    It should print:

    COMMON_LOOKUP_ID COMMON_LOOKUP_CONTEXT  COMMON_LOOKUP_TYPE
---------------- ---------------------- ------------------
               1 SYSTEM_USER_LAB        SYSTEM_ADMIN
               2 SYSTEM_USER_LAB        DBA
               3 SYSTEM_USER_LAB        SYSTEM_GROUP
               4 SYSTEM_USER_LAB        COST_CENTER
               5 SYSTEM_USER_LAB        INDIVIDUAL
            1001 CONTACT_LAB            EMPLOYEE
            1002 CONTACT_LAB            CUSTOMER
            1003 MEMBER_LAB             INDIVIDUAL
            1004 MEMBER_LAB             GROUP
            1005 MEMBER_LAB             DISCOVER_CARD
            1006 MEMBER_LAB             MASTER_CARD
            1007 MEMBER_LAB             VISA_CARD
            1008 MULTIPLE               HOME
            1009 MULTIPLE               WORK
            1010 ITEM_LAB               DVD_FULL_SCREEN
            1011 ITEM_LAB               DVD_WIDE_SCREEN
            1012 ITEM_LAB               NINTENDO_GAMECUBE
            1013 ITEM_LAB               PLAYSTATION2
            1014 ITEM_LAB               XBOX
            1015 ITEM_LAB               BLU-RAY

  7. Write two queries against the COMMON_LOOKUP_LAB table:

    • One query should return the common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_GROUP'. This query should return 3. 

      COMMON_LOOKUP_ID
----------------
	       3

    • One query should return the common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_ADMIN'. This query should return 1. 

      COMMON_LOOKUP_ID
----------------
	       1

  8. Use the two queries from the previous step in UPDATE statements as subqueries:

    • One query should assign the return value of a subquery to the system_user_type column. The subquery return common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_GROUP'.

      After you run the first UPDATE statement, you can query the result of the UPDATE statement with the following query:

      SET NULL '<Null>'
COL system_user_lab_id    FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  9999
SELECT   system_user_lab_id
,        system_user_name
,        system_user_group_id
,        system_user_type
FROM     system_user_lab
WHERE    system_user_id = 1;

      It should show:

                          System
 System System        User  System
   User User         Group    User
   ID # Name          ID #    Type
------- ---------- ------- -------
      1 SYSADMIN         1  <Null>
    • One query should assign the return value of a subquery to the system_user_type column. The subquery should return the common_lookup_id column value from the COMMON_LOOKUP_LAB table where the common_lookup_context column value equals an uppercase string literal of 'SYSTEM_USER' and common_lookup_type column value equals an uppercase string literal of 'SYSTEM_ADMIN'.

      After you run the second UPDATE statement, you can query the result of the UPDATE statement with the following query:

      SET NULL '<Null>'
COL system_user_lab_id    FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  9999
SELECT   system_user_lab_id
,        system_user_name
,        system_user_group_id
,        system_user_type
FROM     system_user_lab
WHERE    system_user_id = 1;

      It should show:

                          System
 System System        User  System
   User User         Group    User
   ID # Name          ID #    Type
------- ---------- ------- -------
      1 SYSADMIN	 1	 3

  9. You need to enable the fk_system_user_lab_1 and fk_system_user_lab_2 foreign key constraints for the SYSTEM_USER_LAB table; and enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key constraints for the COMMON_LOOKUP_LAB table.

    1. You need to enable the fk_system_user_lab_1 and fk_system_user_lab_2 foreign key foreign key constraints for the SYSTEM_USER_LAB table:

      • Enable the fk_system_user_lab_1 foreign key constraint.
      • Enable the fk_system_user_lab_2 foreign key constraint.

      COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'SYSTEM_USER_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

      It still should show a gap of not null constraints between nn_system_user_lab_1 and nn_system_user_lab4 constraint names. However, you also should show that you enabled the fk_system_user_lab_3 and fk_system_user_lab_4 foreign keys.

      You should get results like these:

      Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     ENABLED

    2. You need to enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key foreign key constraints for the COMMON_LOOKUP_LAB table:

      • Enable the fk_clookup_lab_1 foreign key constraint.
      • Enable the fk_clookup_lab_2 foreign key constraint.

      COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

      There should no longer be a gap of not null constraint names. You should see nn_clookup_lab_2 and nn_clookup_lab_3 not null constraints.

      You should get results like these:

      Table Name         Constraint Name          Constraint Type Status
------------------ ------------------------ --------------- --------
COMMON_LOOKUP_LAB  PK_CLOOKUP_LAB_1         PRIMARY KEY     ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_1         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_2         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_3         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_4         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_5         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_6         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  NN_CLOOKUP_LAB_7         NOT NULL        ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_1         FOREIGN KEY     ENABLED
COMMON_LOOKUP_LAB  FK_CLOOKUP_LAB_2         FOREIGN KEY     ENABLED

  10. You need to enable the fk_clookup_lab_1 and fk_clookup_lab_2 foreign key foreign key constraints for the COMMON_LOOKUP_LAB table:

    • Enable the fk_clookup_lab_1 foreign key constraint.
    • Enable the fk_clookup_lab_2 foreign key constraint.

    COL table_name       FORMAT A14  HEADING "Table Name"
COL constraint_name  FORMAT A24  HEADING "Constraint Name"
COL constraint_type  FORMAT A15  HEADING "Constraint Type"
COL status           FORMAT A8   HEADING "Status"
SELECT   table_name
,        constraint_name
,        CASE
           WHEN constraint_type = 'R'        THEN 'FOREIGN KEY'
           WHEN constraint_type = 'P'        THEN 'PRIMARY KEY'
           WHEN constraint_type = 'U'        THEN 'UNIQUE'
           WHEN constraint_type = 'C' AND  REGEXP_LIKE(constraint_name,'^.*nn.*$','i')
           THEN 'NOT NULL'
           ELSE 'CHECK'
         END constraint_type
,        status
FROM     user_constraints
WHERE    table_name = 'COMMON_LOOKUP_LAB'
ORDER BY CASE
           WHEN constraint_type = 'PRIMARY KEY' THEN 1
           WHEN constraint_type = 'NOT NULL'    THEN 2
           WHEN constraint_type = 'CHECK'       THEN 3
           WHEN constraint_type = 'UNIQUE'      THEN 4
           WHEN constraint_type = 'FOREIGN KEY' THEN 5
         END
,        constraint_name;

    There should no longer be a gap in the not null constraints of the SYSTEM_USER_LAB table. It should display the following results:

    You should get results like these:

     
Table Name         Constraint Name              Constraint Type Status
------------------ ---------------------------- --------------- --------
SYSTEM_USER_LAB    PK_SYSTEM_USER_LAB_1         PRIMARY KEY     ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_1         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_2         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_3         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_4         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_5         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_6         NOT NULL        ENABLED
SYSTEM_USER_LAB    NN_SYSTEM_USER_LAB_7         NOT NULL        ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_1         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_2         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_3         FOREIGN KEY     ENABLED
SYSTEM_USER_LAB    FK_SYSTEM_USER_LAB_4         FOREIGN KEY     ENABLED

  11. You should write two INSERT statements that use the system_user_lab_s1 sequence and four subqueries. The subqueries should do the following:

    1. One subquery should return the common_lookup_lab_id column value from the common_lookup_lab table where the common_lookup_context value equals an uppercase 'SYSTEM_USER' string literal and the common_lookup_type value equals an uppercase 'DBA' string literal.
    2. One subquery should return the common_lookup_lab_id column value from the common_lookup_lab table where the common_lookup_context value equals an uppercase 'SYSTEM_USER' string literal and the common_lookup_type value equals an uppercase 'SYSTEM_GROUP' string literal.
    3. One subquery should return the system_user_lab_id column value from the system_user_lab table where the system_user_name value equals an uppercase 'SYSADMIN' string literal.
    4. One subquery should return the system_user_lab_id column value from the system_user_lab table where the system_user_name value equals an uppercase 'SYSADMIN' string literal.

    Table Name: SYSTEM_USER_LAB
    Column Value #1 Value #1
    SYSTEM_USER_LAB_ID 1001 1002
    SYSTEM_USER_NAME DBA1 DBA2
    COMMON_LOOKUP_CONTEXT SYSTEM_USER SYSTEM_USER
    COMMON_LOOKUP_TYPE SYSTEM_GROUP SYSTEM_GROUP
    FIRST_NAME Phineas Phineas
    MIDDLE_NAME Phineas  
    LAST_NAME Phineas Fogg
    CREATED_BY SYSADMIN SYSADMIN
    CREATION_DATE SYSDATE SYSDATE
    LAST_UPDATED_BY SYSADMIN SYSADMIN
    LAST_UPDATE_DATE SYSDATE SYSDATE

  12. You should use the following query to validate the three inserts into the SYSTEM_USER_LAB table: 

    SET NULL '<Null>'
COL system_user_id        FORMAT  9999
COL system_user_group_id  FORMAT  9999
COL system_user_type      FORMAT  9999
COL system_user_name      FORMAT  A10
COL full_user_name        FORMAT  A30
SELECT   system_user_id
,        system_user_group_id
,        system_user_type
,        system_user_name
,        CASE
           WHEN last_name IS NOT NULL THEN
             last_name || ', ' || first_name || ' ' || middle_name
         END AS full_user_name
FROM     system_user;

    It should print the following:

           System
System   User System System
  User  Group   User User
  ID #   ID #   Type Name       FULL_USER_NAME
------ ------ ------ ---------- ------------------------------
     1      1      3 SYSADMIN   <Null>
  1001      2      3 DBA1       Barnum, Phineas Taylor
  1002      2      3 DBA2       Fogg, Phineas

Written by michaelmclaughlin

January 22nd, 2018 at 1:08 am

Posted in