Please submit the SQL commands with the results. The results should be cleaned up of duplicates, and
Question:
Please submit the SQL commands with the results. The results should be cleaned up of duplicates, and if they have a large result in the result set (more than 15 rows), please submit the first few. Also, the results should not be listed by the ID but the actual description itself. For instance, if you are listing the movie count by language you have to show the language.
Please download the Sakila Movie database from MySQL website (https://downloads.mysql.com/docs/sakiladb.zip). The compressed file will include 3 files: sakila.mwb: This shows the E-R diagram, including triggers (we didn't cover in class) and other items within the database. Please double-click and open it in MySQL Workbench on your screen. sakila-schema.sql: This script is the DDL statements for creating the database, and its tables, views, and various routines in the database. sakila-data.sql: It inserts the data into the database. Please run these files in the above order in your MySQL Workbench so you have the database set up. (It is exactly the same way as we did in class for the ClassicModels database). Please note: You may have this database installed as a sample database in your MySQL Workbench, in that case you don't need the above steps. IMPORTANT: When you start MySQL Workbench and open a query window to solve the following questions please run this command first to make the sakila database active: use sakila; QUESTIONS: 1. Which the last name is the most common actor last name?
2. How many actor(s) appeared in at least 2 movies?
3. How many different movie titles are there for each language?
4. Which movie title has the most copies across all stores?
5. List the top 10 customers for each store in terms of the number of rentals.
6. The Movie Rental Company wants to tier its customers based on the amount they spent so far in the database.
If the total is between 100 - 150 the tier is in the "Silver" tier,
Between 150.01 and 199.99 the customer is in the "Gold" tier,
Above 200, the customer is in the "Platinum" tier.
Otherwise, the customer is in the "Standard" tier. Using this query, create another table named customer_tier with the following columns customer_id, and reward_tier with suitable data types. Provide the top customer at each tier.
7. Find how many customers who have not rented a movie for more than 2 months (60 days) as of 1/1/2006 (Hint: You can calculate the days for this using the expression date('2006-01-01') - rental_date)
. 8. What are the longest and shortest movies at each category?
9. Which movies were rented in 2006 but not in 2005?
10. Compare each store's rental numbers between 2005 and 2006.