There are five different categories of awards: movie awards, crew awards, director awards, writer awards and actor
Question:
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.
task is to answer the following questions using SQL queries. For each question, answer must be a single SQL query that may contain subqueries, and must be written answers into the template file myqueries.sql.
- How many persons were born before 1974 whose last name ends with 'e'? List that number.
- Find the average run time of movie(s) which were produced after 1991 and categorised as 'R' 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.
- How many movies have exactly 2 crew members? List that number.
- Find director(s) who have never been nominated for a director award. List their count.
- List the first and last name of director(s) who have directed the minimum number of 'action' movies. Order your result in the ascending order of their first names.
- 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).
- Which movie(s) have won the smallest number of both director and actor awards in a single year? List their title(s) and production year(s).
- How many movies have won at least one award (including movie awards, crew awards, director awards, writer awards and actor awards)? List that number.
- 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., {(title1, production year1), (title2, production year2)} and {(title2, production year2), (title1, production year1)} are considered 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.
- Find all the writers who have written a movie with at least one other writer (i.e., have never written a movie on their own). List their ids, first and last names.
The relational database moviedb has the following database schema:
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:
moviedb=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | actor_award | table | postgres
public | appearance | table | postgres
public | award | table | postgres
public | crew | table | postgres
public | crew_award | table | postgres
public | director | table | postgres
public | director_award | table | postgres
public | movie | table | postgres
public | movie_award | table | postgres
public | person | table | postgres
public | restriction | table | postgres
public | restriction_category | table | postgres
public | role | table | postgres
public | scene | table | postgres
public | writer | table | postgres
public | writer_award | table | postgres
(16 rows)
This is my answer,
-- Q1
SELECT COUNT(*)
FROM person
WHERE EXTRACT(YEAR FROM birth_date) < 1974 AND last_name LIKE '%e';
-- Q2
SELECT ROUND(AVG(m.run_time), 2) AS avg_run_time
FROM movie AS m
JOIN restriction AS R ON m.title = R.title AND m.production_year = R.production_year
WHERE m.production_year > 1991 AND R.description = 'R' AND R.country = 'USA';
-- Q3
SELECT COUNT(*)
FROM (SELECT title, production_year, COUNT(*) AS crew_count
FROM crew
GROUP BY title, production_year
) AS CrewCount
WHERE crew_count = 2;
-- Q4
SELECT COUNT(DISTINCT d.id)
FROM director AS d
LEFT JOIN director_award AS DA ON d.title = DA.title AND d.production_year = DA.production_year
WHERE DA.title IS NULL AND DA.production_year IS NULL;
-- Q5
WITH DirectorActionCount AS (
SELECT d.id, COUNT(m.title) AS action_count
FROM director AS d
LEFT JOIN movie M ON d.title = m.title AND d.production_year = m.production_year
WHERE m.major_genre = 'action'
GROUP BY d.id
),
MinActionCount AS (
SELECT MIN(action_count) AS min_count FROM DirectorActionCount
)
SELECT P.first_name, P.last_name
FROM DirectorActionCount DAC
JOIN person p ON DAC.id = P.id
JOIN MinActionCount MAC ON DAC.action_count = MAC.min_count
ORDER BY p.first_name;
-- Q6
SELECT ROUND(CAST(SUM(CASE WHEN m.major_genre = 'comedy' AND m.country = 'Australia' THEN 1 ELSE 0 END) AS DECIMAL) / COUNT(*), 2) AS proportion
FROM movie AS m
WHERE m.major_genre = 'comedy';
-- Q7
WITH AwardCounts AS (
SELECT title, production_year, COUNT(DISTINCT award_name) AS total_awards
FROM (
SELECT title, production_year, award_name FROM director_award
UNION ALL
SELECT title, production_year, award_name FROM actor_award
) AS CombinedAwards
GROUP BY title, production_year
),
MinAwards AS (
SELECT MIN(total_awards) AS min_awards FROM AwardCounts
)
SELECT A.title, A.production_year
FROM AwardCounts A
JOIN MinAwards MA ON A.total_awards = MA.min_awards;
-- Q8
SELECT COUNT(DISTINCT title)
FROM (
SELECT title FROM movie_award
UNION
SELECT title FROM crew_award
UNION
SELECT title FROM director_award
UNION
SELECT title FROM writer_award
UNION
SELECT title FROM actor_award
) AS AllAwards;
-- Q9
SELECT DISTINCT
LEAST(MA1.title, MA2.title) AS movie1,
GREATEST(MA1.title, MA2.title) AS movie2,
MA1.production_year
FROM movie_award MA1, movie_award MA2
WHERE MA1.year_of_award = MA2.year_of_award
AND MA1.title <> MA2.title
ORDER BY movie1, movie2, MA1.production_year;
-- Q10
SELECT DISTINCT W.id, W.first_name, W.last_name
FROM writer AS W
JOIN writer AS OtherWriters ON W.title = OtherWriters.title AND W.production_year = OtherWriters.production_year
AND W.id <> OtherWriters.id
ORDER BY W.id;
When I run this it shows:
\i myqueries.sql
count
-------
0
(1 row)
psql:myqueries.sql:13: ERROR: column r.title does not exist
LINE 3: JOIN restriction AS R ON m.title = R.title AND m.production_...
^
HINT: Perhaps you meant to reference the column "m.title".
psql:myqueries.sql:23: ERROR: column "title" does not exist
LINE 2: FROM (SELECT title, production_year, COUNT(*) AS crew_count
^
psql:myqueries.sql:30: ERROR: column d.title does not exist
LINE 3: LEFT JOIN director_award AS DA ON d.title = DA.title AND d.p...
^
psql:myqueries.sql:49: ERROR: column d.title does not exist
LINE 4: LEFT JOIN movie M ON d.title = m.title AND d.production_...
^
HINT: Perhaps you meant to reference the column "m.title".
psql:myqueries.sql:57: ERROR: column m.major_genre does not exist
LINE 1: SELECT ROUND(CAST(SUM(CASE WHEN m.major_genre = 'comedy' AND...
^
psql:myqueries.sql:75: ERROR: column "title" does not exist
LINE 4: SELECT title, production_year, award_name FROM direc...
^
psql:myqueries.sql:90: ERROR: column "title" does not exist
LINE 3: SELECT title FROM movie_award
^
psql:myqueries.sql:102: ERROR: column ma1.title does not exist
LINE 2: LEAST(MA1.title, MA2.title) AS movie1,
^
psql:myqueries.sql:111: ERROR: column w.title does not exist
LINE 3: JOIN writer AS OtherWriters ON W.title = OtherWriters.title ...
Expert Answer:
