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. return Carrie Fisher's actor_id.
  2. return distinct names of actors who appeared in the same film as Carrie Fisher (including Carrie Fisher herself).
  3. write the same query as (2) above, in a different way. At least one of your answers should use no nested queries, only joins.
  4. Actors who appeared in the same film as Carrie Fisher are said to have `zero degree of separation' from her. Write a query which returns distinct names of actors who have 1 degree of separation from Fisher, that is appeared in the same film as someone who appeared in the same film as her. (The result should also include those who have zero degree of separation from her.)
  5. return distinct names of those who have 0, 1 or 2 degrees of separation from Carrie Fisher.

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 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 5th of March, submit using cw system (G51DBS cw3 is coursework number 97) until Friday at 18:00 this counts as an early submission, and from 7/03 at 18:00 until 13/03 at 18:00 this counts as a late submission.