G51DBS 2009-2010 Coursework 4

This coursework mostly uses material in lecture 9, the last query also lecture 8 (aliases and self-joins).

Database

As the previous exercise, this one uses my tables: 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 movie title and year ordered by year in ascending order
  2. return year and the number of movies per year as Year_Number
  3. return the average number of movies per year as Avg_Number
  4. return in a single query MOVIE_ID and price for the cheapest and for the most expensive DVD
  5. return pairs of different movie_IDs for which the DVDs have the same price, ordered first by the first ID in the pair (ascending) and then by the second (also ascending). For this exercise, I would like you to do a self-join on nza.DVD (see lecture 8). Remember that Oracle does not allow the use of "AS" for an alias in the FROM clause; when you mean 
    FROM nza.DVD AS DVD
    you have to write 
    FROM nza.DVD DVD

Submission and marking

We will try to mark this exercise in the labs, 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 273. The deadline is 19 March at 23:59.