Laboratory Exercise on Tables, SQL Joins, and PHP Processing

Overview: This laboratory exercise is the third 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.

• The second lab considered SQL queries that extract data from several tables (e.g., 2 or more)

• This lab is the first of two exercises that combine joining tables within the context of PHP programming.

• The final lab in the series provides additional practice with joining tables, PHP programming, and Web applications.

Background

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

MySQL and PHP Processing

Previous labs have discussed several capabilities of MySQL and PHP.

• Labs Getting Started with MySQL and Changing SQL Tables: MySQL provides queries (e.g., SELECT, INSERT, UPDATE) to store, change, and retrieve database records.

• Labs Connecting, SELECT with PHP and Modifying a Database with PHP: PHP contains procedures to access database elements, and assign database records and elements to PHP variables.

• Labs Joining Two Tables and Joining Multiple Tables: MySQL provides the JOIN operator to integrate records from multiple tables.

Overall, these labs indicate how to work with one or more MySQL tables, and how to work with data elements within PHP. This lab observes that once a PHP program retrieves data from a database, the program can use those data, stored in variables, for further processing.

Processing Course Data

1. The Lab on Joining Two MySQL Tables presented a table of courses offered in computer science and mathematics at Willamette University in Spring 2019. This first problem considers only the courses table.

   1. Retrieve 200-level CS classes from the table, and count (and print) the number of such courses in the university catalog.
   2. Retrieve all MATH classes offered, and determine the sum of the total number of credits of these courses, as identified in the university catalog.
   3. Count the number of 400-level CS courses and the number of 400-level MATH courses in the university catalog. Which program has more courses in the catalog, and determine how many more?

2. Expanding on The Lab on Joining Two MySQL Tables, form a JOIN of the courseSchedule and courses tables.

   1. How many class sections are offered with start times 9:10?
   2. For the class sections starting at 9:10, what is the sum of the credits offered from these sections?
   3. How many class sections are taught by Professor Otto?

Grocery Store Orders

The first lab on joining tables described tables groceries (individual items available for sale) and groceryOrders (which of these items were desired in a customer order).

3. Form JOINs of these two tables within a PHP program to answer these questions:

   1. How many different items are requested for order 1?
   2. What is the total cost for order 2?
   3. How many gallons of whole milk were ordered (spanning all orders)?

4. If one adds an "ORDER BY groceryOrders.orderNumber" clause to a query, the returned records are sorted by the designated field. Thus, in this case, all records for order 1 come first, then for order 2, etc.

   Using a join of groceries and groceryOrders, print a table giving the order number and total cost for each order.

5. Consider the groceries, groceryOrders, storeGrocery, and stores tables. For order 3, which stores stock each requested item.

created 20 January 2019 revised 25 January 2019