[Oracle] Oracle 예제 (1)

Q. 각 부서별 같은 업무(JOB)를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수를 출력하라.

단, 부서번호 오름차순으로 정렬하라.

SELECT DEPTNO
      ,JOB
      ,COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO;

Q. 다음 쿼리가 실행되지 않는 이유를 설명하라.

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

A : WHERE절에 그룹함수 사용 불가

Q. 10번 부서의 월급의 평균, 최고 월급, 최저 월급, 인원수를 구하여 출력하라.

SELECT AVG(SAL) AS AVG_SAL
      ,MAX(SAL) AS MAX_SAL
      ,MIN(SAL) AS MIN_SAL
     ,COUNT(*) AS CNT
FROM EMP
WHERE DEPTNO = 10;

Q. EMP 테이블에서 같은 업무를 하는 사람의 수가 2명 이상인 업무와 인원수를 출력하라.

SELECT JOB
      ,COUNT(*) AS CNT
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >= 2;

Q. EMP 테이블에서 부서 인원이 5명 보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하라

SELECT DEPTNO
      ,COUNT(*) AS CNT
      ,SUM(SAL) AS SUM_SAL
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 5;

Q. EMP 테이블에서 부서별 급여 합계 금액이 3000 이상인 부서번호, 급여 합계 금액, 부서인원을 급여 합계가 높은 순으로 출력하라.

SELECT DEPTNO
      ,SUM(SAL) AS SUM_SAL
      ,COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) >= 3000
ORDER BY SUM_SAL DESC;

Q. 아래의 임시 테이블에서 CODE 컬럼의 값이 중복된 개수를 출력하시오.

WITH T AS
(
SELECT 'A' AS CODE FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'E' FROM DUAL
UNION ALL SELECT 'A' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL
)
SELECT * FROM T;

A : SELECT COUNT(COUNT(*)) AS CNT FROM T GROUP BY CODE HAVING COUNT(*) >= 2;

 

Q. 부서코드, JOB, 부서별급여 합계를 아래와 같이 출력하시오.

SELECT CASE WHEN DEPTNO IS NULL AND JOB IS NULL THEN '총계'
            WHEN DEPTNO IS NOT NULL AND JOB IS NULL THEN '' ELSE TO_CHAR(DEPTNO) END AS DEPTNO
      ,CASE WHEN JOB IS NULL AND DEPTNO IS NULL THEN ''
            WHEN JOB IS NULL AND DEPTNO IS NOT NULL THEN '소계('||DEPTNO||')부서' ELSE JOB END AS JOB
      ,'$'||TO_CHAR(SUM(SAL),'FM999,999') AS TOTSUM
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB);

Q. 부서별 각 업무(JOB)에 종사하는 사원의 수를 아래 형식으로 부서번호 내림차순으로 출력하라.

▶ COUNT 함수 활용

SELECT DEPTNO
     ,CASE WHEN COUNT(CASE WHEN JOB = 'ACCOUNT' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'ACCOUNT' THEN 1 END)||'명' END AS ACCOUNT
     ,CASE WHEN COUNT(CASE WHEN JOB = 'ANALYST' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'ANALYST' THEN 1 END)||'명' END AS ANALYST
     ,CASE WHEN COUNT(CASE WHEN JOB = 'DEV' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'DEV' THEN 1 END)||'명' END AS DEV
     ,CASE WHEN COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END)||'명' END AS MANAGER
     ,CASE WHEN COUNT(CASE WHEN JOB = 'QA' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'QA' THEN 1 END)||'명' END AS QA
     ,CASE WHEN COUNT(CASE WHEN JOB = 'SALES' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'SALES' THEN 1 END)||'명' END AS SALES
     ,CASE WHEN COUNT(CASE WHEN JOB = 'SUPPORT' THEN 1 END) != 0 THEN COUNT(CASE WHEN JOB = 'SUPPORT' THEN 1 END)||'명' END AS SUPPORT
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO DESC;

▶ SUM 함수 활용

SELECT DEPTNO
      ,CASE WHEN SUM(CASE WHEN JOB = 'ACCOUNT' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'ACCOUNT' THEN 1 END)||'명' END AS ACCOUNT
      ,CASE WHEN SUM(CASE WHEN JOB = 'ANALYST' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'ANALYST' THEN 1 END)||'명' END AS ANALYST
      ,CASE WHEN SUM(CASE WHEN JOB = 'DEV' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'DEV' THEN 1 END)||'명' END AS DEV
      ,CASE WHEN SUM(CASE WHEN JOB = 'MANAGER' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'MANAGER' THEN 1 END)||'명' END AS MANAGER
      ,CASE WHEN SUM(CASE WHEN JOB = 'QA' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'QA' THEN 1 END)||'명' END AS QA
      ,CASE WHEN SUM(CASE WHEN JOB = 'SALES' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'SALES' THEN 1 END)||'명' END AS SALES
      ,CASE WHEN SUM(CASE WHEN JOB = 'SUPPORT' THEN 1 END) != 0 THEN SUM(CASE WHEN JOB = 'SUPPORT' THEN 1 END)||'명' END AS SUPPORT

FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO DESC;