Database Tutorial

Course Tutorial Site

Site Admin

Old Lab 5

without comments

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

  1. [4 points] Write INNER JOIN queries that use the USING subclause and return the following results:
  1. [2 points] Write INNER JOIN queries that use the ON subclause and return the following results:
  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:

  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.

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

  1. [4 points] Write INNER JOIN queries that use the USING subclause and return the following results:
  1. [2 points] Write INNER JOIN queries that use the ON subclause and return the following results:
  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:

  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.

Written by michaelmclaughlin

May 4th, 2018 at 4:52 pm

Posted in