Question

Suppose a database has the following three relations.
Movie (mid: integer, title: string, director: string, releaseDate: date)
PlaysAt (theaterID: integer, movieId: integer, showDate: date)
Theater (tID: integer, name: string, phone: string, screencount: integer)
"movieID" in PlaysAt is a foreign key referencing "mid" in Movie. "theaterID" in PLaysAt is a foreign key referencing "tID" in Theater.

Write the following queries in both relational algebra and SQL.

1. Find the titles of movies playing on 2 / 26 / 2019 and the IDs of the theaters they are playing at. (You may assume that a simple "=" works on the date data type.)
2. Find the names of theaters that are playing a movie titled "Pride and Prejudice" and a movie titled "Sense and Sensibility."
3. Find the phone numbers of theaters where the movie titled "The Green Book" is playing on 3 / 2 / 2019.

Write the following queries in SQL only.

1. Find the titles and movie IDs of movies whose titles start with "Ha" and end with "en".
2. For each movieID, find the number of different theaters that are playing the movie on 2 / 26 / 2019. (List both movieIDs and counts of theaters in output.)
3. List all movieIDs and movie titles. If the movie is playing in a movie theater, also list the theater's ID and the show date.
4. For each theaterID, find the number of different movies that are playing at that theater on 11 / 9 / 2018. The user is only interested in information about theaters that are playing more than three movies on 11 / 9. (List both theater IDs and counts of movies in output.)

Answer 1