cc-sql-lab2

-- 1. แสดงชื่อ ที่อยู่ เงินเดือน และชื่อแผนกของพนักงานทุกคน เรียงตามชื่อแผนก
select e.name, e.address, e.salary, d.name
from employees e
join departments d on d.id = e.department_id
order by d.name;

-- 2. แสดงชื่อ ราคาสินค้า ชื่อผู้จำหน่าย และเบอร์โทรของผู้จำหน่ายสินค้า
select p.name, p.price, s.name, s.phone_number
from products p
join suppliers s on s.id = p.supplier_id;

-- 3. แสดงชื่อลูกค้าและชื่อเซลล์ในแต่ละ Order
select o.id, o.date, c.name, e.name
from orders o
join customers c on c.id = o.customer_id
join employees e on e.id = o.employee_id
order by o.id;

-- 4. แสดงจำนวน Order ของลูกค้าแต่ละคน
select c.id, c.name, count(*) order_amount
from customers c
join orders o on c.id = o.customer_id
group by c.id
order by order_amount desc;

-- select *
-- from orders
-- where customer_id = 7;

-- 5. แสดงจำนวน Order ของเซลล์แต่ละคน
select e.id, e.name, d.name, count(*) order_amount
from employees e
join orders o on e.id = o.employee_id
join departments d on d.id = e.department_id
where d.name ='ฝ่ายขาย'
group by e.id;

-- 6. จงหาชื่อ Supplier และจำนวน Product ที่แตกต่างกันทั้งหมดของแต่ละ Supplier
select s.id, s.name, count(*) 'num'
from suppliers s 
join products p on s.id = p.supplier_id
group by s.id;

-- 7. แสดงชื่อ supplier ที่มี product ราคามากกว่า 800 อย่างน้อย 1 ชนิด
select distinct s.name
from products p
join suppliers s on s.id = p.supplier_id
where p.price > 800;

-- 8. แสดงชื่อของ employee ทีอยู่ในแผนกฝ่ายขาย
select d.id, d.name, 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
join departments d on d.id = e.department_id
group by d.id
order by d.id;

-- 10. แสดงยอดขายรวมทั้งหมด
select sum(price*amount*(1-discount))
from order_items;

-- select sum(price*amount*(1-discount))
-- from order_items
-- where id=16 
-- (800 * 2 * 0.95)

-- 11. แสดงยอดขายรวมในแต่ละวัน
select o.date, format( sum(oi.price * oi.amount * (1-oi.discount) ),2) total
from order_items oi
join orders o on o.id = oi.order_id
group by o.date;

-- select o.date, sum(price * amount * (1-discount))
-- from order_items oi
-- join orders o on o.id = oi.order_id
-- where o.date = '2021-02-01';

-- 12. แสดงชื่อลูกค้าและยอดซื้อรวมของลูกค้าแต่ละคน
select c.id, c.name, format( sum(oi.price * oi.amount * (1-oi.discount) ),2) 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(oi.price * oi.amount * (1-oi.discount) ),2) 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) ) 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 s.name, p.id, p.name,  sum(oi.price * oi.amount * (1-oi.discount) ) 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) total_amount
from products p
join order_items oi on p.id = oi.product_id
group by p.id
order by total_amount desc
limit 1;

-- 17. แสดงข้อมูลว่า ลูกค้าแต่ละคนซื้อ product ใดบ้าง และมียอดสั้งซื้อของแต่ละ product เท่าใด  

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

-- 18. แสดงชื่อลูกค้าที่มียอดสั่งไฮยีน่ามากสุด 5 อันดับแรก
select c.name, p.name, sum(oi.price*oi.amount*(1-discount)) total
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
where p.name like "%ยีน่า%"
group by c.id, p.id
order by total desc
limit 5;

-- 19. จงหาชื่อ customer ที่มียอดสั่งซื้อมากกว่า 10000
select c.name, sum(oi.price*oi.amount*(1-discount)) 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
having total > 10000
order by total desc;

-- 20. แสดงชื่อเซลล์และยอดขาย เฉพาะคนที่ยอดขายมากกว่าค่าเฉลี่ย (ยอดรวม / ตามจำนวน sales (ฝ่ายขาย))
-- select count(*) sales_num
-- from employees e
-- where e.department_id = 6;

-- select sum(oi.price*oi.amount*(1-discount)) total
-- from order_items oi;

-- select e.name,sum(oi.price*oi.amount*(1-discount)) total, count(*)
-- 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;

-- --------
WITH sales_num_cte AS (
    SELECT count(*) AS sales_num
    FROM employees e
    WHERE e.department_id = 6
),
total_sales_cte AS (
    SELECT sum(oi.price * oi.amount * (1 - discount)) AS total_sales
    FROM order_items oi
),
avg_sales_cte AS (
	select total_sales / sales_num as avg_sales
	from sales_num_cte, total_sales_cte
)

select e.name,sum(oi.price*oi.amount*(1-discount)) 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
having total > (select avg_sales from avg_sales_cte);

-- 21. แสดงชื่อ employee ที่ไม่มียอดขาย และแสดงลูกค้าที่ไม่มียอดซื้อ (2 results)
-- select *
-- from employees
-- where id not in
-- (select distinct e.id
-- from employees e
-- join orders o on e.id = o.employee_id
-- order by e.id);

select *
from employees
where id not in
(select distinct employee_id
from orders where employee_id is not null);

select * 
from customers
where id not in
(select distinct customer_id
from orders where customer_id is not null);

select *
from employees e
left join orders o on e.id=o.employee_id
where o.id is null
group by e.id
;

-- 22. จงหาชื่อลูกค้าและยอดซื้อโดยที่ยอดซื้อมีค่าสูงที่สุดในกลุ่มลูกค้าที่มียอดซื้อต่ำกว่าค่าเฉลี่ย
WITH cust_num_cte AS (
    SELECT count(*) AS cust_num
    FROM customers
),
total_sales_cte AS (
    SELECT sum(price * amount * (1 - discount)) AS total_sales
    FROM order_items
),
avg_sales_cte AS (
	select total_sales / cust_num as avg_sales
	from cust_num_cte, total_sales_cte
)
select c.name,sum(oi.price*oi.amount*(1-discount)) 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
having total < (select avg_sales from avg_sales_cte)
order by total desc
limit 1;

-- 23. จงหาชื่อลูกค้าที่มีออเดอร์จากการซื้อแบบไม่ผ่านเซลล์มากสุด
select c.name, sum(oi.price*oi.amount*(1-discount)) total
from orders o
join customers c on c.id=o.customer_id
join order_items oi on o.id=oi.order_id
where o.employee_id is null
group by c.id
order by total desc
limit 1;

-- 24. จงหาชื่อลูกค้าที่ไม่เคยซื้อสินค้าจากผู้ผลิตไท เอฟเวอเรสต์
select * from customers
where id not in
(select distinct c.id
from orders o 
join customers c 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
join suppliers s on s.id=p.supplier_id
where s.name like 'ไท เอฟเวอเรสต์'
order by id
);

-- 25. จงหาชื่อลูกค้าที่เคยซื้อน้ำดื่มคชสารและน้ำดื่มแมมมอธ
select distinct c.id
from orders o 
join customers c 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
where p.name like '%คชสาร%' and c.id in 
(select distinct c.id
from orders o 
join customers c 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
where p.name like '%แมมมอธ%' )
order by c.id
;

-- 26. จงหาชื่อลูกค้าที่เคยซื้อไท อาเซียน แอตแลนติกแต่ไม่เคยซื้อไท เอฟเวอเรสต์
select distinct c.id, c.name
from orders o 
join customers c 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
join suppliers s on s.id=p.supplier_id
where s.name like '%อาเซียน%' and c.id not in
(select distinct c.id
from orders o 
join customers c 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
join suppliers s on s.id=p.supplier_id
where s.name like '%เอฟเวอเรสต์%'
);