Structured Query Language: Assume that the Moive database contains the following
ID: 3681472 • Letter: S
Question
Structured Query Language:
Assume that the Moive database contains the following tables:
MOVIES (MovieID, Title, Rating)
THEATERS (TheaterID, TheaterName)
LOCATION (LocationID, City, State, TheaterID)
TICKETS (TheaterID, MovieID, SaleDate, TicketSold)
Create the SQL syntax and use comment lines for the following request:
a. List the movie titles showing at the Paradise and Majestic theaters. Use a subquery.
b. List all rated R movie titles and grand total ticket sales for showing with ticket sales greater than 20.
c. List unrated movie titles that sold more than 10, but exclude the city of Yorba Linda location.
d. List the movie titles and profit amounts for each showing. Each movie showing earns 20% profit from its ticket sales. Assume individual ticket price is $12.50.
Thank you!
Explanation / Answer
a.
select movies.title from movies where movies.movieid=
select movieid from tickets, theaters where theaters.theatherid=tickets.theatherid and theathers.theathernmae='Paradise' or theathers.theathernmae='Majestic';
b.
select movies.title, sum(tickets.ticketsold) from movies, tickets where movies.rating='R' and tickets.ticketid=movies.moviesid group by tickets.ticketid;
c.
select movies.title from movies, tickets where tickets.ticketsold>10 and movies.rating = 'unrated' and movies.moviesid=tickets.movieid;