COMP2400 - Relational Databases Assignment 1 (SQL) 2026 | Australian National University

COMP2400 - Relational Databases Assignment 1 (SQL)

Instructions:

• This assignment should be done individually (no group work). Do not post any idea/interpretation/partial solution/result related to this assignment on the Wattle Discussion forum. Join the drop-in sessions if you need any clarifications or need any technical support for accessing the moviedb database.
• This assignment will count for 20% of the final grade. Each question is worth 2 marks for a total of 20 marks.
  A copy of the moviedb database is available on partch server. You should connect to the moviedb database by entering the following in your terminal
  psql moviedb
• You must submit one file: myqueries.sql for all the questions on Wattle before the due date. You can download the template file from the folder "Assignment 1 (SQL) for COMP2400" on Wattle. You are welcome to run your query against the moviedb database one by one following previous lab instructions. You must enter your queries into the template file, and more specifically, for the submitted file myqueries.sql, it should be executable in the given database moviedb
  moviedb=> i myqueries.sql
• The correctness of queries should not depend on any database state, and the current content in moviedb is available for you to get familiar with the moviedb database. Note that partial marks may be awarded if the query only has minor issues.
• Sample SQL questions and solutions on moviedb are available on Wattle, which will be helpful for you to work on your assignment.
• Late submission is not granted under any circumstance. You will be marked on what- ever you have submitted at the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself in a situation beyond your control that you believe significantly affects an assessment, you should submit an Assessment Extension Request through Wattle along with the supporting documents.
• Provide references to all the resources that you have used to complete this assignment.
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this course is expected to be able to explain and defend any submitted assessment item. The course convener can conduct or initiate an additional interview about any submitted assessment item for any student. If there is a significant discrepancy between the two forms of assessment, it will be automatically treated as a case of suspected academic misconduct.

Questions:

The relational database moviedb has the following database schema:

1 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
foreign keys
(title, production year}
[title, production.year] C MOVIE[title, production_year] [id] C PERSON[id]
WRITER(id, title, production-year, credits)
primary key
foreign keys
fid, title, production-year}
[title, production year] C MOVIE[title, production_year] [id] C PERSON[id]
CREW(id, title, production-year, contribution)
primary key: {id, title, production_year}
foreign keys: [title, production-year] C MOVIE[title, production_year] [id] C PERSON[id]
SCENE(title, production year, scene no, description)
primary key: {title, production-year, scene_no}
foreign keys: [title, production year] C MOVIE[title, production_year]
ROLE(id, title, production year, description, credits)
primary key {title, production year, description}
foreign keys
[title, production.year] C MOVIE[title, production-year] [id] C PERSON[id]
RESTRICTION(title, production year, description, country)
primary key {title, production year, description, country}
foreign keys: [title, production year] C MOVIE [title, production_year]
[description, country] C 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] C SCENE[title, production-year, scene_no] [title, production year, description] C ROLE[title, production year, description]
MOVIE AWARD(title, production year, award name, year of award, category, result)
primary key
foreign keys:
CREW AWARD(id, title, primary key
foreign keys:
DIRECTOR AWARD (title, primary key: foreign keys:
{title, production year, award name, year of award, category} [title, production.year] C MOVIE[title, production_year] [award_name] C AWARD[award_name]
production_year, award_name, year of award, category, result) (id, title, production year, award_name, year of award, category} [id, title, production-year] C CREW[id, title, production_year] [award_name] C AWARD[award_name]
production_year, award_name, year of award, category, result) [{title, production year, award name, year of award, category) [title, production-year] C DIRECTOR[title, production-year] [award_name] C 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} [id, title, production year] C WRITER[id, title, production_year] [award_name] C AWARD[award_name]
foreign keys:
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} [title, production-year, description] C ROLE[title, production-year, description] [award_name] C AWARD[award_name]
foreign keys:

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. How many persons were born after 1974 whose first name ends with 'e'? List that number.
2. Find the average run time of movie(s) which were produced before 1991 and categorised as 'PG-13' restriction in the USA. List the average as a decimal (round to two decimal places). Hint: in PostgreSQL, the function ROUND(x, n) can round x to n decimal places, e.g., if x=0.1129, then ROUND(x, 2) = 0.11.
3. Of all the movies that have crew members, how many movies have at most 2 crew members? List that number.
4. Find director(s) who have never been nominated for a director award. List their count. Hint: If an award has been won, then implicitly the winner must have been nominated.
5. List the first and last name of director(s) who have directed the maximum number of 'comedy' movies. Order your result in ascending order of their first names.
6. What proportion of comedy movies are produced in Australia among all comedy movies in this database? List the proportion as a decimal (round to two decimal places).
7. Of all the movies that have won both a director award and an actor award in the same year, which movie(s) have won the largest combined total of both director and actor awards in a single year? List their title(s) and production year(s).
8. How many movies have won at least one award (including movie awards, crew awards, director awards, writer awards and actor awards)? List that number.
9. Which director(s) directed the least variety of movies (i.e., the least number of distinct major genres)? List their id(s).
10. List all the pairs of movies which have won any award in the same year. List the pairs of their title and production_year. Note that the result should not contain duplicated pairs of title and production_year, e.g., {(titlel, production_year1), (title2, produc- tion_year2)} and {(title2, production_year2), (titlel, production_year1)} are consid- ered as duplicated pairs and your query should only produce one of them in the result. Hint: in PostgreSQL, the function CONCAT(A1, A2,..., An) can be used to combine selected attributes.