University of Washington, CSE 190 M, Spring 2007
Lab 9: SQL Queries (Thursday, May 24th, 2007)

This lab's purpose is to give you practice writing SQL queries on the imdb database. Since imdb is a large database that must be shared by all students, you'll first run your queries on a smaller database called imdb_small. Because it's hard to debug query problems in PHP, we'll first practice entering queries directly at the MySQL console on webster through SSH.

1. Test Log-in to webster:

If you get stuck at any time during the following steps, please try it a second time, and if that still doesn't work, get a TA to come help you ASAP.

SSHFirst, let's try logging in and executing an example query to MySQL. Open your SSH Secure Shell client (the shell, not the Secure File Transfer) and connect it to webster.cs.washington.edu with your normal UW NetID and password.

Once you have connected, type the following command at the prompt that appears (case-sensitive):

mysql -u YOUR_UW_NETID -p

You should have received an email with an SQL password; use that password here. At the mysql> prompt that appears, type the following command:

USE imdb_small;

(You won't need to give that command in your PHP code.) You should see the message Database changed. Now, type in the following query:

SELECT * FROM Movie;

The results should appear immediately. You should see the following results (trimmed):

+--------+----------------+------+
| id     | name           | year |
+--------+----------------+------+
|  10920 | Aliens         | 1986 | 
|  17173 | Animal House   | 1978 | 
|  18979 | Apollo 13      | 1995 | 
|  30959 | Batman Begins  | 2005 | 
...
| 350424 | Vanilla Sky    | 2001 | 
+--------+----------------+------+
36 rows in set (0.00 sec)

If you successfully see the results as shown, you've verified that you can connect to MySQL successfully, so you should proceed to Step 2. If not, please ask a TA for help.

2. Test Running a Query in PHP Code:

Next, let's make sure that you can successfully execute PHP code that performs a query against the database on webster. Download and edit a copy of the following file:

In the code, change YOUR_UW_NETID and YOUR_EMAILED_MYSQL_PASSWORD to your appropriate values. Now open an SSH Secure File Transfer window and connect to dante. (Don't close your existing SSH shell window for webster.) Upload the file to dante, then view the file on the web in your browser. If you see a list of all the movies, go on to Step 3. If not, ask a TA for help.

3. Practice Building Queries:

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 TextPad, then paste them into SSH by right-clicking its window.

We will post the answers to all these queries on the web site after the lab is over.

Now, go back to the webster SSH window, and try to come up with SQL queries that accomplish the following tasks. Recall that the IMDb data exists in the following tables:

Actor
idfnamelnamegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
Movie
idnameyear
112290Fight Club1999
209658Meet the Parents2000
210511Memento2000
...
Cast
aidmidRole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...
  1. Show all columns of all actors who have the first name of Julia. If you have the right query, you should see the following result:
    +--------+-------+------------------+--------+
    | id     | fname | lname            | gender |
    +--------+-------+------------------+--------+
    | 572929 | Julia | Carothers Hughes | F      | 
    | 770247 | Julia | Roberts          | F      | 
    | 784772 | Julia | Schuler          | F      | 
    | 806043 | Julia | Sweeney          | F      | 
    +--------+-------+------------------+--------+
    4 rows in set (0.00 sec)
    
    Answer:
    SELECT * FROM Actor WHERE fname = 'Julia';
    
  2. Show the IDs and names of all movies released between 1995 and 2000 inclusive. If you have the right query, you should see the following result (trimmed):
    +--------+----------------------------+
    | id     | name                       |
    +--------+----------------------------+
    |  18979 | Apollo 13                  | 
    |  46169 | Braveheart                 | 
    | 109093 | Fargo                      | 
    | 112290 | Fight Club                 | 
    ...
    | 333856 | Titanic                    | 
    +--------+----------------------------+
    13 rows in set (0.00 sec)
    
    Answer: 
    SELECT id,name FROM Movie WHERE year BETWEEN 1995 AND 2000;
    
  3. Show the IDs of all actors who appeared in the movie Fight Club. You can use the movie ID of Fight Club that you found in the previous query. If you have the right query, you should see the following result (trimmed):
    +--------+
    | aid    |
    +--------+
    |  12508 | 
    |  17438 | 
    |  17946 | 
    ...
    | 793011 | 
    +--------+
    63 rows in set (0.00 sec)
    
    Answer:
    SELECT aid FROM Cast WHERE mid = 112290;
    
  4. Show all roles played by any actor in the movie named Pi. To achieve this, join the Movie table with the Cast table and filter out all records except those that represent roles played in Pi. An important part of understanding this problem is that you shouldn't need to know the movie ID of Pi ahead of time to do the query. If you have the right query, you should see the following result (trimmed):

    +-------------------------+
    | role                    |
    +-------------------------+
    | Man Delivering Suitcase | 
    | Brad                    | 
    | Transit Cop             | 
    ...
    | Devi                    | 
    +-------------------------+
    28 rows in set (0.00 sec)
    
    Answer:
    SELECT role 
    FROM   Movie, Cast
    WHERE  Movie.name = 'Pi'
    AND    Movie.id = Cast.mid;
    
  5. Show the first/last names of all actors who appeared in the movie Pi, along with the roles each played. To achieve this, you will need to join all three tables. If you have the right query, you should see the following result (trimmed):

    +------------------+------------+-------------------------+
    | fname            | lname      | role                    |
    +------------------+------------+-------------------------+
    | Abraham          | Aronofsky  | Man Delivering Suitcase | 
    | Peter            | Cheyenne   | Brad                    | 
    | Scott            | Franklin   | Transit Cop             | 
    ...
    | Samia            | Shoaib     | Devi                    | 
    +------------------+------------+-------------------------+
    28 rows in set (0.01 sec)
    
    Answer:
    SELECT Actor.fname, Actor.lname, Cast.role 
    FROM   Actor, Movie, Cast
    WHERE  Actor.id = Cast.aid
    AND    Movie.name = 'Pi'
    AND    Movie.id = Cast.mid;
    
  6. Show the first/last names of all actors who appeared in both of the following movies: Kill Bill: Vol. 1 and Kill Bill: Vol. 2. To achieve this, you will need to join five tables: an Actor, two Movies, and two Cast records. You'll also need several WHERE conditions to link the actor to both Cast records, to only grab the two Kill Bill movies, and to link each Cast record to one of those two movies. Our answer has 6 separate tests joined by AND operators. If you have the right query, you should see the following result:

    +-------------------+-----------+
    | fname             | lname     |
    +-------------------+-----------+
    | David             | Carradine | 
    | Chia Hui          | Liu       | 
    | Michael (I)       | Madsen    | 
    | Christopher Allen | Nelson    | 
    | Michael (I)       | Parks     | 
    | Stevo             | Polyi     | 
    | Vivica A.         | Fox       | 
    | Daryl             | Hannah    | 
    | Lucy              | Liu       | 
    | Uma               | Thurman   | 
    +-------------------+-----------+
    10 rows in set (0.00 sec)
    
    Answer:
    SELECT a.fname, a.lname
    FROM   Actor a, Movie m1, Movie m2, Cast c1, Cast c2
    WHERE  a.id = c1.aid
    AND    a.id = c2.aid
    AND    m1.id = c1.mid
    AND    m2.id = c2.mid
    AND    m1.name = 'Kill Bill: Vol. 1'
    AND    m2.name = 'Kill Bill: Vol. 2';
    

4. Extra Stuff:

If you finish all of the above queries, you can try the following:

Valid XHTML 1.0 Strict Valid CSS!