Untitled

-- 17.
SELECT
    c.name AS customer_name,
    p.name AS product_name,
    oi.amount,
    oi.price
FROM
    orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON oi.product_id = p.id;

-- 18.
SELECT c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_purchase DESC
LIMIT 5;

-- 19.
SELECT
    customers.name AS CustomerName,
    SUM(order_items.price) AS TotalQuantity
FROM
    customers
INNER JOIN
    orders ON customers.id = orders.customer_id
INNER JOIN
    order_items ON orders.id = order_items.order_id
GROUP BY
    customers.id, customers.name
HAVING
    TotalQuantity > 10000;

-- 20.
SELECT e.name as sales_name, sum(oi.price * oi.amount * (1-oi.discount)) as total_sales
FROM employees e
JOIN orders o ON e.id = o.employee_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY e.name
HAVING total_sales > (SELECT AVG(total_sales) FROM (SELECT e.id, sum(oi.price * oi.amount * (1-oi.discount)) as total_sales
FROM employees e
JOIN orders o ON e.id = o.employee_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY e.id) AS avg_sales);

-- 21.
SELECT e.name as employee_name
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE oi.order_id IS NULL
UNION
SELECT c.name as customer_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE oi.order_id IS NULL;

-- 22.
SELECT customer_name, MAX(total_purchase) AS max_purchase
FROM (
SELECT c.id, c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name
HAVING total_purchase < (
SELECT AVG(total_purchase)
FROM (
SELECT c.id, SUM(oi.amount * (oi.price - oi.discount)) AS total_purchase
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id
) AS avg_purchase
)
) AS customer_purchases
GROUP BY customer_name
LIMIT 0, 1000;

-- 23.
SELECT
    c.name as customer_name,
    COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
LEFT JOIN employees e ON e.id = o.employee_id
WHERE e.id IS NULL
GROUP BY c.id, c.name
ORDER BY order_count DESC
LIMIT 1;

-- 24.
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN suppliers s ON p.supplier_id = s.id
WHERE s.name NOT LIKE 'ไท เอฟเวอเรสต์';

-- 25.
SELECT c.name as customer_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE p.name IN ('น้ำดื่มคชสาร', 'น้ำดื่มแมมมอธ')
GROUP BY c.name;

-- 26.
SELECT
    c.name as customer_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN suppliers s ON p.supplier_id = s.id
WHERE s.name = 'ไท อาเซียน แอตแลนติก'
AND c.id NOT IN (
SELECT c.id
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN suppliers s ON p.supplier_id = s.id
WHERE s.name = 'ไท เอฟเวอเรสต์'
)
GROUP BY c.id, c.name;

-- Nattapon Nupao SNRU