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
    	AND c.shipping_address_id = 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 i
    		ON i.order_id = o.order_id
    	JOIN products p 
    		ON p.product_id = i.product_id
    ORDER BY last_name, order_date, product_name;
        
    -- Exercise 5

    SELECT p.product_name,
    	p.list_price
    FROM products p JOIN products p2
    	ON p.list_price = p2.list_price AND p.product_id <> p2.product_id
    ORDER BY product_name;

    -- Exercise 6

    SELECT c.category_name,
    	p.product_id
    FROM categories c LEFT 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
    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;