23.09.15. SQL Developer 예제 38 ~ 48번

38. 평균값 출력하기

-- 예제 38. 사원 테이블에서 평균 월급을 출력하세요.
select round( avg(sal))
    from emp ;
-- 예제 38_문제1. 직업과 직업별 평균월급을 출력하는데 직업별 평균월급이 높은 것부터 출력하세요
select job, round(avg(sal)) as 평균
    from emp 
    group by job
    order by 평균 desc ; 
-- 예제 38_문제2. 부서번호, 부서번호별 평균월급을 출력하는데 부서번호별 평균월급을 출력할때에 천단위 표시를 하시오.
select deptno, to_char(round(avg(sal)), '999,999') as 평균
    from emp 
    group by deptno ;

1) 질의문의 실행순서 : from -> group by -> select -> order by 

 

39. 토탈값 출력하기

-- 예제39. 부서번호, 부서번호별 토탈 월급을 출력하세요.
select deptno, sum(sal) 
    from emp 
    group by deptno ;
--예제39_문제1. 1981년도에 입사한 사원들의 월급의 토탈값을 출력하세요.
select sum(sal)
    from emp 
    where hiredate between to_date ('1981/01/01', 'RRRR/MM/DD') and to_date('1981/12/31', 'RRRR/MM/DD') + 1 ;

select sum(sal) 
    from emp 
    where to_char(hiredate, 'RRRR') = '1981' ;
--예제39_문제2. 직업과 직업별 토탈월급을 출력하는데 직업별 토탈월급이 6000 이상인 것만 출력하세요
select job,  sum(sal)
    from emp 
    group by job 
    having sum(sal) >= 6000 ;

/*
select 컬럼명
 from 테이블명
 where 검색조건
 group by 그룹핑할 컬럼명
 having 그룹함수로 검색조건
 order by 정렬할 컬럼명
 */

1) group by절 이후 having 조건 작성
2) group by로 묶은 결과에 조건을 명시하는 경우에는 having절을 이용해야 조건식이 적용되서 결과를 출력함.

40. 건수 출력하기

--예제40. 사원 테이블의 전체 인원수가 어떻게 되는지 출력하세요.
select count(empno) 
    from emp ; 
select count(*)
    from emp ;
/* 평균 커미션을 출력하세요.*/
select avg(comm)
    from emp ;

select avg(nvl(comm, 0))
    from emp ;
--예제40_문제1. 부서번호, 부서번호별 인원수를 출력하세요.
select deptno, count(*)
    from emp 
    group by deptno ;
--예제40_문제2. 직업과 직업별 인원수를 출력하는데 직업이 SALESMAN 은 제외하고 출력하고 직업별 인원수가 3명 이상인 것만 출력하세요.
select job, count(*)
    from emp 
    where job != 'SALESMAN'
    group by job
    having count(*) >= 3  ;
/* 테이블 데이터를 가져온 다음 where 절로 필터링을 하면 having절로 조건을 주는 것보다 질의문을 더 효과적으로 구현할 수 있다.*/

1) 그룹함수를 null 값을 무시합니다. 
2) 테이블 테이터에 null 이 존재하면 그룹함수는 해당 값을 포함하지 않고 계산을 함.

 

41. 데이터 분석 함수로 순위 출력하기 ①

-- 예제41. 직업이 ANALYST, MANAGER 인 사원들의 이름, 직업, 월급과 월급에 대한 순위를 출력하세요
select ename, job, sal, rank() over( order by sal desc ) 순위
    from emp 
    where job in ('ANALYST', 'MANAGER') ;
/* 
    1. rank() : 순위를 뜻함. 
    2. over : 확장하다라는 뜻. 
    3. over 뒤에 rank를 적용할 수 있는 구문을 작성할 수 있음. 
*/
-- 예제41_문제1. 부서번호가 20번인 사원들의 이름과 부서번호와 월급과 월급에 대한 순위를 출력하세요
select ename, deptno, sal, rank() over( order by sal desc) 순위
    from emp
    where deptno = 20 ;

42. 데이터 분석 함수로 순위 출력하기 ②

-- 예제42. 직업이 ANALYST, MANAGER 인 사원들의 이름과 직업과 월급과 순위를 출력하는데 그 옆에 순위가 동일한 사람이 여러명인 경우 바로 다음 순위가 출력되게 하시오.
select ename, job, sal, dense_rank() over( order by sal desc) 순위
    from emp 
    where job in ( 'ANALYST', 'MANAGER') ;
-- 예제42_문제1. 직업, 이름, 월급, 순위를 출력하는데 순위가 직업별로 각각 월급이 높은 사원순으로 순위를 부여하세요.
select job, ename, sal, dense_rank() over( partition by job order by sal desc) 순위 
    from emp ;
-- 예제42_문제2. 월급이 2975인 사원은 사원테이블에서 월급이 순위가 몇위인가?
select dense_rank(2975) within group ( order by sal desc ) 순위
    from emp ;

1) partition by 컬럼명 : '컬럼명' 별로

2) within group 뒤에 rank 를 적용한 컬럼에서 dense_rank(기준값)의 순위를 출력

 

43.  데이터 분석 함수로 등급 출력하기

-- 예제43. 직업이 ANALYST, MANAGER, CLERK 인 사원들의 이름과 직업과 월급과 등급을 출력하는데 등급을 4등급을 나눠서 출력하세요.
select ename, job, sal, ntile(4) over ( order by sal desc ) 등급 
    from emp 
    where job in( 'ANALYST', 'MANAGER') ;

-- 예제43_문제1. 이름, 입사일, 입사한 사원순으로 등급을 나누는데 등급을 5등급으로 나눠서 출력하세요.
select ename, hiredate, ntile(5) over ( order by hiredate asc ) 등급
    from emp ;

1) ntile(등급을 나눌 숫자) over ( order by 컬럼명 desc ) 

 

44. 데이터 분석 함수로 순위 비율 출력하기

-- 예제44. 이름과 월급과 순위와 자신의 월급의 순위에 대한 비율을 출력하세요.
select ename, sal, dense_rank() over ( order by sal desc ) 순위 ,
                        round( cume_dist() over ( order by sal desc), 2) 비율
    from emp ;
-- 예제44_문제1. 부서번호, 이름과 월급과 월급의 순위에 대한 비율을 출력하세요. 순위 비율이 부서번호별 각각 출력되게 하시오
select deptno, ename, sal, round (cume_dist() over ( partition by deptno 
                                                     order by sal desc ), 2 ) 순위
    from emp ;

 

45. 데이터 분석 함수로 데이터를 가로로 출력하기

-- 예제45. 부서번호를 출력하고 해당 부서번호별로 속한 사원들의 이름을 가로로 출력하세요.
select deptno, listagg( ename, ',' ) within group ( order by ename asc ) as 이름 
    from emp 
    group by deptno ;
-- 예제45_문제1. 직업, 직업별로 속한 사원들의 이름을 가로로 출력하는데 가로로 출력될 때에 월급이 높은 사원부터 출력되게 하시오.
select job, listagg( ename, ',' ) within group ( order by sal desc ) as 이름
    from emp 
    group by job ;

 

46. 데이터 분석 함수로 바로 전 행과 다음 행 출력하기

-- 예제46. 직업이 ANALYST, MANAGER 인 사원들의 사원번호와 이름과 월급을 출력하는데 다음과 같이 월급의 그 전행과 그 다음행이 출력되게 하세요. 
select ename, sal, lag(sal, 1) over ( order by sal asc ) 전행, 
                   lead(sal, 1) over ( order by sal asc ) 다음행
    from emp 
    where job in ( 'ANALYST', 'MANAGER') ;
-- 예제46_문제1. 이름, 입사일, 바로 전에 입사한 사원과의 간격일을 출력하세요.
select ename, hiredate - lag(hiredate, 1) over (order by hiredate asc ) as 간격일
			/* lead(hiredate, 1) over( order by hiredate asc ) */
	from emp

 

47. ROW를 COLUMN으로 출력하기 ①

-- 예제 47.부서번호와 부서번호별 토탈 월급을 출력하는데 다음과 같이 가로로 출력하세요
select deptno, sum(sal)
	from emp 
	group by deptno ;
    
select deptno, decode(deptno, 10, sal, 0 ) as "10"
    from emp ;
    
select sum( decode(deptno, 10, sal, 0 ) ) as "10"
    from emp ;    

select sum( decode(deptno, 10, sal, 0 ) ) as "10",
        sum( decode(deptno, 20, sal, 0 ) ) as "20",
        sum( decode(deptno, 30, sal, 0 ) ) as "30"
    from emp ;    
    
select sum( decode(deptno, 10, sal, null ) ) 
    from emp ; 
-- 예제47_문제1 직업, 직업별 토탈 월급을 가로로 출력하세요.
select job, sum(sal) 
    from emp 
    group by job ;
    
select sum( decode ( job, 'ANALYST', sal ) ) as "ANALYST", 
          sum( decode( job, 'CLERK', sal) ) as "CLERK", 
          sum( decode( job, 'MANAGER', sal) ) as "MANAGER", 
          sum( decode( job, 'SALESMAN', sal) ) as "SALESMAN", 
          sum ( decode( job, 'PRESIDENT', sal) ) as "PRESIDENT" 
    from emp  ;

1) 그룹함수는 null 을 연산에서 제외하기 떄문에 연산 효율이 더 좋은 편.

 

48. ROW를 COLUMN으로 출력하기 ②

-- 예제48. 부서번호와 부서번호별 토탈 월급을 출력하는데 다음과 같이 가로로 출력하세요
select * 
    from ( select deptno, sal from emp ) ; 
/* in line view(from절 서브 쿼리) 쿼리문이 가장 처음 실행되는 from절에 새로운 쿼리를 작성하는 방식 */
select * 
    from ( select deptno, sal from emp ) 
    pivot ( sum(sal) for deptno in (10,  20,  30) ) ;

-- 예제48_문제1. 직업, 직업별 토탈월급을 pivot문을 이용하여 가로로 출력하세요
select * 
    from ( select job, sal from emp )
    pivot ( sum(sal) for job in ('ANALYST' as "ANALYST", 'CLERK' as "CLERK", 'SALESMAN' as "SALESMAN" , 'MANAGER' as "MANAGER", 'PRESIDENT' as "PRESIDENT") ) ;

1) pivot(그룹함수 for 기준 컬럼 in (데이터1, 데이터2, ...) )

2) 서브쿼리(sub-query) 는 데이터를 불러올 때 1차적으로 선별하여 불러오기 위해 작성해야 함. 
3) pivot문 다음 select절이 실행되므로 pivot문의 결과를 출력할 수 있도록 select절에 작성해야 함.