Untitled

-- 1 
SELECT e.name, e.address, d.name
FROM employees e, departments d
WHERE e.department_id = d.id
ORDER BY d.name;

SELECT e.name, e.address, d.name
FROM employees e
JOIN departments d on e.department_id
ORDER BY d.name;

-- 2 
SELECT p.name, p.price, s.phone_number
FROM  products p
JOIN suppliers s on s.id = p.supplier_id;
 
-- 3
SELECT o.id, c.name custName, e.name
FROM orders o
JOIN customers c on c.id = o.customer_id
JOIN employees e on e.id = o.employee_id;

-- 4
SELECT c.id, c.name, count(*) AS order_amount
FROM customers c  
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;
 
-- 5
SELECT e.id, e.name, count(*) as order_amount
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
GROUP BY e.id;

-- 6
SELECT s.id, s.name, count(*)
FROM suppliers s
LEFT JOIN products p ON s.id = p.supplier_id
GROUP BY s.id;

-- 7
SELECT s.id, s.name, count(*) as amount
FROM products p
JOIN suppliers s on s.id = p.supplier_id
WHERE p.price > 800
GROUP BY s.id;

-- 8 
SELECT e.name
FROM employees e
JOIN  departments d on d.id = e.department_id
WHERE d.name = "ฝ่ายขาย";

-- 9
SELECT d.id, d.name, format (avg(e.salary), 2) AS AVG_salary
FROM employees e
RIGHT JOIN departments d ON d.id = e.department_id
GROUP BY d.id
Order by d.id;

-- 10 
SELECT format(SUM(price * amount * (1-discount)), 2) as SalasALL
FROM order_items;

-- 11
SELECT o.date, format(SUM(price * amount * (1-discount)), 2) as Sales
FROM order_items od
JOIN orders o ON o.id = od.order_id
GROUP BY o.date;

-- 12
SELECT c.id, 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;

-- 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 e.id = oi.order_id
GROUP BY e.id;

-- 14
SELECT p.id, p.name, SUM(oi.price * oi.amount * (1-oi.discount)) as `Total`
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
HAVING `Total` < 20000; 

-- 15. แสดงชื่อ Product ที่มียอดขายมากที่สุด 5 อันดับแรก รวมถึงชื่อ supplier ด้วย

SELECT p.id, p.name, s.name, sum(oi.price * oi.amount * (1-oi.discount)) as `total`
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN suppliers s ON s.id = p.supplier_id
GROUP BY p.id
ORDER BY total DESC
Limit 5;

-- 16. แสดง product ที่มีจำนวนชิ้นในการขายมากสุด
SELECT p.id, p.name, sum(oi.amount) 'amount'
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY amount desc
LIMIT 1;
 
-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด 
SELECT c.name as customer_name, p.name as product_name, sum(oi.price * oi.amount * (1-oi.discount)) as total_spent
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 p.id = oi.product_id
GROUP BY c.name, p.name;

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

-- 19. จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
SELECT c.name as customer_name, FORMAT(SUM(oi.price * oi.amount * (1-oi.discount)), 2) as total_spent
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 SUM(oi.price * oi.amount * (1-oi.discount)) > 10000;

-- 20. แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / ตามจำนวน sales (ฝ่ายขาย))
SELECT employees.name AS SalespersonName,SUM(order_items.price) AS TotalSales
FROM employees
        INNER JOIN
    orders ON employees.id = orders.employee_id
        INNER JOIN
    order_items ON orders.id = order_items.order_id
GROUP BY employees.id , employees.name
HAVING TotalSales > (SELECT 
        AVG(sales)
    FROM
        (SELECT 
            SUM(order_items.price) AS sales
        FROM
            orders
        INNER JOIN order_items ON orders.id = order_items.order_id
        INNER JOIN employees ON orders.employee_id = employees.id
        GROUP BY orders.employee_id) AS sales_avg);

-- 21. แสดงชื่อ employee ที่ไม่มียอดขาย และแสดงลูกค้าที่ไม่มียอดซื้อ (2 results) ต่อไป
-- Employees with no sales
SELECT e.name AS employee_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE e.id = o.employee_id
);
-- Customers with no purchases
SELECT c.name AS customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.id = o.customer_id
);

-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
SELECT cps.customer_name, 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;

-- 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 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
LEFT JOIN products p ON oi.product_id = p.id
LEFT JOIN suppliers s ON p.supplier_id = s.id
WHERE s.name <> 'ไท เอฟเวอเรสต์' OR s.id IS NULL
GROUP BY c.id, c.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, c.name
HAVING COUNT(DISTINCT p.name) = 2
ORDER BY customer_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;