Homework answers / question archive / Will present a relational database and ask you to write SQL queries to solve the listed questions

Will present a relational database and ask you to write SQL queries to solve the listed questions

Computer Science

Share With

---

Will present a relational database and ask you to write SQL queries to solve the listed questions.The database is a variation of the "Movie Database" from Exam 1. There are several differences in it, so look it over carefully before writing your SQL queries.

 

 

Please read and follow the following instructions carefully.

 

• Number your answers.

 

• Submit your answers in MS Word as .doc or .docx documents only.

 

• Do not include the questions with your answers. (This is different from the homework instructions.)

 

• 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.

 

·        Line up each clause on the left margin as:

SELECT

FROM

WHERE

etc.

 

• Do not communicate with anyone regarding this exam.

 

The Movie Database - It has been modified and is not the same as in Exam 1

 

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.

 

 

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.