Question

***This is a complete question. Do not tag this as incomplete.

Write SQL queries to the below Relational Database.

• Regarding the SQL queries:

• Do not use SELECT * in any query.

• Do not use any SQL features (including Nullif) that were not covered in this course.

• Do not use subqueries IF joins can be used to answer a question. However, some questions may require the use of subqueries.

The Movie Database

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

Ans: The SQL queries are as follows:

1. Select theaterNum, Address, Phone, City, State, max(Capacity)

from theater

where state= 'Tennessee';

2. Select count(ReviewNum), title,

from movie

where DirName = 'John Carter ' and year  between 2014 and 2019

Inner join review on

movie.movienum = review.reviewnum

group by movienum;

3. Select phone

from theatre

where state = 'Tennessee ' order by theaterNum asc;

4. Select sum(Revenue), Title

from movie

where DirName= 'James Smith' and prodName= ' Mary Jones ' and year=2015

group by movienum;

5. Select title, RevName

from movie

Inner join review on

  movie.movienum = review.reviewnum

where Title = 'The Matrix';

6. Select city from

theater

where capacity >= 200 and state=' Tennessee'

order by city asc;

7. Select title, revenue

from movie

where revenue > (Select max(revenue) from movie where dirname="John Carter" and year= 2010)

and year =2010;

8. Select theaterNum, Address, phone, city, state, capacity

from theater

Inner join showings on

theater.theaternum= showings.theaterNum

Inner join Movie on

movie.movienum= showings.movieNum

where state = 'Tennessee' or  'Arkansas' , or 'Mississippi' and title like "R%" or "S" or "T"

order by theaterNum asc;

9. Select actornum, ActorName, max(currentage)

from actor

Inner join Actedin on

actor.actornum = actedin.actornum

Inner join Movie on

movie.movienum = actedin.moviewnum

where year = 1995 between 2005 and dirname=" James Smith " and producername = "Mary Jones";

10. Select sum(revenue), prodname, actorname

from movie

Inner join Actedin on

actor.actornum = actedin.actornum

Inner join Movie on

movie.movienum = actedin.moviewnum

where year between 2010 and 2018 and currentage>40

group by prodname;