Untitled

-- Homework 15+

-- No. 01
select e.name, e.address, e.salary, d.name from employees as e join departments as d on d.id = e.department_id;

-- No. 02
select p.name, price, s.name, phone_number from products as p left join suppliers as s on s.id = supplier_id;

-- No. 03
select o.id, o.date, c.name, e.name from orders as o inner join customers as c on o.customer_id = c.id inner join employees as e on o.employee_id = e.id;

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

-- No. 05
select e.id, e.name, count(o.id) as order_amount from employees as e left join orders as o on e.id = o.employee_id group by e.id;

-- No. 06
select s.id, s.name, p.name, p.price, count(*) as product_count from suppliers as s left join products as p on s.id = p.supplier_id group by s.id;

-- No. 07
select s.id, s.name, p.name, p.price, count(*) as amount from products p join suppliers s on s.id = p.supplier_id where p.price > 800 group by s.id;

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

-- No. 09
select d.name, format(avg(e.salary), 2) as avg_salary from employees e join departments d on e.department_id = d.id group by d.id;

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

-- No. 11
select o.date, format(sum(amount * price * (1 - discount)), 2) as sum_price from order_items oi left join orders o on o.id = oi.order_id group by o.date;

-- No. 12
select c.id, c.name, format(sum(amount * price * (1 - discount)), 2) as sum_price from customers c join orders o on c.id = o.customer_id join order_items oi on oi.order_id = o.id group by c.name;

-- No. 13
select e.id, e.name, format(sum(amount * price * (1 - discount)), 2) as sun_price 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;

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

-- No. 15
select p.id, p.name, s.name, format(sum(oi.amount * oi.price * (1 - oi.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;

-- No. 16
select p.id, p.name, sum(oi.amount) as sum_amount from products p join order_items oi on p.id = oi.product_id group by p.id order by sum_amount desc limit 1;

-- No. 17
select c.name as Customer_Name, p.name as Product_Name, 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;

-- No. 18
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;

-- No. 19
select o.customer_id as id, c.name, sum(oi.amount * oi.price * (1 - oi.discount)) as total from order_items oi join orders o on oi.order_id = o.id join customers c on o.customer_id = c.id group by o.customer_id having total > 10000 order by total desc;

-- No. 20
select e.name, sum(oi.price) 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 , e.name having total > ( select avg(sales) from (select sum(oi.price) sales from orders o join order_items oi on o.id = oi.order_id join employees e on o.employee_id = e.id group by o.employee_id) sale_avg);

-- No. 21
select e.name from employees e where not exists (select 1 from orders o where e.id = o.employee_id);

-- No. 22
select cps.Name, format(cps.total_purchase, 2) Total from (select c.id id, c.name Name, sum(oi.price * oi.amount * (1 - oi.discount)) 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) cps order by cps.total_purchase desc limit 1;

-- No. 23
select c.name Name, count(o.id) 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;

-- No. 24
select c.name 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;

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

-- No. 26
select c.name as 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;