View 활용해보기

CREATE OR REPLACE VIEW customer_sales AS
SELECT
	c.name AS customer,
	TO_CHAR(a.order_date, 'YYYY') AS YEAR,
	SUM(b.quantity * b.unit_price) sales_amount
	FROM ORDERS A,
	ORDER_ITEMS B,
	customers C
WHERE 1=1
	AND a.STATUS = 'Shipped'
	AND a.ORDER_ID = b.ORDER_ID 
	AND a.CUSTOMER_ID = c.CUSTOMER_ID 
GROUP BY c.NAME, TO_CHAR(a.ORDER_DATE, 'YYYY') 
ORDER BY c.NAME;


SELECT * FROM customer_sales;

SELECT 
	customer,
	sales_amount
FROM 
	customer_sales
WHERE 
	YEAR = 2017
ORDER BY 
	sales_amount DESC;

SELECT 
	customer,
	sum(sales_amounts) AS sum_sales_amount
FROM customer_sales 
GROUP BY CUSTOMER 
ORDER BY sum_sales_amount DESC;