P.432

연습문제 1

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

쿼리

CREATE OR REPLACE VIEW v_prof_dept2
AS
SELECT p.profno, p.name, p.deptno
FROM professor p, department d
WHERE p.deptno = d.deptno;

연습문제 2

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

결과 화면

DNAME                           MAX_HEIGHT MAX_WEIGHT
------------------------------- ---------- ----------
Multimedia Engineering                 179         81
Electronic Engineering                 177         83
Library and Information science        184         62
Computer Engineering                   182         72
Mechanical Engineering                 182         70
Software Engineering                   168         52

쿼리

SELECT d.dname, s.height "MAX_HEIGHT", s.weight "MAX_WEIGHT"
FROM ( SELECT deptno1, MAX(height) height, MAX(weight) weight
       FROM student
       GROUP BY deptno1 ) s, department d
where s.deptno1 = d.deptno;

연습문제 3

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

결과 화면

DNAME                           MAX_HEIGHT NAME                     HEIGHT
------------------------------- ---------- -------------------- ----------
Computer Engineering                   182 Richard Dreyfus             182
Multimedia Engineering                 179 Charlie Sheen               179
Software Engineering                   168 Sandra Bullock              168
Electronic Engineering                 177 Demi Moore                  177
Mechanical Engineering                 182 Danny Glover                182
Library and Information science        184 Daniel Day-Lewis            184

쿼리

SELECT d.dname, a.max_height, s.name, s.height
FROM ( SELECT deptno1, MAX(height) max_height
       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를 사용해서 아래와 같이 출력하세요(학년 컬럼으로 오름차순 정렬해서 출력하세요).

결과 화면

     GRADE NAME                     HEIGHT AVG_HEIGHT
---------- -------------------- ---------- ----------
         1 Sean Connery                175      170.4
         1 Christian Slater            173      170.4
         1 Charlie Sheen               179      170.4
         2 Richard Dreyfus             182      175.6
         2 Daniel Day-Lewis            184      175.6
         3 Micheal Keaton              177      166.6
         3 Macaulay Culkin             171      166.6
         4 James Seo                   180      175.8
         4 Demi Moore                  177      175.8
         4 Danny Glover                182      175.8

쿼리

SELECT s2.grade, s2.name, s2.height, s.avg_height
FROM ( SELECT AVG(height) avg_height, grade
       FROM student
       GROUP BY grade)s, student s2
WHERE s.grade = s2.grade AND s2.height > s.avg_height
ORDER BY s2.grade;

연습문제 5

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

결과 화면

   RANKING NAME                        PAY
---------- -------------------- ----------
         1 Meryl Streep                570
         2 Audie Murphy                550
         3 Emma Thompson               530
         4 Meg Ryan                    500
         5 Whoopi Goldberg             490

Inline View를 사용한 쿼리

SELECT rownum "RANKING", name, pay
FROM ( SELECT name, pay
       FROM professor
       ORDER BY pay DESC)
WHERE rownum >= 1 AND rownum <= 5;

Inline View를 사용하지 않은 쿼리

SELECT COUNT(p2.pay) "RANKING", p1.name, p1.pay
FROM professor p1, professor p2
WHERE p1.pay <= p2.pay
GROUP BY p1.name, p1.pay
HAVING COUNT(p2.pay) <= 5
ORDER BY 1;

연습문제 6

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

결과 화면

       NUM     PROFNO NAME                        PAY   SUM(PAY) ROUND(AVG(PAY),1)
---------- ---------- -------------------- ---------- ---------- -----------------
         1       1001 Audie Murphy                550        550               550
         2       1002 Angela Bassett              380        380               380
         3       1003 Jessica Lange               270        270               270
                                                            1200               400
         4       2001 Winona Ryder                250        250               250
         5       2002 Michelle Pfeiffer           350        350               350
         6       2003 Whoopi Goldberg             490        490               490
                                                            1090             363.3
         7       3001 Emma Thompson               530        530               530
         8       3002 Julia Roberts               330        330               330
         9       3003 Sharon Stone                290        290               290
                                                            1150             383.3
        10       4001 Meryl Streep                570        570               570
        11       4002 Susan Sarandon              330        330               330
        12       4003 Nicole Kidman               310        310               310
                                                            1210             403.3
        13       4004 Holly Hunter                260        260               260
        14       4005 Meg Ryan                    500        500               500
        15       4006 Andie Macdowell             220        220               220
                                                             980             326.7
        16       4007 Jodie Foster                290        290               290
                                                             290               290

쿼리

SELECT num, profno, name, pay, SUM(pay), ROUND(AVG(pay),1)
FROM(SELECT profno, name, pay, rownum num
     FROM professor )
GROUP BY CEIL(num/3), ROLLUP((profno, name, pay, num))
ORDER BY CEIL(num/3);

출처