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.
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.)
Find the names of theaters that are playing a movie titled
"Pride and Prejudice" and a movie titled
"Sense and Sensibility."
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.
Find the titles and movie IDs of movies whose titles start with
"Ha" and end with "en".
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.)
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.
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.)