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

Deadline: 10:00pm sharp on April 19, 2010

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

SQL Problems

1. Solve Problem 3.11 (a), (b) and (d) from the textbook. For part (b), you may assume that there are only two semesters each year — "Spring" and "Fall" — and that Spring is before Fall.
   [3 + 3 + 4 points]


2. Solve all parts of Problem 3.15 from the textbook.
   [4 + 4 + 4 points]


3. Referring to the same schema as in the above problem, write an SQL query to list all customers who have at most one account at the "Perryridge" branch.
   [5 points]


4. Referring to the same schema again, write a sequence of SQL data modification statements that achieve effect of closing the "North Town" branch of this bank and merging all its assets into the "Perryridge" branch. You will need to update multiple tables. Your solution (a) should not assume anything about what cities "North Town" and "Perryridge" are located in, and (b) should not use the MySQL feature of user-defined variables.
   [10 points]


5. Solve Problem 3.16 (c), (d) and (e) from the textbook. For part (e), note that the size of the payroll of a company is defined to be the total salary it pays out.
   [5 + 5 + 5 points]


6. Solve Problem 3.24 from the textbook.
   [8 points]


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

• Each of your queries must work correctly under the current version of MySQL on the machine "sunapee". Thus, you will be unable to use certain SQL features discussed in the textbook.


• Prepare one SQL script file containing solutions to Problems 1 through 6, 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 7 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.