[오라클 SQL과 PL/SQL] 9장 연습문제 (View 1~6)

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

 

---

 

1.
Professor 테이블과 department 테이블을 조인하여 교수번호와 교수이름, 소속 학과이름을 조회하는 view 를 생성하세요. View 이름은 v_prof_dept2 로 하세요.

 

문제풀이

CREATE OR REPLACE VIEW v_prof_dept2
AS
	SELECT profno, name, dname
	FROM professor P
	JOIN department D ON (P.deptno = D.deptno);

 

실행결과

 

---

 

2.
Inline View 를 사용하여 아래 그림과 같이 Student 테이블과 department 테이블을 사용하여 학과별로 학생들의 최대 키와 최대 몸무게, 학과이름을 출력하세요.

 

문제풀이

SELECT dname, S.MAX_HEIGHT, S.MAX_WEIGHT
FROM (SELECT deptno1, MAX(height) "MAX_HEIGHT", MAX(weight) "MAX_WEIGHT"
      FROM student
      GROUP BY deptno1) S
JOIN department D ON (S.deptno1 = D.deptno);

 

실행결과

 

---

 

3.
Student 테이블과 department 테이블을 사용하여 학과이름, 학과별 최대키, 학과별로 가장 키가 큰 학생들의 이름과 키를 Inline View를 사용하여 아래와 같이 출력하세요. 

 

문제풀이

SELECT dname, MAX_HEIGHT, MAX_WEIGHT, name, height
FROM (SELECT deptno1, MAX(height) "MAX_HEIGHT", MAX(weight) "MAX_WEIGHT"
      FROM student
      GROUP BY deptno1) A, student S, department D
WHERE S.deptno1 = A.deptno1
AND S.height = A.MAX_HEIGHT
AND S.deptno1 = D.deptno;

 

실행결과

 

---

 

4.
Student 테이블에서 학생의 키가 동일 학년의 평균 키 보다 큰 학생들의 학년과 이름과 키,
해당 학년의 평균 키를 출력하되 Inline View를 사용해서 아래와 같이 출력 하세요.
(학년 컬럼으로 오름차순 정렬해서 출력하세요) 

 

문제풀이

SELECT S.grade, S.name, S.height, A.AVG_HEIGHT
FROM (SELECT grade, AVG(height) "AVG_HEIGHT"
      FROM student
      GROUP BY grade) A, student S
WHERE S.height > A.AVG_HEIGHT
ORDER BY S.grade;

 

실행결과

 

---

 

5.
professor 테이블을 조회하여 아래와 같이 교수들의 급여순위와 이름과 급여를 출력하시오
단 급여순위는 급여가 많은 사람부터 1위~5위까지 출력하세요

 

문제풀이

SELECT ROWNUM "Ranking", name, pay
FROM (SELECT name, pay FROM professor ORDER BY pay DESC)
WHERE ROWNUM BETWEEN 1 AND 5;

* ROWNUM은 FROM을 통해 조회할 수 있는 전체 데이터를 기준으로 번호를 매기기 때문에, 일정 조건이 적용된 데이터에만 번호를 매기고 싶으면 inline view 등을 통해야한다.

 

실행결과

 

---

 

6.
아래 화면과 같이 교수 테이블을 교수 번호로 정렬한 후 출력하되, 3건씩 분리해서 급여 합계와 급여 평균을 출력하세요.

 

문제풀이

SELECT ROWNUM "NUM", profno, name, pay, SUM(pay), ROUND(AVG(pay), 1)
FROM professor
GROUP BY CEIL(ROWNUM/3), ROLLUP((profno, name, pay, ROWNUM));

* ROLLUP() 함수에서 여러 컬럼을 사용하려는 경우, ()를 통해 한 번 묶어주어야한다.
ex) ROLLUP((컬럼1, 컬럼2, 컬럼3))

 

실행결과