[오라클 SQL과 PL/SQL] 10장 연습문제 (Sub Query(서브쿼리))

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

 

---

 

<단일행 Sub Query 연습문제1>
Student 테이블과 department 테이블을 사용하여 'Anthony Hopkins' 학생과 1전공(deptno1)이 동일한 학생들의 이름과 1전공 이름을 출력하세요.

 

문제풀이

SELECT name "STUD_NAME", dname "DEPT_NAME"
FROM student S, department D
WHERE S.deptno1 = D.deptno
AND S.deptno1 = (SELECT deptno1
		 FROM student
		 WHERE name LIKE 'Anthony Hopkins');

 

실행결과

 

---

 

<단일행 Sub Query 연습문제2>
Professor 테이블과 department 테이블을 조회하여 'Meg Ryan' 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.

 

문제풀이

SELECT name "PROF_NAME",
       TO_CHAR(hiredate, 'YYYY-MM-DD') "HIREDATE",
       dname "DEPT_NAME"
FROM professor P, department D
WHERE P.deptno = D.deptno
AND hiredate > (SELECT hiredate
		FROM professor
		WHERE name LIKE 'Meg Ryan');

 

실행결과

 

---

 

<단일행 Sub Query 연습문제3>
Student 테이블에서 1전공(deptno1)이 201번인 학과의 평균 몸무게보다 몸무게가 많이 나가는 학생들의 이름과 몸무게를 출력하세요.

 

문제풀이

SELECT name, weight
FROM student
WHERE weight > (SELECT AVG(weight)
                FROM student
                WHERE deptno1 = 201);

 

실행결과

 

---

 

<다중행 Sub Query 연습문제1>
Emp2 테이블을 사용하여 전체 직원 중 'Section head' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요. 단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호와 $ 표시를 하세요.

 

문제풀이

SELECT name,
       position,
       TO_CHAR(pay, '$999,999,999') "SALARY"
FROM emp2
WHERE pay > ANY (SELECT pay
                 FROM emp2
                 WHERE position LIKE 'Section head');

 

실행결과

 

---

 

<다중행 Sub Query 연습문제2>
Student 테이블을 조회하여 전체 학생 중에서 체중이 2학년 학생들 중 체중이 가장 적게 나가는 학생보다 몸무게가 적은 학생의 이름과 학년, 몸무게를 출력하세요.

 

문제풀이

SELECT name, grade, weight
FROM student
WHERE weight < ALL (SELECT weight
		    FROM student
		    WHERE grade = 2);

 

실행결과

---

 

<다중행 Sub Query 연습문제3>
Emp2 테이블과 dept2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

 

문제풀이

SELECT dname,
       name,
       TO_CHAR(pay, '$999,999,999') "SALARY"
FROM emp2 E2, dept2 D2
WHERE E2.deptno = D2.dcode
AND pay < ALL (SELECT AVG(pay)
	       FROM emp2
	       GROUP BY deptno);

 

실행결과

 

---

 

<다중 컬럼 Sub Query 연습문제1>
Professor 테이블과 department 테이블을 조회하여 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요.
(입사일 순으로 오름차순 정렬하세요.)

 

문제풀이

SELECT profno,
       name "PROF_NAME",
       TO_CHAR(hiredate, 'YYYY-MM-DD') "HIREDATE",
       dname "DEPT_NAME"
FROM professor P, department D
WHERE P.deptno = D.deptno
AND (P.deptno, hiredate) IN (SELECT deptno, MIN(hiredate)
                             FROM professor
                             GROUP BY deptno)
ORDER BY hiredate;

 

실행결과

 

---

 

<다중 컬럼 Sub Query 연습문제2>
Emp2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름과 직급, 연봉을 출력하세요. 연봉 순으로 오름차순 정렬하세요.

 

문제풀이

SELECT name,
       position,
       TO_CHAR(pay, '$999,999,999') "SALARY"
FROM emp2
WHERE (position, pay) IN (SELECT position, MAX(pay)
                          FROM emp2
                          GROUP BY position)
ORDER BY pay;

 

실행결과