,

Lab :

Except where otherwise noted, the contents of this document are Copyright 2012 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.

original lab idea and code by Victoria Kirst and Jeff Prouty; revised by Brian Le and Marty Stepp

Basic lab instructions

• You may want to bring your textbook to labs to look up syntax and examples.
• Have a question? Ask a TA for help, or look at the book or lecture slides.
• We encourage you to talk to your classmates; it's okay to share code and ideas during lab.
• You are not expected to finish all of the exercises. Just do as much as you can in the allotted time. You don't need to finish the rest after you leave the lab.
• Before you leave, check in with a TA to get credit for your work.

Today's lab

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

• Because it's hard to debug query problems in PHP, we'll first practice entering queries into our Query Tester web page first.
• If webster is down, use Query Tester #2 instead, username cse190m, password cse190m.
• 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.
• To do queries on imdb, you need a MySQL password. Check your UW email for your password if you don't know it already. Use your UW NetID as your username.

imdb Database

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

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

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 |
+--------+------------+------------------+--------+
| 572929 | Julia      | Carothers Hughes | F      | 
| 770247 | Julia      | Roberts          | F      | 
| 784772 | Julia      | Schuler          | F      | 
| 806043 | Julia      | Sweeney          | F      | 
+--------+------------+------------------+--------+    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.

• Download and edit the following file: test_query.php
• In the code, change YOUR_UW_NETID and YOUR_MYSQL_PASSWORD to your appropriate values.
• Use your file transfer program to upload the file to the server.
• View the file on the web in your browser. If you see a list of all the movies, go on to the next exercise.

Multi-table (JOIN) queries

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

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 : Connect to Webster directly

• You can talk to a database directly using a secure shell (SSH) terminal program.
  • On Windows, download and run PuTTY and tell it to connect to webster.cs.washington.edu.
  • On a Mac or Linux, just open your Terminal app and type: ssh yourUWnetID@webster.cs.washington.edu)
• Log in with your normal UW NetID and password.

Exercise : Log in to MySQL console

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

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

simpsons database

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 190M | 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 |
+----------------------+    2 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!