SQL Quiz 1

USE my_guitar_shop;

-- Chapter 6 Exercise 1
-- ------------------------------

SELECT
    COUNT(order_id) as number_of_orders,
    SUM(tax_amount) as tax_amount
FROM
    orders;


-- Chapter 6 Exercise 2
-- ------------------------------

SELECT
    category_name,
    COUNT(*) as product_count,
    MAX(list_price) as most_expensive
FROM
    categories
INNER JOIN
    products p on categories.category_id = p.category_id
GROUP BY
    category_name ;


-- Chapter 6 Exercise 3
-- -------------------------------

SELECT
    email_address,
    SUM(oi.item_price * oi.quantity) AS price,
    SUM(discount_amount*oi.quantity) as discount
FROM
    customers
INNER JOIN
    orders o on customers.customer_id = o.customer_id
INNER JOIN
    order_items oi on o.order_id = oi.order_id
GROUP BY
    email_address
ORDER BY
    price DESC;


-- Chapter 6 Exercise 4
-- ----------------------------------


SELECT
    email_address,
    COUNT(DISTINCT o.order_id) as 'orders',
    SUM((item_price - discount_amount)*quantity) as OrderTotal
FROM customers
JOIN orders o on customers.customer_id = o.customer_id
JOIN order_items oi on o.order_id = oi.order_id
GROUP BY email_address
HAVING orders > 1
ORDER BY OrderTotal DESC;


-- Chapter 6 Exercise 5
-- ----------------------------------

SELECT
    email_address,
    COUNT(DISTINCT o.order_id) as 'orders',
    SUM(item_price) - sum(discount_amount) as OrderTotal
FROM customers
JOIN orders o on customers.customer_id = o.customer_id
JOIN order_items oi on o.order_id = oi.order_id
WHERE item_price > 400
GROUP BY email_address
HAVING orders > 1
ORDER BY orders DESC;


-- Chapter 6 Exercise 6
-- ----------------------------------

SELECT
    product_name,
    COUNT(*) as transactions, -- just added this for testing (not in question)
    SUM((item_price - discount_amount)*quantity) as totals
FROM products
JOIN order_items oi on products.product_id = oi.product_id
GROUP BY product_name WITH ROLLUP;


-- Chapter 6 Exercise 7
-- ----------------------------------

SELECT
    email_address,
    COUNT(DISTINCT product_id) as total_products
FROM customers
JOIN orders o on customers.customer_id = o.customer_id
JOIN order_items oi on o.order_id = oi.order_id
GROUP BY email_address
HAVING total_products > 1
ORDER BY email_address ASC;



-- Chapter 6 Exercise 8
-- ----------------------------------

SELECT
    IF (GROUPING(category_name) = 1, 'GRAND TOTAL', category_name) as Category,
    IF (GROUPING(product_name) = 1, '-------------->', product_name) as Product,
    SUM(quantity) as total
FROM categories c
JOIN products p on c.category_id = p.category_id
JOIN order_items oi on p.product_id = oi.product_id
GROUP BY c.category_name, p.product_name WITH ROLLUP
HAVING GROUPING(p.product_name) =1;