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).
Database
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.
Exercise
Create a file cw4.sql containing SQL statements which answer the following
queries:
- return (as n_actors) the number of actors in the database
- list titles and years of release of all films in the database,
in reverse chronological order (most recent first)
- return titles of movies and names of actors which played in that movie,
so that all rows for the same movie are grouped together
- return (Title, n_actors): titles of movies and how many actors took part in
that movie
- 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:
- query 1
- query 2
- query 3
- query 4
- 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.