University of Washington, CSE 190M

Lab 5: SQL Queries

Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

original lab idea and code by Victoria Kirst and Jeff Prouty; revised by Brian Le and Marty Stepp

Valid HTML5 Valid CSS

Basic lab instructions

Today's lab

This lab's purpose is to give you practice writing SQL queries on the imdb database.

imdb Database

In the Query Tester web page, write SQL queries that accomplish the tasks on the following slides. Recall the IMDb tables:

actors
idfirst_namelast_namegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
movies
idnameyear
112290Fight Club1999
209658Meet the Parents2000
210511Memento2000
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...

Each query should return its results immediately. If the page hangs for a long time, your query needs to be revised.

Exercise Single-table query 1: Julia

Show all columns of all actors who have the first name Julia. If you have the right query, you should see:

+--------+------------+------------------+--------+
| id     | first_name |    last_name     | gender |
+--------+------------+------------------+--------+
| 572929 | Julia      | Carothers Hughes | F      | 
| 770247 | Julia      | Roberts          | F      | 
| 784772 | Julia      | Schuler          | F      | 
| 806043 | Julia      | Sweeney          | F      | 
+--------+------------+------------------+--------+    4 rows in set

Exercise : Single-table query 2: 1995-2000

Show just the IDs and names of all movies released between 1995-2000 inclusive. You should see (trimmed):

+--------+----------------------------+
| id     | name                       |
+--------+----------------------------+
|  18979 | Apollo 13                  | 
|  46169 | Braveheart                 | 
...
| 333856 | Titanic                    | 
+--------+----------------------------+    13 rows in set

Exercise : Single-table query 3: Fight Club

Show the IDs of all actors who appeared in the movie Fight Club. Use the ID of Fight Club from the previous query.

+-----------+
| actor_id  |
+-----------+
|     12508 | 
|     17438 | 
...
|    793011 | 
+-----------+    63 rows in set

Exercise : Test a Query in PHP

Next, let's make sure that you can successfully execute PHP code that talks to the database.

Multi-table (JOIN) queries

The following SQL queries are more difficult because they involve joining results from multiple tables.

actors
idfirst_namelast_namegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
movies
idnameyear
112290Fight Club1999
209658Meet the Parents2000
210511Memento2000
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...

Each query should return its results immediately. If the page hangs for a long time, your query needs to be revised.

Exercise : Multi-table query 1: Pi

Show all roles played in the movie named Pi. To achieve this, join the movies table with the roles table and filter out all records except those in Pi. You shouldn't need to know the movie ID of Pi ahead of time to do the query.

+-------------------------+
| role                    |
+-------------------------+
| Man Delivering Suitcase | 
| Brad                    | 
...
| Devi                    | 
+-------------------------+    28 rows in set

Exercise : Multi-table query 2: Pi Actor Names

Show the first/last names of all actors who appeared in Pi, along with their roles. You will need to join all three tables.

+------------------+------------+-------------------------+
| first_name       | last_name  | role                    |
+------------------+------------+-------------------------+
| Abraham          | Aronofsky  | Man Delivering Suitcase | 
| Peter            | Cheyenne   | Brad                    | 
...
| Samia            | Shoaib     | Devi                    | 
+------------------+------------+-------------------------+    28 rows in set

Exercise : Multi-table query 3: Kill Bill

Show the first/last names of all actors who appeared in both Kill Bill: Vol. 1 and Kill Bill: Vol. 2. Join five tables: an actors, two movies, and two roles. Use JOIN ON to link the actor to both roles, and to link each role to one of the movies. Use WHERE to grab only the two Kill Bill movies. (Our answer has 4 JOIN ONs and 2 WHERE parts.)

+-------------------+-----------+
| first_name        | last_name |
+-------------------+-----------+
| David             | Carradine | 
| Chia Hui          | Liu       | 
...
| Uma               | Thurman   | 
+-------------------+-----------+    10 rows in set

Exercise : Connect to Webster directly

screenshot screenshot

Exercise : Log in to MySQL console

Once connected via SSH, type the following command at the prompt:

mysql -u username -p

Type in your MySQL password (should have been emailed to you). The screen will not show anything as you type the password.

Now at the mysql> prompt, you can type SQL commands. End each with a semicolon ;.

screenshot

Exercise : (h4x0rz only): More queries

simpsons database

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
to test queries on this database, use username homer, password d0ughnut

Exercise : CSE 143 Grades

List all of the grades given in the course Computer Science 143. Do this as a single query and do not hard-code 143's ID number in the query.

+-------+
| grade |
+-------+
| C     |
| A+    |
| D-    |
| B     |
+-------+    4 rows in set

Exercise : CSE 143 Grades and Names

Modify your previous query to list the names and grades of all students that took Computer Science 143 and got a B- or better. Do this as a single query and do not hard-code 143's ID number in the query.

+-------+-------+
| name  | grade |
+-------+-------+
| Lisa  | A+    |
| Ralph | B     |
+-------+-------+    2 rows in set

Exercise : All Grades

List all names of all students who were given a B- or better in any class, along with the name of the class(es) and the (B- or better) grade(s) they got. Arrange them by the student's name in ABC order.

+----------+-----------------------+-------+
| name     | name                  | grade |
+----------+-----------------------+-------+
| Bart     | Computer Science 142  | B-    |
| Lisa     | Computer Science 190M | A+    |
| Lisa     | Computer Science 143  | A+    |
| Milhouse | Computer Science 142  | B+    |
| Ralph    | Computer Science 143  | B     |
+----------+-----------------------+-------+    5 rows in set

Exercise : Popular Classes

List the names of all courses that have been taken by 2 or more students. Do this as a single query and do not hard-code any ID numbers in the query. Don't show duplicates.

+----------------------+
| name                 |
+----------------------+
| Computer Science 142 |
| Computer Science 143 |
+----------------------+    2 rows in set

If you finish them all...

If you finish all the exercises, you can add any other content or code you like to your page.

If the lab is over or almost over, check with a TA and you may be able to be dismissed.

Great work!