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
This lab's purpose is to give you practice writing SQL queries on the imdb
database.
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
.
webster
through SSH.
If you get stuck, please ask a TA to come help you ASAP.
ssh yourUWnetID@webster.cs.washington.edu
webster.cs.washington.edu
with your normal UW NetID and password.
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.
Go back to your SSH window, and write SQL queries that accomplish the following tasks. Recall the IMDb tables:
id | first_name | last_name | gender |
---|---|---|---|
433259 | William | Shatner | M |
797926 | Britney | Spears | F |
831289 | Sigourney | Weaver | F |
... |
id | name | year |
---|---|---|
112290 | Fight Club | 1999 |
209658 | Meet the Parents | 2000 |
210511 | Memento | 2000 |
... |
actor_id | movie_id | role |
---|---|---|
433259 | 313398 | Capt. James T. Kirk |
433259 | 407323 | Sgt. T.J. Hooker |
797926 | 342189 | Herself |
... |
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.
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';
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;
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;
Next, let's make sure that you can successfully execute PHP code that talks to the database.
YOUR_UW_NETID
and YOUR_MYSQL_PASSWORD
to your appropriate values.
The following SQL queries are more difficult because they involve joining results from multiple tables.
id | first_name | last_name | gender |
---|---|---|---|
433259 | William | Shatner | M |
797926 | Britney | Spears | F |
831289 | Sigourney | Weaver | F |
... |
id | name | year |
---|---|---|
112290 | Fight Club | 1999 |
209658 | Meet the Parents | 2000 |
210511 | Memento | 2000 |
... |
actor_id | movie_id | role |
---|---|---|
433259 | 313398 | Capt. James T. Kirk |
433259 | 407323 | Sgt. T.J. Hooker |
797926 | 342189 | Herself |
... |
Each query should respond immediately. If your query hangs for more than a few seconds, press Ctrl-C to abort it.
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';
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';
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 ON
s 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';
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.)
imdb_small
who have appeared in the most films, in descending order.
imdb_small
, sorted in descending order. (Hint: Look at the other tables available in the database by using SHOW TABLES;
and DESCRIBE tableName;
.
imdb_small
at first.)
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!