University of Washington, CSE 190 M

Lab 8: 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

Basic lab instructions

• You may want to bring your textbook to labs to look up syntax and examples.
• We encourage you to talk to your classmates; it's okay to share code and ideas during lab.
• You don't need to finish all of the exercises. Do as much as you can in the allotted time.
• Before you leave, check in with a TA to get credit.
• Resources: lecture slides; textbook Chapter 11

Today's lab

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.

Exercise : Connect to Webster

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

• Open your SSH Secure Shell client (the shell, not the File Transfer) by clicking:
  Start → Programs → UWick Apps → Secure SSH → Terminals → Secure Terminal
  • (On a Mac, run the Terminal program then type: ssh yourUWnetID@webster.cs.washington.edu
• Connect to webster.cs.washington.edu with your normal UW NetID and password.

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:

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.

• Download and edit 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 to webster. Upload the file.
• View the file on the web in your browser. If you see a list of all the movies, go on to the next exercise.

Exercise : Multi-table (join) queries

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

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

• 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 of the previous queries on it. (Any query should finish instantaneously. If not, press Ctrl-C.)
• Write a query that shows the top 5 actors in imdb_small who have appeared in the most films, 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.)

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!