题意：
在myqueries.sql中写查询语句完成相应的数据库操作，提交至partch，连接moviedb执行
解析：
moviedb中奖项分为五大类:电影奖、剧组奖、导演奖、编剧奖和演员奖。一部电影只有获得提名才能获奖。任务是使用SQL查询回答给出的问题。对于每个问题，答案必须是一个可能包含子查询的SQL查询，将查询写入模板文件myqueries.sql
涉及知识点：
sql查询语句
更多可加微信撩骚
COMP2400 - Relational Databases
Assignment: SQL
Due date: 11:59pm, Aug 27, 2019
Instructions:
• This assignment should be done individually (no group work).
• This assignment will count for 20% of the final grade.
• You need to check whether your computer can connect to your own database (i.e., your UID, such as u1234567) and to the database moviedb at the server partch following the instructions below:
– Log into your account on partch from the lab computer or from your own computer.
– To connect to moviedb, enter “psql moviedb”.
• You must submit one file: myqueries.sql for all the questions on Wattle before the due date. You can download the template files from the folder “SQL Assignment for COMP2400” on Wattle. 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, i.e., “moviedb=> \i myqueries.sql”.
• Late submission is not granted under any circumstance. You will be marked on whatever 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 follow the ANU’s special consideration process (http://www.anu.edu.au/students/programadministration/assessments-exams/special-assessment-consideration).
• 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.
Question 1 20 Marks
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 and save your queries into the template file myqueries.sql on the desktop.
1.1 Find all people who were born in 1945. List their ids, first names and last names. (2 Mark)
1.2 How many movies were produced in the USA? List that number. (2 Mark)
1.3 Which comedy movies (i.e., the major genre of the movie is comedy) were produced in 1993? List the titles of these movies. (2 Mark)
1.4 How many directors have directed at least one action movie (i.e., the major genre of the movie is action)? List that number. (2 Mark)
1.5 Find all movies that won at least two movie awards. List their titles and production years along with the corresponding number of movie awards. Order your result in ascending order of the number of movie awards. (2 Mark)
1.6 How many directors have never won a director award? List that number. (2 Mark)
1.7 Find all writers who have also played roles in at least one movie written by themselves. List their ids, and the titles and production years of the corresponding movies. (2 Mark)
1.8 What is the maximum number of crew members in a movie? List that number. (2 Mark)
1.9 Who directed the movie(s) with the maximum number of scenes? List the id(s), first and last name(s). (2 Mark)
1.10 A person has worked on a movie if this person is a director, a writer or a crew member of this movie. Who worked on at least three different movies in this database? List their ids, first and last names. (2 Mark)
