Coursework 4

This coursework mostly uses material in lecture 9. It will also help if you did the previous exercise (which used lectures 7 and 8).


This time you will not create a database yourself, but use an existing one. I gave you "read only" (that is, SELECT but not UPDATE) privileges on my tables: nza.Actor, nza.Director, nza.Movie and nza.Part. You can see what are the column names in, for example, Actor, using DESC nza.Actor at the sqlplus prompt.


Create a file cw4.sql containing SQL statements which answer the following queries:
  1. return (as n_actors) the number of actors in the database
  2. list titles and years of release of all films in the database, in reverse chronological order (most recent first)
  3. return titles of movies and names of actors which played in that movie, so that all rows for the same movie are grouped together
  4. return (Title, n_actors): titles of movies and how many actors took part in that movie
  5. return titles of movies ordered by the number of actors who took part in the movie: those with the highest number of actors first.

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 for each query is here:
  1. query 1
  2. query 2
  3. query 3
  4. query 4
  5. query 5
If we don't manage to finish marking it in the lab on the 12th of March, I will accept submissions via cw , coursework number 98, early deadline 14th of March 18:00, late deadline 18 April 18:00.