[KOSTA] Spring 기반 Cloud 서비스 개발자 양성 과정 25일차 - SQL 그룹함수 실습

📃 풀이

SELECT EMPNO, RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO
      , ENAME, RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5
  AND LENGTH(ENAME) < 6;

📃 풀이

SELECT EMPNO, ENAME, SAL, TRUNC(SAL/21.5,2) AS DAY_PAY, 
ROUND((SAL/21.5)/8, 1) AS TIME_PAY 
FROM EMP

📃 풀이

SELECT EMPNO, ENAME, HIREDATE, 
TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE,3), '월요일'),'YYYY-MM-DD') AS R_JOB,
NVL(TO_CHAR(COMM), 'N/A')
FROM EMP

SELECT EMPNO, ENAME, MGR, 
CASE WHEN MGR IS NULL THEN '0000'
    WHEN SUBSTR(MGR, 1, 2) = '78' THEN '8888'
    WHEN SUBSTR(MGR, 1, 2) = '77' THEN '7777'
    WHEN SUBSTR(MGR, 1, 2) = '76' THEN '6666'
    WHEN SUBSTR(MGR, 1, 2) = '75' THEN '5555'
    ELSE TO_CHAR(MGR)
    END AS CHG_MGR
    FROM EMP;

📃 풀이

SELECT DEPTNO, TRUNC(AVG(SAL),0), MAX(SAL), MIN(SAL), COUNT(*) FROM EMP
GROUP BY DEPTNO;

SELECT JOB , COUNT(*) FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 3

📃 풀이

SELECT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR, DEPTNO, COUNT(*) FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;

📃 풀이

SELECT NVL2(COMM, 'O','X')AS EXIST_COMM, COUNT(*) FROM EMP
GROUP BY COMM;

SELECT DEPTNO, TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR, 
COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL) 
FROM EMP
GROUP BY ROLLUP (DEPTNO, TO_CHAR(HIREDATE, 'YYYY')) 
ORDER BY DEPTNO;