23.10.17. Class 복습 겸 문제

[문제] 최고 급여를 받는 사원의 정보, 부서 이름 정보를 출력해주세요. 

 

1) subquery, inline view, join 
1#. subquery 

SELECT *
FROM hr.employees
WHERE salary = '최고급여';

"   +   "       

 

SELECT MAX(salary) 
FROM hr.employees ;

"   ↓   "

SELECT *
FROM hr.employees
WHERE salary = ( SELECT MAX(salary) 
                 FROM hr.employees );

"   +   "                 

SELECT department_name
FROM hr.departments
WHERE department_id = '변수';

"   ↓   "

2#. inline view 

SELECT *
FROM ( 
       SELECT *
       FROM hr.employees
       WHERE salary = ( SELECT MAX(salary) 
                        FROM hr.employees )
      )e ;

3#. join 

SELECT e.*, d.department_name
FROM ( SELECT *
       FROM hr.employees
       WHERE salary = ( SELECT MAX(salary) 
                        FROM hr.employees )
      )e, hr.departments d
WHERE e.department_id = d.department_id ;

2) subquery, scalar subquery
1#. subquery 

SELECT *
FROM hr.employees
WHERE salary = ( SELECT MAX(salary) 
                 FROM hr.employees );

                 
2#. scalar subquery

SELECT e*, '부서이름'
FROM hr.employees e
WHERE salary = ( SELECT MAX(salary) 
                 FROM hr.employees );

 

"   +   "  

SELECT department_name
FROM hr.departments 
WHERE department_id = 90;

"   ↓   "

SELECT 
    e.*, 
    ( SELECT department_name
      FROM hr.departments 
      WHERE department_id = e.department_id) dept_name
FROM hr.employees e
WHERE salary = ( SELECT MAX(salary) 
                 FROM hr.employees );