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 (available in the maclochlainn/lib2/preseed folder on Github.com) 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.
This lab deploys an imperfect attempt at a core database design pattern, which is known as the lookup pattern. The idea requires that you store commonly used terms for drop down or popup lists that let you choose values, like Yes
or No
.
The following articles cover key aspects of INSERT
statements and how you can write a scalar subquery to lookup surrogate key values with the natural key values. You will also learn how to write INSERT
statements that use named notation. Named notation is superior to positional notation because table designs may change over time and lead to positional notation INSERT
statements putting data where it doesn’t belong.
The data modeling concepts are taken from Chapter 3 of MySQL Workbench: Data Modeling and Development, the common lookup design pattern is found on ToadWorld.com, and the common lookup table discussion are found on technical blog:
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; |
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) |
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; |
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 |
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; |
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; |
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); |
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); |
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/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 |
-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lab2/apply_oracle_lab2.sql
@/home/student/Data/cit225/oracle/lib2/preseed/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:
- You need to disable the following foreign key constraints from the
SYSTEM_USER_LAB
table:
- Disable the
fk_system_user_lab_3
foreign key constraint.
- Disable the
fk_system_user_lab_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; |
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 |
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
- 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; |
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 |
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
- 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 |
- You need to disable the following foreign key constraints from the
COMMON_LOOKUP
table:
- Disable the fk_clookup_lab_1 foreign key constraint.
- Disable 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; |
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 |
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
- 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 |
- 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; |
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 |
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
- Write two queries against the
COMMON_LOOKUP_LAB
table:
- One query should return the
common_lookup_lab_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_LAB_ID
--------------------
3 |
COMMON_LOOKUP_LAB_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_LAB_ID
--------------------
1 |
COMMON_LOOKUP_LAB_ID
--------------------
1
- 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; |
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> |
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; |
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 |
System
System System User System
User User Group User
ID # Name ID # Type
------- ---------- ------- -------
1 SYSADMIN 1 3
- 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.
- 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; |
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 |
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
- 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; |
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 |
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
- 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; |
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 |
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
- You should write two
INSERT
statements that use the system_user_lab_s1
sequence and four subqueries. The subqueries should do the following:
- 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.
- 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.
- 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.
- 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 |
- 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; |
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 |
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
Test Case
Click the Test Case Instructions link to open the test case instructions inside the current webpage.
Test Case Instructions →
After you write the apply_oracle_lab3.sql
script, you should navigate to the /home/student/Data/cit225/oracle/lab3
directory. You call the sqlplus
utility from the same directory and then you run the apply_oracle_lab3.sql
script with the following syntax:
You should submit your apply_oracle_lab3.sql
script file and apply_oracle_lab3.txt
log file for a grade.
Leave a Reply