1 Dvd Rental Exercises
2 Exercise 1
3 Exercise 2
4 Exercise 3
5 Exercise 4
6 Exercise 5
7 Exercise 6
8 Exercise 7
9 Exercise 8
10 Exercise 9

DVD Rental Exercises

In these exercises we will use a new data set, about a DVD rental business.

You must first import this data set. Download the ddl file from here:

DVD rental ddl

You must import this by running the ddl code, like this:

Right click on your schema folder, and select the "Run SQL Script...".

This should import a new schema, the dvd rental.

Then, you must import data. Download the data her:

DVD rental data

And then run it, in the same way as before. You should now have a "dvdrental" schema with data. Verify this.

The below is a Global Relations Diagram to get an overview of the database:

You may want to right click the above, and open the image in a new tab, if it is too small.

Over the following slides, you will be asked questions, which must be answered by querying the dvdrental database.
All questions can be answered with a single sql statement (consider this a challange), but sometimes it is easier to do multiple statements.

For most questions, you can also find the answer as a hint. Do give it a serious try first, before you look at the answer.

Exercise 1

Create a list of all stores.

Show answer

Show SQL

SELECT * FROM store;

Exercise 2

Create a list of films (title and description) longer than 120 minutes.

Show answer

Show SQL

SELECT title, description
FROM film
WHERE lenght > 120;

Exercise 3

What are the addresses of each store?

Show answer

Show SQL

SELECT *
FROM address
WHERE address_id IN (
    SELECT address_id
    FROM store
    );

Exercise 4

What is the name of the customer who lives in the city 'Apeldoorn'?

Show answer

Show SQL

SELECT first_name, last_name
FROM customer
WHERE address_id IN (
    SELECT address_id
    FROM address
    WHERE city_id = (
        SELECT city_id
        FROM city
        WHERE city = 'Apeldoorn'
        )
    );

Exercise 5

What are the categories of the film 'ARABIA DOGMA'?

Show answer

Show SQL

SELECT name FROM category, film_category
WHERE category.category_id = film_category.category_id
AND film_category.film_id = (
    SELECT film_id
    FROM film
    WHERE title = 'ARABIA DOGMA'
    );

Exercise 6

Which actors (their names) were in the film 'INTERVIEW LIAISONS'?

Show answer

Show SQL

SELECT first_name, last_name
FORM actor, film_actor
WHERE film_actor.actor_id=actor.actor_id
AND film_actor.film_id IN (
    SELECT film_id
    FROM film
    WHERE title='INTERVIEW LIAISONS'
    );

Exercise 7

What is the name of the staff who rented a copy of 'HUNCHBACK IMPOSSIBLE' to customer KURT EMMONS?

Show answer

Show SQL

SELECT customer_id, rental.staff_id, first_name, last_name
FROM rental,
     staff
WHERE rental.staff_id = staff.staff_id
  AND inventory_id IN (SELECT inventory_id
                       FROM inventory
                       WHERE film_id = (
                           SELECT film_id
                           FROM film
                           WHERE title = 'HUNCHBACK IMPOSSIBLE')
                         AND customer_id = (
                           SELECT customer_id
                           FROM customer
                           WHERE customer.first_name = 'KURT'
                             AND customer.last_name = 'EMMONS'
                       )
);

Exercise 8

Show how many inventory items are available at each store.

Show answer

Show SQL

SELECT store.store_id, COUNT(inventory_id)
FROM store, inventory
WHERE inventory.store_id = store.store_id
GROUP BY store.store_id;

Exercise 9

How many times have VIVIAN RUIZ rented something?

Show answer

Show SQL

SELECT first_name, last_name, COUNT(payment.customer_id) AS rent_count
FROM payment, customer
WHERE payment.customer_id = customer.customer_id
AND payment.customer_id = (
    SELECT customer_id
    FROM customer
    WHERE fist_name = 'VIVIAN'
    AND last_name = 'RUIZ'
    );