[오라클 SQL과 PL/SQL] 4장 연습문제 (조인(Join) 1 ~ 6)

『 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL (개정 4판) 』
저자: 서진수, 김균도
사용 프로그램: sqlplus
※ 본 게시글에 작성된 문제는 책 본문과 일치하지 않는 부분이 존재하며, 풀이 또한 정확하지 않습니다.

 

---

 

1.
학생 테이블 (student) 과 학과 테이블 (department) 테이블을 사용하여 학생이름, 1 전공학과번호(deptno1) , 1전공 학과 이름을 출력하세요.
( ANSI Join 문법과 Oracle Join 문법으로 각각 SQL 을 작성하세요 )

 

문제풀이 (ANSI Join)

SELECT name "STU_NAME", deptno1, dname "DEPT_NAME"
FROM student S
JOIN department D ON S.deptno1 = D.deptno;

 

문제풀이 (Oracle Join)

SELECT name "STU_NAME", deptno1, dname "DEPT_NAME"
FROM student S, department D
WHERE S.deptno1 = D.deptno;

 

실행결과

ANSI Join(좌) / Oracle Join(우)

 

---

 

2.
emp2 테이블과 p_grade 테이블을 조회하여 현재 직급이 있는 사원의 이름과 직급, 현재 연봉, 해당 직급의 연봉의 하한금액과 상한 금액을 아래 결과 화면과 같이 출력하세요.

 

문제풀이

SELECT	name,
	e2.position,
	TO_CHAR(pay, '999,999,999') "PAY",
	TO_CHAR(s_pay, '999,999,999') "Low PAY",
	TO_CHAR(e_pay, '999,999,999') "High Pay"
FROM emp2 e2, p_grade p
WHERE p.position = e2.position;

※ TO_CHAR(number, ‘999,999’)를 사용할 때 ‘999,999’ 부분은 첫 번째 인자 보다 커야함
(ex. 첫 번째 인자의 값이 75000000 일 때 ‘999,999,999’로 해야 깨지지 않음)

 

실행결과

 

---

 

3.
Emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력하세요. 예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미합니다.
나이는 오늘(sysdate)을 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산하세요.

 

문제풀이

SELECT	name,
	TRUNC(MONTHS_BETWEEN(SYSDATE, birthday)/12) "AGE",
	e2.position "CURR_POSITION",
	p.position "BE_POSITION"
FROM emp2 e2, p_grade p
WHERE	TRUNC(MONTHS_BETWEEN(SYSDATE, birthday)/12) >= s_age
	AND TRUNC(MONTHS_BETWEEN(SYSDATE, birthday)/12) <= e_age;

 

실행결과

※ SYSDATE()는 현재 날짜를 기준으로 하기 때문에 교재와 결과가 다름

 

---

 

4. customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력하세요.

 

문제풀이

SELECT c.gname "CUST_NAME", point, g.gname "GIFT_NAME"
FROM customer c, gift g
WHERE g.g_start <= c.point AND g.gname = 'Notebook';

 

실행결과

 

---

 

5.
professor 테이블에서 교수의 번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하세요. 단, 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하세요.
(Oracle Join 구문과 ANSI Join 구문으로 각각 SQL을 작성하세요)

 

문제풀이 (Oracle Join)

SELECT	p1.profno "PROFNO",
	p1.name "NAME",
	p1.hiredate "HIREDATE", COUNT(p2.hiredate) "COUNT"
FROM professor p1, professor p2
WHERE p2.hiredate(+) < p1.hiredate
GROUP BY p1.profno, p1.name, p1.hiredate
ORDER BY COUNT(p2.hiredate);

 

문제풀이 (ANSI Join)

SELECT	p1.profno "PROFNO",
	p1.name "NAME",
	p1.hiredate "HIREDATE",
	COUNT(p2.hiredate) "COUNT"
FROM professor p1
JOIN professor p2 ON p2.hiredate(+) < p1.hiredate
GROUP BY p1.profno, p1.name, p1.hiredate
ORDER BY COUNT(p2.hiredate);

 

실행결과

Oracle Join(좌) / ANSI Join(우)

 

---

 

6.
emp 테이블에서 사원번호, 사원이름, 입사일, 자신보다  먼저 입사한 사람 인원수를 출력하세요. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하세요.
(Oracle Join 구문과 ANSI Join 구문으로 각각 SQL을 작성하세요)

 

문제풀이 (Oracle Join)

SELECT	e1.empno "EMPNO",
	e1.ename "ENAME",
	e1.hiredate "HIREDATE",
	COUNT(e2.hiredate) "COUNT"
FROM emp e1, emp e2 WHERE e2.hiredate(+) < e1.hiredate
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY COUNT(e2.hiredate);

 

문제풀이 (ANSI Join)

SELECT	e1.empno "EMPNO",
	e1.ename "ENAME",
	e1.hiredate "HIREDATE",
	COUNT(e2.hiredate) "COUNT"
FROM emp e1
JOIN emp e2 ON e2.hiredate(+) < e1.hiredate
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY COUNT(e2.hiredate);

 

실행결과

Oracle Join(좌) / ANSI Join(우)