Assignment: SQL
Getting Data out of the Database
For this assignment, you will be working with the same movie rental database as the in-class exercise (MovieDB). The schema is also the same, so you should use the one provided for the in-class exercise as a guide.
You will construct a single SQL queryusing MySQL Workbench that provides the answer to each of the questions below. You should also provide the answer to the question. Some of the questions can be answered by querying one table; others will require joining multiple tables to get the answer.
Also pay attention to what it says to Display. This is how your results should appear. For example:
Display: name and special features
Means that your SQL query should return only two columns of data: name and special features
(Note that this always isn't a field name, sometimes it is the result of a SUM(), COUNT(), or AVG()!)
Guidelines
• You must submit your answers electronically in a single Word document. You can copy and paste the SQL query and the results from SQL Workbench.
• You must include your name at the top of the document.
• Your answers should be emailed, as an attachment, from your Temple email account to the following OWLbox folder:[email protected]
• Please include your last name in the file name.
• The email must be sent by the start of class the day the assignment is due.
If you do not follow these instructions, your assignment will be counted late.
Evaluation
Your submission will be graded using two factors:
• A correctly formed SQL query that answers the specific question asked (no extra rows or columns).
• Providing the correct answer to the question (the answer the query provides).
Questions
1. What are the title and special features for films rated PG-13 and longer than 180 minutes?
Display: name and special features
2. What are the three most popular firstnames among the actors in the database?
Display: first name and the how many times that name appears in the database
3. What is the average rental cost (rate) for eachmovie rating (i.e., G, PG, R, NC-17)?
Display: rating and average rental rate
4. How many R movies mention ‘drama' in their description?
(Hint: use WHERE...LIKE with a wildcard. Remember, %dog% will match any value containing "dog," %dog will any value ending in "dog," and dog% will match any value beginning in "dog.")
Display: number of movies
5. Which store (identified by store_id) has the most movies in their inventory?
(Hint: List all stores. The list is short!)
Display: store_id, how many movies
6. Who were the stars of the movie "Operation Operation"?
Display: first name and last name
7. Which actors with a last name beginning with the letter "J" have starred in movies in theFrench language?
(Two hints: (1) Be sure to reference the language name "French" in your query and (2) Don't forget previous hints!)
Display: first name and last name
8. Which are the languages of the movies that Patricia Johnson has rented and how many has she rented in each of those languages?
(Hint: Assume no ties, just limit your results to the first three rows.)
Display: language name and how many movie rentals
9. What is the shortestR-rated movie in French and how long is it?
(Two hints: (1) Use a subselect statement with the MIN() function to return only the shortest movie and (2) be sure to reference the language name "French" in your query.)
Display: movie title and movie length
10. What was the shortest movie starring PenelopeGuiness and how long is it?
(Hint: Use a subselect statement with the MIN() function to return only the shortestmovie.)
Display: movie title and movie length.