SQL pivot, 누적 데이터 출력, 테이블 Join 기초

• Pivot 문은 sum + decode 함수로 구현해서 봐야할 레포팅 결과를 간단하게 검색하게 해주는 오라클 함수

        Row를 column으로 출력하는 함수 --->pivot

        Column을 row로 출력하는 함수 ---> unpivot

 

 @  pivot 문 주의 사항

1. From 절 서브쿼리에 필요한 컬럼만 기술
2. From 절 서브쿼리에 함수를 사용했으면 별칭 반드시 준다

• Unpivot 문

      Unpivot - 가로------------>세로 // 컬럼이 데이터가 된다

      Pivot - 세로 -------------> 가로 // 데이터가 컬럼이 된다.

---

• 누적데이터 출력하는 방법

Ex) select enames, sal, sum(sal) over(order by sal rows between unbounded preceding and current row) 누적치 from emp;

@ unbounded preceding - 제일 첫 번째 행

@ unbounded following - 맨 마지막 행

@ current row - 현재 행

 

Ex) select ename, sal, sum(sal) over(order by sal rows

    between unbounded preceding and unbounded following) 누적치 from emp;

 

• 조인 - 여러개의 테이블의 컬럼들을 하나의 결과로 볼 때 사용하는 sql 문법

     @ 테이블 명시해줘야 빠르다

 

Ex ) select * from dept;

Deptno : 부서번호, dname : 부서명, loc : 부서위치

 

Select * from emp;

 

Ex ) select ename, loc from emp, dept where 조인 조건

-> select ename, loc from emp, dept where emp.deptno = dept.deptno;

조인 종류

1. 오라클 조인 문법

• Equi join : 조인 조건에서 사용하는 연산자가 = 인 경우
• Non equi join : 조인 조건에서 사용하는 연산자가 = 아닌 경우
• Outer join : equi 조인으로는 볼 수 없는 결과를 볼 때 사아ㅛㅏㅇ하는 조인
• Self join : 자기 자신의 테이블과 조인하는 조인

1. 1999 ansi 조인 문법(american national standard institute)

• On 절을 사용한 조인
• Using 절을 사용한 조인
• Left/ right/ full outer 조인
• Cross 조인

 

   Non equi join - 조인하려는 두 테이블 사이의 연결고리 연산자가

   = 이 아닌 경우의 조인

 

Ex) 이름, 월급, 등급을 출력

select e.ename, e.sal, s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

 

 

 

여러개의 테이블 조인하기

Dept---------------emp-----------------salgrade

       조인 연결고리   조인 연결고리             

E.deptno = d.deptno // e.sal between s.losal and s.hisal

@ 테이블 3개 조인하려면, 조인 연결고리가 2개가 있어야 한다.

 

예시

문제 187. 이름, 월급, 부서위치, 급여 등급 출력

Select e.ename, e.sal, d.loc, s.grade

From emp e, dept d, salgrade s

Where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

 

---

SQL 로 구구단 2단 출력하기

-> select '2x'||level||'='||(2*level) from dual connect by level<=9;

SQL로 구구단 전체 출력하기

select n||'x'||m||'='||n*m from     (select level n     from dual      connect by level<=9),          (select level m      from dual      connect by level<=9);

 

SQL로 피타고라스 정리 구현

select case when power(&a,2) + power(&b,2) = power(&c,2)

then '직각삼각형이 맞습니다' else '직각삼각형이 아닙니다' end from dual;

 

---

- pivot 문제

 

문제 157. pivot 문으로 직업과 직업별 토탈 월급을 가로로 출력

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

 

문제 158. 입사한 년도, 입사한 년도별 토탈월급 세로로 출력

-> select to_char(hiredate,'RRRR'), sum(sal) from emp group by to_char(hiredate, 'RRRR');

 

문제 159. 158번 문제를 pivot문을 사용해서 가로로 출력하세요

-> select * from (select to_char('hiredate','RRRR') as hire_year, sum(sal) from emp)

    pivot(sum(sal) for hire_year in ('1980' as "1980",'1981' as "1981",'1982' as "1982",'1983' as "1983"));

 

문제 160. 입사한 년도, 입사한 년도별 인원 수 세로로 출력하세요

-> select to_char(hiredate, 'RRRR'), count(*) from emp group by to_char(hiredate, 'RRRR');

 

문제 161. 입사한 년도, 입사한 년도 별 인원수를 가로로 출력하세요

-> select * from

    (select to_char(hiredate,'RRRR") as hire_year, empno as emp from emp)

pivot(count(emp) for hire_year in ('1980','1981','1982','1983'));

 

---

- unpivot 문제

 

문제 162. 살인이 가장 많이 일어나는 시간대

-> select * from (select crime_type, dense_rank() over (order by c desc) 순위

    from crime_time

    unpivot(c for time in (F0T3,F3T6, F6T9,F9T12,F12T15,F15T18,F18T21,F21T24))

    where crime_type ='살인')

    where rownum = 1;

 

문제 163. 치킨집 폐업이 가장 많았던 년도

-> select * from

    (select 년도, 치킨집, rank() over(order by 치킨집 desc) 순위 from closing)

    where rownum=1;

 

---

- 누적 데이터 구하기 문제

 

문제 164. 이름, 나이, 나이의 누적치가 출력되게 하세요

-> select ename, age, sum(age) over(order by age rows between unbounded preceding and current row) 누적나이 from emp11;

 

문제 165. 통신사, 이름, 나이, 나이의 누적치가 출력되게 할 것, 나이의 누적치가 통신사별로 각각 누적

-> select lower(telecom), ename, age, sum(age) over(partition by lower(telecom) order by age rows between unbounded preceding and current row) 누적나이 from emp11;

 

문제 166. 직업, 이름, 월급, 월급의 누적치 출력, 직업별로 각각 파티션해서 월급 출력

-> select job, ename, sal, sum(sal) over (partition by job order by sal rows between unbounded preceding and current row) 누적월급 from emp;

문제 167. 직업, 이름, 월급, 월급에 대한 순위 출력

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

 

문제 168. 직업, 이름, 월급 월급에 대한 순위, 직업별로 파티션 해서 출력

-> Select job, ename, sal, rank() over(partition by job order by sal desc)  순위 from emp;

문제 169. 위의 결과에서 순위가 1등인 사원들만 출력하세요

-> select * from(

select job, ename, sal, rank() over(partition by job order by sal desc) 순위 from emp;

) where 순위 = 1;

 

문제 170. 통신사별로 나이가 제일 많은 학생의 이름과 나이와 통신사 출력

-> select * from(

    select job, ename, sal, rank() over(partition by lower(telecom) order by age desc) 순위 from emp11

) where 순위 =1;

 

 

---

- 조인 문제

 

문제 174. dallas에서 근무하는 사원들의 이름과 월급, 부서위치를 출력

-> select ename, sal, loc from emp, dept where emp.deptno = dept.deptno and dept.loc = 'DALLAS';

 

문제 175. 월급이 1000에서 3000사이인 사원들의 이름과 월급과 부서위치

->  select ename, sal, loc from emp, dept where emp.deptno = dept.deptno and emp.sal between 1000 and 3000;

 

문제 176. 월급이 2800 이상인 사원들의 이름과 월급과 부서위치 출력

select e.ename, e.sal, d.loc

from emp e, dept d

where e.deptno = d.deptno and e.sal>=2800

 

문제 178.직업이 SALESMAN인 사원들의 이름과 직업과 부서위치 출력

select e.ename, e.job, d.loc

from emp e, dept d

where e.deptno = d.deptno and job = 'SALESMAN';

 

문제 179. 커미션이 null인 사원들의 이름과 커미션과 부서명을 출력

select e.ename ,d.dname

from emp e, dept d

where e.deptno = d.deptno and comm is null;

 

문제 180. CHICAGO 에서 근무하는 사원들 중에 월급이 1000 이상인 사원들의 이름, 직업, 월급과 부서위치 출력

select e.ename, e.job, d.loc

from emp e, dept d

where d.loc = 'CHICAGO' and e.sal>=1000 and d.deptno = e.deptno;

 

문제 181. 부서위치, 부서위치 별 토탈 월급

select d.loc, sum(e.sal)

from emp e, dept d

where d.deptno = e.deptno group by d.loc;

문제 182. 위의 결과에서 부서위치 별 토탈 월급이 9000이상만 출력

select d.loc, sum(e.sal)

from emp e, dept d

where d.deptno = e.deptno group by d.loc

having sum(e.sal)>=9000;

문제 183. 위의 결과 다시 출력, 부서 위치 별 토탈 월급 낮은 것부터 출력

select d.loc, sum(e.sal)

from emp e, dept d

where d.deptno = e.deptno group by d.loc

having sum(e.sal)>=9000

order by sum(e.sal) asc;

 

 

---

-   Non equi join 문제

 

문제 184. 급여등급이 5등급인 사원들만 출력

-> select e.ename, e.sal, s.grade

    from emp e, salgrade s

    where e.sal between s.losal and s.hisal and s.grade = 5;

 

문제 185. 급여등급, 해당 급여등급에 속한 사원들의 이름을 가로로 출력

select s.grade, listagg(e.ename,',')

    within group (order by e.ename asc)

    from emp e, salgrade s

    where e.sal between s.losal and s.hisal group by s.grade;

 

 

문제 186. 위의 결과를 아래와 출력

select s.grade, listagg(e.ename||'('||e.sal||')',',')

    within group (order by e.ename asc)

    from emp e, salgrade s

    where e.sal between s.losal and s.hisal group by s.grade;