G51DBS 2008-2009 Coursework 4

This coursework mostly uses material in lecture 9.

Database

As the previous exercise, this one uses my tables: nza.Director, nza.Movie and nza.DVD. You can see what are the column names nza.DVD using DESC nza.DVD at the sqlplus prompt, and see the data using SELECT * FROM nza.DVD

Exercise

Create a file cw4.sql containing SQL statements which answer the following queries:
  1. return average price of all dvds, as Average_Price
  2. return the name of director, movie title and price as DVD_Price for the most expensive DVD
  3. return a list of director names, movie titles and prices of DVDs as DVD_Price of the movies they directed (separately for each DVD)
  4. list of director names and total price of DVDs as Total_DVD_Price of the movies each director directed, where this total price is strictly greater than 10 pounds
  5. list of director names and total price of DVDs as Total_DVD_Price of the movies each director directed, ordered by the total price in descending order

Submission and marking

We will try to mark this exercise in the lab, again just by looking at your cw4.sql file and checking the output. Model output is here: all queries . Whether we have time to mark it in the lab or not, please submit via cw as well, coursework id is 191. The deadline is 20 March at 23:59.