Question

May I ask the SQL code as follows?

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]

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 Mark)

1.2 How many writers are there in this database? List the total number of writers. (1 Mark)

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 Mark)

1.4 How many persons were born in 1965 and 1966? List these two years and the number of persons who were born in each of these two years. (1 Mark)

1.5 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 Mark)

1.6 How many crew members have won at least one crew award? List that number. (1 Mark)

1.7 What is the maximum number of scenes in any movie? List that number. (1.5 Mark)

1.8 Give the list of directors who have directed at least two American movies (i.e., produced in USA). Provide their ids and the number of American movies that they each have directed. (1.5 Mark)

1.9 Who has/have won a most recent writer award? List their id(s). (1.5 Mark)

1.10 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.5 Mark)

1.11 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 Mark)

1.12 Which crew members have worked on a movie that has won the greatest number of movie awards? Give the ids of the crew members. (2 Mark)

1.13 Find all persons who have played roles in movies across all of the restriction categories in USA. Provide their ids, first and last names. (2 Mark)

1.14 Which two writers have co-written the greatest number of movies? Provide a pair of their ids and the number of movies they have co-written. If more than one pair of writers have co-written the greatest number of movies, provide all the pairs of their ids as well as the number of movies co-written by them. Note: the result should not contain duplicated pairs, e.g., (id1,id2) and (id2, id1) are considered as duplicated pairs and your query should only produce one of them in the result. (2 Mark)

Answer 1

Please Note: As per Chegg Answering Guidelines, I have answered the first Question and four sub-parts of the Question posted. Please Re-Post for the reamining of the Questions.

Answers)

1.1 Find all the movies produced in Australia. List the titles and production years of these Australian movies. (1 Mark)

Answer)
select title, productionyear from Movie where country='Australia';

1.2 How many writers are there in this database? List the total number of writers. (1 Mark)

Answer)
select count(id) from Writer;

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 Mark)

Answer)

SELECT Person.id, Person.firstname, Person.lastname
FROM ((Person
INNER JOIN Writer ON Person.id = Writer.id)
INNER JOIN WriterAward ON Writer.id = WriterAward.id);

1.4 How many persons were born in 1965 and 1966? List these two years and the number of persons who were born in each of these two years. (1 Mark)

Answer)
select yearborn,count(id) from Person
group by yearborn having Price=1965 or Price=1966;