-- Joins and Functions exercise 1

select distinct c.first_name, c.last_name from customers c, products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id and c.id = o.customer_id
	and p.category = 'CPU'
	and extract( MONTH from date_ordered ) =
	extract( MONTH from date_sub( current_date(), interval 1 month ) )
	and year(date_ordered) = year( date_sub( current_date(), interval 1 month) );

-- Joins and Functions exercise 2

select distinct c.first_name, c.last_name from customers c, products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	and p.category = 'CPU' and p.category = 'HD'
	and extract( MONTH from date_ordered ) >
	extract( MONTH from date_sub( current_date(), interval 1 month ) )
	and year(date_ordered) = year( date_sub( current_date(), interval 1 month) );

-- Joins and Functions exercise 3

select distinct c.first_name, c.last_name from customers c, products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	and year(o.date_ordered) <= '2004-12-31';
	
-- Joins and Functions exercise 4

SELECT description
	FROM products p
	LEFT JOIN (
	SELECT *
	FROM orders o, order_details d
	WHERE o.id = d.order_id and MONTH(o.date_ordered) >= MONTH(date_sub(current_date(), interval 3 month))
	)a ON p.id = a.product_id where a.order_id is null;

-- Joins and Functions exercise 5

select * from products p, orders o, order_details d, customers c
	where p.id = d.product_id and d.order_id = o.id
	and c.first_name = 'John' and c.last_name = 'Doe'
	order by o.date_ordered, date_shipped;
	
-- Joins and Functions exercise 6

select p.description, p.price
	from products p, products s
	where s.description = 'Intel Pentium D'
	and p.price > s.price;

-- Joins and Functions exercise 7

select * 
	from products p, orders o, order_details d, customers c
	where p.id = d.product_id and d.order_id = o.id
	and date_ordered > 
	date_sub( current_date(), interval 1 day )
	and c.address like '%90001';
	
-- Aggregations exercise 1

select 
	month(o.date_ordered) as Month,
	year(date_ordered) as Year,
	sum(p.price*d.quantity) as Price		
	from products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	and o.date_ordered between '2011-01-01' AND current_date()
	group by month(o.date_ordered),
	year(o.date_ordered);
	
-- Aggregations exercise 2

select 
	o.id AS Order_ID, 
	sum(d.quantity) AS Total_Items,
	sum(p.price*d.quantity) AS Total_Amount
	from products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	group by o.id;

-- Aggregations exercise 3

select p.description as Three_Top_Products, d.quantity as Quantity
	from products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	and extract( MONTH from date_ordered ) =
	extract( MONTH from date_sub( current_date(), interval 1 month ) )
	and year(date_ordered) = year( date_sub( current_date(), interval 1 month) ) 
	order by d.quantity limit 0,3;
	
-- Aggregations exercise 4

select p.description as Three_Top_Products, sum(p.price*d.quantity) AS Total_Revenue
	from products p, orders o, order_details d
	where p.id = d.product_id and d.order_id = o.id
	and extract( MONTH from date_ordered ) =
	extract( MONTH from date_sub( current_date(), interval 1 month ) )
	and year(date_ordered) = year( date_sub( current_date(), interval 1 month) ) 
	group by p.id order by sum(p.price*d.quantity) limit 0,3;

-- Aggregations exercise 5

SELECT c.first_name, c.last_name, COUNT( * )
	FROM customers c, orders o
	WHERE c.id = o.customer_id
	AND EXTRACT( YEAR
	FROM date_ordered ) = date_sub( current_date( ) , INTERVAL 1 year )
	GROUP BY o.customer_id;

-- Aggregations exercise 6

select c.first_name as First_Name, c.last_name as Last_Name, sum(p.price*d.quantity) AS Dollar_Amount
	from products p, orders o, order_details d, customers c
	where c.id = o.customer_id and p.id = d.product_id and d.order_id = o.id
	and year( date_sub( current_date(), interval 1 year ) ) 
	= year(o.date_ordered) group by c.id order by sum(p.price*d.quantity) limit 0,3;