Question

This is t a relational database please write SQL queries to solve the listed questions.

The database is a variation of the “Movie Database” . There are several differences in it, so look it over carefully before writing your SQL queries

Notes:

• TheaterNum, MovieNum, and ActorNum are numeric primary key fields in their respective tables. Movie and actor names are not assumed to be unique unless specified otherwise in a question.
• In the THEATER table, Capacity is the number of seats in a theater.
• In the MOVIE table, Year is the year a movie was filmed.
• The ACTED IN table lists all of the actors who acted in a movie. The Star field can have the values Y (yes) or N (no). Assume there is only one star in each movie.

City

State

Mayor

CITY

TheaterNum

Address

Phone

City

State

Capacity

THEATER

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

MOVIE

TheaterNum

MovieNum

SHOWINGS

DirName

Dir Address

Dir Cell

DIRECTOR

ProdName

Prod Addr

Proc Cell

PRODUCER

ActorNum

ActorName

CurrentAge

PlaceBirth

ACTOR

ActorNum

PreviousJob

PREVIOUSJOB

ActorNum

MovieNum

Star

ACTEDIN

NewsName

City

State

NEWSPAPER

RevName

Years Work

REVIEWER

ReviewNum

Text

Date

MovieNum

NewsName

RevName

REVIEW

Questions

Remember to follow all of the instructions listed on the first page.

1. Which theater(s) in Tennessee have the largest capacity?

2. How many reviews were written for each movie directed by John Carter that were filmed in the period 2014 to 2019

3. List the phone number of every theater in Tennessee. Order the results by theater number.

4. What was the total revenue generated by movies made in 2015 that were both directed by James Smith and produced by Mary Jones?

5. Assume there is only one movie titled, “The Matrix.” Who reviewed it?

6. List the cities in Tennessee that have theaters with capacities of at least 200 seats. List the cities in alphabetic order.

7. Which movies have generated more revenue than the movie directed by John Carter in 2010 that generated the most revenue of the movies he directed that year?

8. Which theaters in Tennessee, Arkansas, or Mississippi (you may use 2-letter abbreviations) showed movies whose titles began with any of the letters R, S, or T? List the theaters in numeric order.

9. Who is the oldest actor who starred in a movie made between 1995 and 2005 that was both directed by James Smith and produced by Mary Jones?

10. What was the total revenue generated by movies produced by each producer from 2010 to 2018 that starred an actor who is currently under 40 years of age? Only include producers whose movies generated more than a total of $75,000,000.

Answer 1

1. Which theater(s) in Tennessee have the largest capacity?

THEATER

TheaterNum

Address

Phone

City

State

Capacity

Query :

select * from THEATER where Capacity=(select max(Capacity) from THEATER);

Two queries are there. The inner query ‘select max(Capacity) from THEATER’ will select the largest capacity. The outer query will display the row.

2. How many reviews were written for each movie directed by John Carter that were filmed in the period 2014 to 2019

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

REVIEW

ReviewNum

Text

Date

MovieNum

NewsName

RevName

Ans:

Select count(ReviewNum),MovieNum from REVIEW where MovieNum in (select MovieNum from MOVIE where DirName=’John Carter’ and Year between 2014 and 2019)GROUP BY MovieNum ORDER BY COUNT(ReviewNum) DESC;

In this query , the inner query ‘select MovieNum from MOVIE where DirName=’John Carter’ and Year between 2014 and 2019’‘ will give movie directed by John Carter that were filmed in the period 2014 to 2019

GROUP BY MovieNum ORDER BY COUNT(ReviewNum) DESC: This will group the Movies

The outer query will display the count of reviews were written for each movie

3. List the phone number of every theater in Tennessee. Order the results by theater number.

THEATER

TheaterNum

Address

Phone

City

State

Capacity

Ans:

Select Phone from THEATER where State=’Tennessee’ order by TheaterNum;

This query will phone number of every theater in Tennessee Order by theater number ascendingly .

4. What was the total revenue generated by movies made in 2015 that were both directed by James Smith and produced by Mary Jones?

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

Ans:

Select sum(Revenue) from MOVIE where DirName=’James Smith’ and ProdName=’Mary Jones’ and Year=2015;

This query will display total revenue generated by movies made in 2015 that were both directed by James Smith and produced by Mary Jones by the condition ‘DirName=’James Smith’ and ProdName=’Mary Jones’ and Year=2015;’

5. Assume there is only one movie titled, “The Matrix.” Who reviewed it?

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

REVIEW

ReviewNum

Text

Date

MovieNum

NewsName

RevName

Ans:

Select RevName from REVIEW where MovieNum=(select MovieNum from MOVIE where Title=’The Matrix’);

The inner query will select the Movie Number with title “The Matrix.” The outer query will Display the reviewer name.

6. List the cities in Tennessee that have theaters with capacities of at least 200 seats. List the cities in alphabetic order.

THEATER

TheaterNum

Address

Phone

City

State

Capacity

Ans

Select City from THEATER where State=’Tennessee’ and Capacity>=200 order by City;

This query will List the cities in alphabetic order in Tennessee that have theaters with capacities of at least 200 seats.

7. Which movies have generated more revenue than the movie directed by John Carter in 2010 that generated the most revenue of the movies he directed that year?

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

Ans:

Select * from MOVIE where Revenue>(select max(Revenue) from MOVIE where DirName=’John Carter’ and Year=2010 );

This query will display the movies that have generated more revenue than the movie directed by John Carter in 2010 that generated the most revenue of the movies he directed that year. The inner query will get the Revenue that generated the most revenue in 2010.

8. Which theaters in Tennessee, Arkansas, or Mississippi (you may use 2-letter abbreviations) showed movies whose titles began with any of the letters R, S, or T? List the theaters in numeric order.

THEATER

TheaterNum

Address

Phone

City

State

Capacity

SHOWINGS

TheaterNum

MovieNum

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

Ans:

Select * from THEATER where State in (‘Tennessee’,’ Arkansas’,’Mississippi’) and TheaterNum in (select * SHOWING.TheaterNum from SHOWING join MOVIE on SHOWING.MovieNum=MOVIE.MovieNum where (MOVIE.Title like ‘R%’ or MOVIE.Title like ‘S%’ or MOVIE.Title like ‘T%’)) order by TheaterNum;

This query is to load theaters in Tennessee, Arkansas, or Mississippi which showed movies in numeric order whose titles begin with any of the letters R, S, orT.

9. Who is the oldest actor who starred in a movie made between 1995 and 2005 that was both directed by James Smith and produced by Mary Jones?

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

ACTOR

ActorNum

ActorName

CurrentAge

PlaceBirth

ACTEDIN

ActorNum

MovieNum

Star

Ans:

Select top 1 * from ACTOR where ActorNum in (Select ACTEDIN.ActorNum from MOVIE join ACTEDIN on MOVIE.MovieNum=ACTEDIN.MovieNum where MOVIE.DirName=’James Smith’ and MOVIE.ProdName=’Mary Jones’ and MOVIE.Year between 1995 and 2005 and ACTEDIN.Star=’Y’) order by CurrentAge desc;

This query will display oldest actor who starred in a movie made between 1995 and 2005 that was both directed by James Smith and produced by Mary Jones. In this case three tables are there, we joined tables MOVIE and ACTEDIN where MOVIE.MovieNum =ACTEDIN.MovieNum.

10. What was the total revenue generated by movies produced by each producer from 2010 to 2018 that starred an actor who is currently under 40 years of age? Only include producers whose movies generated more than a total of $75,000,000.

MOVIE

MovieNum

Title

Year

Length

Type

DirName

ProdName

Revenue

ACTEDIN

ActorNum

MovieNum

Star

ACTOR

ActorNum

ActorName

CurrentAge

PlaceBirth

Ans:

Select sum(MOVIE.Revenue),MOVIE.ProdName from MOVIE join ACTEDIN on MOVIE.MovieNum = ACTEDIN.MovieNum where MOVIE.Year between 2010 and 2018 and ACTED.Star=’Y’ and ACTEDIN.ActorNum in (select ActorNum from ACTOR where CurrentAge<=40)  GROUP BY MOVIE.ProdName ORDER BY sum(MOVIE.Revenue) DESC;

This query is to find total revenue generated by movies produced by each producer from 2010 to 2018 that starred an actor who is currently under 40 years of age. Here we join MOVIE table with ACTEDIN table and match the ActorNum.