-- Problem #1
USE ap;

SELECT
	product_code,
    product_name,
    list_price,
    discount_percent

FROM my_guitar_shop.products

ORDER BY list_price DESC;

-- Problem #2
SELECT CONCAT(last_name, ', ', first_name)
	AS Full_Name FROM my_guitar_shop.customers
WHERE 'M' < last_name
ORDER BY last_name ASC;

-- Problem #3
SELECT
	product_name,
    list_price,
    date_added
FROM my_guitar_shop.products
WHERE list_price > 500 and list_price < 2000
ORDER BY date_added DESC;

-- Problem #4
SELECT
	product_name,
    list_price,
    discount_percent,
    CAST((list_price * (discount_percent/100)) AS DECIMAL(6,2)) AS discount_amount,
    CAST(list_price - (list_price * (discount_percent/100)) AS DECIMAL(6,2)) AS discount_price
FROM my_guitar_shop.products
ORDER BY discount_price DESC
LIMIT 5;

-- Problem #5
SELECT
	item_id,
    item_price,
    discount_amount,
    quantity,
    (item_price * quantity) as price_total,
    (discount_amount * quantity) as discount_total,
    (((item_price * quantity) -  (discount_amount * quantity))) AS item_total
FROM my_guitar_shop.order_items
WHERE (((item_price * quantity) -  (discount_amount * quantity)) * quantity) > 500
ORDER BY (((item_price * quantity) -  (discount_amount * quantity)) * quantity) DESC;

-- Problem #6

SELECT
	order_id,
    order_date,
    ship_date
FROM my_guitar_shop.orders
Where ship_date is null;

-- Problem #7

SELECT
	NOW() as today_unformatted,
    DATE_FORMAT(NOW(),'%d-%b-%Y') as today_formatted

FROM my_guitar_shop.orders;

-- Problem #8
SELECT
	[100 AS price],
	.07 as tax_rate,
    (price * tax_rate) as tax_amount