P.258

연습문제 1

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

STU_NAME                DEPTNO1 DEPT_NAME
-------------------- ---------- --------------------------------------
James Seo                   101 Computer Engineering
Danny Devito                101 Computer Engineering
Richard Dreyfus             101 Computer Engineering
Billy Crystal               101 Computer Engineering
Rene Russo                  102 Multimedia Engineering
Charlie Sheen               102 Multimedia Engineering
Tim Robbins                 102 Multimedia Engineering
Nicholas Cage               102 Multimedia Engineering
Sandra Bullock              103 Software Engineering
Anthony Hopkins             103 Software Engineering
Wesley Snipes               201 Electronic Engineering
Christian Slater            201 Electronic Engineering
Macaulay Culkin             201 Electronic Engineering
Steve Martin                201 Electronic Engineering
Demi Moore                  201 Electronic Engineering
Sean Connery                201 Electronic Engineering
Micheal Keaton              202 Mechanical Engineering
Danny Glover                202 Mechanical Engineering
Bill Murray                 301 Library and Information science
Daniel Day-Lewis            301 Library and Information sciencecience

ANSI Join

SELECT s.name "STU_NAME", s.deptno1 "DEPTNO1", d.dname "DEPT_NAME"
FROM student s JOIN department d
ON s.deptno1 = d.deptno;

Oracle Join

SELECT s.name "STU_NAME", s.deptno1 "DEPTNO1", d.dname "DEPT_NAME"
FROM student s, department d
WHERE s.deptno1 = d.deptno;

연습문제 2

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

NAME            POSITION                       PAY          Low PAY           High PAY
--------------- ------------------------------ ------------ ----------------- -----------------
AL Pacino       Department head                 72,000,000   60,010,000        75,000,000
Woody Harrelson Section head                    50,000,000   45,010,000        51,000,000
Tommy Lee Jones Deputy department head          60,000,000   51,010,000        60,000,000
Gene Hackman    Section head                    56,000,000   45,010,000        51,000,000
Kevin Bacon     Department head                 75,000,000   60,010,000        75,000,000
Hugh Grant      Section head                    51,000,000   45,010,000        51,000,000
Keanu Reeves    Deputy Section chief            35,000,000   30,000,000        45,000,000
Val Kilmer      Department head                 68,000,000   60,010,000        75,000,000
Chris O'Donnell Section head                    49,000,000   45,010,000        51,000,000

SQL문

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

연습문제 3

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

NAME                 AGE CURR_POSITION             BE_POSITION
-------------------- --- ------------------------- -------------------------
Jack Nicholson        34                           Deputy department head
Denzel Washington     36                           Deputy department head
Richard Gere          37                           Department head
Clint Eastwood        38                           Department head
Harrison Ford         38                           Department head
Kevin Costner         38                           Department head
Robert De Niro        39                           Department head
Tom Cruise            39                           Department head
JohnTravolta          39                           Department head
Sly Stallone          39                           Department head
Val Kilmer            42 Department head           Director
Tommy Lee Jones       43 Deputy department head    Director
Woody Harrelson       44 Section head              Director
Chris O'Donnell       46 Section head              Director
Gene Hackman          46 Section head              Director
AL Pacino             46 Department head           Director
Hugh Grant            47 Section head              Director
Kevin Bacon           47 Department head           Director
Keanu Reeves          48 Deputy Section chief      Director
Kurt Russell          55 Boss                      Director

SQL문

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

연습문제 4

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

CUST_NAME                      POINT GIFT_NAME
------------------------- ---------- -------------------------
James Seo                     980000 Notebook
Winona Ryder                  625000 Notebook
Michelle Pfeiffer             670000 Notebook

SQL문

SELECT c.gname "CUST_NAME", c.point "POINT", g.gname "GIFT_NAME"
FROM customer c, gift g
WHERE c.point >= g.g_start AND g.gno = 7;

연습문제 5

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

    PROFNO NAME                      HIREDATE                  COUNT
---------- ------------------------- -------------------- ----------
      1001 Audie Murphy              1980-06-23                    0
      3001 Emma Thompson             1981-10-23                    1
      4001 Meryl Streep              1981-10-23                    1
      2003 Whoopi Goldberg           1982-04-29                    3
      4005 Meg Ryan                  1985-09-18                    4
      2002 Michelle Pfeiffer         1985-11-30                    5
      1002 Angela Bassett            1987-01-30                    6
      3002 Julia Roberts             1997-07-01                    7
      1003 Jessica Lange             1998-03-22                    8
      4003 Nicole Kidman             1999-12-01                    9
      4007 Jodie Foster              2001-05-23                   10
      2001 Winona Ryder              2001-09-01                   11
      3003 Sharon Stone              2002-02-24                   12
      4004 Holly Hunter              2009-01-28                   13
      4002 Susan Sarandon            2009-08-30                   14
      4006 Andie Macdowell           2010-06-28                   15

Oracle Join

SELECT p1.profno "PROFNO", p1.name "NAME", TO_CHAR(p1.hiredate, 'YYYY-MM-DD') "HIREDATE", 
       COUNT(p2.hiredate) "COUNT"
FROM professor p1, professor p2
WHERE p1.hiredate > p2.hiredate(+)
GROUP BY p1.profno, p1.name, p1.hiredate
ORDER BY 4;

ANSI Join

SELECT p1.profno, p1.name, TO_CHAR(p1.hiredate, 'YYYY-MM-DD') "HIREDATE", 
       COUNT(p2.hiredate) "COUNT"
FROM professor p1 JOIN professor p2
ON p1.hiredate > p2.hiredate(+)
GROUP BY p1.profno, p1.name, p1.hiredate
ORDER BY 4;

연습문제 6

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

     EMPNO ENAME                HIREDATE      COUNT
---------- -------------------- -------- ----------
      7369 SMITH                80/12/17          0
      7499 ALLEN                81/02/20          1
      7521 WARD                 81/02/22          2
      7566 JONES                81/04/02          3
      7698 BLAKE                81/05/01          4
      7782 CLARK                81/06/09          5
      7844 TURNER               81/09/08          6
      7654 MARTIN               81/09/28          7
      7839 KING                 81/11/17          8
      7902 FORD                 81/12/03          9
      7900 JAMES                81/12/03          9
      7934 MILLER               82/01/23         11

Oracle Join

SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.hiredate) "COUNT"
FROM emp e1, emp e2
WHERE e1.hiredate > e2.hiredate(+)
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY 4;

ANSI Join

SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.hiredate) "COUNT"
FROM emp e1 JOIN emp e2
ON e1.hiredate > e2.hiredate(+)
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY 4;

출처