Create and populate the DVD Rental database on your laptop following the instructions to prepare for the SQL Assessment.

Queries

The following practice queries are intended to familiarize you with the DVD Rental database. These queries are not related to the assessment itself. There is no penalty for not completing them, and you may work on them with others. (solutions)

  1. Retrieve the first name, last name, and email address of all customers living in London, United Kingdom. Sort the results by email address in ascending order.

    SELECT first_name, last_name, email
FROM customer INNER JOIN address USING (address_id)
              INNER JOIN city USING (city_id)
              INNER JOIN country USING (country_id)
WHERE city = 'London'
  AND country = 'United Kingdom'
ORDER BY email ASC;

  2. Retrieve the category of all films rated G. Do not include duplicates in the results. Sort the results by category in ascending order.

    SELECT DISTINCT category.name
FROM film INNER JOIN film_category USING (film_id)
          INNER JOIN category USING (category_id)
WHERE film.rating = 'G'
ORDER BY category.name ASC;

  3. Retrieve the first name, last name, and total amount spent by each customer who has spent at least $100. Sort the results in decreasing order of the amount spent, using the customer id to break ties.

    SELECT first_name, last_name, SUM(amount) AS total
FROM customer INNER JOIN payment USING (customer_id)
GROUP BY customer_id, first_name, last_name
HAVING SUM(amount) >= 100
ORDER BY total DESC, customer_id ASC;

  4. Retrieve the title, rating, and language of all films that have never been rented. Sort the results by title in ascending order.

    SELECT film.title, film.rating, language.name
FROM film INNER JOIN language USING (language_id)
WHERE film_id NOT IN (SELECT film_id
                      FROM inventory INNER JOIN rental
                              USING (inventory_id))
ORDER BY film.title ASC;

  5. Retrieve the first and last name of the actors and actresses who appear in a film that contains “water” in its title and who appear in a film that contains “bow” in its title. Either “water” or “bow” may be capitalized. Sort the results by the actor id in ascending order.

    SELECT actor.first_name, actor.last_name
FROM film f1 INNER JOIN film_actor fa1 ON f1.film_id = fa1.film_id
             INNER JOIN actor ON fa1.actor_id = actor.actor_id
             INNER JOIN film_actor fa2 ON actor.actor_id = fa2.actor_id
             INNER JOIN film f2 ON fa2.film_id = f2.film_id
WHERE f1.film_id <> f2.film_id  -- do not match the same film twice
  AND (f1.title LIKE '%water%' OR f1.title LIKE '%Water%')
  AND (f2.title LIKE '%bow%' OR f2.title LIKE '%Bow%')
ORDER BY actor.actor_id ASC;