[an error occurred while processing this directive] CSE 190 M Lab 9 (Databases and SQL)

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

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

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.

SSH 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:

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)

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.

The answers to all these queries can be seen by checking the following "Show Answers" checkbox after the lab is over.

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:

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

[an error occurred while processing this directive]