Untitled

-- LAB-2C
-- lab 17 แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด 
SELECT c.id, c.name, p.name, FORMAT(SUM(oi.price * amount * (1 - discount)), 2) as Total
FROM customers c
JOIN products p ON c.id = p.id
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id;

-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
SELECT
    c.name AS customer_name,
    SUM(oi.price) 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
ORDER BY
    total_purchase DESC
LIMIT 5;

-- 19. จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT
    c.name AS customer_name,
    SUM(oi.price) 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
HAVING
    total_purchase > 10000;

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

-- lab 21 แสดงชื่อ employee ที่ไม่มียอดขาย และแสดงลูกค้าที่ไม่มียอดซื้อ (2 results)
-- employee ที่ไม่มียอดขาย
SELECT e.name AS employee_name
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
WHERE o.id IS NULL;

-- ลูดค้าที่ไม่มียอดซื้อ
SELECT c.name AS customer_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

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

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

-- 24. จงหาชื่อลูกค้าที่ไม่เคยซื้อสินค้าจากผู้ผลิตไท เอฟเวอเรสต์
SELECT
    c.name AS customer_name
FROM
    customers c
WHERE
    c.id NOT IN (SELECT DISTINCT o.customer_id FROM orders o 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 = 'ไท เอฟเวอเรสต์');

-- 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.id
HAVING
    COUNT(DISTINCT p.id) = 2;

-- lab 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;