Lab #5 : MySQL SQL Joins
You begin these steps by running the cleanup_mysql.sql
and create_mysql_store_ri2.sql
scripts. A great starting point for this lab is to review the create_mysql_store.sql
script. The create_mysql_store_ri2.sql
script creates 10 tables, and the seed_mysql_store_ri2.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/mysql/lib/cleanup_mysql.sql \. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri2.sql \. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri2.sql TEE apply_mysql_lab5.txt ... insert code here ... NOTEE |
- [4 points] Write
INNER JOIN
queries that use theUSING
subclause and return the following results:
Instruction Details ↓
- Display the
MEMBER_ID
andCONTACT_ID
in theSELECT
clause from a join of theMEMBER
andCONTACT
tables. You should make the join with theUSING
subclause based on theMEMBER_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
andADDRESS_ID
in theSELECT
clause from a join of theCONTACT
andADDRESS
tables. You should make the join between the tables with theUSING
subclause based on theCONTACT_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
andSTREET_ADDRESS_ID
in theSELECT
clause from a join of theADDRESS
andSTREET_ADDRESS
tables. You should make the join between the tables with theUSING
subclause based on theADDRESS_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
andTELEPHONE_ID
in theSELECT
clause from a join of theCONTACT
andTELEPHONE
tables. You should make the join between the tables with theUSING
subclause based on theCONTACT_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 theON
subclause and return the following results:
Instruction Details ↓
- Display the
CONTACT_ID
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should make the join with theON
subclause based on theCREATED_BY
andSYSTEM_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
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should make the join with theON
subclause based on theLAST_UPDATED_BY
andSYSTEM_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 theON
subclause to perform a self-join on theSYSTEM_USER
table. The solution requires that you create three copies of the SYSTEM_USER table by using aliases likesu1
,su2
, andsu3
. 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
andCREATED_BY
columns from one row, and theSYSTEM_USER_ID
column from a row where it is also the primary key. You should make the join with theON
subclause based on theCREATED_BY
andSYSTEM_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 ID | Created By | +-----------+------------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +-----------+------------+ |
- Display the
SYSTEM_USER_ID
andLAST_UPDATED_BY
columns from one row, and theSYSTEM_USER_ID
column from a row where it is also the primary key. You should make the join with theON
subclause based on theLAST_UPDATED_BY
andSYSTEM_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 ID | Last Updated By | +-----------+-----------------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +-----------+-----------------+ |
- Display the
SYSTEM_USER_NAME
andSYSTEM_USER_ID
columns from the first copy of theSYSTEM_USER
table, then theSYSTEM_USER_NAME
as the “Created By” andSYSTEM_USER_ID
as the “Created By” columns from the second copy of theSYSTEM_USER
table, and finally theSYSTEM_USER_NAME
as the “Updated User” andLAST_UPDATED_BY
as the “Updated By” columns from the third copy of theSYSTEM_USER
table. You perform a three table join by joining the first copy of the table by using theCREATED_BY
column in the first copy of theSYSTEM_USER
table and theSYSTEM_USER_ID
column in the second copy of theSYSTEM_USER
table; and by joining the first result set with the first copy of the table by using theLAST_UPDATED_BY
column in the first copy of theSYSTEM_USER
table and theSYSTEM_USER_ID
column in the third copy of theSYSTEM_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 theRENTAL
table, theRENTAL_ID
andITEM_ID
from theRENTAL_ITEM
table, andITEM_ID
column from theITEM
table. You should make a join from theRENTAL
table to theRENTAL_ITEM
table, and then theITEM
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 |