Laboratory Exercise on Joins of Multiple SQL Tables

Overview: This laboratory exercise is the second of four labs that provides practice with processing that involves joining tables in a database.

• The first lab focused upon processing involving processing that draws data from two tables.

• This lab considers SQL queries that extract data from several tables (e.g., 2 or more).

• Two additional labs utilize SQL queries with JOINs within the context of PHP programming and the Web.

Background

As preparation for this lab, be sure you have read pages 201-207 in the textbook.

Joining Three (or more) Tables

The previous lab discussed the use of the JOIN operator to bring two tables, using the general framework:

  SELECT * FROM Table1 JOIN Table2 ON Table1.field=Table2.field

SQL allows three to be joined, by joining two and then the third. This may be accomplished in several ways:

• Join the first two and then join that combination to the third:

     SELECT * FROM Table1 JOIN Table2 ON Table1.fielda=Table2.fielda JOIN Table3 ON Table2.fieldb = Table3.fieldb
      

  Although not strictly necessary, sometimes parentheses clarify how the elements come together.

     SELECT * FROM (Table1 JOIN Table2 ON Table1.fielda=Table2.fielda) JOIN Table3 ON Table2.fieldb = Table3.fieldb
          

• Join the second and third and join that combination to the first. As above, parentheses are included for clarity.

     SELECT * FROM Table1 JOIN (Table2 JOIN Table3 ON Table2.fieldb = Table3.fieldb) ON Table1.fielda=Table2.field;
      

Notes on JOIN Operators

Examples here and in the previous lab have used both ON and WHERE clauses:

• ON clauses are used for connecting records by identifying common fields.

• WHERE clauses are used for restricting values within one or more tables.

The discussions throughout this course focus upon the INNER JOIN (or JOIN) operator that returns only records that have common fields that match. If a record in one table has no matches in the other, that record is not reported in the JOIN.

For those interested in further reading, SQL also provides some other operators, such as the following:

• LEFT JOIN includes all items in left table, whether or not it matches anything in the right table.

• RIGHT JOIN is analogous.

Faculty and Courses

1. Each of the following SQL statements combine information about faculty, courses, and the class schedule. Type each statement into MySQL and describe the outcome.

   select * from faculty JOIN courseSchedule ON faculty.facultyID=courseSchedule.facultyID JOIN courses ON courseSchedule.courseID=courses.courseID;
   
   select * from (faculty JOIN courseSchedule ON faculty.facultyID=courseSchedule.facultyID) JOIN courses ON courseSchedule.courseID=courses.courseID;
   
   select * from faculty JOIN (courseSchedule JOIN courses ON courseSchedule.courseID=courses.courseID) ON faculty.facultyID=courseSchedule.facultyID;
         

2. Choose one of these queries, and modify it to obtain only the faculty member's first and last name and the course program, number and title. (e.g., change SELECT * to indicate the needed fields).

3. Add a WHERE clause to one of the queries in step 1 to obtain only those courses from instructor Walker.

4. Revise one of the queries in step 1 to obtain the published course schedule for computer science for Spring, as found in the Registrar's course listings. For this problem, you need not achieve the same format as on the Registrar's page, and you may omit course enrollments/capacities, prerequisites, and section number. Also, the listing may include either the instructor's first initial or first name, as you wish.

Grocery Store Inventory

The previous lab utilized a grocery table that indicated various grocery items, including their category and price. Suppose the grocery store chain selling these items has several separate shops, perhaps:

1. Salem downtown
2. West Salem
3. Sunnyside
4. Keizer
5. Four Corners

Each grocery item is available in at least one of these shops, but not all items are available everywhere. Thus, the MySQL database contains two more tables:

• stores contains the following numbered list of stores and their general locations:

      +---------+------------------+----------------+
      | storeID | name             | location       |
      +---------+------------------+----------------+
      |       1 | Salem Center     | Downtown Salem |
      |       2 | Westside Market  | West Salem     |
      |       3 | Southside Market | Sunnyside      |
      |       4 | Northside Market | Keizer         |
      |       5 | Eastside Market  | Four Corners   |
      +---------+------------------+----------------+
      

• storeGrocery identifies which store contains with grocery item(s). The start of this table contains:

  +----------------+---------+--------+
  | groceryStoreID | storeID | itemID |
  +----------------+---------+--------+
  |              2 |       1 |      1 |
  |              3 |       2 |      2 |
  |              4 |       3 |      3 |
  |              5 |       4 |      3 |
  |              6 |       5 |      4 |
  |              7 |       1 |      5 |
  |              8 |       3 |      5 |
  |              9 |       5 |      5 |
  |             10 |       1 |      6 |
  |             11 |       3 |      6 |
  |             12 |       5 |      6 |
  |             13 |       1 |      7 |
  |             14 |       2 |      7 |
  |             15 |       3 |      7 |
  |             16 |       4 |      7 |
  |             17 |       5 |      7 |
  . . .
      

  Here, the first column is a row number in the table. The next columns specify the store number discussed above, and the id of the grocery item in the grocery table.

5. Create and run an SQL query that joins the storeGrocery table with the stores table, based on storeID, and joins that result with the groceries table, based on itemID and id.

6. Modify the previous SQL query to find the inventory for store 1.

7. Modify the SQL query in step 5 to determine which store(s) stock "Cauliflower Florets".

8. Modify the SQL query in step 5 to determine which stores stock some type of bakery goods (these have id numbers between 48 and 52, inclusive).
   (Note: The largest ID number in the current grocery database is 52, so bakery items involve any id >= 48.)

9. Modify the SQL query in step 5 to determine which store(s) stock some type of dairy products (these have id numbers between 34 and 47, inclusive).

created 22 February 2019 revised 23 February 2019