University of Washington, CSE 190 M, Spring 2009
Lab 9: SQL Queries
Thursday, May 28, 2009

revised by Brian Le

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.

You can also view this document with the answers enabled by checking the "Show Answers" checkbox below, though you shouldn't do so while you're working on the lab problems.

Lab 9 Resources

• Lecture Slides
• Web Programming Step by Step textbook Ch. 11
• lecture queries

Exercises for Today:

1. Test Log-in to Webster
2. Test Running a Query in PHP
3. Practice Queries (Single-Table)
4. Practice Queries (Multi-Table Joins)
5. Extra Details

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) by clicking:

Start → Programs → UWick Apps → Secure SSH → Terminals → Secure Terminal

Connect 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

It will ask you for a password now. You should have received an email with an SQL password; use that password here. The terminal will not show any feedback as you are typing the letters of your password, not even a * for each letter. Just type the whole thing and press Enter. If it doesn't work after multiple tries, raise your hand for the TA to help you.

Once you are in, 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 movies;

(Make sure to remember the semicolon at the end of every SQL query! Otherwise the MySQL prompt will think you're not finished yet, and it will show a --> prompt on each line until you type a semicolon.)

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

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_MYSQL_PASSWORD to your appropriate values. Now open an SSH Secure File Transfer window and connect to webster. Upload the file, 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 Queries (Single-Table)

Go back to your 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     | 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 (0.00 sec)
   

   Answer:
   SELECT *
   FROM actors
   WHERE first_name = '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 movies
   WHERE year BETWEEN 1995 AND 2000;
   

3. Show the IDs of all actors who appeared in the movie named 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):

   +-----------+
   | actor_id  |
   +-----------+
   |     12508 | 
   |     17438 | 
   |     17946 | 
   ...
   |    793011 | 
   +-----------+
   63 rows in set (0.00 sec)
   

   Answer:
   SELECT actor_id
   FROM roles
   WHERE movie_id = 112290;
   

4. Practice Queries (Multi-Table Joins)

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

1. Show all roles played by any actor in the movie named Pi. To achieve this, join the movies table with the roles 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 r.role
   FROM roles r
        JOIN movies m ON m.id = r.movie_id
   WHERE m.name = 'Pi';
   

2. 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):

   +------------------+------------+-------------------------+
   | first_name       | last_name  | 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 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';
   

3. 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 actors, two movies, and two roles records. You'll need several JOIN ON conditions to link the actor to both roles records and to link each roles record to one of those two movies. You will also need multiple WHERE conditions to only grab the two Kill Bill movies. Our answer has 4 separate JOIN ON operators and 2 separate WHERE operators. If you have the right query, you should see the following result:

   +-------------------+-----------+
   | first_name        | last_name |
   +-------------------+-----------+
   | 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.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';
   

5. (for l33t h4x0rZ only): Extra Details

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.
• 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.)
• Write a query that shows the 5 actors in imdb_small who have appeared in the most films, sorted in descending order.
• Write a query that shows the top 3 most popular genres of films in imdb_small, sorted in descending order. (Hint: Look at the other tables available in the database by using SHOW TABLES; and DESCRIBE tableName; .
• Write a query that shows the name of the director who has directed the most horror films. (Use imdb_small at first.)
• Write a query that lists the names of all directors who have directed more comedies than any other type of movie. (This query is very hard and not solvable with just the material we have learned so far. It's mostly just to keep the wise-guys busy who finish everything else.)
• Write some of the queries needed for Homework 9. 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.