This coursework mostly uses material in 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. 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.
Create a file cw3.sql containing SQL statements which answer the following
- return Carrie Fisher's actor_id.
- return distinct names of actors who appeared in the same film as
Carrie Fisher (including Carrie Fisher herself).
- write the same query as (2) above, in a different way. At least
one of your answers should use no nested queries, only joins.
- 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.)
- 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
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.
- query 1
- query 2
- query 3
- query 4
- query 5