Description
Rules:
All the questions and the sample result sets in this assignment are based on filmdb.sqlite we
have been uploaded in bb website.
Your result set, especially the data type and the order of each column, must strictly follow
the description and the sample result set in each question.
You need submit “.sql” files for these six questions.
The name of each “.sql” file should be q1, q2, q3, q4, q5, q6 respectively to represent these
six questions.
Do not forget to add ‘;’ in the end of each query.
Do not compress them into a folder, please submit them directly.
Please submit those queries into sakai website as soon as possible, so that you can get
chance to receive feedback before deadline. After the deadline, we will check the assignment
automatically by a script and then given your grade, at that time, any argument about your
grade of this assignment will not be accepted.
Single table query
1. List the non-US movies released in 1991 and with titles begin with “The” .
2. How many actors have acted more than 30 movies.
3. What is the percentage of American films in all films in the 1970s. (The result should be
expressed percentage and approximated to 2 decimal places )
Multiple table query4. List the names of the known directors of 2016 films by ascending order (no need to display
anything about the film). The film only from following regions: kr, hk, gb, ph. If the film is
Korean(kr) or HONG KONG(hk), the name should be displayed as surname followed by first
name, otherwise it must be first name followed by surname.
hint: Note that coalesce() is required in this version, otherwise all the directors who are
only known by one name have a null row returned for them. use
coalesce(first_name, ‘ ‘) and don’t forget to place a single space between
surname and first name.
5. List all films made after 2000 (including 2000) with the max number of actors borned after
2000 (including 2000).
you can use subquery factoring: with … as (…)
The result set would be multiple rows if more than one films have max number of
actors
6. List the number of different actors who have been played in the same movie with Yifei Liu
(Yife herself is not included).
