Scenario: 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.

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 actors 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)