hw17-01-67

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 department_id = d.id
order by d.name;
--

select p.name, p.price ,s.phone_number
from products p
join suppliers s on s.id = p.suppliers_id;

--

select *
from orders o
join customers c on  c.id = o.customer_id;


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

select c.id, c.name, count(o.id) as order_amount 
from customers c
left join orders o on c.id = o.customer_id
group by c.id; 

--

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;

--

select s.id, s.name, count(*)
from suppliers s
left join products p on s.id = p.supplier_id
group by s.id;

--

select s.id, s.name, p.name, p.price
from products p
join suppliers s on s.id = p.supplier_id
where p.price >800;

--

select e.name
from employees e
join departments d on d.id = e.department_id
where d.name = 'ฝ่ายขาย';

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

--
select format(sum(price *  amount * (1-discount)),2) as SalesAll
from order_items;

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

--

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;

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

--

select p.id, p.name,format(sum(oi.price *  amount * (1-discount)),2) as Total
from products p
join order_items oi on p.id = oi.product_id
group by p.id
having `total` <20000;

--

select p.id, p.name,format(sum(oi.price *  amount * (1-discount)),2) 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;

--

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;

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

--
select c.name as Customer_Name, p.name as Product_Name, oi.amount as Amount, p.price as Price_Products 
from order_items oi 
join orders o on oi.order_id = o.id 
join products p on oi.product_id = p.id 
join customers c on o.customer_id = c.id 
where p.name like '%ไฮยี%' order by oi.amount DESC limit 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
HAVING SUM(oi.price * oi.amount * (1-oi.discount)) > 10000;

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

--
SELECT e.name AS employee_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE e.id = o.employee_id
);

SELECT c.name AS customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.id = o.customer_id
);
--
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;

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

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