-- Name: Austin Wang
-- Email: austin.w.wang@vanderbilt.edu
-- HW #2


-- 1. Returns a columm from Customers table named full_name
--    that joins the last_name and first_name columns
--    formatted as (e.g. Doe, John)
--    Sort by last name in ascending order
--    Return only customers w/ last names starting with A-S
SELECT CONCAT_WS(', ', last_name, first_name) AS 'full_name'
FROM customers
WHERE LEFT(last_name, 1) <= 'S'
ORDER BY last_name ASC;


-- 2. Returns columns from Products table as follows:
-- 			product_name		The product_name column
-- 			list_price			The list_price column
--    Return only rows w/ list price >700 and <2000
--    Must use BETWEEN in WHERE to get full credit
--    Sort by date_added in DESC order
SELECT product_name, list_price
FROM products
WHERE list_price BETWEEN 700 AND 2000
ORDER BY date_added DESC;


-- 3. Returns the following columns and data from Products
-- 			product_name		The product_name column
-- 			list_price			The list_price column
-- 			discount_percent	The discount_percent column
-- 			discount_amount		Calculated from previous two columns
-- 			discount_price		Calculated from previous three columns
--    Round discount_amount & discount_price to 2 decimal places
--    Sort by discount_price in descending sequence
--    Use LIMIT to restrict to first 5 rows
SELECT product_name, list_price, discount_percent,
	   ROUND((list_price * discount_percent * 0.01), 2) AS 'discount_amount',
       ROUND((list_price - (list_price * discount_percent * 0.01)), 2) AS 'discount_price'
FROM products
ORDER BY discount_price DESC
LIMIT 5;


-- 4. Returns the following columns from Orders
-- 			order_id		The order_id column
-- 			order_date		The order_date column
-- 			order_date_formatted	Day-Mon-Year format (e.g. 2-Aug-2021)
--    Return only rows where ship_date contains null
--    (Hint: use IS NULL)
--    Must put each attribute in SELECT clause on a separate line
--    and tab them (2 times)
SELECT
		order_id,
		order_date,
        DATE_FORMAT(order_date, '%d-%b-%Y') order_date_formatted
FROM orders
WHERE ship_date IS NULL;


-- 5. Joins Customers to Addresses and returns the following columns:
--    first_name, last_name, line1, city, state, zip_code
--    Return one row for each customer
--    Only return addresses that are the shipping address for a customer
SELECT first_name, last_name, line1, city, state, zip_code
FROM customers
	JOIN addresses
    ON customers.shipping_address_id = addresses.address_id;

-- NOTE: I got 2 points off for this. Should instead JOIN the two tables using customer_id, then use a WHERE statement to specify that the shipping address = address.
    
    
-- 6. Joins Customers, Orders, Order_Items, Products
--    Return these columns:
-- 		last_name, first_name, order_date,
-- 		product_name, item_price, discount_amount, quantity
--    Must use table aliases
--    Sort by last_name, order_date, and product_name
SELECT last_name, first_name, order_date,
	   product_name, item_price, discount_amount, quantity
FROM customers
	JOIN orders ON customers.customer_id = orders.customer_id
    JOIN order_items ON orders.order_id = order_items.order_id
    JOIN products ON order_items.product_id = products.product_id
ORDER BY last_name, order_date, product_name;


-- 7. Return product_name and list_price from Products
--    Return one row for each product that has
--    the same list_price as another product
--    Sort by product name
SELECT DISTINCT P1.product_name, P1.list_price
FROM products P1
	JOIN products P2 ON P1.list_price = P2.list_price
WHERE P1.product_name != P2.product_name
ORDER BY P1.product_name;


-- 8. Returns the following columns
-- 		category_name	The category_name column from Categories
-- 		product_id		The product_id column from Products
--    Return one row for each category that has never been used
SELECT category_name, product_id
FROM categories
	LEFT JOIN products ON categories.category_id = products.category_id
WHERE product_id IS NULL;