[Oracle] Sql Developer Sub Query

• 2024.05.11 복습용

Sub Query(서브쿼리)

---

select
    [출력할 컬럼] FROM table
    WHERE [컬럼 연산자]
        (SELECT select_list
        FROM table
        WHERE .....);

1. 단일행 서브 쿼리

   • 내부 쿼리문의 결과로 얻어지는 행이 1개
   • 단일행 비교 연산자 : >, >=, <, <=, =, <>, !=

2. 다중행 서브 쿼리

   • 내부 쿼리문의 결과로 얻어지는 행이 2개 이상
   • 다중행 비교 연산자 : in, any, some, all, exists

종류	의미
in	메인쿼리의 비교조건('='연산자로 비교할 경우)이 서브쿼리 결과중 하나라도 일치하면 참
any some	메인쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상이 일치하면 참 (가장 큰 값을 만족하면 전체가 만족됨)
all	메인쿼리의 비교조건이 검색결과와 모든 값이 일치하면 참 (가장 작은 값을 만족하면 전체가 만족됨)
exists	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

--사용 예시

--scott 과 동일한 부서에서 근무하는 사원 출력
select ename, dno from employee
    where dno=(select dno from employee where ename = 'SCOTT');
    
--부서별 최소 급여를 받는 사원의 사원번호와 이름 출력
select eno, ename from employee
    where salary in 
    (select min(salary) from employee group by dno);

--직급이 세일즈맨이 아니면서 급여가 임의의 세일즈맨보다 낮은 사원 출력
select eno, ename, job, salary from employee
    where salary < any (select salary from employee where job='SALESMAN');

연습_01

--1.사원번호가 7788인 사원과 담당업무가 같은 사원명, 담당업무 출력
select ename "사원명", job "담당업무" from employee
    where job = (select job from employee where eno = 7788);
    
--2.사원번호가 7499보다 급여가 많은 사원명, 담당업무, 급여 출력
select ename "사원명", job "담당업무", salary "급여" from employee
    where salary > (select salary from employee where eno = 7499);
    
--3.최소, 최대 급여를 받는 사원명, 담당업무, 급여 출력
select ename "사원명", job "담당업무", salary "급여" from employee
    where salary in (select min(salary) from employee union select max(salary) from employee); 
    
--4.부서별 최소급여를 받는 사원명, 급여, 부서번호 출력
select ename "사원명", salary "급여", dno "부서번호" from employee 
    where (dno, salary) in (select dno, min(salary) from employee group by dno);
    
--5.4번 문제에서 부서명, 근무지를 추가하여 출력
select ename "사원명", salary "급여", dno "부서번호", dname "부서명", loc "근무지" 
    from employee NATURAL JOIN department
    where (dno, salary) in (select dno, min(salary) from employee group by dno);
    
--6.담당업무가 ANALYST인 사원보다 급여가 적으면서 업무가 ANALYST가 아닌
--  사번, 이름, 담당업무, 급여 출력 (사번으로 오름차순 정렬)
select eno "사번", ename "이름", job "담당업무", salary "급여" from employee
    where salary < all (select salary from employee where job='ANALYST') and job <> 'ANALYST'
    order by eno asc;
    
--7.직속상관이 없는 직원 출력
select ename "이름", job "담당업무" from employee where eno
    in(select eno from employee where manager is null);
    
--8. 7번 문제에서 부서명, 근무지 추가 - 서브쿼리, 조인+서브쿼리 2가지
select e.ename "이름", d.dname "부서명", d.loc "근무지" from employee e, department d
    where e.dno = d.dno and eno
    in (select eno from employee where manager is null);
    
select e.ename as "이름",
    (select d.dname from department d where d.dno = e.dno) as "부서명",
    (select d.loc from department d where d.dno = e.dno) as "근무지"
     from employee e where e.manager is null;

--9. 부하직원이 없는 직원의 사번, 사원명, 급여 출력
--관리자가 null이 아닌 관리자 번호를 가져옴
--사번이 서브쿼리 관리자 번호와 아닌 사번을 출력
select eno "사번", ename "사원명", salary "급여" from employee
    where eno not in (select manager from employee where manager is not null);

--10.BLAKE와 동일한 부서에 속한 사원명, 입사일 출력, BLANK는 출력 제외
select ename "사원명", dno "부서번호", hiredate "입사일" from employee
    where dno = (select dno from employee where ename = 'BLAKE') and ename <> 'BLAKE';
    
--11.근무지가 DALLAS인 사원명, 부서번호, 담당업무 출력 - 이름 오름차순
--   서브쿼리, 조인 2가지 방법
select ename "이름", dno "부서번호", job"담당업무" from employee
    where dno = (select dno from department where loc = 'DALLAS')
    order by ename asc;
    
select e.ename as "이름", e.dno as "부서번호", e.job as "담당업무" from employee e, department d 
    where e.dno = d.dno and d.loc = 'DALLAS' order by e.ename asc;
    
--12. SCOTT과 동일한 부서에서 근무하는 사원명, 부서번호 출력
select ename "사원명", dno "부서번호" from employee
    where dno = (select dno from employee where ename = 'SCOTT');
    
--13. 12번 문제에서 부서명, 근무지 출력 - 조인사용(동등, 자연 조인 각각 사용)
select e.ename "사원명", e.dno "부서번호", d.dname "부서명", d.loc "근무지" from employee e, department d 
    where e.dno = d.dno and e.dno = (select dno from employee where ename = 'SCOTT');
    
select ename "사원명", dno "부서번호", dname "부서명", loc "근무지" from employee natural join department
    where dno = (select dno from employee where ename = 'SCOTT');
    
--14. 담당업무별로 업무가 ANALYST인 사원보다 급여가 적고 업무가 CLERK가 아닌
--    사원들중에서 담당업무, 최고급여 출력
select job "담당업무", max(salary) "급여" from employee
    where salary < all (select salary from employee where job='ANALYST') 
    and job <> 'CLERK' group by job;