exercises-09.sql

-- Exercises 9

use sakila;

-- 9.1
SELECT title FROM film
WHERE film_id IN (
	SELECT fc.film_id FROM film_category fc
    INNER JOIN category c ON c.category_id = fc.category_id
	WHERE c.name = 'Action'
);

-- 9.2
SELECT f.title FROM film f
WHERE f.film_id IN (
	SELECT fc.film_id FROM film_category fc
    INNER JOIN category c ON c.category_id = fc.category_id
    WHERE fc.film_id = f.film_id AND c.name = 'Action'
);

-- 9.3
WITH actor_roles_count AS (
	SELECT
		a.actor_id,
		CONCAT(a.first_name, ', ', a.last_name) AS fullname,
		COUNT(fa.film_id) AS roles
	FROM actor a
		INNER JOIN film_actor fa ON fa.actor_id = a.actor_id
	GROUP BY a.actor_id, fullname
	ORDER BY roles ASC
)
SELECT actor_id, fullname,
	CASE
		WHEN roles BETWEEN 30 AND 99999 THEN 'Hollywood Star'
		WHEN roles BETWEEN 20 AND 29 THEN 'Prolific Actor'
		WHEN roles BETWEEN 1 AND 19 THEN 'Newcomer'
	END AS level
FROM actor_roles_count
ORDER BY actor_id;