Sakila Query Example

To test my comprehension of SQL, I decided to find an example database (DB) that had an interesting degree of complexity.

After some searching I found Sakila, a DB made by MySQL that is meant to provide a standardised schema to study and work from.

Sakila is a well normalised model of a DVD rental store, featuring things like films, actors, film-actor relationships, and a central inventory table that connects films, stores and rentals.

To think of an interesting query, I started making simple read queries using inner joins to get an idea of the tables and data I am working with.

SELECT release_year,
       title,
       name,
       description,
       length,
       first_name,
       last_name
FROM film
JOIN film_category ON
  film_category.film_id = film.film_id
JOIN category ON
  category.category_id = film_category.category_id
JOIN film_actor ON
  film_actor.film_id = film.film_id
JOIN actor ON
  actor.actor_id = film_actor.actor_id;

The table produced is a little too large to publish on this page, but if you wish to view the redacted version, you can see it here

From this I decided that I wanted part of my query to draw from the film descriptions, titles and actors as I felt it would enable me to use wildcards in an interesting way and give me a chance to concatenate columns.

Next I decided to look into the customer and rental tables, as that would give me the opportunity to use the central inventory table to make a more complex query.

SELECT  first_name,
        last_name,
        country,
        city,
        address,
        postal_code,
        phone,
        create_date,
        email,
        rental.rental_id,
        rental_date,
        amount,
        payment_date
FROM country
JOIN city ON
  city.country_id = country.country_id
JOIN address ON
  address.city_id = city.city_id
JOIN customer ON
  customer.address_id = address.address_id
JOIN rental ON
  rental.customer_id = customer.customer_id
JOIN payment ON
  payment.rental_id = rental.rental_id
ORDER BY first_name, last_name;

If you wish to view the redacted table, click here

From this information, I decided to use the rental_date column as that would allow me to use a BETWEEN operator on a date.

Here is the final query I came up with:

-- Customer, Adrian Clary, rented a film
-- between the 11th and 28th of July 2005
-- that included 'monkey' and 'womanizer'
-- in the description. He wants to know
-- the title of the film and the actors
-- that starred in it.

SELECT *
    FROM (
    SELECT title,
		   description,
           CONCAT(
                  actor.first_name,
             ' ', actor.last_name
                 )
             AS actor_name,
           CONCAT(
                 customer.first_name,
            ' ', customer.last_name
                 )
             AS customer_name,
           rental.rental_date
    FROM film
  	JOIN film_actor ON
      film_actor.film_id = film.film_id
    JOIN actor ON
      actor.actor_id = film_actor.actor_id
    JOIN inventory ON
      inventory.film_id = film.film_id
  	JOIN rental ON
      rental.inventory_id = inventory.inventory_id
    JOIN customer ON
      customer.customer_id = rental.customer_id
    ) AS query_for_adrian_c
	WHERE description LIKE '%monkey%%womanizer%'
	AND rental_date BETWEEN
      '2005-07-11%' AND '2005-07-28%'
	AND customer_name = 'Adrian Clary'
    ORDER BY actor_name;

First I create a subquery, so that the concatenated customer and actor names can be accessed in the new table created. As I am using MySQL, I use the CONCAT() function to concatenate the first and last name of the actors and customer respectively.

Each JOIN are inner joins that just enable each column to be linked to their associated primary and foreign keys.

I use a LIKE operator in the WHERE clause and the % wildcard character to search for a 'description' entry with 'monkey' and 'womanizer' anywhere in it.

The BETWEEN operator is used to search for a date within the given range. The % wildcard here is used to specify that the time portion of the datetime entry is not necessary for the search.

To wrap up the where clause, we make sure we are searching for the information associated with the correct customer (there is a surprising amount of film titles with 'monkey' and 'womanizer' in the description that were rented out between the 11th and 28th July). To do this, we search for 'Adrian Clary' in the column that was aliased 'customer_name'.

Finally we order the aliased actor names alphabetically (ASC is the default value of ORDER BY).

To view the table produced by this query, click here