COMP2400/6240 - Relational Databases
Sample SQL Questions
The Movie Database
The moviedb relational database schema is as defined in the Assignment 1 specification.
Questions
Your task is to write SQL queries that answer the following questions. For each question,
your answer must be a single SQL query (that may contain subqueries).
1. How many unique persons are in the database?
2. How many different first names of persons are there in database?
3. List all distinct first names of persons in the database, along with for each the number
of persons that have that first name, ordered by that number.
4. How many persons share the most common first name?
5. What is the most common first name?
6. Find all the movies made in Australia. List the titles and production years of these
Australian movies.
7. Which directors have directed crime movies (i.e., movies whose major genre is ’crime’)?
List the directors’ first and last names, and the titles and production years of these
crime movies.
8. Which directors have directed crime movies (i.e., movies whose major genre is ’crime’)?
List only the directors’ first and last names, without repetition.
9. List all movies (title and production year) for which any award, of any kind, has been
won, along with the total number of awards won for each movie, ordered by the number
of awards (most award-winning movie first).
1
Solutions
1. How many unique persons are in the database?
Solution:
select
count(∗)
from
person;
OR
select
count(id)
from
person;
Why do these two queries give the same answer?
2. How many different first names of persons are there in database?
Solution:
First, to list the all person’s first names (in alphabetical order):
select
first name
from
person
order by
first name;
To list the all distinct first names:
select distinct
first name
from
person
order by
first name;
To count how many distinct first names:
select
count(distinct first name)
from
person;
What will
select
count(first name)
from
person;
produce? Do you understand why?
3. List all distinct first names of persons in the database, along with for each the number
of persons that have that first name, ordered by that number.
Solution:
select
first name,
count(∗) as number with name
from
person
group by
first name
order by
number with name asc;
2
4. How many persons share the most common first name?
Solution:
select
max(number with name)
from (
−− use the query from above as a table subquery
select
first name,
count(∗) as number with name
from
person
group by
first name
);
5. What is the most common first name?
Solution:
select
first name
from (
−− like above, use the "name, count" query as a
−− table subquery
select
first name,
count(∗) as number with name
from
person
group by
first name
)
where
−− but here we need a restriction to the row(s) that
−− have a count equal to the maximum
number with name = (
−− so we need the query to get the maximum as a
−− correlated subquery:
select
max(number number with name)
from (
select
first name,
count(∗) as number with name
from
person
group by
first name
)
);
which can also be written
−− the "with" keyword allows us to define a named subquery
−− once, and then use in several places within our query:
with name count as (
select
first name,
count(∗) as number with name
from
person
group by
first name
)
select
3
first name
from
name count −− use it here
where
number with name = (
select
max(number with name)
from
name count −− and here
);
6. Find all the movies made in Australia. List the titles and production years of these
Australian movies.
Solution:
select
title,
production year
from
movie
where
country = ’Australia’;
For case-insensitive matching:
select
title,
production year
from
movie
where
lower(country) = ’australia’;
7. Which directors have directed crime movies (i.e., movies whose major genre is ’crime’)?
List the directors’ first and last names, and the titles and production years of these
crime movies.
select
first name,
last name,
title,
production year
from
−− join person and director to get first name and
−− last name of directors
person
join director using (id)
−− join with movie which has attribute major genre
join movie using (title, production year)
where
−− restrict to ’crime’ genre
major genre = ’crime’;
8. Which directors have directed crime movies (i.e., movies whose major genre is ’crime’)?
List only the directors’ first and last names, without repetition.
select distinct
first name,
last name
from
person
join director using (id)
join movie using (title, production year)
where
major genre = ’crime’;
4
(Try the previous and this query for some of the other genres, e.g., ’drama’ or ’action’,
to see the difference.)
9. List all movies (title and production year) for which any award, of any kind, has been
won, along with the total number of awards won for each movie, ordered by the number
of awards (most award-winning movie first).
select title,
production year,
(select count(∗)
from movie award
where movie.title = movie award.title
and movie.production year = movie award.production year
and lower(movie award.result) = ’won’
) +
(select count(∗)
from actor award
where movie.title = actor award.title
and movie.production year = actor award.production year
and lower(actor award.result) = ’won’
) +
(select count(∗)
from crew award
where movie.title = crew award.title
and movie.production year = crew award.production year
and lower(crew award.result) = ’won’
) +
(select count(∗)
from director award
where movie.title = director award.title
and movie.production year = director award.production year
and lower(director award.result) = ’won’
) +
(select count(∗)
from writer award
where movie.title = writer award.title
and movie.production year = writer award.production year
and lower(writer award.result) = ’won’
) as total awards
from movie
where total awards > 0
order by total awards desc;
5