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)
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.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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started