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. Use a UNION statement. Display the address ID, address, and district from the address table where the district is California or Alberta. Display the address ID, address, and district only from the address table where the address contains Jol. (Note: Failure to construct this using a UNION statement will result in 0 points for #1)
2. Display the language ID and name from the language table and the film ID from the film table. Display all languages whether they have an associated film or not. Order results by language ID in ascending order. (Hint: This objective was demonstrated in Chapter 9)
3. List the actor ID, their first and last name of all actors and a count of the films they have done. Result set should be sorted descending by the actor's first name and new column should be named FilmCount. The objective you are to show here is how to use a subquery in an expression.
4. Show the customer ID, their first and last name, and the date of their last payment (name this column RecentPayment) of all those that are inactive customers sorted descending by last name and then ascending by first name. The objective you are to show here is how to use a subquery in an expression. (Hint: On the active column in the customer table, 0 means inactive and 1 means active)
5. List the film ID, title, and description from the film_text table for those films that have Zero Cage as the actor. Order your results set by the film description. The objective you are to show here is how to use a subquery in a filter. (Hint: You will have to use a JOIN in your subquery to get the correct results)