select '10' "DEPTNO",
sum(decode(
job,
'ANALYST',
sal,
0
)) "ANALYST",
sum(decode(
job,
'CLERK',
sal,
0
)) "CLERK",
sum(decode(
job,
'MANAGER',
sal,
0
)) "MANAGER",
sum(decode(
job,
'PRESIDENT',
sal,
0
)) "PRESIDENT",
sum(decode(
job,
'SALESMAN',
sal,
0
)) "SALESMAN",
sum(sal)
from emp
where deptno = 10
union all
select '20' "DEPTNO",
sum(decode(
job,
'ANALYST',
sal,
0
)) "ANALYST",
sum(decode(
job,
'CLERK',
sal,
0
)) "CLERK",
sum(decode(
job,
'MANAGER',
sal,
0
)) "MANAGER",
sum(decode(
job,
'PRESIDENT',
sal,
0
)) "PRESIDENT",
sum(decode(
job,
'SALESMAN',
sal,
0
)) "SALESMAN",
sum(sal)
from emp
where deptno = 20
union all
select '30' "DEPTNO",
sum(decode(
job,
'ANALYST',
sal,
0
)) "ANALYST",
sum(decode(
job,
'CLERK',
sal,
0
)) "CLERK",
sum(decode(
job,
'MANAGER',
sal,
0
)) "MANAGER",
sum(decode(
job,
'PRESIDENT',
sal,
0
)) "PRESIDENT",
sum(decode(
job,
'SALESMAN',
sal,
0
)) "SALESMAN",
sum(sal)
from emp
where deptno = 30
union all
select '토탈' "DEPTNO",
sum(decode(
job,
'ANALYST',
sal,
0
)) "ANALYST",
sum(decode(
job,
'CLERK',
sal,
0
)) "CLERK",
sum(decode(
job,
'MANAGER',
sal,
0
)) "MANAGER",
sum(decode(
job,
'PRESIDENT',
sal,
0
)) "PRESIDENT",
sum(decode(
job,
'SALESMAN',
sal,
0
)) "SALESMAN",
sum(sal)
from emp;
select nvl(to_char(deptno), '토탈'),
sum(decode(
job,
'ANALYST',
sal,
0
)) "ANALYST",
sum(decode(
job,
'CLERK',
sal,
0
)) "CLERK",
sum(decode(
job,
'MANAGER',
sal,
0
)) "MANAGER",
sum(decode(
job,
'PRESIDENT',
sal,
0
)) "PRESIDENT",
sum(decode(
job,
'SALESMAN',
sal,
0
)) "SALESMAN",
sum(sal)
from emp group by grouping sets ((deptno), ());
카페 게시글
2. SQL 수업
Re: 오늘의 마지막 문제. (SQLP 기출문제) 다음과 같이 결과를 출력하시오
다음검색