exercises-14.sql

-- Exercise 14
use sakila;

-- Exercise 14.1
CREATE VIEW film_ctgry_actor AS
	SELECT f.title, c.name, a.first_name, a.last_name
    FROM film f
		INNER JOIN film_category fc ON f.film_id = fc.film_id
        INNER JOIN category c ON fc.category_id = c.category_id
        INNER JOIN film_actor fa ON fa.film_id = f.film_id
        INNER JOIN actor a ON a.actor_id = fa.actor_id;
        
SELECT * FROM film_ctgry_actor WHERE last_name = 'FAWCETT';

-- Exercise 14.2
-- using scalar subquery to calculate 'tot_payments'
CREATE VIEW total_customer_sales_per_country
AS
select ctr.country, (
	select SUM(p.amount)
	from city ct
		inner join address a on a.city_id = ct.city_id
		inner join customer c on c.address_id = a.address_id
		inner join payment p on p.customer_id = c.customer_id
	where ct.country_id = ctr.country_id) tot_payments
from country ctr;

-- the subquery above will execute for each row in the table
-- using the country_id from primary query to distinguish
-- each country: "correlated sub-query"
select * from total_customer_sales_per_country;		-- 109 rows returned


-- Another way without correlated subquery
CREATE OR REPLACE VIEW total_customer_sales_per_country2
AS
WITH country_address AS (
	select
		ct.country_id,
		SUM(p.amount) as tot_payments
	from city ct
		inner join address a on a.city_id = ct.city_id
		inner join customer c on c.address_id = a.address_id
		inner join payment p on p.customer_id = c.customer_id
	group by ct.country_id
)
select c.country, ca.tot_payments
from country_address ca
	right join country c				-- we must consider every country, regardless of wethere there are payments in that country or not
		on c.country_id = ca.country_id;

-- Test
select * from total_customer_sales_per_country2;	-- 109 rows returned