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 categories c JOIN products p
ON c.category_id = p.category_id
ORDER BY category_name, product_name ASC;


-- 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 customers c JOIN addresses a
ON c.customer_id = a.customer_id
WHERE c.shipping_address_id = a.address_id;

-- Exercise 4
SELECT 
last_name, 
first_name, 
order_date, 
product_name, 
item_price, 
discount_amount, 
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 
p1.product_name, 
p1.list_price
FROM products p1 JOIN products p2
ON p1.list_price = p2.list_price
AND p1.product_id <> p2.product_id;

-- Exercise 6
SELECT *
FROM categories c JOIN products p
ON p.category_id = c.category_id
WHERE p.product_id IS NULL;

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