COMP2400/6240 – Relational Databases
SQL Sample Questions
Due date: TBD
Question 1 Unknown Marks
The relational database moviedb has the following database schema:
Movie(title, production year, country, run time, major genre)
primary key : {title, production year}
Person(id, first name, last name, year born)
primary key : {id}
Award(award name, institution, country)
primary key : {award name}
Restriction Category(description, country)
primary key : {description, country}
Director(id, title, production year)
primary key : {title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Writer(id, title, production year, credits)
primary key : {id, title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Crew(id, title, production year, contribution)
primary key : {id, title, production year}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Scene(title, production year, scene no, description)
primary key : {title, production year, scene no}
foreign keys : [title, production year] ⊆ Movie[title, production year]
Role(id, title, production year, description, credits)
primary key : {title, production year, description}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[id] ⊆ Person[id]
Restriction(title, production year, description, country)
primary key : {title, production year, description, country}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[description, country] ⊆ Restriction Category[description, country]
Appearance(title, production year, description, scene no)
primary key : {title, production year, description, scene no}
foreign keys : [title, production year, scene no]⊆Scene[title, production year, scene no]
[title, production year, description]⊆Role[title, production year, description]
1
Movie Award(title, production year, award name, year of award,category, result)
primary key : {title, production year, award name, year of award, category}
foreign keys : [title, production year] ⊆ Movie[title, production year]
[award name] ⊆ Award[award name]
Crew Award(id, title, production year, award name, year of award, category, result)
primary key : {id, title, production year, award name, year of award, category}
foreign keys : [id, title, production year] ⊆ Crew[id, title, production year]
[award name] ⊆ Award[award name]
Director Award(title, production year, award name, year of award, category, result)
primary key : {title, production year, award name, year of award, category}
foreign keys : [title, production year] ⊆ Director[title, production year]
[award name] ⊆ Award[award name]
Writer Award(id, title, production year, award name, year of award, category, result)
primary key : {id, title, production year, award name, year of award, category}
foreign keys : [id, title, production year] ⊆ Writer[id, title, production year]
[award name] ⊆ Award[award name]
Actor Award(title, production year, description, award name, year of award,category,result)
primary key : {title, production year, description, award name, year of award, category}
foreign keys : [award name] ⊆ Award[award name]
[title,production year,description]⊆Role[title,production year,description]
There are five different categories of awards: movie awards, crew awards, director awards, writer awards and
actor awards. A movie can only win an award after being nominated for the award.
Your task is to answer the following questions using SQL queries. For each question, your answer must be a single
SQL query that may contain subqueries, and you must write your answers into the template file myqueries.sql.
1.1 Find all the movies produced in Australia. List the titles and production years of these Australian movies.
1.2 How many persons are there in this database? List that number.
1.3 Which writers have won a writer award in 1994? List the ids, the first and last names of the writers along
with the award names.
1.4 Which directors have directed crime movies (i.e., the major genre of the movie is crime)? List the directors’
ids and the titles and production years of these crime movies.
1.5 How many different crew members have worked on at least one movie produced in 1993? List that number.
1.6 List all titles of movies produced in 1995 along with the corresponding number of scenes in each movie.
Order your result in the ascending order of the number of scenes in each movie.
1.7 Which year was the youngest director (i.e., the youngest person in the database who has ever directed a
movie) born in? List that year.
1.8 How many directors are there in this database who have never written a movie? List that number.
1.9 What is the maximum number of scenes in any movie? List that number.
1.10 Who has/have won a most recent writer award? List their id(s).
1.11 Find all the movies which have won exactly two movie awards. List the titles and production years of
these movies.
1.12 Who have won both writer and director awards for the same movie? Provide their ids, and the titles and
production years of the corresponding movies.
1.13 Who have been nominated for an actor award at least twice but never won? List their ids, the first and
last names, and order them in the ascending order of their last names.
2
1.14 Which crew members have worked on a movie that has won the greatest number of movie awards? Give
the ids of the crew members.
1.15 Who is/are the most productive director(s) (i.e., who has/have directed the greatest number of movies)?
List their id(s).
3