guitar-shop-database/How_to_retrive_data_from_two_or_more_tables.sql

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

/*2*/
SELECT 
	first_name,
    last_name,
    line1,
    city,
    state,
    zip_code
FROM 
	addresses a
JOIN
	customers c
ON 
	a.customer_id = c.customer_id
WHERE 
	email_address LIKE "allan.sherwood%";
    
/*3*/
SELECT
    first_name,
    last_name,
    line1,
    city,
    state,
    zip_code
FROM 
	addresses a
JOIN
	customers c
ON 
	a.customer_id = c.customer_id
WHERE
	address_id = shipping_address_id;
    
/*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 i
ON 
    o.order_id = i.order_id
JOIN
	products p
ON
	i.product_id = p.product_id
ORDER BY 
	last_name, order_date, product_name;
    
/*5*/
SELECT 
	p1.product_name,
    p2.list_price
FROM 
	products p1
JOIN 
	products p2
ON 
	p1.product_id <> p2.product_id
    AND p1.list_price = p2.list_price
ORDER BY product_name;

/*6*/
SELECT 
	category_name,
    product_id
FROM
	categories c
LEFT OUTER JOIN 
	products p
ON
	c.category_id = p.category_id
WHERE 
	product_id IS NULL;
    
/*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;