CS 33 (Spring 2009): Homework 2: Working with SQL

Deadline: 10:00pm sharp on April 20, 2009

Please read through this page entirely and carefully, before beginning your work.

SQL Problems

1. Solve all parts of Problem 3.9 from the textbook.
   [15 points]


2. Solve both parts of Problem 3.15 from the textbook.
   [16 points]


3. Solve all parts of Problem 3.19 from the textbook.
   [15 points]


4. Consider the longest SQL query on Page 96 of the textbook (occurring roughly in the middle of that page). The query uses the except operation for set subtraction. Unfortunately, MySQL does not (yet) support this operation. Rewrite the query so that it will work with the current version of MySQL on the machine "sunapee". Needless to say, your new query should return the very same relation that the textbook's query would: the ordering of tuples within the relation is unimportant.
   [7 points]


5. Similarly, rewrite the first query on Page 97 of the textbook (the one that uses where unique) so that it will work in MySQL. Ensure that your query returns each appropriate customer name only once.
   [7 points]


6. Revisit HW1, Part 2, Problem 3 (the one about actors and films). Write an SQL script to solve the same problem. Specifically, your script should create three tables named "actor", "film", and "actor_film" with appropriate schemas, populate these tables by reading in the contents of the respective three files in ~cs33/data/ and then execute a sequence of queries (possibly just one query) so that the output of the final query is a table satisfying the following properties. (1) The two columns of the table contain the desired actor names and film names, as per the statement of HW1, Part 2, Problem 3. (2) Each actor is paired with each film they appear in. (3) The rows are ordered by actor IDs first, and film IDs next; thus, all rows featuring a particular actor should appear adjacently. Once again, note that no IDs should be shown in your output.
   [20 points]

What to turn in

• Prepare one SQL script file containing solutions to Problems 1 through 5, in order, with comments interspersed indicating which problem you are solving. Use good indentation/layout so that your queries are easily readable. Follow the examples in the textbook.


• For grading purposes, we will execute your queries on different database instances from the specific database instance currently in "bankdb" — therefore, you ought to test your queries thoroughly against suitably modified databases before submitting.


• Prepare a second SQL script file containing a solution to Problem 6 only. For populating your database tables using the contents of the text files, use the load data SQL command: read the MySQL manual for an explanation of the various options available with this command.


• Submit exactly two files — both SQL scripts — each of which should have the extension ".sql". As before, use the homework submission form to submit.