Assignment: SQL Out - Getting Data out of the Database
For this assignment, you will be working with the same movie rental database as your in-class exercise (Moviedb). The schema is also the same, so you can use the one provided for the in-class exercise as a guide.
You will writeSQL query that would providethe answers to the questions on the next pages. Some of the questions can be answered by querying one table; others will require joining multiple tables to get the answer.
Guidelines
• You must include your name at the top of the document.
• You must include both the query and a screenshot of the output.
If you do not follow these instructions, your assignment will be counted late.
Evaluation
Your submission will be graded using the followingfactor:
1) A correctly formed SQL query that answers the question asked.
2) The correctness of the output of the query.
Questions
1. What is the rental rate and replacement cost for the film "Samurai Lion"?
Query:
Output:
2. What are the special features for the film "Alien Center"?
Query:
Output:
3. What are the maximum, minimum, average and total length of films?
Query:
Output:
4. Which country has the most cities in the database?
Query:
Output:
5. Which city has the most customers in the database?
Query:
Output:
6. What are the most popular last names among the actors in the database?
Query:
Output:
7. Which staff processed the most rental in the database?
Query:
Output:
8. Which movies are the least expensive to rent (on average) with regard to rating (e.g., G, R, PG)?
Query:
Output:
9. How many R movies mention a ‘Robot' in their description?(Hint: use WHERE...LIKE with a wildcard. Remember, %dog% will match any value containing "dog")
Query:
Output:
10. Who were the stars of the movie "King Evolution"?
Query:
Output:
11. What is the longest PG movie in English? And how long is it?
(Hint: Don't use the MAX function in the SELECT statement. It will give you the wrong answer.)
Query:
Output:
12. Who are the actorshave ‘Matt' in his first name and starred in movies in English?
Query:
Output:
13. Whohas rented the least movies? How many movies did he rent?
Query:
Output:
14. How many payments were processed on June 15, 2005? What was the total amount of payment on that day?
Query:
Output:
15. Which film attracted the most rental revenue? How much did that film generate in payment?
Query:
Output:
16. What was the longest movie starring Ben Harris? What's the movie length?
(Hint: Don't use the MAX function in the SELECT statement. It might give you the wrong answer.)
Query:
Output:
Attachment:- Movie-Rental-Schema.rar