University of Washington, CSE 190 M

Lab 9: SQL

Except where otherwise noted, the contents of this document are Copyright 2010 Marty Stepp and Jessica Miller.

created by Brian Le, Sylvia Tashev, and Marty Stepp

Valid XHTML 1.1 Valid CSS!

Basic lab instructions

Today's lab

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

Exercise : Connect to Webster

If you get stuck, please ask a TA to come help you ASAP.

SSH
screenshot screenshot

Exercise : Log in to MySQL console

Once connected, type the following command at the prompt:

mysql -u YOUR_UW_NETID -p

It will ask for a password. You should have received an email with your SQL password. The terminal will not echo any feedback as you are typing it.

Now at the mysql> prompt that appears, type these commands: (Make sure to remember the semicolon at the end of every SQL command!)

USE imdb_small;
SELECT * FROM movies;

Several movie results should appear. If not, please ask a TA for help.

Exercise : Single-table queries

Go back to your SSH window, and write SQL queries that accomplish the following tasks. 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 respond immediately. If your query hangs for more than a few seconds, press Ctrl-C to abort it. You may wish to build up your queries in a text editor, then paste them into SSH by right-clicking the SSH window.

Exercise -A: Single-table query 1

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
SELECT *
FROM actors
WHERE first_name = 'Julia';

Exercise -B: Single-table query 2

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
SELECT id, name
FROM movies
WHERE year BETWEEN 1995 AND 2000;

Exercise -C: Single-table query 3

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
SELECT actor_id
FROM roles
WHERE movie_id = 112290;

Exercise : Test a Query in PHP

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

Exercise : 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 respond immediately. If your query hangs for more than a few seconds, press Ctrl-C to abort it.

Exercise -A: Multi-table query 1

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
SELECT r.role
FROM roles r
    JOIN movies m ON m.id = r.movie_id
WHERE m.name = 'Pi';

Exercise -B: Multi-table query 2

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
SELECT a.first_name, a.last_name, r.role 
FROM   actors a
    JOIN roles r ON r.actor_id = a.id
    JOIN movies m ON m.id = r.movie_id
WHERE m.name = 'Pi';

Exercise -C: Multi-table query 3

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
SELECT a.first_name, a.last_name
FROM actors a
    JOIN roles r1 ON r1.actor_id = a.id
    JOIN roles r2 ON r2.actor_id = a.id
    JOIN movies m1 ON m1.id = r1.movie_id
    JOIN movies m2 ON m2.id = r2.movie_id
WHERE m1.name = 'Kill Bill: Vol. 1'
    AND m2.name = 'Kill Bill: Vol. 2';

Exercise : (h4x0rz only): More queries

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!