my-guitar-shop-database-exersices/How-to-retrieve-data-from-two-or-more-tables.sql

-- Exercise 1 --
SELECT 
	category_name, 
    product_name, 
    list_price
FROM products p JOIN categories c 
	ON p.category_id = c.category_id
ORDER BY category_name, product_name;

-- Exercise 2 --
SELECT
	first_name, 
    last_name, 
    line1, 
    city,
    state, 
    zip_code
FROM customers c JOIN addresses a 
	ON c.customer_id = a.customer_id
WHERE email_address LIKE 'allan.sherwood@yahoo.com';

-- Exercise 3 --
SELECT
	first_name, 
    last_name, 
    line1, 
    city,
    state, 
    zip_code
FROM addresses a JOIN customers c 
	ON c.customer_id = a.customer_id AND c.shipping_address_id = address_id;

-- Exercise 4 --
SELECT 
	last_name AS 'Last Name',
	first_name AS 'Fist Name',
	order_date AS 'Order Date',
	product_name AS 'Product Name',
	item_price AS 'Item Price', 
	discount_amount AS 'Discount Amount',
	quantity AS 'Quantity'
FROM customers c 
	JOIN orders o 
		ON c.customer_id = o.customer_id 
    JOIN order_items oi 
		ON  oi.order_id =  o.order_id
	JOIN products p
		ON p.product_id = oi.product_id
ORDER BY last_name, order_date, product_name;


-- Exercise 5 --
SELECT 
    p.list_price, 
    p.product_name
FROM products p INNER JOIN products p2
	ON p.list_price = p2.list_price
WHERE p.product_id != p2.product_id
ORDER BY product_name;

-- Exercise 6 --
SELECT
	category_name,
    product_id
FROM categories c LEFT OUTER JOIN products p 
	ON c.category_id = p.category_id
WHERE p.product_id IS NULL;

-- Exercise 7 --
SELECT
	'SHIPPED' AS ship_status,
    order_id,
    order_date
FROM orders o
WHERE ship_date IS NOT NULL

		UNION
    
SELECT 
	'NOT SHIPPED' AS ship_status,
	order_id,
    order_date
FROM orders o
WHERE ship_date IS NULL
ORDER BY order_date;