Consider a database with the following schema:
1. The movies table stores the movies played in theaters throughout the city.
2. The districts table stores the city districts where the movie theaters are
3. The theaters table stores movie theaters.
4. The movies2theaters table stores the movies and the corresponding theaters
that play that movie.
5. The theaters2districts table lists the theaters and their districts; note that
some theaters belong to several districts.
Tables like movies2theaters and theaters2districts are sometimes called
“junction” tables. They store foreign keys from main tables and connect them in
this way. Junction tables are not usually meant to store any additional
Before you start answering the questions, it is helpful to understand the schema
and browse the data. You could start by issuing a few trivial select * queries
from tables in the schema (e.g. SELECT * FROM movies;).
You can use your home folder as temporary storage for your queries and result
1. [2 points] Write a SQL query that lists only the length of all movies with a ‘G’
2. [2 points] Write a Relational Algebra expression that performs the same task.
3. [3 points] Write a SQL query that lists only the phone number and district
name of Boeing Imax Theater.
4. [3 points] Write a Relational Algebra expression that performs the same task.
5. [3 points] Write a SQL query that lists the title, length, and movietime of
movies that are played in the Cineplex Odeon Uptown theater.
6. [3 points] Write a Relational Algebra expression that performs the same task.
7. [4 points] Compose the query plan for the Relational Algebra expression from
8. [4 points] Write a SQL query that lists the districts that have at least two
theaters playing either ‘Chicken Run’ or any ‘PG’-rated movie.
9. [3 points] Write a SQL query that lists the average length of movies played at
Landmark Neptune Theatre.
10. [4 points] Write a SQL query that shows how many times the Landmark
Metro Cinemas theater has played a movie that is shorter than 2 hours.
11. [7 points] Write a SQL query that lists the name and address of all
theaters showing less than five different movies.
12. [7 points] Write a SQL query that lists theater names, phone numbers, and
the number of different movies that are shown in these theaters sorted by this
number (of different movies) in descending order.
13. [7 points] Write a SQL query that lists all theaters in Capitol Hill district that
play the longest R-rated movie and output their names (in ascending order)
and telephone numbers.
14. [4 points] Write the relational algebra expression for the following
statement: Addresses and phone numbers of theaters that play ‘Frozen’
15. [4 points] Compose the query plan for the query from question 14.
Switch to your personal database (listed in the result of the query “show
databases;”) to answer the following questions:
16. [3 points] Create a table called ‘query16’ with the result of the query from
question 5. Add a new boolean-valued column called ‘want to see’ to the new
17. [4 points] Copy the table ‘query16’ into a new table, ‘query17’. Modify the
column named ‘want to see’ of the newly created table so that it is true for the
movies that contain the word ‘The’ in their title and false otherwise.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx