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:
  1. Titles of movies made between 1980 and 1989 (inclusive)
  2. Names of directors who made a movie between 1980 and 1989 (inclusive). Do not use joins and subqueries.
  3. The same query again, but using a join.
  4. The same query again, but using a subquery.
  5. 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.