Jaybon 의 지식창고

실시간 데이터 처리
OLTP

정기적 데이터 처리
OLAP

GROUP BY ROLLUP(DEPTNO, JOB)
1. 그룹바이
2. DEPTNO별 소계 --ROLLUP의 특징
3. DEPTNO, JOB 소계 --GROUP의 원래기능

원본테이블

SELECT *
FROM CAL;

요일을 열이름으로 하여 달력만들기

SELECT
MAX(DECODE(DAY, '일', NUM_DAY)) "일", 
MAX(DECODE(DAY, '월', NUM_DAY)) "월",
MAX(DECODE(DAY, '화', NUM_DAY)) "화", 
MAX(DECODE(DAY, '수', NUM_DAY)) "수",
MAX(DECODE(DAY, '목', NUM_DAY)) "목", 
MAX(DECODE(DAY, '금', NUM_DAY)) "금",
MAX(DECODE(DAY, '토', NUM_DAY)) "토"
FROM CAL
GROUP BY WEEK
ORDER BY WEEK;

EMP 원본

SELECT *
FROM EMP;

직업별 부서별 인원수

SELECT DEPTNO, 
COUNT(DECODE(JOB, 'CLERK', 0)) "CLERK",
COUNT(DECODE(JOB, 'MANAGER', 0)) "MANAGER",
COUNT(DECODE(JOB, 'PRESIDENT', 0)) "PRESIDENT",
COUNT(DECODE(JOB, 'ANALYST', 0)) "ANALYST",
COUNT(DECODE(JOB, 'SALESMAN', 0)) "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

PROFESSOR 원본

SELECT *
FROM PROFESSOR;

교수직책별 평균급여

SELECT 
FLOOR(AVG(DECODE(POSITION, '정교수', PAY))) "정교수",
FLOOR(AVG(DECODE(POSITION, '조교수', PAY))) "조교수",
FLOOR(AVG(DECODE(POSITION, '전임강사', PAY))) "전임강사"
FROM PROFESSOR;

월급과 보너스를 합하기

원본

SELECT *
FROM EMP;

SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM)
FROM EMP;

소계값, 총계값을 UNION ALL 로 만들기

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 10

UNION ALL

-- 유니온 올 하려면 타입을 같게 맟춰야한다 TO_CHAR까지...
SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 10

UNION ALL

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 20

UNION ALL

SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 20

UNION ALL

SELECT ENAME, JOB, DEPTNO, TO_CHAR(S,'$999,999')
FROM
(
SELECT ENAME, JOB, SAL+DECODE(COMM, null, 0, COMM) "S", DEPTNO
FROM EMP
ORDER BY DEPTNO
)
WHERE DEPTNO = 30

UNION ALL

SELECT '소계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP
WHERE DEPTNO = 30

UNION ALL

SELECT '총계', NULL, NULL, TO_CHAR(SUM(SAL), '$999,999') "SUM"
FROM EMP;

SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO

UNION ALL

SELECT DEPTNO, JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO, JOB

UNION ALL

SELECT NULL DEPTNO, NULL JOB, ROUND(AVG(SAL),1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
ORDER BY DEPTNO, JOB;

소계값, 총계값을 ROLLUP으로 구하기

SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY ROLLUP (DEPTNO, JOB);

--학생 테이블의 키의 평균을 구하시오.
SELECT AVG(HEIGHT)
FROM STUDENT;

--학생 테이블에서 DEPTNO1 별 학생들의 키의 평균을 구하시오. 
SELECT DEPTNO1, AVG(HEIGHT)
FROM STUDENT
GROUP BY DEPTNO1;

--학생 테이블에서 DEPTNO1별 학생들의  키의 평균과 소계값을 구하시오.
SELECT DEPTNO1, GRADE, ROUND(AVG(HEIGHT), 1) , COUNT(*)
FROM STUDENT
GROUP BY ROLLUP (DEPTNO1, GRADE);

-- 직업별 급여합계 조회 마지막줄에 총합계 추가해서 표시
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (JOB);

--PANMAE 테이블에서 날짜별(P_DATE) 상품이(P_CODE) 몇개(P_QTY) 판매 되었는지와 소계값을 구하시오
SELECT P_DATE, P_CODE, SUM(P_QTY)
FROM PANMAE
GROUP BY ROLLUP (P_DATE, P_CODE);

--PANMAE 테이블에서 상품별(P_CODE)로 날짜마다(P_DATE) 얼마의 금액(P_TOTAL)이 판매 되었는지와 소계값을 구하시오
SELECT P_CODE, P_DATE, SUM(P_TOTAL)
FROM PANMAE
GROUP BY ROLLUP (P_CODE, P_DATE);

환경
윈도우10 64비트
오라클 18c 익스프레스

스캇 예제
예제를 확인하시려면

https://ondolroom.tistory.com/163

MAX

SELECT MAX(SAL), ENAME
FROM EMP;

MAX는 한줄 ENAME는 여러줄 이기 때문에 오류가 난다 

GROUP BY

SELECT MAX(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;

-- GROUP BY에 나눌 컬럼을 적고 SELECT에 그룹핑할 컬럼을 적어라
-- 문법은 행의 개수를 맟추는 것
SELECT MAX(SAL), JOB
FROM EMP
GROUP BY JOB;

-- 그룹을 2가지 이상하면 많은 것을 기준으로 그룹
SELECT MAX(SAL), DEPTNO, ENAME
FROM EMP
GROUP BY DEPTNO, ENAME;

DEPTNO를 그룹으로 만들었으나 ENAME가 양이 더 많기때문에 중복으로 나타남

-- 부서별 월급의 총합을 구하고 부서를 오름차순으로 정렬 
SELECT SUM(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

-- 직업별 직원수를 구하라
SELECT COUNT(*), JOB
FROM EMP
GROUP BY JOB;

-- 직업별 연봉의 평균(+소수점 탈락)
SELECT FLOOR(SUM(SAL)/COUNT(JOB)), JOB
FROM EMP
GROUP BY JOB
HAVING JOB != 'MANAGER';

HAVING을 쓰는 이유!

--그룹함수(AVG 등)은 무조건 GROUP BY 다음에 써야한다, 그래서 HAVING을 사용
SELECT DEPTNO, AVG(NVL(SAL,0))
FROM EMP
GROUP BY DEPTNO 
HAVING AVG(NVL(SAL,0)) > 2000;

서브쿼리

-- 서브쿼리 (셀렉트 문안에 또 셀렉트가 들어가는 것)
SELECT  -- 스칼라 서브쿼리
FROM  -- 인라인뷰 서브쿼리
WHERE   -- ? 서브쿼리

인라인뷰 서브쿼리

일단 테이블에서 원하는 자료만 추출한다

SELECT ENAME "EN"
FROM EMP;

해당코드를 FROM( 이곳 )에 넣는다

SELECT *
FROM
(
SELECT ENAME "EN"
FROM EMP
)
WHERE EN = 'SMITH';

ROWNUM

--ROWNUM은 하드디스크에서 가져온 순서대로 번호를 매김

SELECT ROWNUM
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9;

SELECT MAX(POINT)
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9;

UNION ALL

SELECT MAX(POINT)
FROM GOGAK
WHERE ROWNUM > 0 AND ROWNUM < 9

UNION ALL --합치기

--1번부터 불러오지 않으면 NULL이뜬다
SELECT MAX(POINT)
FROM 
( -- 서브쿼리
  SELECT GNO, POINT, ROWNUM "NO"
  FROM GOGAK
)
WHERE NO > 8 AND NO < 21;

위코드를 DECODE나 CASE를 사용해서 만들어본다

DECODE

-- DECODE로 구분하기, DECODE는 부등호를 쓸수 없다 
SELECT GNO, GNAME, POINT, DECODE(NO, 1, 1, 2) "NO" 
FROM 
( 
  SELECT GNO, GNAME, POINT, CEIL(ROWNUM /9) "NO" 
  FROM GOGAK 
);

CASE

-- CASE로 구분하기
SELECT GNO, GNAME, CASE WHEN NO < 9 THEN 1 ELSE 2 END "NO"
FROM
(
  SELECT GNO, GNAME, POINT, ROWNUM "NO"
  FROM GOGAK
);

위 코드들이 서브쿼리의 인수가 된다

SELECT NO, MAX(POINT)
FROM
(
  SELECT GNO, GNAME, POINT, DECODE(NO, 1, 1, 2) "NO"
  FROM
  (
    SELECT GNO, GNAME, POINT, CEIL(ROWNUM /9) "NO"
    FROM GOGAK
  )
)
GROUP BY NO;

또는

-- 세로연산의 핵심은 그룹을 만드는 것

SELECT MAX(POINT), G
FROM
(
    SELECT point, CASE WHEN rownum < 9 THEN 1 ELSE 2 END "G"
    FROM GOGAK
)
GROUP BY G;

-- 5명씩 끊어서 POINT의 합을 구하시오
SELECT SUM(POINT)
FROM
(
    SELECT POINT, CEIL(ROWNUM/5) "FIVE"
    FROM GOGAK
)
GROUP BY FIVE;

-- 같은 나이의 POINT의 합을 구하시오
SELECT SUM(POINT)
FROM
(
    SELECT POINT, SUBSTR(JUMIN, 1, 2) "NAI"
    FROM GOGAK
)
GROUP BY NAI;

-- 순위매기기
SELECT ENAME, SAL, ROWNUM
FROM
(
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC
);

환경
윈도우10 64비트
오라클 18c 익스프레스

스캇 예제
예제를 확인하시려면

https://ondolroom.tistory.com/163

문제 링크

https://blog.naver.com/codingspecialist/221056527127

https://blog.naver.com/codingspecialist/221056538130

https://blog.naver.com/codingspecialist/221056546987

정답

SELECT *
FROM emp;

--1
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10;


--2
SELECT ename, hiredate, deptno
FROM emp
WHERE empno = 7369;


--3
SELECT *
FROM emp
WHERE ename = 'ALLEN';

DESC emp;


--4
SELECT *
FROM emp
WHERE hiredate = '1980/12/17';


--5
SELECT *
FROM emp
WHERE job != 'MANAGER';


--6
SELECT *
FROM emp
WHERE hiredate > '1981/04/02';


--7
SELECT ENAME, SAL, DEPTNO
FROM emp
WHERE SAL >= 800;


--8
SELECT *
FROM emp
WHERE DEPTNO >= 20;


--9
SELECT *
FROM emp
WHERE hiredate < '1981/12/09';


--10
SELECT EMPNO, ENAME
FROM emp
WHERE EMPNO <= 7698;


--11
SELECT *
FROM emp
WHERE hiredate BETWEEN '1981/04/03' AND '1982/12/08';


--12
SELECT *
FROM emp
WHERE SAL BETWEEN 1601 AND 2999;


--13
SELECT *
FROM emp
WHERE SAL < 7654 OR SAL > 7782;


--14
SELECT *
FROM emp
WHERE REGEXP_LIKE(SUBSTR(ENAME, 1,1), '[C-I]');


--15
SELECT *
FROM emp
WHERE SUBSTR(HIREDATE, 1, 2) <> '81';


--16
SELECT *
FROM emp
WHERE JOB = 'SALESMAN' OR JOB = 'MANAGER';


--17
SELECT *
FROM emp
WHERE DEPTNO != 20 AND DEPTNO != 30;


--18
SELECT empno, ename, hiredate, deptno
FROM emp
WHERE SUBSTR(ENAME, 1, 1) = 'S';


--19
SELECT *
FROM emp
WHERE SUBSTR(HIREDATE, 1, 2) = '81';


--20
SELECT *
FROM emp
WHERE ENAME LIKE '%S%';


--21
SELECT *
FROM emp
WHERE ENAME LIKE 'S%' AND ENAME LIKE '%T';


--22
SELECT *
FROM emp
WHERE ENAME LIKE '_A%';


--23
SELECT *
FROM emp
WHERE COMM IS NULL;


--24
SELECT *
FROM emp
WHERE COMM IS NOT NULL;


--25
SELECT ENAME, SAL, DEPTNO, COMM
FROM emp
WHERE deptno = 30 AND sal >= 1500;


--26
SELECT empno, ename, deptno
FROM emp
WHERE SUBSTR(ENAME, 1, 1) = 'k' or deptno = 30;


--27
SELECT *
FROM emp
WHERE sal >= 1500 and deptno = 30 AND JOB = 'MANAGER';


--28
SELECT *
FROM emp
WHERE deptno = 30
ORDER BY EMPNO;


--29
SELECT *
FROM emp
ORDER BY SAL DESC;

--30
SELECT *
FROM emp
ORDER BY DEPTNO, SAL DESC;


--31
SELECT *
FROM emp
ORDER BY DEPTNO DESC, ENAME, SAL DESC;


--32
SELECT *
FROM emp
WHERE COMM IS NOT NULL
ORDER BY (SAL+COMM) DESC;


--33
SELECT ENAME, SAL, SAL*0.13 "BONUS", deptno
FROM emp
WHERE DEPTNO = 10;


--34
SELECT ENAME, deptno, SAL, sal*12+sal*1.5 "INCOME"
FROM emp
WHERE DEPTNO = 30;


--35
SELECT ENAME, SAL, sal*0.15
FROM emp
WHERE sal >= 2000;


--36
SELECT ENAME, SAL, sal-sal*0.1
FROM emp
ORDER BY sal DESC;

오라클 명령어 실행순서

5. SELECT
1. FROM
2. WHERE - 조건
3. GROUP BY
4. HAVING - 조건
6. ORDER BY

89~110 페이지 개인공부

환경
윈도우10 64비트
오라클 18c 익스프레스

스캇 예제
예제를 확인하시려면

https://ondolroom.tistory.com/163

DECODE

SELECT DECODE(조건값, 비교할갌, 참일경우 출력, 거짓일 경우 출력)
FROM DUAL;

-- DECODE = IF문과 같이 사용, 인수는(조건변수, 비교할변수, 참일경우, 거짓일경우)

SELECT DECODE('LOVE', 'LOVE','참', '거짓')
FROM DUAL;

SELECT deptno, name, DECODE(deptno, 101, 'Computer Engineering', ' ')
FROM professor;

SELECT deptno, name, DECODE(deptno, 101, 'Computer Engineering', 'ETC')
FROM professor;

SELECT deptno, name, DECODE(조건값, 비교할값1, 비교할값1 일 경우 출력, 
                                    비교할값2, 비교할값2 일 경우 출력,
                                    비교할값3, 비교할값3 일 경우 출력,
                                    비교할 값이 모두 아닐경우) "열 이름"
FROM professor;

SELECT deptno, name, DECODE(deptno, 101, 'Computer Engineering', 
                                    102, 'Multimidia Engineering',
                                    103, 'Software Engineering',
                                    'ETC') "DNAME"
FROM professor;

SELECT deptno, name, DECODE(조건값, 비교할값, DECODE(조건값2, 비교할값2, 참일경우 출력2, 거짓일 경우 출력2), 거짓일 경우 출력) "BEST"
FROM professor;

SELECT deptno, name, DECODE(deptno, 101, DECODE(name, '조인형', 'BEST!', ' '), ' ') "BEST"
FROM professor;

SELECT deptno, name, DECODE(deptno, 101, DECODE(name, '조인형', 'BEST!', 'GOOD!'), ' ') "BEST"
FROM professor;

SELECT deptno, name, DECODE(deptno, 101, DECODE(name, '조인형', 'BEST!', 'GOOD!'), 'N/A') "BEST"
FROM professor;

SELECT name, jumin, DECODE(SUBSTR(jumin, 7,1), '1','MAN','2', 'WOMAN') "Gender"
FROM student
WHERE deptno1 = 101;

SELECT name, tel, DECODE(SUBSTR(tel, 1, INSTR(tel,')')-1), '02', 'SEOUL',
                                '031', 'GYEONGGI',
                                '051', 'BUSAN',
                                '052', 'ULSAN',
                                '055', 'GYEONGNAM') "LOC"
FROM student
WHERE deptno1 = 101;

환경
윈도우10 64비트
오라클 18c 익스프레스

스캇 예제
예제를 확인하시려면

https://ondolroom.tistory.com/163

현재날짜 출력

-- 오늘 날짜 출력
SELECT SYSDATE
FROM DUAL;

아래링크처럼 적용하면 시간까지 출력가능

참고

http://www.java2s.com/Code/Oracle/SQL-Plus/altersessionsetnlsdateformatddMONyyyyhh24miss.htm