revised 23 February 2019
| CS 199 | Willamette University | Spring, 2019 |
|
Programming in PHP, Databases with MySQL, and Web Applications |
||
Overview: This laboratory exercise is the first in a four-lab series that provides practice with processing that involves joining tables in a database.
This lab focuses upon processing involving processing that draws data from two tables.
The next 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.
As preparation for this lab, be sure you have read pages 193-201 in the textbook.
The lab on changing MySQL tables presented a table of courses that began:
+----------+---------+--------+----------------------------------------------------------------+---------+---------+ | courseID | program | number | title | credits | writing | +----------+---------+--------+----------------------------------------------------------------+---------+---------+ | 1 | CS | 141 | Introduction to Programming | 1.00 | | | 2 | CS | 199 | Programming in PHP. Databases with MySQL, and Web Applications | 1.00 | | | 3 | CS | 241 | Data Structures | 1.00 | | | 4 | CS | 343 | Analysis of Algorithms | 1.00 | | | 5 | CS | 451 | Topics in Computer Science | 1.00 |
This table provides information from the catalog on some computer science and mathematics courses offered in Spring 2019. As this comes from the catalog, these data elements are likely to change slowly. Most course numbers, titles, and credits will be the same from one semester to the next.
In contrast, the following listing shows the first part of a courseSchedule table for the specific sections of courses in computer science and mathematics offered in Spring 2019. This table represents short-term information (e.g., sections, instructors, times, and rooms) that will be different each semester. The first part of this table follows:
+------------------+----------+-----------+------+-----------+---------+--------------+ | courseScheduleID | courseID | facultyID | day | startTime | endTime | room | +------------------+----------+-----------+------+-----------+---------+--------------+ | 1 | 1 | 2 | MWF | 1:50 | 4:00 | FORD 202 | | 2 | 2 | 4 | MWF | 8:00 | 10:10 | FORD 202 | | 3 | 3 | 3 | MWF | 10:20 | 12:30 | FORD 202 | | 4 | 4 | 1 | TR | 8:00 | 11:10 | FORD 204/202 | | 5 | 5 | 4 | TR | 12:50 | 4:00 | FORD 202 | | 6 | 6 | 3 | MWF | 1:50 | 4:00 | FORD 204/224 | | 7 | 7 | 2 | MWF | 12:40 | 1:40 | FORD 201 |
By separating course information into these two tables, long-term information can be used semester-after-semester without much editing, whereas details for a specific semester can be edited without changing the long-term data.
A JOIN operation allows data from two tables to be brought together. In this case, we would like a table that shows scheduling information as well as program names, course numbers and titles and credits for each offering. The key here is that the courseID in the courses table should match the corresponding field in the courseSchedule table.
SQL makes such combining of tables reasonably straight forward with a SELECT statement that includes a JOIN:
SELECT * FROM courseSchedule JOIN courses ON courseSchedule.courseID = courses.courseID;
Here the SELECT statement draws from two tables, and the WHERE clause requires that the courseID from the courseSchedule table matches the corresponding field in the courses table. Executing this query yields:
+------------------+----------+-----------+------+-----------+---------+--------------+----------+---------+--------+----------------------------------------------------------------+---------+---------+ | courseScheduleID | courseID | facultyID | day | startTime | endTime | room | courseID | program | number | title | credits | writing | +------------------+----------+-----------+------+-----------+---------+--------------+----------+---------+--------+----------------------------------------------------------------+---------+---------+ | 1 | 1 | 2 | MWF | 1:50 | 4:00 | FORD 202 | 1 | CS | 141 | Introduction to Programming | 1.00 | | | 2 | 2 | 4 | MWF | 8:00 | 10:10 | FORD 202 | 2 | CS | 199 | Programming in PHP. Databases with MySQL, and Web Applications | 1.00 | | | 3 | 3 | 3 | MWF | 10:20 | 12:30 | FORD 202 | 3 | CS | 241 | Data Structures | 1.00 | | | 4 | 4 | 1 | TR | 8:00 | 11:10 | FORD 204/202 | 4 | CS | 343 | Analysis of Algorithms | 1.00 | | | 5 | 5 | 4 | TR | 12:50 | 4:00 | FORD 202 | 5 | CS | 451 | Topics in Computer Science | 1.00 | | | 6 | 6 | 3 | MWF | 1:50 | 4:00 | FORD 204/224 | 6 | CS | 465 | Language, Logic and Computation | 1.00 | | . . .
As with any SELECT statement, we can add conditions to restrict which records to combine. Thus, the statement
SELECT * FROM courseSchedule JOIN courses ON courseSchedule.courseID = courses.courseID and courses.program="CS";
retrieves just the course information for computer science offerings:
+------------------+----------+-----------+------+-----------+---------+--------------+----------+---------+--------+----------------------------------------------------------------+---------+---------+ | courseScheduleID | courseID | facultyID | day | startTime | endTime | room | courseID | program | number | title | credits | writing | +------------------+----------+-----------+------+-----------+---------+--------------+----------+---------+--------+----------------------------------------------------------------+---------+---------+ | 1 | 1 | 2 | MWF | 1:50 | 4:00 | FORD 202 | 1 | CS | 141 | Introduction to Programming | 1.00 | | | 2 | 2 | 4 | MWF | 8:00 | 10:10 | FORD 202 | 2 | CS | 199 | Programming in PHP. Databases with MySQL, and Web Applications | 1.00 | | | 3 | 3 | 3 | MWF | 10:20 | 12:30 | FORD 202 | 3 | CS | 241 | Data Structures | 1.00 | | | 4 | 4 | 1 | TR | 8:00 | 11:10 | FORD 204/202 | 4 | CS | 343 | Analysis of Algorithms | 1.00 | | | 5 | 5 | 4 | TR | 12:50 | 4:00 | FORD 202 | 5 | CS | 451 | Topics in Computer Science | 1.00 | | | 6 | 6 | 3 | MWF | 1:50 | 4:00 | FORD 204/224 | 6 | CS | 465 | Language, Logic and Computation | 1.00 | | | 7 | 7 | 2 | MWF | 12:40 | 1:40 | FORD 201 | 7 | CS | 496 | Senior Seminar in Computer Science II | 0.50 | W | +-------------+----------+-----------+------+-----------+---------+--------------+----------+---------+--------+----------------------------------------------------------------+---------+---------+
As described above, the JOIN operation (sometmes called an INNER JOIN) selects those records from one table that have a corresponding field in the second table. For many applications, this is precisely the pairing desired in this example (and it is the only type of JOIN operation discussed in this course).
However, SQL also provides other versions of the JOIN operation (called LEFT JOIN or RIGHT OIN, for example) that may include records in one table for which there is no corresponding field value in the other table. If you are interested in exploring such operations further, try a Web search on "LEFT JOIN" or "RIGHT JOIN" or even "OUTER JOIN".
Log into MySQL and execute the above SELECT statement (with the JOIN).
SELECT * FROM courses JOIN courseSchedule ON courseSchedule.courseID = courses.courseID;
SELECT * FROM courses JOIN courseSchedule ON courseSchedule.courseID = courses.courseID WHERE courses.program="CS";
Modify the SELECT statement to address the following questions:
Within the database, table groceries contains information on a range of food items. When logged into MySQL, the describe groceries command clarifies the fields contained in each table record:
+----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | category | varchar(30) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | | unitName | varchar(30) | YES | | NULL | | +----------+------------------+------+-----+---------+----------------+
In addition, a groceryOrders table contains items ordered by various customers:
+-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | orderItemID | int(10) unsigned | NO | PRI | NULL | auto_increment | | orderNumber | int(10) unsigned | YES | | NULL | | | itemID | int(10) unsigned | YES | | NULL | | | quantity | decimal(10,2) | YES | | NULL | | +-------------+------------------+------+-----+---------+----------------+
The first part of this groceryOrders table contains information for order 1 and part of order 2:
+-------------+-------------+--------+----------+ | orderItemID | orderNumber | itemID | quantity | +-------------+-------------+--------+----------+ | 22 | 1 | 1 | 5.10 | | 23 | 1 | 5 | 3.70 | | 24 | 1 | 11 | 2.50 | | 25 | 2 | 1 | 3.50 | | 26 | 2 | 5 | 4.50 | | 27 | 2 | 9 | 4.20 | | 28 | 2 | 13 | 5.10 | . . .
Use SELECT statements to retrieve complete information for both the groceries and groceryOrders table.
Although the groceryOrders table stores the itemID for a grocery item, one must consult the groceries table to discover a description of a grocery item.
Write an SQL query that joins the groceryOrders and grocery tables, matching itemId's from each table.
Modify the SQL query to retrieve all items for selected groceryOrders
As stated, the design of these tables places information about the grocery items themselves in one table and information about ordering in another table. Reflect upon this organization and identify any advantages or disadvantages. In your reflection, consider
|
created 22 February 2019 revised 23 February 2019 |
|