,

Lab :

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.

Valid HTML5 Valid CSS

Basic lab instructions

Today's lab

This lab's purpose is to give you practice writing SQL queries on the imdb database.

imdb_small Database

In the Query Tester web page, write SQL queries that accomplish the tasks on the following slides. Recall the IMDb tables:

actors
idfirst_namelast_namegenderfilm_count
433259WilliamShatnerM162
797926BritneySpearsF65
831289SigourneyWeaverF72
...
movies
idnameyear
112290Fight Club1999
209658Meet the Parents2000
210511Memento2000
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...

Each query should return its results immediately. If the page hangs for a long time, your query needs to be revised. For your reference, all example outputs are from the imdb_small database

PLEASE REMEMBER: Test your queries on imdb_small before imdb, you don't want to run bad queries on a very large database!

Exercise : Single-table query 1: Julia

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 | film_count |
+--------+------------+------------------+--------+------------+
| 572929 | Julia      | Carothers Hughes | F      | 1          |
| 770247 | Julia      | Roberts          | F      | 1          |
| 784772 | Julia      | Schuler          | F      | 1          |
| 806043 | Julia      | Sweeney          | F      | 1          |
+--------+------------+------------------+--------+------------+    4 rows in set

Exercise : Single-table query 2: 1995-2000

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

Exercise : Single-table query 3: Fight Club

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

Exercise : Test a Query in PHP

Next, let's make sure that you can successfully execute PHP code that talks to the database.

Multi-table (JOIN) queries

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

actors
idfirst_namelast_namegenderfilm_count
433259WilliamShatnerM162
797926BritneySpearsF65
831289SigourneyWeaverF72
...
movies
idnameyear
112290Fight Club1999
209658Meet the Parents2000
210511Memento2000
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...

Each query should return its results immediately. If the page hangs for a long time, your query needs to be revised.

Exercise : Multi-table query 1: Pi

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

Exercise : Multi-table query 2: Pi Actor Names

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

Exercise : Multi-table query 3: Kill Bill

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

Exercise : (h4x0rz only): More queries

simpsons database

students
idnameemailpassword
123Bartbart@fox.combartman
456Milhousemilhouse@fox.comfallout
888Lisalisa@fox.comvegan
404Ralphralph@fox.comcatfood
...
teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 1549012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
...
to test queries on this database, use username homer, password d0ughnut

Exercise : CSE 143 Grades

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

Exercise : CSE 143 Grades and Names

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

Exercise : All Grades

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

Exercise : Popular Classes

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 |
| Computer Science 154 |
+----------------------+    3 rows in set

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!