Old Lab 5 at Database Tutorial

Old Lab 5

Lab #5: Oracle SQL Joins

You begin these steps by running the cleanup_oracle.sql and create_oracle_store.sql scripts. A great starting point for this lab is to review the create_oracle_store.sql script. The create_oracle_store.sql script creates 10 tables, and the seed_oracle_store.sql script inserts seeding data into the tables. You should use the apply_oracle_lab5.sql script provided in the downloaded instance:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lib/cleanup_oracle.sql
@/home/student/Data/cit225/oracle/lib/create_oracle_store.sql
@/home/student/Data/cit225/oracle/lib/seed_oracle_store.sql
 
SPOOL apply_oracle_lab5.txt
 
... insert code here ...
 
SPOOL OFF

[4 points] Write INNER JOIN queries that use the USING subclause and return the following results:

Instruction Details ↓

Display the MEMBER_ID and CONTACT_ID in the SELECT clause from a join of the MEMBER and CONTACT tables. You should make the join with the USING subclause based on the MEMBER_ID column, which is the primary and foreign key of the respective tables.

 MEMBER_ID CONTACT_ID
---------- ----------
      1001       1001
      1001       1002
      1002       1003
      1002       1004
      1003       1005
      1003       1006
      1003       1007
      1004       1008
      1005       1009
      1006       1010
      1007       1011
      1008       1012

Display the CONTACT_ID and ADDRESS_ID in the SELECT clause from a join of the CONTACT and ADDRESS tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respective tables.

CONTACT_ID ADDRESS_ID
---------- ----------
      1001       1001
      1002       1002
      1003       1003
      1004       1004
      1005       1005
      1006       1006
      1007       1007
      1008       1008
      1009       1009
      1010       1010
      1011       1011
      1012       1012

Display the ADDRESS_ID and STREET_ADDRESS_ID in the SELECT clause from a join of the ADDRESS and STREET_ADDRESS tables. You should make the join between the tables with the USING subclause based on the ADDRESS_ID column, which is the primary and foreign key of the respect tables.

ADDRESS_ID STREET_ADDRESS_ID
---------- -----------------
      1001              1001
      1002              1002
      1003              1003
      1004              1004
      1005              1005
      1006              1006
      1007              1007
      1008              1008
      1009              1009
      1010              1010
      1011              1011
      1012              1012

Display the CONTACT_ID and TELEPHONE_ID in the SELECT clause from a join of the CONTACT and TELEPHONE tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respect tables.

CONTACT_ID TELEPHONE_ID
---------- ------------
      1001         1001
      1002         1002
      1003         1003
      1004         1004
      1005         1005
      1006         1006
      1007         1007
      1008         1008
      1009         1009
      1010         1010
      1011         1011
      1012         1012

[2 points] Write INNER JOIN queries that use the ON subclause and return the following results:

Instruction Details ↓

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

[2 points] Write INNER JOIN queries that use the ON subclause to perform a self-join on the SYSTEM_USER table. The solution requires that you create three copies of the SYSTEM_USER table by using aliases like su1, su2, and su3. Please note that joining two tables is like matching two sets, while joining three table is like joining two tables into a temporary result set (or pseudo table) and then joining the third table to the temporary result set as if it were a table. It should return the following results:

Instruction Details ↓

Display the SYSTEM_USER_ID and CREATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID CREATED_BY SYSTEM_USER_ID
-------------- ---------- --------------
             1          1              1
             2          1              1
             3          1              1
             4          1              1

Display the SYSTEM_USER_ID and LAST_UPDATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID LAST_UPDATED_BY SYSTEM_USER_ID
-------------- --------------- --------------
             1               1              1
             2               1              1
             3               1              1
             4               1              1

Display the SYSTEM_USER_NAME and SYSTEM_USER_ID columns from the first copy of the SYSTEM_USER table, then the SYSTEM_USER_NAME as the “Created By” and SYSTEM_USER_ID as the “Created By” columns from the second copy of the SYSTEM_USER table, and finally the SYSTEM_USER_NAME as the “Updated User” and LAST_UPDATED_BY as the “Updated By” columns from the third copy of the SYSTEM_USER table. You perform a three table join by joining the first copy of the table by using the CREATED_BY column in the first copy of the SYSTEM_USER table and the SYSTEM_USER_ID column in the second copy of the SYSTEM_USER table; and by joining the first result set with the first copy of the table by using the LAST_UPDATED_BY column in the first copy of the SYSTEM_USER table and the SYSTEM_USER_ID column in the third copy of the SYSTEM_USER table. You should get the following output:

System User   System ID Created User Created By Updated User Updated By
------------ ---------- ------------ ---------- ------------ ----------
SYSADMIN	      1 SYSADMIN	      1 SYSADMIN	      1
DBA		      2 SYSADMIN	      1 SYSADMIN	      1
DBA		      3 SYSADMIN	      1 SYSADMIN	      1
DBA		      4 SYSADMIN	      1 SYSADMIN	      1

[2 points] Display the RENTAL_ID column from the RENTAL table, the RENTAL_ID and ITEM_ID from the RENTAL_ITEM table, and ITEM_ID column from the ITEM table. You should make a join from the RENTAL table to the RENTAL_ITEM table, and then the ITEM table. Join the tables based on their respective primary and foreign key values.

Instruction Details ↓

 RENTAL_ID  RENTAL_ID    ITEM_ID    ITEM_ID
---------- ---------- ---------- ----------
      1001       1001       1002       1002
      1001       1001       1004       1004
      1001       1001       1005       1005
      1002       1002       1016       1016
      1002       1002       1021       1021
      1003       1003       1019       1019
      1004       1004       1014       1014
      1005       1005       1007       1007
      1005       1005       1001       1001

After creating the foregoing result set, alter the RENTAL table and drop the NN_RENTAL_3 constraint.

Lab #5: Oracle SQL Joins

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lib/cleanup_oracle.sql
@/home/student/Data/cit225/oracle/lib/create_oracle_store.sql
@/home/student/Data/cit225/oracle/lib/seed_oracle_store.sql
 
SPOOL apply_oracle_lab5.txt
 
... insert code here ...
 
SPOOL OFF

[4 points] Write INNER JOIN queries that use the USING subclause and return the following results:

Instruction Details ↓

Display the MEMBER_ID and CONTACT_ID in the SELECT clause from a join of the MEMBER and CONTACT tables. You should make the join with the USING subclause based on the MEMBER_ID column, which is the primary and foreign key of the respective tables.

 MEMBER_ID CONTACT_ID
---------- ----------
      1001       1001
      1001       1002
      1002       1003
      1002       1004
      1003       1005
      1003       1006
      1003       1007
      1004       1008
      1005       1009
      1006       1010
      1007       1011
      1008       1012

Display the CONTACT_ID and ADDRESS_ID in the SELECT clause from a join of the CONTACT and ADDRESS tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respective tables.

CONTACT_ID ADDRESS_ID
---------- ----------
      1001       1001
      1002       1002
      1003       1003
      1004       1004
      1005       1005
      1006       1006
      1007       1007
      1008       1008
      1009       1009
      1010       1010
      1011       1011
      1012       1012

Display the ADDRESS_ID and STREET_ADDRESS_ID in the SELECT clause from a join of the ADDRESS and STREET_ADDRESS tables. You should make the join between the tables with the USING subclause based on the ADDRESS_ID column, which is the primary and foreign key of the respect tables.

ADDRESS_ID STREET_ADDRESS_ID
---------- -----------------
      1001              1001
      1002              1002
      1003              1003
      1004              1004
      1005              1005
      1006              1006
      1007              1007
      1008              1008
      1009              1009
      1010              1010
      1011              1011
      1012              1012

Display the CONTACT_ID and TELEPHONE_ID in the SELECT clause from a join of the CONTACT and TELEPHONE tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respect tables.

CONTACT_ID TELEPHONE_ID
---------- ------------
      1001         1001
      1002         1002
      1003         1003
      1004         1004
      1005         1005
      1006         1006
      1007         1007
      1008         1008
      1009         1009
      1010         1010
      1011         1011
      1012         1012

[2 points] Write INNER JOIN queries that use the ON subclause and return the following results:

Instruction Details ↓

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

[2 points] Write INNER JOIN queries that use the ON subclause to perform a self-join on the SYSTEM_USER table. The solution requires that you create three copies of the SYSTEM_USER table by using aliases like su1, su2, and su3. Please note that joining two tables is like matching two sets, while joining three table is like joining two tables into a temporary result set (or pseudo table) and then joining the third table to the temporary result set as if it were a table. It should return the following results:

Instruction Details ↓

Display the SYSTEM_USER_ID and CREATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID CREATED_BY SYSTEM_USER_ID
-------------- ---------- --------------
             1          1              1
             2          1              1
             3          1              1
             4          1              1

Display the SYSTEM_USER_ID and LAST_UPDATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID LAST_UPDATED_BY SYSTEM_USER_ID
-------------- --------------- --------------
             1               1              1
             2               1              1
             3               1              1
             4               1              1

Display the SYSTEM_USER_NAME and SYSTEM_USER_ID columns from the first copy of the SYSTEM_USER table, then the SYSTEM_USER_NAME as the “Created By” and SYSTEM_USER_ID as the “Created By” columns from the second copy of the SYSTEM_USER table, and finally the SYSTEM_USER_NAME as the “Updated User” and LAST_UPDATED_BY as the “Updated By” columns from the third copy of the SYSTEM_USER table. You perform a three table join by joining the first copy of the table by using the CREATED_BY column in the first copy of the SYSTEM_USER table and the SYSTEM_USER_ID column in the second copy of the SYSTEM_USER table; and by joining the first result set with the first copy of the table by using the LAST_UPDATED_BY column in the first copy of the SYSTEM_USER table and the SYSTEM_USER_ID column in the third copy of the SYSTEM_USER table. You should get the following output:

System User   System ID Created User Created By Updated User Updated By
------------ ---------- ------------ ---------- ------------ ----------
SYSADMIN	      1 SYSADMIN	      1 SYSADMIN	      1
DBA		      2 SYSADMIN	      1 SYSADMIN	      1
DBA		      3 SYSADMIN	      1 SYSADMIN	      1
DBA		      4 SYSADMIN	      1 SYSADMIN	      1

[2 points] Display the RENTAL_ID column from the RENTAL table, the RENTAL_ID and ITEM_ID from the RENTAL_ITEM table, and ITEM_ID column from the ITEM table. You should make a join from the RENTAL table to the RENTAL_ITEM table, and then the ITEM table. Join the tables based on their respective primary and foreign key values.

Instruction Details ↓

 RENTAL_ID  RENTAL_ID    ITEM_ID    ITEM_ID
---------- ---------- ---------- ----------
      1001       1001       1002       1002
      1001       1001       1004       1004
      1001       1001       1005       1005
      1002       1002       1016       1016
      1002       1002       1021       1021
      1003       1003       1019       1019
      1004       1004       1014       1014
      1005       1005       1007       1007
      1005       1005       1001       1001

After creating the foregoing result set, alter the RENTAL table and drop the NN_RENTAL_3 constraint.

Written by michaelmclaughlin

May 4th, 2018 at 4:52 pm

Posted in

Database Tutorial

Old Lab 5

My Technical Blog

Tutorial Pages

Blogroll