문제/SQL

23.10.17. Class 복습 겸 문제

잇꼬 2023. 10. 17. 13:49
728x90
반응형
SMALL

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

 

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 );
728x90
반응형
LIST

'문제 > SQL' 카테고리의 다른 글

231024 Oracle SQL 필기 TEST's 오답노트  (0) 2023.10.24
231018 복습 겸 문제  (0) 2023.10.18
23.10.16. Class 복습 겸 문제  (0) 2023.10.16
23.10.13. Class 복습 겸 문제  (1) 2023.10.13
23.10.12. Class 복습 겸 문제  (0) 2023.10.12