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 = d.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. แสดงชื่อลูกค้าและชื่อเซลล์ในแต่ละ Order
SELECT o.id, c.name custName, e.name Sales
FROM orders o
JOIN customers c on c.id = o.customer_id
JOIN employees e on e.id = o.employee_id;

-- 4. แสดงจำนวน Order ของลูกค้าแต่ละคน
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. แสดงจำนวน Order ของเซลล์แต่ละคน
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. จงหาชื่อ Supplier และจำนวน Product ที่แตกต่างกันทั้งหมดของแต่ละ Supplier
SELECT s.id, s.name, count(*)
FROM suppliers s
LEFT JOIN products p ON s.id = p.supplier_id
GROUP BY s.id;

-- 7. แสดงชื่อ supplier ที่มี product ราคามากกว่า 800 อย่างน้อย 1 ชนิด
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. แสดงชื่อของ employee ทีอยู่ในแผนกฝ่ายขาย
SELECT e.name
FROM employees e
JOIN departments d on d.id = e.department_id
WHERE d.name = "ฝ่ายขาย";

-- 9. แสดงค่าเฉลี่ยของเงินเดือน Employee ในแต่ละแผนก
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 SalesALL
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 o.id = oi.order_id
GROUP BY e.id;

-- 14. แสดงยอดขายรวมของแต่ละ Product โดยแสดงเฉพาะที่มียอดต่ำกว่า 20000
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
SELECT c.name AS customer_name, p.name AS product_name, oi.amount
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;

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

-- 19
SELECT c.name, SUM(oi.amount * oi.price * (1 - oi.discount)) AS total_order_amount
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_order_amount > 10000;


-- 20
SELECT e.name AS salesperson_name, SUM(oi.amount * oi.price * (1 - oi.discount)) AS total_sales
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_sales > (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_sales);


-- 21
-- Employees without sales
SELECT e.name AS employee_name
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
WHERE o.id IS NULL;

-- Customers without orders
SELECT c.name AS customer_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;


-- 22
SELECT c.name AS customer_name, MAX(total_order_amount) AS max_order_amount
FROM customers c
JOIN (
    SELECT customer_id, SUM(oi.amount * oi.price * (1 - oi.discount)) AS total_order_amount
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    GROUP BY customer_id
    HAVING SUM(oi.amount * oi.price * (1 - oi.discount)) <
        (SELECT AVG(subquery.total_order_amount)
        FROM (
            SELECT customer_id, SUM(oi.amount * oi.price * (1 - oi.discount)) AS total_order_amount
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            GROUP BY customer_id
        ) subquery
    )
) subquery ON c.id = subquery.customer_id
GROUP BY customer_name;

-- 23
SELECT
    Customers.CustomerID,
    Customers.CustomerName,
    COUNT(Orders.OrderID) AS OrderCount
FROM
    Customers
JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
    Orders.EmployeeID IS NULL
GROUP BY
    Customers.CustomerID,
    Customers.CustomerName
ORDER BY
    OrderCount DESC    
LIMIT 1;

-- 24
SELECT DISTINCT
    Customers.CustomerID,
    Customers.CustomerName
FROM
    Customers
LEFT JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN
    OrderDetails ON Orders.OrderID = OrderDetails.OrderID
LEFT JOIN
    Products ON OrderDetails.ProductID = Products.ProductID
LEFT JOIN
    Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE
    Suppliers.SupplierName <> 'ไท เอฟเวอเรสต์' OR Suppliers.SupplierID IS NULL
ORDER BY
    Customers.CustomerID;

-- 25
SELECT DISTINCT
    Customers.CustomerID,
    Customers.CustomerName
FROM
    Customers
JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
JOIN
    OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN
    Products ON OrderDetails.ProductID = Products.ProductID
WHERE
    Products.ProductName IN ('น้ำดื่มคชสาร', 'น้ำดื่มแมมมอธ')
ORDER BY
    Customers.CustomerID;


-- 26
SELECT DISTINCT
    Customers.CustomerID,
    Customers.CustomerName
FROM
    Customers
JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
JOIN
    OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN
    Products ON OrderDetails.ProductID = Products.ProductID
JOIN
    Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE
    Suppliers.SupplierName = 'ไท อาเซียน แอตแลนติก'
    AND Customers.CustomerID NOT IN (
        SELECT
            Customers.CustomerID
        FROM
            Customers
        JOIN
            Orders ON Customers.CustomerID = Orders.CustomerID
        JOIN
            OrderDetails ON Orders.OrderID = OrderDetails.OrderID
        JOIN
            Products ON OrderDetails.ProductID = Products.ProductID
        JOIN
            Suppliers ON Products.SupplierID = Suppliers.SupplierID
        WHERE
            Suppliers.SupplierName = 'ไท เอฟเวอเรสต์'
    )
ORDER BY
    Customers.CustomerID;