실습 | 다중 컬럼 서브쿼리/ Correlated 서브쿼리/ DBeaver 에서 with문 사용 안될 때 해결 법

📂SQL 실전 문제풀이 ( Lv5. 예산이 가장 큰 프로젝트는? )

문제1 : 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원만 출력 (직원명/부서/월급 컬럼)

▶ POINT.  부서별 가장 높은 월급을 받는 직원 필터링 

 

[테이블 정보]

 

[풀이 과정]

• 필터링 조건이 그룹바이한 결과값을 적용해야 하므로 서브쿼리 사용해야함
• 3가지 서브쿼리로 필터링 가능
• 풀이1. where 절 다중 컬럼 서브쿼리 사용
  • 부서별 가장 높은 월급을 조회하는 서브쿼리를 아우터쿼리 다중 컬럼 조건으로 필터링 

SELECT name 
	  ,department 
	  ,salary 
FROM employees
WHERE (department,salary) IN (SELECT department  -- 부서별 최고 월급자 서브쿼리로 필터링 조건
			       	    ,max(salary)
                              FROM employees
                              GROUP BY department)

 

• 풀이2. rank() 윈도우 함수 & 서브쿼리 사용
  • 부서별 가장 높은 월급자 순으로 순번 매긴 후 필터링 (가장 높은 값만 필요하니까 dense_rank 사용해도 무방) 

WITH rk_t AS (
	SELECT *
	      ,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
	FROM employees
)
SELECT name
      ,department
      ,salary
FROM rk_t
WHERE rk=1

 

[정답 쿼리]

• correalted 서브쿼리 사용 
• 부서별 가장 높은 월급이 같을 때만 출력해줌. 

 SELECT 
    e.name,
    e.department,
    e.salary
FROM 
    employees e
WHERE e.salary = ( -- 부서별 월급이 최대값인 (서브쿼리 결과) 월급만 필터링
        SELECT 
        	MAX(e2.salary)
        FROM 
        	employees e2
        WHERE 
        	e2.department = e.department)

 

문제2 : 직원이 참여한 프로젝트 중 예산이 10,000 이상인 프로젝트만 조회(직원명/프로젝트명/프로젝트 예산 컬럼 출력)

▶  POINT. 직원이 참여한 프로젝트 필터링 (조인 조건)

 

[풀이 과정]

• 직원이 참여한 프로젝트 : inner join으로 참여안한 프로젝트 제외 후 연결
• 예산 조건에 집계 필요 없으므로 where 절로 단순 필터링 

SELECT e.name 
	  ,p.Project_name 
	  ,p.budget 
FROM employeeprojects ep
	INNER JOIN employees e ON ep.employee_id =e.employee_id 
	INNER JOIN projects p ON ep.project_id =p.project_id 
WHERE p.budget >= 10000
ORDER BY p.budget DESC

🔥DBeaver 에서 with문 사용시 주의사항&해결법🔥

- DBeaver 는 공백 행을 기준으로 쿼리를 구분한다

- 따라서 with 문 사용시, with문과 아우터 쿼리 사이의 공백행이 있으면 안됌

▼오류 메시지 : with 문으로 생성한 테이블이 존재 하지 않는다는 오류가 뜸 

▼ 해결 : with 문과 select 절 사이의 빈 공백 행을 제거 해줌