--1  
    SELECT items.item_id, items.name, items.price, item_category.name
    FROM items
    INNER JOIN item_category
    ON items.category_id = item_category.category_id
    ORDER BY items.item_id;
    
--2
    SELECT orders.order_id, orders.order_time, users.name, items.name, items.price, order_items.quantity
    FROM orders
    INNER JOIN users
    ON orders.user_id = users.user_id
    INNER JOIN order_items
    ON orders.order_id = order_items.order_id
    INNER JOIN items
    ON order_items.item_id = items.item_id
    ORDER BY orders.order_time;
    
--3
    SELECT orders.order_id, orders.order_time,  users.name, SUM(items.price * order_items.quantity) AS total_price
    FROM orders
    INNER JOIN users
    ON orders.user_id = users.user_id
    INNER JOIN order_items
    ON orders.order_id = order_items.order_id
    INNER JOIN items
    ON order_items.item_id = items.item_id
    GROUP BY orders.order_id, orders.order_time, users.name
    ORDER BY orders.order_time;
    
--4
    SELECT DATE_FORMAT(orders.order_time, '%Y/%m') AS order_month, SUM(order_items.quantity) AS order_count
    FROM orders
    INNER JOIN order_items
    ON orders.order_id = order_items.order_id
    GROUP BY DATE_FORMAT(orders.order_time, '%Y/%m')
    ORDER BY DATE_FORMAT(orders.order_time, '%Y/%m');
    
--5
    SELECT DATE_FORMAT(orders.order_time, '%Y') AS order_year, SUM(items.price * order_items.quantity) AS sales_amount
    FROM orders
    INNER JOIN order_items
    ON orders.order_id = order_items.order_id
    INNER JOIN items
    ON order_items.item_id = items.item_id
    GROUP BY DATE_FORMAT(orders.order_time, '%Y')
    ORDER BY DATE_FORMAT(orders.order_time, '%Y');
    
--6
    SELECT SUM(items.price * order_items.quantity) AS sales_amount
    FROM order_items
    INNER JOIN items
    ON order_items.item_id = items.item_id;
    
--7
    SELECT item_category.name, SUM(items.price * order_items.quantity) AS sales_amount
    FROM order_items
    INNER JOIN items
    ON order_items.item_id = items.item_id
    INNER JOIN item_category
    ON items.category_id = item_category.category_id
    GROUP BY item_category.name;
    
--8
    SELECT items.name, SUM(items.price * order_items.quantity) AS sales_amount
    FROM order_items
    INNER JOIN items
    ON order_items.item_id = items.item_id
    GROUP BY items.name
    ORDER BY sales_amount DESC;
    
--9
    SELECT item_category.name, SUM(items.price * order_items.quantity)
        / (SELECT SUM(items.price * order_items.quantity)
            FROM order_items
            INNER JOIN items
            ON order_items.item_id = items.item_id)
        * 100 AS ratio
    FROM order_items
    INNER JOIN items
    ON order_items.item_id = items.item_id
    INNER JOIN item_category
    ON items.category_id = item_category.category_id
    GROUP BY item_category.name
    ORDER BY ratio DESC;
    
--10
    SELECT items.name, SUM(items.price * order_items.quantity)
        / (SELECT SUM(items.price * order_items.quantity)
            FROM order_items
            INNER JOIN items
            ON order_items.item_id = items.item_id)
        * 100 AS ratio
    FROM order_items
    INNER JOIN items
    ON order_items.item_id = items.item_id
    GROUP BY items.name
    ORDER BY ratio DESC;