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.

file: rentalcompany

table-column names:

ACTOR - actor_id, first_name, last_name, last_update

ADDRESS - address_id, address, address2, district, city_id, postal_code, phone, location, last_update

CATEGORY - category_id, name, last_update

CITY - city_id, city, country_id, last_update

COUNTRY - country_id, country, last_update

CUSTOMER - customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update

FILM - film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update

FILM_ACTOR - actor_id, film_id last_update

FILM_CATEGORY - film_id, category_id, last_update

FILM_TEXT - film_id, title, description

INVENTORY - inventory_id, film_id, store_id, last_update

LANGUAGE - laguage_id, name, last_update

PAYMENT - payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update

RENTAL - rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update

STAFF - staff_id, first_name, last_name, address_id, picture, email, store_id, active, username, password, last_update

STORE - store_id, manager_staff_id, address_id, last_update

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