Learning SQL queries
In this tutorial you’ll learn:
- The key integrity constraints in a relational database.
- Basic SQL queries.
1 Description of the data
We consider the database of a DVD rental store chain containing data on films, actors, customers and the transactions of the store.
This database goes by the name Sakila and was developed by Mike Hillyer, a former member of the MySQL team.
Figure 1.1: The conceptual schema of the Sakila database
The tables of the database are documented on this page.
In this lab, we’ll use SQLite as a relational DBMS, due to the unavailability of the PostgreSQL servers. SQLite is a simple relational DBMS; data is stored in just one file.
Download and install DB Browser for SQLite.
Download the database file here.
Open DB Browser for SQLite and select “Open Database”.
Select the file that you’ve just downloaded. In the “Database Structure” tab you should see the tables of the database.
Click on the tab “Execute SQL” to access a text area where you can type SQL queries.
2 Integrity constraints
Exercise
Exercise 2.1 Execute the following statement:
insert into film_actor (actor_id, film_id) values(1, 25)
What is this statement supposed to do? What is the reaction of the DBMS?
Solution
This statement should insert a new row in table film_actor, where the value of actor_id is 1 and the value of film_id is 25.
The DBMS returns an error because there is already a row with these values and the two columns film_actor, film_id form the primary key.
Exercise
Exercise 2.2 Write the statement to delete the film with film_id=1 from the table Film.
Execute the command. What happens?
Solution
delete from film where film_id=1
The statement is rejected because there are rows in other tables that reference the row that we intend to delete. This is the effect of the foreign key constraint.
Exercise
Exercise 2.3 Look at the definition of the foreign key constraints in table film_actor (see in the Database structure tab).
Is the definition of the foreign key constraint to table film coherent with the behavior observed in the previous exercise?
Solution
The foreign key linking table film_actor to table film is defined with the option RESTRICT on delete. This is coherent with the behavior that we observed in the previous exercise. Referenced rows cannot be deleted if there are still referencing rows.
Exercise
Exercise 2.4 Execute the following query:
SELECT f.film_id as film_id_in_table_film,
fa.film_id AS film_id_in_table_film_actor,
fa.actor_id as actor_id, f.title as title
FROM film f JOIN film_actor fa USING (film_id)
WHERE f.title='ACE GOLDFINGER'
What does the query? Note down the identifier of the film in both tables film and film_actor. (columns film_id_in_table_film and film_id_in_table_film_actor).
Solution
The query returns the list of all actors in the film titled Ace Goldfinger. We note that the identifier of the film in both tables is identical (2), as it should because the query joins the two tables on the equality of these two values.
Exercise
Exercise 2.5 Write and execute a statement to set the value 10000 to the identifier of the film ACE GOLDFINGER in table Film.
After the modification, execute the query of the previous exercise. What changed? Explain in details what happened.
Solution
The statement to modify the film_id of the given film is as follows:
UPDATE film SET film_id=10000 WHERE title=‘ACE GOLDFINGER’
After executing the same query as the previous exercise, we see that the identifier of the film has changed in the table film_actor too. This is expected, because the foreign key constraint between the colum film_id in table film_actor and the column film_id in table film has the option ON UPDATE CASCADE. This means that if we modify the identifier of the film in table film, the modification is propagated to all the referencing columns.
Exercise
Exercise 2.6 Execute the following statement:
UPDATE film_actor SET film_id=2 WHERE film_id=10000
What does it? What happens? Explain.
Solution
The statement intends to set the identifier of the film titled Ace Goldfinger (in the previous exercise we gave it the identifier 10000) back to its original value. However, we execute the statement on the table film_actor. The action is not allowed, as the identifier 2 does not correspond to any film in table film.
The foreign key enforces the referential integrity constraint. A row cannot refer to a non-existing entity in the referenced table.
3 Basic queries
Exercise
Exercise 3.1 Write the following SQL queries:
Return all the information on all customers.
Return the first and last name of all customers.
- Return the first and last name of all customers of the store with identifier 1.
Solution
select * from customer
select first_name, last_name from customer
select first_name, last_name from customer where store_id=1
4 Sorting and paginating
Exercise
Exercise 4.1 Write the following SQL queries:
Return the last and first name of all customers. Sort by last name in ascending order.
Same as in 1., but only return the first 100 customers.
- Return the last and first name of all customers of the store with identifier 1. Sort by last name in ascending order and by first name in descending order.
Solution
select last_name, first_name from customer order by last_name asc
select last_name, first_name from customer order by last_name asc, first_name DESC limit 100
select first_name, last_name from customer where store_id=1
5 Aggregating queries
Exercise
Exercise 5.1 Write the following SQL queries:
Count the number of films in the database (expected result: 1000).
How many distinct actor last names are there?
Compute the total amount of payments across all rentals (expected result: 67416.51).
Compute the average, minimum and maximum duration of rental across all films (expected result: 4.9850000000000000, 3, 7).
Return the number of actors for each film.
Return the number of copies of each film in each store (table inventory).
Same as 6., but only returns the pairs (film, store) if the number of copies is greater than or equal to 3.
Solution
select count(*) from film
select count (distinct last_name) from actor
select sum(amount) from payment
select avg(rental_duration), min(rental_duration), max(rental_duration) from film
select film_id, count(*) as nb_actors from film_actor group by film_id
select film_id, store_id, count(*) as nb_films from inventory group by film_id, store_id
select film_id, store_id, count(*) as nb_films from inventory group by film_id, store_id having count(*) >=3
6 Join queries
Exercise
Exercise 6.1 Write the following SQL queries:
Return the first and last name of the manager of the store with identifier 1 (expected result: Mike Hillyer).
Return the first and last name of the actors in the film ACE GOLDFINGER.
Return first and last name of each actor and the number of films in which they played a role.
Same as in 3., but order by number of films in descending order.
Same as in 4., but only return actors who played a role in at least 10 films.
- Return the identifier, the first and family name of the customers who have rented between 5 and 10 movies in the category Family.
Solution
select first_name, last_name from staff join store using(store_id) where store_id=1
select first_name, last_name from film join film_actor using(film_id) join actor using(actor_id) where title='ACE GOLDFINGER'
select first_name, last_name, count(*) as nb_films from actor join film_actor using(actor_id) group by actor_id
select first_name, last_name, count(*) as nb_films from actor join film_actor using(actor_id) group by actor_id order by nb_films desc
select first_name, last_name, count(*) as nb_films from actor join film_actor using(actor_id) group by actor_id having count(*) >= 10 order by nb_films desc
select cust.customer_id, first_name, last_name, count(*) as nb_films from customer cust join rental using(customer_id) join inventory using(inventory_id) join film_category using(film_id) join category cat using(category_id) where cat.name='Family' group by customer_id having count(*) between 5 and 10