The answers are expected to have the following items: the SQL queries, the results of running these queries in MySQL database, the Relational Algebra expressions, and the query plans/trees.
Consider a database with the following schema: see image.
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 located.
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 this way. Junction tables are not usually meant to store any additional data/objects.
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 listings.
1. Write a SQL query that lists only the length of all movies with an 'R' rating.
2. Write a Relational Algebra expression that performs the same task.
3. Write a SQL query that lists only the phone number and district name of Boeing Imax Theater.
4. Write a Relational Algebra expression that performs the same task.
5. Write a SQL query that lists the title, length, and movie time of movies that are played in the Cineplex Odeon Uptown theater.
6. Write a Relational Algebra expression that performs the same task.
7. Compose the query plan for the Relational Algebra expression from question 6.
8. Write a SQL query that lists the districts that have at least two theaters playing either 'Chicken Run' movie or any other 'PG'-rate movie
9. Write a SQL query that lists the average length of movies played at Landmark Neptune Theatre.
10. 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. Write a SQL query that lists the name and address of all theaters showing less than five different movies.
12. 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. 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. Write the relational algebra expression for the following statement: Addresses and phone numbers of theaters that play 'Frozen' movie.
15. Compose the query plan for the query from question 14.
16. 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 table.
17. 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.