-- 1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하라.
SELECT
DEPTNO 부서번호,
COUNT(*) 인원수,
SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 4;
-- 2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하라.
SELECT
DEPTNO 부서번호,
COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) =
(SELECT MAX(COUNT(*)) FROM EMP GROUP BY DEPTNO);
-- 3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하라.
SELECT
MGR 사원번호
FROM EMP
GROUP BY MGR
HAVING COUNT(MGR) =
(SELECT MAX(COUNT(*)) FROM EMP GROUP BY MGR);
-- 4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하라.
SELECT
COUNT(DECODE(DEPTNO, 10, 0)) "10인 사원수",
COUNT(DECODE(DEPTNO, 30, 0)) "30인 사원수"
FROM EMP;
-- 5. EMP 테이블에서 사원번호가 7521인 사원의 직업과 같고 사원번호가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하라.
SELECT
EMPNO 사원번호,
ENAME 이름,
JOB 직업,
SAL 급여
FROM EMP
WHERE
JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7521)
AND
SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7934);
-- 6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하라.
-- 조건1 : 직업별로 내림차순 정렬
SELECT
A.EMPNO 사원번호,
A.ENAME 이름,
A.JOB 업무,
B.DNAME
FROM EMP A, DEPT B
WHERE
A.DEPTNO = B.DEPTNO
AND
SAL IN
(SELECT MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB DESC;
-- 7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하라.
-- 조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.
-- 조건2. 시급은 소수 두 번째 자리에서 반올림한다.
-- 조건3. 부서별로 오름차순 정렬
-- 조건4. 시급이 많은 순으로 출력
SELECT
DEPTNO 부서번호,
ENAME 사원이름,
ROUND(((SAL / 20) / 8), 1) 시급
FROM EMP
ORDER BY DEPTNO, 시급 DESC;
-- 8. 각 사원 별 커미션이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하라.
-- 조건1. 보너스가 NULL이면 0으로 출력
SELECT
A.EMPNO 사원번호,
A.ENAME 사원이름,
NVL(A.COMM, 0) 커미션,
A.DEPTNO 부서번호,
B.DNAME 부서명,
B.LOC 부서위치
FROM
EMP A ,DEPT B
WHERE
A.DEPTNO = B.DEPTNO
AND
(A.COMM = 0 OR A.COMM IS NULL)
AND
B.LOC LIKE '%GO';
-- 9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하라.
SELECT
DEPTNO 부서번호,
CASE
WHEN
AVG(SAL) > 2000
THEN '초과'
ELSE '미만'
END
AS "2000 미만 초과"
FROM
EMP
GROUP BY DEPTNO;
-- 10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하라.
SELECT
EMPNO 사원번호,
ENAME 사원명,
DEPTNO 부서번호,
HIREDATE 입사일
FROM
EMP
WHERE
HIREDATE IN
(SELECT MIN(HIREDATE) FROM EMP GROUP BY DEPTNO);
-- 11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하라.
SELECT
A.DEPTNO 부서번호,
B.DNAME 부서명,
COUNT(DECODE(TO_CHAR(A.HIREDATE, 'YYYY'),'1980',0)) 입사1980,
COUNT(DECODE(TO_CHAR(A.HIREDATE, 'YYYY'),'1981',0)) 입사1981,
COUNT(DECODE(TO_CHAR(A.HIREDATE, 'YYYY'),'1982',0)) 입사1982
FROM EMP A, DEPT B
WHERE
A.DEPTNO = B.DEPTNO
GROUP BY A.DEPTNO, B.DNAME;
-- 12. 1981년 5월 31일 이후 입사자 중 커미션이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 커미션을 출력하라.
SELECT
ENAME 입사자,
DECODE(COMM, NULL, 500, 0, 500, COMM) 커미션
FROM EMP
WHERE
HIREDATE > TO_DATE(19810531)
ORDER BY ENAME;
-- 13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하라.
-- 조건1. 입사일 오름차순 정렬
SELECT
A.DEPTNO 부서번호,
A.ENAME 사원명,
A.JOB 직업,
A.HIREDATE 입사일
FROM EMP A, DEPT B
WHERE
A.DEPTNO = B.DEPTNO
AND
A.HIREDATE >= TO_DATE(19810601)
AND
A.HIREDATE <= TO_DATE(19811231)
AND
B.DNAME = 'SALES'
ORDER BY HIREDATE;
-- 14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하라.
-- 조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
-- 조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM--DD HH24:MI:SS') 현재시간,
TO_CHAR(SYSDATE + 1/24, 'YYYY-MM--DD HH24:MI:SS') "1시간후"
FROM DUAL;
-- 15. 각 부서별 사원수를 출력하라.
-- 조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력
-- 조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력
-- 조건3. 부서번호 오름차순 정렬
SELECT
B.DEPTNO 부서번호,
B.DNAME 부서명,
DECODE(COUNT(A.EMPNO), 0, '없음', COUNT(A.EMPNO)) 사원수
FROM EMP A, DEPT B
WHERE
A.DEPTNO(+) = B.DEPTNO
GROUP BY B.DEPTNO, B.DNAME
ORDER BY B.DEPTNO;
-- 16. 사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하라.
-- 조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.
SELECT
A.EMPNO 사원번호,
A.ENAME 사원명,
A.MGR 매니저번호,
B.ENAME 매니저명
FROM EMP A, EMP B
WHERE
A.MGR = B.EMPNO
AND
A.SAL >= B.SAL;
-- 17. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때, 모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하라.
SELECT
ENAME 사원명,
COMM,
(SELECT COMM FROM EMP WHERE ENAME LIKE 'A%LL%') COMM2,
DECODE(COMM, NULL, 0, COMM) +
(SELECT COMM FROM EMP WHERE ENAME LIKE 'A%LL%') "COMM+COMM2"
FROM EMP;
-- 18. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.
-- 조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력
-- 조건2. 부서번호 오름차순 정렬
-- 조건3. 입사일 오름차순 정렬
SELECT
A.DEPTNO 부서번호,
B.DNAME 부서명,
A.EMPNO 사원번호,
A.ENAME 사원명,
A.HIREDATE 입사일
FROM EMP A RIGHT OUTER JOIN DEPT B
ON
A.DEPTNO = B.DEPTNO
AND
TO_CHAR(A.HIREDATE, 'YYYYMMDD') > 19810531
ORDER BY A.DEPTNO, A.HIREDATE;
-- 19. 입사일로부터 지금까지 근무년수가 30년 이상 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하라.
-- 조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)
SELECT
EMPNO 사원번호,
ENAME 사원명,
HIREDATE 입사일,
TRUNC((SYSDATE - HIREDATE)/365) 근무년수
FROM EMP
WHERE
TRUNC((SYSDATE - HIREDATE)/365) < 30;
'DB > 오라클 SQL 문제풀이' 카테고리의 다른 글
| 오라클 DB 간단한 문제 풀어보기 (1) (0) | 2015.03.13 |
|---|