4 SQL . Select 의 6가지 절, 분석 전용 함수

select의 6가지 절

• select
• from
• where - 그룹함수 사용 불가
• group by
• having - 그룹함수 사용가능
• order by

코딩 순서 : Select - from - where - group by - having - order by

실행 순서 : from - where - group by - having - select - order by

 

connect by - 각 행들의 연결 관계를 설정,

                  CONNECT BY 절의 결과에는 LEVEL 이라는 컬럼이 있으며, 이는 계층의 깊이를 의미

 

Sum 함수 - 토탈 값을 출력하는 함수

Count 함수 - 건 수를 세는 함수 / Null 값 무시

 

그룹함수 - max, min, avg, count, sum, variance(분산) , stddev (표준편차)

• Variance 함수 : 분산을 구하는 그룹함수 => 데이터의 퍼짐 정도

                         - 평균을 중심으로 얼마나 데이터가 퍼져있는지 데이터의 퍼짐정도

 

Ex) 사원테이블의 월급의 분산값을 출력

-> select variance(sal) from emp;

 

 

• Stddv 표준편차 구하는 함수 - 표준편차는 분산값에 루트 씌운 값 ,실제 데이터와 평균값의 차이

Ex) select stddev(sal) from emp;

 

• 데이터 분석함수 - 데이터 분석을 용이하게 해주는 함수

1. Rank
2. Dense_rank
3. Ntile
4. Lisgagg
5. Lead
6. Lag
7. Pivot
8. Unpivot
9. 누적 데이터를 출력하는 함수

rank : 순위를 출력하는 함수

Ex) 이름, 월급, 월급에 대한 순위

-> select ename, sal, rank() over (order by sal desc) as 순위 from emp;

Listagg 함수 : 결과를 가로로 출력하는 함수

Ex) select deptno, listagg(ename,',') within group (order by ename) 이름 from emp group by deptno;

 

Ntile 함수 - 등급을 출력하는 함수

Ex) select ename, sal, ntile(4) over (order by sal desc) 등급 from emp;

@ ntile(4) -> 0~ 25% : 1 / 25~50% : 2 / 50 ~ 75% : 3 / 75 ~ 100%  : 4

Lag 함수는 바로 전행을 출력

Lead 함수는 바로 다음행을 출력하는 함수

Ex) select ename, sal, lag(sal, 1) over (order by sal asc) 이전행,

     lead(sal, 1) over (order by sal asc) 다음행 from emp;

 

---

문제 108. 직업, 직업별 최대 월급을 출력하는데, 직업별 최대월급이 높은 직업부터 출력

-> select job, max(sal) from emp group by job order by max(sal) desc;

 

문제 109. 108번 문제에서 SALESMAN은 제외하고 출력

-> select job, max(sal) from emp where job != 'SALESMAN' group by job order by max(sal) desc;

• not in은 여러개의 값에 대해서 사용하는 게 좋음
• != 은 하나의 값만 비교할 때 사용할 것

 

문제 111. 부서번호, 부서번호 별 평균 월급 출력, 부서번호별 평균 월급이 낮은 것부터 출력

-> select deptno, avg(sal) from emp group by deptno order by avg(sal) asc;

문제 112. 111번 문제의 결과에서 평균 월급이 2000 이상인 것만 출력

-> select deptno, avg(sal) from emp group by deptno having avg(sal)>=2000 order by avg(sal) asc;

 

 

문제 113. 1부터 10까지의 숫자의 평균값

-> select avg(level) from dual connect by level<=10;

 

• 그룹함수의 특징 -> 그룹함수는 null 값을 무시한다.
• Group 함수는 null 값을 무시하기 때문에, 튜닝 후 sql이 더 검색속도가 빠르다

문제 114. 커미션의 평균값 출력

-> select avg(comm) from emp;

 

문제 115. 위의 커미션을 null을 0으로 변경하고 전체 사원수로 나눠서 평균값 출력

-> select avg(nvl(comm,0)) from emp;

 

문제 117. 직업, 직업별 토탈 월급 출력

-> select job, sum(sal) from emp group by job;

 

문제 118. 위의 결과를 다시 출력하는데, 직업이 Salesman은 제외하고 출력

-> select job, sum(sal) from emp where job != 'SALESMAN' group by job;

 

문제 119. 위의 결과를 다시 출력하는데, 직업별 토탈월급이 6000 이상인 것만 출력

-> select job, sum(sal) from emp where job != 'SALESMAN'

    group by job having sum(sal)>=6000

 

 

문제 120. 위의 결과 다시 출력, 직업별 토탈 월급이 높은 것부터

-> select job, sum(sal)  from emp where job != 'SALESMAN'

    group by job having sum(sal)>=6000 order by sum(sal) desc;

 

문제 128. 직업의 종류가 몇개인지 확인

-> select count(distinct job) from emp;

 

문제 129. 직업, 직업 별 토탈 월급 출력

-> select job, sum(sal) from emp group by job;

 

문제 130. 위의 결과에서 최대값 출력

-> select max(sum(sal)) from emp group by job;

 

문제 131. connect by 절 이용해서 1부터 10사이의 숫자 중 짝수만 출력

-> select level from dual where mod(level,2) =0 connect by level<=10;

 

문제 132. 1부터 10 까지 홀수 출력

-> select level from dual where mod(level,2) = 1 connect by level<=10;

 

문제 134. 1부터 10까지 곱을 출력

->select round(exp(sum(ln(level)))) from dual connect by level<=10;

 

 

 

문제 136. 직업이 salesman인 사원들의 이름과 직업과 입사일과 순위 출력

              순위가 먼저 입사한 사원 순으로 출력

-> select ename, job,hiredate ,rank() over(order by hiredate asc) from emp

    where job = 'SALESMAN';

 

문제 137. 순위가 1인 사원만 출력

-> select ename, job, hiredate, 순위 from

(select ename, job,hiredate ,rank() over(order by hiredate asc) 순위

from emp

where job = 'SALESMAN')

where rownum=1;

 

 

문제 140. 월급이 2450인 사원은 사원 테이블에서 순위가 어떻게 되는가

-> select rank(2450) within group (order by sal desc) 순위 from emp;

 

문제 141. 81/11/17 에 입사한 사원은 사원 테이블에서 몇번째로 입사한 사원

-> select rank('81/11/17') within group (order by hiredate asc) 순위 from emp;

-> select rank(to_date('81/11/17','RR/MM/DD')) within group (order by hiredate asc) 순위 from emp;

 

문제 145. 1 ~ 10 까지의 숫자 중에 짝수를 가로로 출력

-> select listagg(level,',') 짝수 from dual where mod(level,2)=0 connect by level<=10;

 

문제 146. 위의 sql에 치환 변수를 사용해서 숫자를 직접 지정할 수 있게 하시오

@치환 변수 &

-> select listagg(level,',') 짝수 from dual where mod(level,2)=0 connect by level<=&숫자;

 

 

문제 147. 이름, 입사일, 입사한 사원순으로 등급을 나누는데 5등급으로 나누시오

-> select ename, hiredate, ntile(5) over (order by hiredate asc) from emp;

 

문제 148. 하나의 동전을 100,000 번 던졌을때 앞면이 나올 확률

-> select count(round(dbms_random.value(0,1))) / 100000 as 확률 from dual where round(dbms_random.value(0,1))= 1  connect by level<=100000;

문제 149. 치환변수 사용해서 동전 던지는 횟수 sql 실행할 때마다 다르게 하세요

-> select count(round(dbms_random.value(0,1))) / 100000 as 확률 from dual

where round(dbms_random.value(0,1))= 1  connect by level<=&숫자;

 

 

문제 150. 이름, 입사일, 바로 전에 입사한 사원과의 간격일  출력

-> select ename, hiredate,

    hiredate-lag(hiredate,1) over (order by hiredate asc) 간격 from emp;

 

 

문제 151. 직업, 직업별 토탈 월급을 출력하세요

-> select job, sum(sal) from emp group by job;

-> select * from (select job, sal from emp) pivot (sum(sal) for job in ('SALESMAN', 'CLERK', 'ANALYST','MANAGER','PRESIDENT'));

 

-> select sum(decode(job, 'SALESMAN',sal)) as "SALESMAN",

    sum(decode(job,'CLERK', sal)) as "CLERK",

    sum(decode(job,'ANALYST', sal)) as "ANALYST",

    sum(decode(job,'MANAGER', sal)) as "MANAGER",

    sum(decode(job,'PRESIDENT', sal)) as "PRESIDENT"

    from emp;

 

 

문제 153. 위의 결과는 토탈 월급, 평균 월급이 출력되게 하세요

-> select deptno, avg(decode(job,'SALESMAN', sal)) as "SALESMAN",

                      avg(decode(job,'CLERK', sal)) as "CLERK",

                     avg(decode(job,'ANALYST', sal)) as "ANALYST",

                     avg(decode(job,'MANAGER', sal)) as "MANAGER",

        avg(decode(job,'PRESIDENT', sal)) as "PRESIDENT"

      from emp group by deptno;