ROLLUP & CONNECT BY

COUNT 비교

--1. COUNT(*) / COUNT(컬럼명) / COUNT(DISTINCT 컬럼명) 실행결과와 차이점 
--COUNT(*)는 NULL값 까지 카운트를 하는데 COUNT(컬럼)은 NULL값은 세지 않는다.그리고 COUNT(DISTINCT)는 고유개수(중복제거) 값만을 세준다.
SELECT COUNT(*)
FROM EMP;

SELECT COUNT(MGR)
FROM EMP;

SELECT COUNT(DISTINCT MGR)
FROM EMP;
--2. SELECT AVG(COMM) FROM EMP 와 SELECT AVG(NVL(COMM, 0)) FROM EMP

실행결과

NVL()

--실행결과와 차이점. 왜 결과가 다른지?
-- NVL()을 안해주면 NULL인 애들이 0처리가 안되어 카운트도 같이 안된다.
SELECT AVG(COMM) FROM EMP;
 SELECT AVG(NVL(COMM, 0)) FROM EMP;

실행결과

ROLLUP

--3. GROUP BY ROLLUP 을 활용한 소계 연습 
--  부서별 급여 합계 금액을  아래와 같이 표시
 
SELECT CASE WHEN JOB IS NOT NULL THEN TO_CHAR(DEPTNO) WHEN TO_CHAR(DEPTNO) IS NULL AND JOB IS NULL THEN '총계' ELSE NULL END AS DEPTNO
      ,CASE WHEN DEPTNO IS NOT NULL THEN (CASE WHEN (CASE WHEN JOB IS NOT NULL THEN DEPTNO ELSE NULL END) IS NOT NULL THEN JOB ELSE NVL(JOB, '소계(' || DEPTNO || '번부서)') END) ELSE NULL END AS JOB
      ,SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);
 

실행결과

숫자형인 시간 평균값 구하기

--4. EMP_CHULGYEOL테이블에서 2019년 11월 사원별 평균 출근 시간을 구하기.
-- 사원별 토,일요일을 제외한 평일(21)일에 대한 데이터가 모두 들어가있다.
-- 출근시간이 비어있는 데이터는 해당일자에 결근한 사람이다.
-- 결근시에는 18시에 출근한 것으로 처리한다.
-- 출근시간이 빠른순으로 정렬.

SELECT EMPNO
      ,CASE WHEN SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),3,2)>=60 THEN LPAD(SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),1,2)+1,2,0) || LPAD(MOD(SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),3,2),60),2,0) 
      ELSE LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0) END AS AVG_MIN
FROM EMP_CHULGYEOL
GROUP BY EMPNO
ORDER BY EMPNO;

실행결과

CONNECT BY LEVEL

--1. CONNECT BY LEVEL 을 활용하여 1 ~ 10까지 출력하시오
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <=10;
--2. CONNECT BY LEVEL 을 활용하여 0.1 ~ 0.5까지 출력하시오
SELECT LEVEL*0.1
FROM DUAL
CONNECT BY LEVEL <=5;

실행결과

오류이유

--3. 다음 쿼리가 실행되지 않는 이유?
 --WHERE절에서는 집계함수를 사용하지 못한다.
 --사용할려면 HAVING절에서 사용

SELECT * FROM EMPFAMILY
WHERE AVG(AGE) > 40;

마지막,지우기

WITH T AS(
  SELECT '100,101,102,103,' AS TXT FROM DUAL
)
SELECT RTRIM(TXT,',') AS TXT
FROM T;
--위와 같은 임시테이블의데이터에서 마지막 ',' 만 빼고 출력하려면?

실행결과

구분자 위치로 자르기

WITH TMP AS
(
SELECT '10.100.10.1' AS IP FROM DUAL
UNION ALL SELECT '10.100.1.10' FROM DUAL
UNION ALL SELECT '10.10.10.10' FROM DUAL
UNION ALL SELECT '1.10.1.20' FROM DUAL
UNION ALL SELECT '3.10.1.140'  FROM DUAL
)
SELECT IP
     , LPAD( SUBSTR(IP,1,INSTR(IP,'.',1,1)-1) ,3,0) AS IP_1
     , LPAD( SUBSTR(IP,INSTR(IP,'.',1,1)+1,INSTR(IP,'.',1,2)-INSTR(IP,'.',1,1)-1) ,3,0) AS IP_2
     , LPAD( SUBSTR(IP, INSTR(IP,'.',1,2)+1, INSTR(IP,'.',1,3)-INSTR(IP,'.',1,2)-1) ,3,0) AS IP_3
     , LPAD( SUBSTR(IP, INSTR(IP,'.',1,3)+1),3,0) AS IP_4
FROM TMP
ORDER BY IP_1,IP_2,IP_3,IP_4;
-- 위와 같은 임시테이블의 데이터를 '.' 기준으로 분리하여 다음과 같이 출력하시오.

실행결과