오라클 Day 3

📝 목차

Chapter 07 다중행 함수

---

Chapter 07 다중행 함수

📌 문제 1

SELECT DEPTNO,
	   TRUNC(AVG(SAL)) AS AVG_SAL, 
	   MAX(SAL) AS MAX_SAL, 
	   MIN(SAL) AS MIN_SAL, 
	   COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO;

📌 문제 2

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

📌 문제 3

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

📌 문제 4

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

📌 문제 5

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

Reference

• Do it 오라클로 배우는 데이터베이스 입문