[TIL] 241119

sql퀘스트

1.

#Lv4. 가장 높은 월급을 받는 직원은? 

#1
select 
	e.Name ,
	e.Department ,
	e.Salary,
	e2.Name as Top_Earner, 
	e2.Salary as Top_Salary
from 
	employees e 
join
	employees e2 on e.Department = e2.Department
where 
	e2.Salary = (
		select max(salary)
		from employees e3
		where e3.Department = e.Department
)
;

#계산식과 비교하는것이 아니기때문에 having을 사용할 필요없이 where절로 조건비교

order by 1

#2select 
	Department,
	Avg(Salary) as Avg_Salary
from 
	employees e 
group by 1
having 
	Avg(Salary) = (
	select max(Avg_Salary)
	from (SELECT 
			AVG(Salary) AS Avg_Salary
          FROM 
          	Employees 
          GROUP BY 
          	Department) a
          	)

 

2.

#Lv5. 가장 많이 팔린 품목은?
#1
select c.CustomerName ,
	   sum(p.Price*o.Quantity) as TotalAmount,
	   count(o.OrderID) as OrderCount 
from customers c 
join orders o on c.CustomerID = o.CustomerID 
join products p on o.ProductID = p.ProductID 
group by 1
order by 1

#2-1 where 절이 서브쿼리 바깥에
select
	P.Category ,
	p.ProductName as Top_Product,
	sum(o.Quantity) as TotalSold
from 
	products p 
join
	orders o on p.ProductID =o.ProductID
group by 
	1,2
having
	Sum(o.Quantity) = (
		select max(sum_quantity)
		from 
		   (select
		   		p2.Category,
		   		p2.ProductID,
		   		sum(o2.Quantity) as sum_quantity
			from 
				products p2 
			join
				orders o2 on p2.ProductID = o2.ProductID
			group by p2.Category, p2.ProductID) sub
	    where
	    	sub.Category = p.Category)

#2-2 where 을 서브쿼리 내부로
HAVING 
    SUM(o.Quantity) = (
        SELECT 
            MAX(SumQuantity)
        FROM 
            (SELECT 
                 p2.Category,
                 SUM(o2.Quantity) AS SumQuantity
             FROM 
                 Products p2
             JOIN 
                 Orders o2 ON p2.ProductID = o2.ProductID
             WHERE 
                 p2.Category = p.Category -- WHERE를 내부로 옮김
             GROUP BY 
                 p2.Category, p2.ProductID) AS Subquery
    );

 

백준 문제풀이

 

깃허브연동