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:

First, 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:

• test_query.php

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:

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:

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:

• Place one of your queries into a PHP program that performs the query and displays its results as an HTML table.
• Write some of the queries needed for Homework 8. For example, write a query to list the names and years of all movies in which an actor with a given name appeared, or write a query to list all movies in which both Kevin Bacon and a given actor appeared.
• Switch your database from imdb_small to imdb by issuing the following command to mysql:

  USE imdb;
  

  Then run one af the previous queries on it. (Once again, any well-formed query you run should finish instantaneously. If it does not, press Ctrl-C.)