SQL Assignment 2 ch 4-5

USE my_guitar_shop;

-- Chapter 4 Exercise 1

SELECT category_name, product_name, list_price
FROM categories c INNER JOIN products p
    ON c.category_id = p.category_id
ORDER BY category_name, product_name ASC;

-- Chapter 4 Exercise 2

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

-- Chapter 4 Exercise 3

SELECT first_name, last_name, line1, city, state, zip_code
FROM customers c INNER JOIN addresses a
    ON c.customer_id = a.customer_id
WHERE shipping_address_id = address_id;


-- Chapter 4 Exercise 4

SELECT c.last_name, c.first_name, o.order_date, p.product_name, oi.item_price, oi.discount_amount, oi.quantity
FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    INNER JOIN order_items oi on o.order_id = oi.order_id
    INNER JOIN products p on oi.product_id = p.product_id
ORDER BY c.last_name, o.order_date, p.product_name;


-- Chapter 4 Exercise 5

SELECT p1.product_name, p1.list_price, p2.list_price
FROM products p1
    JOIN products p2 ON p1.list_price = p2.list_price
WHERE p1.product_id != p2.product_id ;


-- Chapter 4 Exercise 6

SELECT category_name, p.product_name
FROM categories
    left join products p on categories.category_id = p.category_id
WHERE p.product_name IS NULL;


-- Chapter 4 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;



--===================================


USE my_guitar_shop;


-- Chapter 5: Exercise 1

INSERT INTO categories (category_name)
VALUES ('Brass');


-- Chapter 5: Exercise 2

UPDATE categories
SET category_name = 'Woodwinds'
WHERE category_id = 5;


-- Chapter 5: Exercise 3

DELETE FROM categories
WHERE category_id = 5;


-- Chapter 5: Exercise 4

INSERT INTO products (category_id, product_code, product_name, description, list_price, discount_percent, date_added)
VALUES ('4','dgx_640','Yamaha DGX 640 88-Key Digital Piano','Long description to come.',799.99,0,NOW());


-- Chapter 5: Exercise 5

UPDATE products
SET discount_percent = 35
WHERE product_id = 11;


-- Chapter 5: Exercise 6

DELETE FROM products WHERE category_id = 4;
DELETE FROM categories WHERE category_name = 'Keyboards';


-- Chapter 5: Exercise 7

INSERT INTO customers (email_address, password, first_name, last_name)
VALUES ('rick@raven.com', '', 'Rick', 'Raven');


-- Chapter 5: Exercise 8

UPDATE customers
SET password = 'secret'
WHERE customer_id = 9;


-- Chapter 5: Exercise 9

UPDATE customers
SET password = 'reset'
WHERE customer_id IS NOT NULL;