Except where otherwise noted, the contents of this document are Copyright © Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.
thanks to former TAs Victoria Kirst, Jeff Prouty, Morgan Doocy, Brian Le for their work on these labs.
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
.
imdb
DatabaseIn the Query Tester web page, write SQL queries that accomplish the tasks on the following slides. 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 return its results immediately. If the page hangs for a long time, your query needs to be revised.
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
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
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
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 return its results immediately. If the page hangs for a long time, your query needs to be revised.
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
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
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
webster.cs.washington.edu
.ssh yourUWnetID@webster.cs.washington.edu
)Once connected via SSH, type the following command at the prompt:
mysql -u username -p
Type in your MySQL password (should have been emailed to you). The screen will not show anything as you type the password.
Now at the mysql>
prompt, you can type SQL commands. End each with a semicolon ;
.
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.)
simpsons
databaseid | name | |
---|---|---|
123 | Bart | bart@fox.com |
456 | Milhouse | milhouse@fox.com |
888 | Lisa | lisa@fox.com |
404 | Ralph | ralph@fox.com |
id | name |
---|---|
1234 | Krabappel |
5678 | Hoover |
9012 | Stepp |
id | name | teacher_id |
---|---|---|
10001 | Computer Science 142 | 1234 |
10002 | Computer Science 143 | 5678 |
10003 | Computer Science 154 | 9012 |
10004 | Informatics 100 | 1234 |
student_id | course_id | grade |
---|---|---|
123 | 10001 | B- |
123 | 10002 | C |
456 | 10001 | B+ |
888 | 10002 | A+ |
888 | 10003 | A+ |
404 | 10004 | D+ |
homer
, password d0ughnut
List all of the grades given in the course Computer Science 143. Do this as a single query and do not hard-code 143's ID number in the query.
+-------+ | grade | +-------+ | C | | A+ | | D- | | B | +-------+ 4 rows in set
Modify your previous query to list the names and grades of all students that took Computer Science 143 and got a B- or better. Do this as a single query and do not hard-code 143's ID number in the query.
+-------+-------+ | name | grade | +-------+-------+ | Lisa | A+ | | Ralph | B | +-------+-------+ 2 rows in set
List all names of all students who were given a B- or better in any class, along with the name of the class(es) and the (B- or better) grade(s) they got. Arrange them by the student's name in ABC order.
+----------+----------------------+-------+ | name | name | grade | +----------+----------------------+-------+ | Bart | Computer Science 142 | B- | | Lisa | Computer Science 154 | A+ | | Lisa | Computer Science 143 | A+ | | Milhouse | Computer Science 142 | B+ | | Ralph | Computer Science 143 | B | +----------+----------------------+-------+ 5 rows in set
List the names of all courses that have been taken by 2 or more students. Do this as a single query and do not hard-code any ID numbers in the query. Don't show duplicates.
+----------------------+ | name | +----------------------+ | Computer Science 142 | | Computer Science 143 | +----------------------+ 2 rows in set
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!