[ 오라클] 예제02 - 단일함수, 그룹함수

와니_ 2019. 5. 21. 10:09

<오라클 DB 실습예제 - 단일함수>

-- 1. 입사일이 12월인 사원의 사번, 사원명, 입사일을 검색하시오.

-- 2. 다음과 같은 결과를 검색할 수 있는 SQL 문장을 작성하시오.

EMPNO	ENAME	급여
7369	SMITH	*******800
7499	ALLEN	******1600
...	...	...
7934	MILLER	******1300

-- 3. 다음과 같은 결과를 검색할 수 있는 SQL 문장을 작성하시오.

EMPNO	ENAME	입사일
7369	SMITH	1980-12-17
7499	ALLEN	1981-02-20
...	...	...
7934	MILLER	1982-01-23

<오라클 DB 실습예제 - 그룹함수>

-- 1. 사원테이블에서 부서별 인원수가 6명 이상인 부서코드 검색하시오.

-- 2. 사원테이블로부터 부서번호, 업무별 급여합계를 계산하고자 한다. 다음과 같은 결과를 출력할 수 있는 SQL문장을 작성하시오.

DEPTNO	CLERK	MANAGER	PRESIDENT	ANALYST	SALESMAN
10	1300	2450	5000
20	1900	2975		6000
30	950	2850			5600

-- 3. 사원테이블로부터 년도별, 월별 급여합계를 출력할 수 있는 SQL 문장을 작성하시오.

-- 4-1. 사원테이블에서 부서별 커미션(comm)을 포함하지 않은 연봉의 합을 구하시오.

-- 4-2. 사원테이블에서 부서별 커미션(comm)을 포함한 연봉의 합을 구하시오.

-- 5. 사원테이블에서 SALESMAN을 제외한 JOB별 급여합계를 구하시오.

<풀이 - 단일함수>

--1.

select empno, ename, hiredate from emp where hiredate like '%12%';

-- 입사일이 12월이 아닌, 2012년 또는 12일인 사원도 함께 검색됨.
select empno, ename, hiredate from emp where hiredate like '__/12/__';

-- 정확한 양식을 지켜줘야 답이 나옴
select empno, ename, hiredate from emp where to_char(hiredate, 'MM')='12';

-- 가장 정확한 답변

--2.

select empno, ename, lpad(sal, 10, '*') 급여 from emp;

--3.

select empno, ename, to_char(hiredate, 'yyyy-mm-dd' 입사일 from emp;

<풀이 - 그룹함수>

-- 풀이 1.
select deptno from emp group by deptno having count(*)>=6;

또는 select deptno, count(*) from emp group by deptno having count(*)>=6;

-- 풀이 2.
select deptno,
SUM(decode(job, 'CLERK', sal)) CLERK,
SUM(decode(job, 'MANAGER', sal)) MANAGER,
SUM(decode(job, 'PRESIDENT', sal)) PRESIDENT,
SUM(decode(job, 'ANALYST', sal)) ANALYST,
SUM(decode(job, 'SALESMAN', sal)) SALESMAN
from emp

group by deptno

order by deptno;

-- 풀이 3.
select to_char(hiredate, 'yyyy') 년, to_char(hiredate, 'mm') 월, sum(sal)
from emp
group by to_char(hiredate, 'yyyy'), to_char(hiredate, 'mm')
order by 년, 월; -- ORDER BY문에서는 ALIAS 쓸 수 있음.(GROUP BY문에서는 안 됨)

-- 풀이 4-1.
select deptno, sum(sal)*12 연봉 -- "연봉"은 ALIAS

from emp

group by deptno

order by deptno;

-- 풀이 4-2.
select deptno, sum(sal+nvl(comm, 0))*12 연봉

from emp

group by deptno

order by deptno;

-- 풀이 5.
select job, sum(sal) from emp group by job having job<>'SALESMAN';

또는 select job, sum(sal) from emp where job<>'SALESMAN' group by job