제 3장. 연습문제 해답(92-94페이지). 총 32문항
1. 문제) 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을 출력하라
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10;
2. 문제) 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하라.
SELECT ename, hiredate, deptno
FROM emp
WHERE empno = 7369
3. 문제) 이름이 ALLEN인 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE ename = 'ALLEN';
4. 문제) 입사일이 83/01/12인 사원의 이름, 부서번호, 월급을 출력하라.
alter session set nls_date_format = 'YYYY/MM/DD';
SELECT ename, deptno, sal
FROM emp WHERE hiredate = '1983/01/12';
5. 문제) 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE job <> 'MANAGER';
6. 문제) 입사일이 81/04/02 이후에 입사한 사원의 정보를 출력하라.
alter session set nls_date_format = 'YYYY/MM/DD';
SELECT *
FROM emp
WHERE hiredate = '1981/04/02';
7. 문제) 급여가 $800 이상인 사람의 이름, 급여, 부서번호를 출력하라.
SELECT ename, sal, deptno
FROM emp
WHERE sal > = 800;
8. 문제) 부서번호가 20번 이상인 사원의 모든 정보를 출력하라.
SELECT * FROM emp WHERE deptno = 20;
9. 문제) 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든 정보를 출력하라.
SELECT * FROM emp WHERE ename > ‘K’;
10. 문제) 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를 출력하라.
ALTER SESSION SET nls_date_format = ‘YYYY/MM/DD’;
SELECT * FROM emp WHERE hiredate < ‘1981/12/09’;
11. 문제) 입사번호가 7698보다 작거나 같은 사람들의 입사번호와 이름을 출력하라.
SELECT empno, ename FROM emp WHERE empno <= 7698;
12. 문제) 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하라.
ALTER SESSION SET nls_date_format = ‘YYYY/MM/DD’;
SELECT ename, sal, deptno From EMP
WHERE hiredate > ‘1981/04/02’
and hiredate < ‘1982/12/09’;
13. 문제) 급여가 $1,600보다 크고 $3,000보다 작은 사람의 이름, 직업, 급여를 출력하라.
SELECT ename, job, sal FROM emp
WHERE sal > 1600 and sal < 3000;
14. 문제) 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를 출력하라.
SELECT * From emp WHERE empno > 7654 and empno < 7782;
15. 문제) 이름이 B와 J 사이의 모든 사원의 정보를 출력하라.
EMP 테이블의 ENAME 컬럼에 저장되어 있는 값의 유형을 잘
확인해 보신 후 다음과 같이 문장을 실행해 보십시오.
select ename from emp
where ename >= 'B%' and ename <= 'J_%'
ENAME이 B로 시작되는 값보다 크고 J로 시작되는 값보다 작은 값을
찾는 조건으로 검색해야 합니다.
16. 문제) 입사일이 81년 이외에 입사한 사람의 모든 정보를 출력하라.
이 예제의 경우에는 다음 장에서 소개되는 함수를 사용하는
경우입니다.
분명, 함수를 배우지 못했기 때문에 정답 처리를 지 못할 것
입니다. 예습에 대한 준비성을 보기 위한 예제입니다.
ALTER SESSION SET nls_date_format = ‘YYYY/MM/DD’;
SELECT * FROM emp WHERE substr(hiredate,1, 4) <> ‘1981’;
17. 문제) 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하라.
SELECT * FROM emp
WHERE job =’MANAGER’or Job = ‘SALESMAN’;
18. 문제) 부서번호와 20, 30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하라.
SELECT ename, empno, deptno FROM emp
WHERE deptno <> 20 AND deptno <> 30;
19. 문제) 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호를 출력하라.
SELECT empno, ename, hiredate, deptno
FROM emp
WHERE substr(ename, 1, 1) = ‘S’;
20. 문제) 입사일이 81년도인 사람의 모든 정보를 출력하라
ALTER SESSION SET nls_date_format = ‘YYYY-MM-DD’;
SELECT * FROM emp WHERE substr(hiredate, 1, 4) = ‘1981’;
21. 문제) 이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라.
SELECT *
FROM emp
WHERE ename like ‘%S%’;
22. 문제) 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를 출력하라(단, 이름은 전체 5자리이다)
SELECT * FROM emp WHERE ename like ‘S___T’;
23. 문제) 첫 번째 문자는 관계없고, 두 번째 문자가 A인 사람의 정보를 출력하라.
SELECT * FROM emp WHERE ename like ‘_A%’;
24. 문제) 커미션이 NULL인 사람의 정보를 출력하라.
SELECT * FROM emp WHERE comm Is null;
25. 문제) 커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.
SELECT * FROM emp WHERE comm Is not null;
26. 문제) 부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름, 부서 ,월급을 출력하라.
SELECT ename, deptno, sal FROM emp
WHERE deptno = 30 and sal > 1500;
27. 문제) 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호, 이름, 부서번호를 출력하라.
SELECT empno, ename, deptno FROM emp
WHERE ename like ‘K%’ or deptno = 30;
28. 문제) 급여가 $1,500 이상이고 부서번호가 30번인 사원 중 직업이 MANAGER인 사람의 정보를 출력하라
SELECT * FROM emp
WHERE sal > 1500 and deptno = 30 and job = ‘MANAGER’;
29. 문제) 부서번호가 30인 사람 중 사원번호 SORT하라.
SELECT * FROM emp WHERE deptno = 30 ORDER BY empno;
30. 문제) 급여가 많은 순으로 SORT하라.
SELECT * FROM emp ORDER BY sal DESC;
31. 문제) 부서번호로 ASCENDING SORT한 후 급여가 많은 사람 순으로 출력하라.
SELECT empno, ename, deptno, sal
FROM emp
ORDER BY deptno asc, sal;
32. 문제) 부서번호가 DESCENDING SORT하고, 이름 순으로 ASCENDING SORT,급여 순으로 DESCENDING SORT 하라.
SELECT empno, deptno, ename, sal
FROM emp
ORDER by deptno desc, ename asc, sal desc;
제 5장. 연습문제 해답(161-162페이지). 총 20문항
1. 문제) EMP Table에서 이름, 급여, 커미션 금액, 총액(sal + comm)을
구하여 총액이 많은 순서로 출력하라. 단, 커미션이 NULL인
사람은 제외한다.
SELECT ename, sal, comm, sal +comm Total
FROM emp
WHERE comm Is not null
ORDER BY total desc;
2. 문제) 10번 부서의 모든 사람들이게 급여의 13%를 보너스로 지불하기로
하였다. 이름, 급여, 보너스 금액, 부서번호를 출력하라.
SELECT ename, sal, sal * 1.13, deptno
FROM emp
WHERE deptno = 10;
3. 문제) 30번 부서의 연봉을 계산하여 이름, 부서번호, 급여, 연봉을
출력하라. 단, 연말에 급여의 150%를 보너스로 지급한다.
SELECT ename, deptno, sal, (sal*12)+(sal*1.5)
FROM emp
WHERE deptno = 30;
4. 문제 ) 부서번호가 20인 부서의 시간당 임금을 계산하여 출력하라.
단, 1달의 근무일수는 12일이고, 1일 근무시간은 5시간이다.
출력양식은 이름, 급여, 시간당 임금(소수이하 첫 번째 자리
에서 반올림)을 출력하라
SELECT ename, sal, (sal/12)/5
FROM emp
WHERE deptno = 20;
5. 문제) 급여가 $1,500부터 $3,000 사이의 사람은 급여의 15%를 회비로
지불하기로 하였다. 이를 이름, 급여, 회비(소수점 두 자리에서
반올림)를 출력하라.
SELECT ename, sal, round(salx0.15, -2)
FROM emp
WHERE sal >= 1500 and sal <= 3000;
6. 문제) 급여가 $2,000 이상인 모든 사람은 급여의 15%를 경조비로 내기
로 하였다. 이름, 급여, 커미션(소수점 이하 절삭)를 출력하라.
SELECT ename, sal, round(sal * 0.15, -2)
FROM emp
WHERE sal > 1500 and sal <= 3000;
7. 문제) 입사일부터 지금까지의 날짜수를 출력하라. 부서번호, 이름, 입사
일, 현재일, 근무일수(소수점 이하 절삭), 근무년수, 근무월수
(30일 기준), 근무주수를 출력하라.
SELECT ENAME, HIREDATE "입사일자" ,
SYSDATE "현재날짜" ,
FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) "년",
FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) "월",
FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)*7) "주",
TRUNC(SYSDATE-HIREDATE) "일"
FROM EMP;
그리고, 커미션 컬럼을 회비라는 의미로 컬럼 표현을 하시면 되겠습니다.
7. 161페이지 7번 해답
SELECT DEPTNO, ENAME,
HIREDATE, <-- 입사일자
SYSDATE, <-- 현재일자
TRUNC(SYSDATE-HIREDATE), <-- 근무일수
TRUNC(SYSDATE-HIREDATE) / 365, <-- 근무년수
TRUNC(SYSDATE-HIREDATE) / 30, <-- 근무월수
TRUNC(SYSDATE-HIREDATE) / 7 <-- 근무주수
FROM EMP;
8. 문제) 모든 사원의 실수령액을 계산하여 출력하라. 단, 급여가 많은
순으로 이름, 급여, 실수령액을 출력하라.(실수령액은 금여에
대해 10%의 세금을 뺀 금액)
SELECT ename, sal, (sal - (sal*0.10))
FROM emp
ORDER BY sal DESC;
9. 문제) 입사일로부터 90일이 지난 후의 사원이름, 입사일, 90일 후의
날, 급여를 출력하라.
SELECT ENAME, HIREDATE, HIREDATE+90, SAL FROM EMP;
10. 문제) 입사일로부터 6개월이 지난 후의 입사일, 6개월 후의 날짜, 급여
를 출력하라
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6), SAL
FROM EMP;
11. 문제) 입사한 달의 근무일수를 계산하여 부서번호, 이름, 근무일수를
출력하라.
SELECT DEPTNO, ENAME, LAST_DAY(HIREDATE) ? HIREDATE
FROM EMP;
12. 문제) 모든 사원의 60일이 지난 후의 ‘MONDAY’는 몇 년, 몇 월,
몇 일 인가를 구하여 이름, 입사일,’MONDAY’를 출력하라
SELECT ENAME, HIREDATE, NEXT_DAY(HIREDATE+60, 'MONDAY')
FROM EMP;
13. 문제) 입사일로부터 오늘까지의 일수를 구하여 이름, 입사일, 근무일수
를 출력하라.
SELECT ENAME, HIREDATE, (SYSDATE - HIREDATE) FROM EMP;
14. 문제) 입사일을 ‘1996년 5월 14일’의 형태로 이름, 입사일을 출력하라.
SELECT ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') FROM EMP;
15. 문제) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 이름만을 출력하라.
SELECT lower(substr(ename, 1, 3))
FROM emp WHERE length(ename) = 6;
16. 문제) 10번 부서 월급의 평균, 최고, 최저, 인원수를 구하여 출력하라.
SELECT avg(sal), max(sal), min(sal), count(*)
FROM emp
WHERE deptno = 10;
17. 문제) 각 부서별 급여의 평균, 최고, 최저, 인언수를 구하여 출력하라.
SELECT avg(sal), max(sal), min(sal), count(*)
FROM emp
GROUP BY deptno;
18. 문제) 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호,업무명, 인원수를 출력하라.
SELECT deptno, job, count(*)
FROM emp
GROUP BY deptno, job;
19. 문제) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하라.
SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*) > 4;
20. 문제) 각 부서별 평균 월급, 전체 월급, 최고 월급, 최저 월급을 구하
여 평균 월급이 많은 순으로 출력하라.
SELECT deptno, avg(sal), sum(sal), max(sal), min(sal)
FROM emp
GROUP BY deptno
ORDER BY avg(sal) DESC;
6장. 연습문제 해답(184-185페이지). 총 13문항
1. 문제) EMP와 DEPT Table을 JOIN하여 부서번호, 부서명, 이름, 급여를 출력하라.
SELECT a.deptno, b.dname, a.ename, a.sal
FROM emp a, dept b
WHERE a.deptno = b.deptno;
2. 문제) 이름이 ‘ALLEN’인 사원의 부서명을 출력하라.
SELECT b.dname
FROM emp a, dept b
WHERE a.deptno = b.deptno and a.ename = 'ALLEN';
3. 문제) DEPT Table에 있는 모든 부서를 출력하고, EMP Table에 있는 DATA
와 JOIN하여 모든 사원의 이름, 부서번호, 부서명, 급여를 출력
하라.
SELECT a.ename, a.deptno, b.dname, a.sal
FROM emp a, dept b
WHERE a.deptno = b.deptno;
4. 문제) EMP Table에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과
같이 출력하라. ‘SMTH의 매니저는 FORD이다’
SELECT a.empno, a.ename, b.mgr
FROM emp a, emp b
WHERE a.empno = b.mgr and a.ename = 'FORD';
4. 184페이지 4번 해답
이 문제에서 요구했던 저의 의도는 하나의 테이블을 2개의 테이블로
조인하는 방법이었습니다. 이런 조인방법을 보통 "Self References
Join"이라고 합니다
SELECT b.ename||'의 매니저는 '||a.ename||'이다'
FROM emp a, emp b
WHERE a.empno = b.mgr;
하나의 emp 테이블을 from 절에 alias를 통해 2번 정의하여 조인하는
방법입니다
5. 문제) ‘ALLEN:의 직무와 같은 사람의 이름, 부서명, 급여, 직무를
출력하라.
SELECT a.ename, b.dname, a.sal, b.loc, a.job
FROM emp a, dept b
WHERE a.job = (select job from emp where ename =ALLEN’);
6. 문제) ‘JONES’가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사
일, 급여를 출력하라.
SELECT empno, ename, hiredate, sal
FROM emp
WHERE deptno = (SELECT deptno
FROM emp WHERE ename =’JONES’);
7. 문제) 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름,
부서명, 입사일, 지역, 급여를 출력하라.
SELECT a.empno, a.ename, a.hiredate, b.loc, a.sal
FROM emp a, dept b
WHERE a.sal > (select avg(sal) from emp);
8. 문제) 10번 부서 사람들 중에서 20번 부서의 사원과 같은 업무를 하는
사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.
SELECT a.empno, a.ename, b.dname, a.hiredate, b.loc
FROM emp a, dept b
WHERE a.deptno = b.deptno and
a.job in (select job from emp where deptno = 20)
and a.deptno = 10;
9. 문제) 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의
사원번호, 이름, 부서명, 입사일, 지역을 출력하라.
SELECT a.empno, a.ename, b.dname, a.hiredate, b.loc
FROM emp a, dept b
WHERE a.deptno = b.deptno and a.deptno = 10
and a.job not in (select job
from emp where deptno = 30);
10. 문제) 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명,
지역, 급여를 급여가 많은 순으로 출력하라.
SELECT a.empno, a.ename, b.dname, b.loc, a.sal
FROM emp a, dept b
WHERE a.deptno = b.deptno
and a.job in (select job
from emp where deptno = 10)
ORDER BY a.sal;
11. 문제) ‘MARTIN’이나 ‘SCOTT의 급여와 같은 사원의 사원번호, 이름,급여를 출력하라.
SELECT a.empno, a.ename, a.sal
FROM emp a
WHERE a.ename IN ('MARTIN', 'SCOTT');
12. 문제) 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT empno, ename, sal
FROM emp
WHERE sal > (select max(sal) from emp where deptno = 30);
13. 문제) 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호,
이름, 급여를 출력하라.
SELECT empno, ename, sal
FROM emp
WHERE sal > (select min(sal) from emp where deptno = 30);
