Learning SQL queries
First, download DB Browser for SQLite at this link.
1 Obtain the data
We consider the database of DVD rental store containing data on films, actors, customers and the transactions of the store.
You can obtain the data at this link.
The following figure shows the physical schema of the database.
Figure 1.1: The physical schema of the database
You should now open the database with DB Browser for SQLite. To this extent, open DB Browser for SQLite, click on Open database and select the downloaded file.
Foreign key alert
By default, SQLite doesn’t check foreign key constraints. Open the preferences of DB Browser for SQLite and make sure the checkbox Foreign keys in the tab Database is checked.
2 Foreign key constraints
Exercise
Exercise 2.1 Try to delete the film with film_id=1 from the table Film. What happens?
Exercise
Exercise 2.2 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 ON f.film_id = fa.film_id WHERE f.title='ACE GOLDFINGER'
What does the query? Note down the identifier of the film in both tables (columns film_id_in_table_film and film_id_in_table_film_actor).
Exercise
Exercise 2.3 Modify the identifier of the film ACE GOLDFINGER in table Film. Write the query of the previous exercise. What happens?
3 Queries
Exercise
Exercise 3.1 Write the following queries in SQL:
Q1. Return the first and last names of all the actors.
Q2. Return the title and the language of each film.
Q3. Return the first and the last name of the manager of the store with code 2.
Q4. Return the first and last names of all the actors who performed in the movie ‘ANGELS LIFE’.
Q5. Return the number of films where each actor performed. Sort the results in descending order.
Q6. Return the film categories that contain between 25 and 55 films.
Q7. Return the first and family name of the customers who have rented more than five family movies.