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.

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.

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.

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.

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.

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.

1. select * 
   from customer
   

2. select first_name, last_name 
   from customer
   

3. select first_name, last_name 
   from customer
   where store_id=1
   

1. select last_name, first_name 
   from customer
   order by last_name asc
   

2. select last_name, first_name 
   from customer
   order by last_name asc
   limit 100
   

3. select first_name, last_name 
   from customer
   where store_id=1
   

1. select count(*) 
   from film
   

2. select  count (distinct last_name) 
   from actor
   

3. select sum(amount) 
   from payment
   

4. select avg(rental_duration), min(rental_duration), max(rental_duration) 
   from film
   

5. select film_id, count(*) as nb_actors
   from film_actor
   group by film_id
   

6. select film_id, store_id, count(*) as nb_films
   from inventory
   group by film_id, store_id
   

7. select film_id, store_id, count(*) as nb_films
   from inventory
   group by film_id, store_id
   having count(*) >=3
   

1. select first_name, last_name
   from staff join store using(store_id)
   where store_id=1
   

2. select first_name, last_name
   from film join film_actor using(film_id) join actor using(actor_id)
   where title='ACE GOLDFINGER'
   

3. select first_name, last_name, count(*) as nb_films
   from actor join film_actor using(actor_id)
   group by actor_id
   

4. 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
   

5. 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
   

6. 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
   

1. select last_name
   from actor
   group by last_name
   having count(*) = 1
   

2. select distinct i.inventory_id
   from film f join inventory i using(film_id)
   join rental r using(inventory_id)
   where f.title='ACADEMY DINOSAUR' 
       and i.store_id=1 
       and r.return_date is not null  
   

3. select  distinct f.title, f.release_year
   from film f join film_category using(film_id)
   join category cat using(category_id)
   where cat.name in ('Family', 'Animation', 'Documentary')
   

4. select customer_id, first_name, last_name
   from customer
   where last_name LIKE 'L%'
   

5. select concat(first_name, ' ', last_name), sum(amount)
   from customer join payment using (customer_id)
   group by customer_id
   order by last_name asc
   

6. select country, sum(amount) as revenue
   from payment join rental using (rental_id)
     join inventory using (inventory_id)
     join store using (store_id)
     join address using (address_id)
     join city using (city_id)
     join country using (country_id)
   group by country_id
   order by revenue desc
   

7. select actor_id, first_name, last_name, count() from film_actor join actor using(actor_id) group by actor_id having count() = (select max(nb_films) from (select count(*) as nb_films from film_actor group by actor_id) t)