Question

1. Using the Sakila database, write a query that shows the first_name and last_name of actors that have not appeared in a film.

2. Using the Sakila database, write a query that shows the film title and category name corresponding to the film. If the category is null, display “Unknown” rather than null.

3. Using the Sakila database, write a query that shows the number of films per rating that have special_features with commentaries.

4. Using the Sakila database, write a query to show the customers first_name, last_name and the amount paid in overage fees. The overage fees can be calculated by subtracting the payment amount from the rental rate of the film when the payment amount is greater than the rental rate.

Answer 1

Q1. Select first_name, last_name from actor where actor_id not in (select actor_id from film_actor);

Q2. Select f.title, ifnull(c.name, "Unknown") from film f left join film_category fc on (f.film_id = fc.film_id) left join category c on (c.category_id = fc.category_id);

Q3. Select rating, count(*) from film where special _features ="Commentaries" group by rating;

Q4. Select c.first_name, c.last_name, sum(f.rental_rate - p.amount) from customer c inner join payment p on (c.customer_id = p.customer_id) inner join rental r on (r.rental_id = p.rental_id and r.customer_id = c.customer_id) inner join inventory i on (i.inventory_id = r.rental_id) inner join film f on (f.film_id = i.film_id) where f.rental_rate > p.amount group by c.first_name, c.last_name;