G51DBS 2009-2010 Coursework 3
This coursework mostly uses material in 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. When you are writing
queries and don't want to type e.g. nza.Actor all the time, alias it as A
or something else short.
Exercise
Create a file cw3.sql containing SQL statements which answer the following
queries:
- Titles of movies made between 1980 and 1989 (inclusive)
- Names of directors who made a movie between 1980 and 1989 (inclusive). Do not use
joins and subqueries.
- The same query again, but using a join.
- The same query again, but using a subquery.
- Titles of movies where one of the actors is Harrison Ford and the director is George Lucas.
[ Don't look up IDs of Ford and Lucas and hard-wire them into
the query!]
Submission and marking
We will try to mark this exercise in the lab, again just by looking at
your cw3.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 266. The deadline is 5 March at 23:59.