23.09.22. SQL Developer 예제 67 ~ 77번

67. 집합 연산자로 데이터를 위아래로 연결하기 ①(UNION ALL)

-- 예제 67. 부서번호와 부서번호별 토탈 월급을 출력하는데 다음과 같이 맨 아래에 전체 토탈 월급도 출력하세요.
delete from emp 
    where deptno = 50 ;
select deptno, sum(sal) 
    from emp
    group by deptno 
union all   
select to_number(null) as deptno, sum(sal) -- 컬럼 갯수가 동일해야 함. 데이터 타입도 동일해야 함.
    from emp
    order by deptno asc ;

-- 예제67_문제1. 직업과 직업별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급도 출력하세요.
select job, sum(sal)
    from emp 
    group by job
union all
select to_char(null) as job, sum(sal) 
    from emp 
    order by job asc ;

 

68. 집합 연산자로 데이터를 위아래로 연결하기 ②(UNION)  

-- 예제 68. 부사번호와 부서번호별 토탈 월급을 출력하고 다음과 같이 맨 아래와 전체 토탈 월급도 출력하는데 부서번호가 오름차순으로 정렬되어서 출력되게 하세요
select deptno, sum(sal) 
    from emp 
    group by deptno ;
    
select to_number(null) as deptno, sum(sal)
    from emp ;
    
select deptno, sum(sal) 
    from emp 
    group by deptno
union
select  to_number(null) as deptno, sum(sal) -- 컬럼 갯수가 동일해야 함. 데이터 타입도 동일해야 함.
    from emp ;

-- 예제68_문제1. 직업과 직업별 토탈월급을 출력하는데 직업이 abcd 순으로 정렬되어서 출력하고 맨 아래에 전체 토탈월급을 출력하세요
select job, sum(sal) 
    from emp 
    group by job 
union
select to_char(null) as job, sum(sal)
    from emp 
    order by job asc ;

/* 예제68_문제2. 다음과 같이 입사한 년도, 입사한 년도별, 토탈월급을 출력하는데 맨아래에 전체 토탈월급이 출력되게 하세요.
    (입사한 년도는 정렬되어서 출력되게 하세요.) */
select to_char(hiredate, 'RRRR') as YEAR, sum(sal)
    from emp
    group by  to_char(hiredate, 'RRRR')
union
select to_char(null) as YEAR, sum(sal)
    from emp 
    order by YEAR asc ;

tip) 최근 업그레이드 되면서 union연산자는 '정렬기능'이 지원되지 않는다. 정렬이 필요한 경우, order by 절을 사용해야 함. 

 

69. 집합 연산자로 데이터의 교집합을 출력하기(INTERSECT)

-- 예제 69. 공통된 집합 연산자
select ename, sal, job, deptno
    from emp 
    where deptno in (10, 20) 
    intersect 
select ename, sal, job, deptno
    from emp 
    where deptno in (20, 30) ;
    
-- 예제 69_문제1. 사원 테이블과 부서 테이블과의 공통된 부서번호가 무엇인지 출력하세요.
select deptno
    from emp
    intersect
select deptno 
    from dept ;

 

70. 집합 연산자로 데이터의 차이를 출력하기(MINUS)

-- 예제 70. 마이너스 집합 연산자
select ename, sal, job, deptno
    from emp 
    where deptno in (10, 20) 
    minus
select ename, sal, job, deptno
    from emp 
    where deptno in (20, 30) ;
    
-- 예제70_문제1. 부서테이블에는 존재하는데 사원테이블에는 존재하지 않는 부서번호를 출력하세요.
select deptno
    from dept
    minus
select deptno
    from emp ;

 

71. 서브 쿼리 사용하기 ①(단일행 서브쿼리)

-- 예제71. JONES 보다 더 많은 월급을 받은 사원들의 이름과 월급을 출력하세요.
select ename, sal
    from emp -- main query문
    where sal >  ( select sal
                          from emp 
                           where ename = 'JONES' ) ; -- 괄호 안에 있는 쿼리문을 서브쿼리문                           

-- 예제71_문제1. ALLEN 보다 더 늦게 입사한 사원들의 이름과 월급을 출력하세요
select ename, sal
    from emp 
    where hiredate >
    ( select hiredate
        from emp
        where ename = 'ALLEN' ) ;

select hiredate
    from emp 
    where ename = 'ALLEN' ;

 

72. 서브 쿼리 사용하기 ②(다중 행 서브쿼리)  

-- 예제72. 직업이 SALESMAN 인 사원들과 같은 월급을 받는 사원들의 이름과 월급을 출력하세요.
select ename, sal
    from emp 
    where sal in
        (select sal 
            from emp 
            where  job = 'SALESMAN' ) ;
            
-- 예제72_문제1. 부서번호가 20번인 사원들과 같은 직업을 갖는 사원들의 이름과 직업을 출력하세요. 
select ename, job
    from emp 
    where job in 
        (select job
            from emp 
            where deptno = 20 ) ;

    1) 단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우 
        연산자 : =, !=, ^=. <>. >, <, <=, >=, 
    2) 다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우 
        연산자 : in, not in, >all, <all, >any, <any

 

73. 서브 쿼리 사용하기 ③(NOT IN) 

-- 예제73. 관리자인 사원들의 이름을 출력하세요
select empno, ename, mgr
    from emp ;
    
select ename
    from emp 
    where empno in 
    ( select mgr
        from emp ) ;
        
-- 예제73_문제1. 관리자가 아닌 사원들의 이름을 출력하세요. 
select ename
    from emp 
    where empno not in
    ( select mgr 
        from emp 
        where mgr is not null ) ;
select ename
    from emp 
    where empno not in
    ( select nvl( mgr, -1)  
        from emp ) ; -- null 처리를 해야 한다.

 

74. 서브 쿼리 사용하기 ④(EXISTS와 NOT EXISTS)

-- 예제74. 부서테이블에 있는 부서번호 중에서 사원 테이블에 존재하는 부서번호에 대한 모든 컬럼을 출력하세요.
select * 
    from dept ;

select * 
    from emp ;
    
select * 
    from dept d
    where exists  -- 메인쿼리부터 실행
        ( select *
            from emp e
            where e.deptno = d.deptno ) ;
            
-- 예제74_문제. 부서테이블에 있는 부서번호 중에서 사원 테이블에 존재하지 않는 부서번호에 대한 모든 컬럼을 출력하세요.
select * 
    from dept d
    where not exists 
        ( select *
            from emp e
            where e.deptno = d.deptno ) ;

 

75. 서브 쿼리 사용하기 ⑤(HAVING절의 서브 쿼리) 

-- 예제75. 직업과 직업별 토탈월급을 출력하는데 직업이 salesman 인 사원들의 토탈월급보다 더 큰 것만 출력되게 하세요.
select job, sum(sal)
    from emp 
    where sum(sal) > 
        (select sum(sal) 
            from emp 
            where job = 'SALESMAN' ) -- 그룹함수 사용시, where절로 검색이 되지 않음.
        group by job ;
        
select job, sum(sal)
    from emp 
        group by job -- 그룹함수일 경우
        having sum(sal) >  -- 검색조건 having
            ( select sum(sal) 
                from emp 
                where job = 'SALESMAN') ; 
                
-- 예제75_문제1. 부서번호, 부서번호별 인원수를 출력하는데 10번 부서번호의 인원수보다 더 큰것만 출력하세요.
select deptno, count(*)
    from emp 
    group by deptno
    having count(*) > 
        ( select count(*)
            from emp 
            where deptno = 10 ) ;

 

76. 서브 쿼리 사용하기 ⑥(FROM절의 서브 쿼리)

-- 예제 76. 사원 테이블에서 월급을 가장 많이 받는 사원의 이름과 월급과 월급의 순위를 출력하세요.
select ename, sal, rank() over( order by sal desc) rnk 
    from emp ; -- 월급 순위 출력

select * 
    from 
    ( select ename, sal, rank() over( order by sal desc) rnk 
        from emp )
        where rnk = 1 ;
        
-- 예제 76_문제1. 직업이 SALESMAN 인 사원들 중에서 가장 먼저 입사한 사원의 이름과 입사일을 출력하세요.
select ename, hiredate
    from 
    ( select ename, hiredate, rank() over( order by hiredate asc ) rnk
        from emp
        where job = 'SALESMAN' )
    where rnk = 1 ;

 

77. 서브 쿼리 사용하기 ⑦(SELECT절의 서브 쿼리)

-- 예제77. 직업이 SALESMAN 인 사원들의 이름과 월급을 출력하면서 그 옆에 직업이 SALESMAN 인 사원들의 최대/최소 월급을 출력하세요.
select ename,  sal, 
    ( select max(sal) from emp where job = 'SALESMAN' ) as maxl, 
    ( select min(sal) from emp where job = 'SALESMAN' ) as minl 
    from emp 
    where job = 'SALESMAN' ;

-- 예제77_문제1. 부서번호 20번인 사원들의 이름과 월급을 출력하고 그 옆에 20번 부서번호인 사원들의 평균 월급이 출력되게 하세요.
select ename, sal,
    ( select avg(sal) as 평균 from emp where deptno = 20 ) 평균
    from emp 
    where deptno = 20 ;

select 문의 6가지 

select  : 서브쿼리 가능
    from : 서브쿼리 가능
    where : 서브쿼리 가능
    group by : 서브쿼리 불가능
    having : 서브쿼리 가능
    order by : 서브쿼리 가능