Sakila Inc. is a regional DVD movie rental business. Its 'sakila' database, available in your MySQL account, consists of tables and views about the films, their inventory, DVD rental and payment details, and customer data. Sakila is asking you to write MySQL statements to solve their problems as stated below. For each unique value of 'store_id', list the total number of customers in the customer table. Label the two displayed columns as 'Store' and 'Num_Customers' respectively. Display the results in decreasing order of the store IDs and then in decreasing order of the number of customers. List the titles, rental date, and return date of all films rented by the customer whose first name and last name are 'JO' and 'FOWLER' respectively. Sort the results in increasing order of rental date. Create a view to list the top 10 films that have the highest average amounts of rental and sort the results in descreasing order of the average amounts. Your results should display these columns (in this order): film title named as 'FilmTitle', number of rentals named as 'Num_Rentals', average amount of rentals named as 'Avg_Amt'. Execute the statement in your MySQL personal database to create the view and keep the view in your database (i.e., do not delete it or change it). (Hint: You should use 'CREATE OR REPLACE VIEW' and 'LIMIT' in your statement. You should reference the tables using 'sakila.' in your statement.)

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

100%

Sakila Inc. is a regional DVD movie rental business. Its 'sakila' database, available in your MySQL account, consists of tables and views about the films, their inventory, DVD rental and payment details, and customer data.

Sakila is asking you to write MySQL statements to solve their problems as stated below.

For each unique value of 'store_id', list the total number of customers in the customer table. Label the two displayed columns as 'Store' and 'Num_Customers' respectively. Display the results in decreasing order of the store IDs and then in decreasing order of the number of customers.
List the titles, rental date, and return date of all films rented by the customer whose first name and last name are 'JO' and 'FOWLER' respectively. Sort the results in increasing order of rental date.
Create a view to list the top 10 films that have the highest average amounts of rental and sort the results in descreasing order of the average amounts. Your results should display these columns (in this order): film title named as 'FilmTitle', number of rentals named as 'Num_Rentals', average amount of rentals named as 'Avg_Amt'. Execute the statement in your MySQL personal database to create the view and keep the view in your database (i.e., do not delete it or change it). (Hint: You should use 'CREATE OR REPLACE VIEW' and 'LIMIT' in your statement. You should reference the tables using 'sakila.' in your statement.)

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions