『 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL (개정 4판) 』 저자: 서진수, 김균도 사용 프로그램: sqlplus ※ 본 게시글에 작성된 문제는 책 본문과 일치하지 않는 부분이 존재하며, 풀이 또한 정확하지 않습니다.
<SUBSTR/INSTR 퀴즈> Student 테이블을 참조해서 아래 화면과 같이 1전공이(deptno1 컬럼) 201번인 학생의 이름과 전화번호와 지역번호를 출력하세요. 단, 지역번호는 숫자만 나와야 합니다.
문제풀이
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, ')')-1) "AREA CODE" FROM Student WHERE deptno1 = 201;
실행결과
<LPAD 퀴즈> emp 테이블을 사용하여 아래 화면과 같이 deptno가 10번인 사원들의 사원 이름을 총 9바이트로 출력하되 빈자리에는 해당 자리의 숫자로 채우세요.
문제풀이
SELECT LPAD(ename, 9, 123456789) "LPAD" FROM emp WHERE deptno = 10;
실행결과
<RPAD 퀴즈> 아래 화면과 같이 emp 테이블에서 deptno가 10번인 사원들의 이름을 총 9자리로 출력하되 오른쪽 빈자리에는 해당 자릿수에 해당되는 숫자가 출력되도록 하세요.
문제풀이
SELECT RPAD(ename, 9, SUBSTR(123456789, LENGTH(ename)+1, 9)) "RPAD" FROM emp WHERE deptno = 10;
실행결과
<REPLACE 퀴즈1> emp 테이블에서 아래와 같이 20번 부서에 소속된 직원들의 이름과 2~3번째 글자만 '-'으로 변경해서 출력하세요.
문제풀이
SELECT ename, REPLACE(ename, SUBSTR(ename, 2, 3), '--') "REPLACE" FROM emp WHERE deptno = 20;
실행결과
<REPLACE 퀴즈 2> Student 테이블에서 아래와 같이 1전공(deptno1)이 101번인 학생들의 이름과 주민등록번호를 출력하되 주민등록번호의 뒤 7자리는 '-'과 '/'로 표시되게 출력하세요.
문제풀이
SELECT name, jumin, REPLACE(jumin, SUBSTR(jumin, 7, 14), '-/-/-/-') "REPLACE"
FROM Student WHERE deptno1 = 101;
실행결과
<REPLACE 퀴즈 3> Student 테이블에서 아래 그림과 같이 1전공이 102번인 학생들의 이름과 전화번호, 전화번호에서 국번 부분만 '*' 처리하여 출력하세요. 단, 모든 국번은 3자리로 간주합니다.
문제풀이
SELECT name, tel, REPLACE(tel, SUBSTR(tel, INSTR(tel, ')')+1, 3), '***') "REPLACE"
FROM Student WHERE deptno1 = 102;
실행결과
<REPLACE 퀴즈 4> Student 테이블에서 아래와 같이 deptno1이 101번인 학과 학생들의 이름과 전화번호와 전화번호에서 지역번호와 국번을 제외한 나머지 번호를 *로 표시해서 출력하세요.
문제풀이
SELECT name, tel, REPLACE(tel, SUBSTR(tel, INSTR(tel, '-')+1, 4), '****') "REPLACE"
FROM Student WHERE deptno1 = 101;
실행결과
<형 변환 함수 퀴즈 : 날짜 변환하기1> Student 테이블의 birthday 컬럼을 사용하여 생일이 1월인 학생의 이름과 birthday 를 아래 화면과 같이 출력하세요. (아래 두 개 그림 중 독자 여러분의 OS에 맞는 것으로 실행하세요)
윈도우용 오라클(좌) / 리눅스용 오라클(우)
문제풀이
SELECT studno, name, birthday FROM Student WHERE TO_CHAR(birthday, 'mm') = 1;
실행결과
<형 변환 함수 퀴즈 : 날짜 변환하기2> emp 테이블의 hiredate 컬럼을 사용하여 입사일이 1,2,3월인 사람들의 사번과 이름, 입사일을 출력하세요. (아래 두 개 그림 중 독자 여러분의 OS에 맞는 것으로 실행하세요)
윈도우용 오라클(좌) / 리눅스용 오라클(우)
문제풀이
SELECT empno, ename, hiredate FROM emp WHERE TO_CHAR(hiredate, 'mm') IN (1, 2, 3);
실행결과
<형 변환 함수 퀴즈 3> emp 테이블을 조회하여 comm 값을 가지고 있는 사람들의 empno, ename, hiredate, 총 연봉, 15% 인상 후 연봉을 아래 화면처럼 출력하세요. 단, 총 연봉은 (sal*12)+comm으로 계산하고 아래 화면에서는 SAL로 출력되었으며 15% 인상한 값은 총 연봉의 15% 인상 값입니다. (HIREDATE 컬럼의 날짜 형식과 SAL 컬럼, 15% UP 컬럼의 $ 표시와 기호가 나오게 하세요)
문제풀이
SELECT empno,
ename,
hiredate,
TO_CHAR(sal*12+comm, '$999,999') "SAL",
TO_CHAR((SAL*12+COMM)+(SAL*12+COMM)*0.15, '$999,999') "15% UP"
FROM emp
WHERE comm IS NOT NULL;
실행결과
<NVL 함수 퀴즈> Professor 테이블에서 201번 학과 교수들의 이름과 급여 , bonus, 총 연봉을 아래와 같이 출력하세요. 단, 총 연봉은 (pay*12+bonus)로 계산하고 bonus가 없는 교수는 0으로 계산하세요.
문제풀이
SELECT profno, name, pay, bonus, TO_CHAR(pay*12+NVL(bonus, 0), '999,999') "TOTAL"
FROM Professor
WHERE deptno = 201;
실행결과
<NVL2 함수 퀴즈> 아래 화면과 같이 emp 테이블에서 deptno가 30번인 사원들을 조회하여 comm 값이 있을 경우 'Exist'을 출력하고 comm 값이 null일 경우 'NULL'을 출력하시오.
문제풀이
SELECT empno, ename, comm, NVL2(comm, 'Exist', 'NULL') "NVL2" FROM emp WHERE deptno = 30;
실행결과
<DECODE 퀴즈 1> Student 테이블을 사용하여 제1전공(deptno1)이 101번인 학과 학생들의 이름과 주민번호, 성별을 출력하되 성별은 주민번호(jumin) 컬럼을 이용하여 7번째 숫자가 1일 경우 "MAN", 2일 경우 "WOMAN"으로 출력하세요.
문제풀이
SELECT name,
jumin,
DECODE(SUBSTR(jumin, 7, 1), '1', 'MAN', 'WOMAN') "Gender"
FROM Student
WHERE deptno1 = 101;
실행결과
<DECODE 퀴즈 2> Student 테이블에서 1 전공이(deptno1) 101번인 학생의 이름과 연락처와 지역을 출력하세요. 단, 지역번호가 02는 "SEOUL" , 031은 "GYEONGGI" ,051은 "BUSAN" ,052는 "ULSAN",055는 "GYEONGNAM"입니다.
<CASE문 퀴즈> emp 테이블을 조회하여 empno, ename, sal ,LEVEL(급여등급)을 아래와 같이 출력하세요. 단, 급여등급은 sal을 기준으로 1-1000이면 Level 1 , 1001-2000이면 Level 2, 2001-3000이면 LEVEL 3 , 3001-4000이면 Level 4 , 4001보다 많으면 Level 5 로 출력하세요.
문제풀이
SELECT empno,
ename,
sal,
CASE WHEN sal BETWEEN '1' AND '1000' THEN 'LEVEL 1'
WHEN sal BETWEEN '1001' AND '2000' THEN 'LEVEL 2'
WHEN sal BETWEEN '2001' AND '3000' THEN 'LEVEL 3'
WHEN sal BETWEEN '3001' AND '4000' THEN 'LEVEL 4'
ELSE 'LEVEL 5'
END "LEVEL"
FROM emp;
