Untitled

-- lab 01 แสดงชื่อ ที่อยู่ เงินเดือน และชื่อแผนกของพนักงานทุกคน เรียงตามชื่อแผนก
SELECT e.name, e.address, d.name
FROM employees e, departments d
WHERE e.department_id = d.id
ORDER BY d.name;

-- lab 02 แสดงชื่อ ราคาสินค้า ชื่อผู้จำหน่าย และเบอร์โทรของผู้จำหน่ายสินค้า
SELECT e.name, e.address, d.name
FROM employees e
JOIN departments d on e.department_id = d.id
ORDER BY d.name;

-- lab 03 แสดงชื่อลูกค้าและชื่อเซลล์ในแต่ละ Order
SELECT p.name, p.price, s.phone_number
FROM products p
JOIN suppliers s on s.id = p.supplier_id;

-- lab 04 แสดงจำนวน Order ของลูกค้าแต่ละคน
SELECT c.id, c.name, count(*) AS order_amount
FROM ecommerce.customers c
LEFT JOIN ecommerce.orders o ON c.id = o.employee_id
GROUP BY c.id;

-- lab 05 แสดงจำนวน Order ของเซลล์แต่ละคน
SELECT c.id, c.name, count(*) AS order_amount
FROM ecommerce.employees c
LEFT JOIN ecommerce.orders o ON c.id = o.employee_id
GROUP BY c.id;
 
-- lab 06 จงหาชื่อ Supplier และจำนวน Product ที่แตกต่างกันทั้งหมดของแต่ละ Supplier
SELECT s.id, s.name, count(*)
FROM ecommerce.suppliers s
LEFT JOIN ecommerce.products p ON s.id = p.supplier_id
GROUP BY s.id;

-- lab 07 แสดงชื่อ supplier ที่มี product ราคามากกว่า 800 อย่างน้อย 1 ชนิด
SELECT s.id, s.name, count(*) AS amount
FROM ecommerce.products p
JOIN ecommerce.suppliers s ON s.id = p.supplier_id
WHERE p.price > 800
GROUP BY s.id;

-- lab 08 แสดงชื่อของ employee ทีอยู่ในแผนกฝ่ายขาย
SELECT e.name
FROM ecommerce.employees e
JOIN ecommerce.departments d ON d.id = e.department_id
WHERE d.name = 'ฝ่ายขาย';

-- lab 09 แสดงค่าเฉลี่ยของเงินเดือน Employee ในแต่ละแผนก
SELECT d.id, d.name, FORMAT(AVG(salary), 2) AS AVG_Salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.id
ORDER BY d.id;

-- lab 10 แสดงยอดขายรวมทั้งหมด
SELECT FORMAT(SUM(price * amount * (1 - discount)), 2) as SaleAll
FROM order_items;

-- lab 11 แสดงยอดขายรวมในแต่ละวัน
SELECT o.date, FORMAT(SUM(price * amount * (1 - discount)), 2) as SaleAll
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
GROUP BY o.date;

-- lab 12 แสดงชื่อลูกค้าและยอดซื้อรวมของลูกค้าแต่ละคน
SELECT c.name, FORMAT(SUM(price * amount * (1 - discount)), 2) as Total
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;

-- lab 13 แสดงชื่อเซลล์และยอดขายของเซลล์แต่ละคน
SELECT e.id, e.name, FORMAT(SUM(price * amount * (1 - discount)), 2) as Total
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;

-- lab 14 แสดงยอดขายรวมของแต่ละ Product โดยแสดงเฉพาะที่มียอดต่ำกว่า 20000
SELECT p.id, p.name, SUM(oi.price * amount * (1 - discount)) as Total
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
HAVING Total < 20000;

-- lab 15 แสดงชื่อ Product ที่มียอดขายมากที่สุด 5 อันดับแรก รวมถึงชื่อ supplier ด้วย
SELECT p.id, p.name, SUM(oi.price * amount * (1 - discount)) as Total
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN suppliers s ON p.supplier_id = s.id
GROUP BY p.id
ORDER BY Total DESC
LIMIT 5;

-- lab 16 แสดง product ที่มีจำนวนชิ้นในการขายมากสุด
SELECT p.id, p.name, SUM(oi.amount) as Amount
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY Amount DESC
LIMIT 1;

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

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

-- lab 19 จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT c.name, SUM(oi.price * amount * (1 - discount)) as Total_Buy
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
GROUP BY c.id
HAVING Total_Buy > 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 23 จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
SELECT c.name, COUNT(o.id) AS Order_Buy
FROM customers c
JOIN orders o ON c.id = o.customer_id
LEFT JOIN employees e ON o.employee_id = e.id
WHERE e.id IS NULL
GROUP BY c.name
ORDER BY Order_Buy DESC
LIMIT 1;

-- lab 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 'ไท เอฟเวอเรสต์';

-- lab 25 จงหาชื่อลูกค้าที่เคยซื้อน้ำดื่มคชสารและน้ำดื่มแมมมอธ
SELECT c.name AS Customer, p.name AS Product
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 ('น้ำดื่มคชสาร', 'น้ำดื่มแมมมอธ');

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