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?

Exercise 2.2 Write the statement to delete the film with film_id=1 from the table Film.

Execute the command. What happens?

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?

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

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.

Exercise 2.6 Execute the following statement:

UPDATE film_actor
SET film_id=2
WHERE film_id=10000

What does it? What happens? Explain.

Exercise 3.1 Write the following SQL queries:

1. Return all the information on all customers.

2. Return the first and last name of all customers.

3. Return the first and last name of all customers of the store with identifier 1.

Exercise 4.1 Write the following SQL queries:

1. Return the last and first name of all customers. Sort by last name in ascending order.

2. Same as in 1., but only return the first 100 customers.

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

Exercise 5.1 Write the following SQL queries:

1. Count the number of films in the database (expected result: 1000).

2. How many distinct actor last names are there?

3. Compute the total amount of payments across all rentals (expected result: 67416.51).

4. Compute the average, minimum and maximum duration of rental across all films (expected result: 4.9850000000000000, 3, 7).

5. Return the number of actors for each film.

6. Return the number of copies of each film in each store (table inventory).

7. Same as 6., but only returns the pairs (film, store) if the number of copies is greater than or equal to 3.

Exercise 6.1 Write the following SQL queries:

1. Return the first and last name of the manager of the store with identifier 1 (expected result: Mike Hillyer).

2. Return the first and last name of the actors in the film ACE GOLDFINGER.

3. Return first and last name of each actor and the number of films in which they played a role.

4. Same as in 3., but order by number of films in descending order.

5. Same as in 4., but only return actors who played a role in at least 10 films.

6. Return the identifier, the first and family name of the customers who have rented between 5 and 10 movies in the category Family.