You work for a DVD rental company. Your manager has asked you to pull some data on the company, its inventory, and customers. You will complete this by developing and running SQL queries.
Initialize Your Database (NOTE: If you already did this in Project 1, you do not need to do this step again):
1. Download RentalCompany.sql from Doc Sharing.
a. Run the script to create tables and data. You should not receive errors. There may be a few warnings, but that is okay.
b. To verify you created all tables, run the following SQL:
SELECT TABLE_NAME, TABLE_ROWS, TABLE_TYPE, CREATE_TIME FROM `information_schema`.`tables` WHERE `table_schema` = 'rentalcompany' AND TABLE_TYPE = 'BASE TABLE';
Your results should look like the table below. This will help verify that you properly created the schema/database and tables.
| actor | 200 | BASE TABLE |
| address | 603 | BASE TABLE |
| category | 16 | BASE TABLE |
| city | 600 | BASE TABLE |
| country | 109 | BASE TABLE |
| customer | 599 | BASE TABLE |
| film | 1000 | BASE TABLE |
| film_actor | 5463 | BASE TABLE |
| film_category | 1000 | BASE TABLE |
| film_text | 1000 | BASE TABLE |
| inventory | 4581 | BASE TABLE |
| language | 6 | BASE TABLE |
| payment | 16086 | BASE TABLE |
| rental | 16005 | BASE TABLE |
| staff | 2 | BASE TABLE |
| store | 2 | BASE TABLE |
Directions:
1. Show me the total number of payments, name column as PaymentCount, we have in the rental company database. (Hint: Your results should only return 1 row)
2. Show me how many unique last names are in the actor table. (Hint: Your results should only return 1 row)
3. Show me the average amount, name column as AverageAmount, from the payment table for customer ID 7. (Hint: Your results should only return 1 row)
4. Show me the maximum rental duration and minimum rental duration from the film table. Name the columns MaxRentalDuration and MinRentalDuration respectively.
5. List the actor ID, last name, and first name of each actor and the count of films (name column FilmCount) each is in for those actors who acted in more than 35 films. Order results by FilmCount. (Hint: You will need to use GROUP BY and HAVING in your query.)