Homework15+

-- นายสุทธิพงษ์ ถาอ้วน มหาวิทยาลัยเทคโนโลยีราชมงคลอีสาน วิทยาเขตขอนแก่น
-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด  
SELECT c.id, c.name, p.id, p.name, SUM(i.amount) Total_sale
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items i ON o.id = i.order_id
JOIN products p ON i.product_id = p.id
GROUP BY c.id, p.id;

-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT c.id, c.name, SUM(i.amount) Total_Sale
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items i ON o.id = i.order_id
JOIN products p ON i.product_id = p.id
WHERE p.name = 'น้ำดื่มตรา ไฮยีน่า'
GROUP BY c.id 
ORDER BY Total_Sale DESC
LIMIT 5;

-- 19. จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT c.id, c.name, SUM(price * amount * (1-discount)) Total_price
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items i ON o.id = i.order_id
GROUP BY c.id
HAVING SUM(price * amount * (1-discount)) > 10000;

-- 20. แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / ตามจำนวน sales (ฝ่ายขาย))
SELECT e.id, e.name, SUM(price * amount * (1-discount)) Total_sale
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
LEFT JOIN order_items i ON o.id = i.order_id
GROUP BY e.id
HAVING Total_sale > (SELECT AVG(Total_sale) FROM (SELECT e.id, e.name, SUM(price * amount * (1-discount)) Total_sale
													FROM employees e
													LEFT JOIN orders o ON e.id = o.employee_id
													LEFT JOIN order_items i ON o.id = i.order_id
													GROUP BY e.id) Employee_AVG_Sales);

-- 21. แสดงชื่อ employee ที่ไม่มียอดขาย และแสดงลูกค้าที่ไม่มียอดซื้อ (2 results)
SELECT e.id, e.name, SUM(price * amount * (1-discount)) Total_sale
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
LEFT JOIN order_items i ON o.id = i.order_id
JOIN (
	SELECT c.id, c.name, SUM(price * amount * (1-discount)) Total_price
	FROM customers c
	LEFT JOIN orders o ON c.id = o.customer_id
	LEFT JOIN order_items i ON o.id = i.order_id
	GROUP BY c.id
	HAVING Total_price IS NULL OR Total_price = 0
)customers ON 1=1
GROUP BY e.id
HAVING Total_sale IS NULL OR Total_sale = 0;

-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
SELECT c.id, c.name, SUM(price * amount * (1-discount)) Total_price
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items i ON o.id = i.order_id
GROUP BY c.id
HAVING Total_price < (
    SELECT AVG(Total_price)
    FROM (
        SELECT SUM(price * amount * (1 - discount)) Total_price
        FROM customers
        LEFT JOIN orders ON customers.id = orders.customer_id
        LEFT JOIN order_items ON orders.id = order_items.order_id
        GROUP BY customers.id
    ) AS customer_AVG_prices
)
ORDER BY Total_price DESC
LIMIT 1;

-- 23. จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
SELECT c.id, c.name, COUNT(customer_id) No_seller
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.employee_id IS NULL
GROUP BY c.id
ORDER BY c.id;

-- 24. จงหาชื่อลูกค้าที่ไม่เคยซื้อสินค้าจากผู้ผลิตไท เอฟเวอเรสต์
SELECT DISTINCT c.id, c.name
FROM customers c
WHERE c.id NOT IN (
    SELECT DISTINCT c.id
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items i ON o.id = i.order_id
    JOIN products p ON i.product_id = p.id
    JOIN suppliers s ON p.supplier_id = s.id AND s.name = 'ไท เอฟเวอเรสต์'
);

-- 25. จงหาชื่อลูกค้าที่เคยซื้อน้ำดื่มคชสารและน้ำดื่มแมมมอธ
SELECT c.id, c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items i ON o.id = i.order_id
JOIN products p ON i.product_id = p.id
WHERE p.name IN ('น้ำดื่มตรา คชสาร', 'น้ำดื่มตรา แมมมอธ')
GROUP BY c.id
ORDER BY c.id;

-- 26. จงหาชื่อลูกค้าที่เคยซื้อไท อาเซียน แอตแลนติกแต่ไม่เคยซื้อไท เอฟเวอเรสต์
SELECT DISTINCT c.id, c.name
FROM customers c
WHERE c.id IN (
    SELECT c.id
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items i ON o.id = i.order_id
    JOIN products p ON i.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 i ON o.id = i.order_id
    JOIN products p ON i.product_id = p.id
    JOIN suppliers s ON p.supplier_id = s.id
    WHERE s.name = 'ไท เอฟเวอเรสต์'
)
ORDER BY c.id;