데이터베이스 – 페이지 4

생능_오라클SQL_04장. JOIN

emp 테이블

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL> SELECT * FROM emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------- ---------- ---------- ----------

7369 SMITH CLERK 7902 80/12/17 800 20

7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30

7521 WARD SALESMAN 7698 81/02/22 1250 500 30

7566 JONES MANAGER 7839 81/04/02 2975 20

7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30

7698 BLAKE MANAGER 7839 81/05/01 2850 30

7782 CLARK MANAGER 7839 81/06/09 2450 10

7788 SCOTT ANALYST 7566 87/04/19 3000 20

7839 KING PRESIDENT 81/11/17 5000 10

7844 TURNER SALESMAN 7698 81/09/08 1500 0 30

7900 JAMES CLERK 7698 81/12/03 950 30

7902 FORD ANALYST 7566 81/12/03 3000 20

7934 MILLER CLERK 7782 82/01/23 1300 10

dept 테이블

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

1

2

3

4

5

6

7

8

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

department 테이블

SQL> SELECT * FROM department;

    DEPTNO DNAME                                                    PART BUILD
---------- -------------------------------------------------- ---------- ------------------------------
       101 Computer Engineering                                      100 Information Bldg
       102 Multimedia Engineering                                    100 Multimedia Bldg
       103 Software Engineering                                      100 Software Bldg
       201 Electronic Engineering                                    200 Electronic Control Bldg
       202 Mechanical Engineering                                    200 Machining Experiment Bldg
       203 Chemical Engineering                                      200 Chemical Experiment Bldg
       301 Library and Information science                           300 College of Liberal Arts
       100 Department of Computer Information                         10
       200 Department of Mechatronics                                 10
       300 Department of Humanities and Society                       20
        10 College of Engineering
        20 College of Liberal Arts

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

SQL> SELECT * FROM department;

DEPTNO DNAME PART BUILD

---------- -------------------------------------------------- ---------- ------------------------------

101 Computer Engineering 100 Information Bldg

102 Multimedia Engineering 100 Multimedia Bldg

103 Software Engineering 100 Software Bldg

201 Electronic Engineering 200 Electronic Control Bldg

202 Mechanical Engineering 200 Machining Experiment Bldg

203 Chemical Engineering 200 Chemical Experiment Bldg

301 Library and Information science 300 College of Liberal Arts

100 Department of Computer Information 10

200 Department of Mechatronics 10

300 Department of Humanities and Society 20

10 College of Engineering

20 College of Liberal Arts

professor 테이블 : 공간 부족으로 NAME, POSITION, HPAGE 필드의 내용을 일부만 출력

SQL> SELECT * FROM professor;

PROFNO NAME ID        POSITION   PAY HIREDATE BONUS DEPTNO EMAIL                HPAGE
------ ---- --------- ---------- --- -------- ----- ------ -------------------- ------------------
  1001 Audi Murphy    a full pro 550 80/06/23   100    101 captain@abc.net      http://www.abc.net
  1002 Ange Bassett   assistant  380 87/01/30    60    101 sweety@abc.net       http://www.abc.net
  1003 Jess Lange     instructor 270 98/03/22          101 pman@power.com       http://www.power.c
  2001 Wino Ryder     instructor 250 01/09/01          102 lamb1@hamail.net
  2002 Mich Pfeiffer  assistant  350 85/11/30    80    102 number1@naver.com    http://num1.naver.
  2003 Whoo Goldberg  a full pro 490 82/04/29    90    102 bdragon@naver.com
  3001 Emma Thompson  a full pro 530 81/10/23   110    103 angel1004@hanmir.com
  3002 Juli Roberts   assistant  330 97/07/01    50    103 naone10@empal.com
  3003 Shar Stone     instructor 290 02/02/24          103 only_u@abc.com
  4001 Mery Streep    a full pro 570 81/10/23   130    201 chebin@daum.net
  4002 Susa Sarandon  assistant  330 09/08/30          201 gogogo@def.com
  4003 Nico Kidman    assistant  310 99/12/01    50    202 mypride@hanmail.net
  4004 Holl Hunter    instructor 260 09/01/28          202 ironman@naver.com
  4005 Meg  Ryan      a full pro 500 85/09/18    80    203 standkang@naver.com
  4006 Andi Macdowell instructor 220 10/06/28          301 napeople@jass.com
  4007 Jodi Foster    assistant  290 01/05/23    30    301 silver-her@daum.net  http://abcdefg.hi.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

SQL> SELECT * FROM professor;

PROFNO NAME ID POSITION PAY HIREDATE BONUS DEPTNO EMAIL HPAGE

------ ---- --------- ---------- --- -------- ----- ------ -------------------- ------------------

1001 Audi Murphy a full pro 550 80/06/23 100 101 captain@abc.net http://www.abc.net

1002 Ange Bassett assistant 380 87/01/30 60 101 sweety@abc.net http://www.abc.net

1003 Jess Lange instructor 270 98/03/22 101 pman@power.com http://www.power.c

2001 Wino Ryder instructor 250 01/09/01 102 lamb1@hamail.net

2002 Mich Pfeiffer assistant 350 85/11/30 80 102 number1@naver.com http://num1.naver.

2003 Whoo Goldberg a full pro 490 82/04/29 90 102 bdragon@naver.com

3001 Emma Thompson a full pro 530 81/10/23 110 103 angel1004@hanmir.com

3002 Juli Roberts assistant 330 97/07/01 50 103 naone10@empal.com

3003 Shar Stone instructor 290 02/02/24 103 only_u@abc.com

4001 Mery Streep a full pro 570 81/10/23 130 201 chebin@daum.net

4002 Susa Sarandon assistant 330 09/08/30 201 gogogo@def.com

4003 Nico Kidman assistant 310 99/12/01 50 202 mypride@hanmail.net

4004 Holl Hunter instructor 260 09/01/28 202 ironman@naver.com

4005 Meg Ryan a full pro 500 85/09/18 80 203 standkang@naver.com

4006 Andi Macdowell instructor 220 10/06/28 301 napeople@jass.com

4007 Jodi Foster assistant 290 01/05/23 30 301 silver-her@daum.net http://abcdefg.hi.

student 테이블의 내용 : 공간 부족으로 NAME 필드의 내용을 왼쪽 4글자만 남김

SQL> SELECT * from student;

STUDNO NAME ID       GRADE JUMIN         BIRTHDAY TEL          HEIGHT WEIGHT DEPTNO1 EPTNO2 PROFNO
------ ---- -------- ----- ------------- -------- ------------ ------ ------ ------- ------ ------
  9411 Jame 75true       4 7510231901813 75/10/23 055)381-2158    180     72     101    201   1001
  9412 Rene Russo        4 7502241128467 75/02/24 051)426-1700    172     64     102          2001
  9413 Sand Bullock      4 7506152123648 75/06/15 053)266-8947    168     52     103    203   3002
  9414 Demi Moore        4 7512251063421 75/12/25 02)6255-9875    177     83     201          4001
  9415 Dann Glover       4 7503031639826 75/03/03 031)740-6388    182     70     202          4003
  9511 Bill Crystal      3 7601232186327 76/01/23 055)333-6328    164     48     101          1002
  9512 Nich Cage         3 7604122298371 76/04/12 051)418-9627    161     42     102    201   2002
  9513 Mich Keaton       3 7609112118379 76/09/11 051)724-9618    177     55     202          4003
  9514 Bill Murray       3 7601202378641 76/01/20 055)296-3784    160     58     301    101   4007
  9515 Maca Culkin       3 7610122196482 76/10/12 02)312-9838     171     54     201          4001
  9611 Rich Dreyfus      2 7711291186223 77/11/29 02)6788-4861    182     72     101          1002
  9612 Tim  Robbins      2 7704021358674 77/04/02 055)488-2998    171     70     102          2001
  9613 Wesl Snipes       2 7709131276431 77/09/13 053)736-4981    175     82     201          4002
  9614 Stev Martin       2 7702261196365 77/02/26 02)6175-3945    166     51     201          4003
  9615 Dani Day-Lewi     2 7712141254963 77/12/14 051)785-6984    184     62     301          4007
  9711 Dann Devito       1 7808192157498 78/08/19 055)278-3649    162     48     101
  9712 Sean Connery      1 7801051776346 78/01/05 02)381-5440     175     63     201
  9713 Chri Slater       1 7808091786954 78/08/09 031)345-5677    173     69     201
  9714 Char Sheen        1 7803241981987 78/03/24 055)423-9870    179     81     102
  9715 Anth Hopkins      1 7802232116784 78/02/23 02)6122-2345    163     51     103

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

SQL> SELECT * from student;

STUDNO NAME ID GRADE JUMIN BIRTHDAY TEL HEIGHT WEIGHT DEPTNO1 EPTNO2 PROFNO

------ ---- -------- ----- ------------- -------- ------------ ------ ------ ------- ------ ------

9411 Jame 75true 4 7510231901813 75/10/23 055)381-2158 180 72 101 201 1001

9412 Rene Russo 4 7502241128467 75/02/24 051)426-1700 172 64 102 2001

9413 Sand Bullock 4 7506152123648 75/06/15 053)266-8947 168 52 103 203 3002

9414 Demi Moore 4 7512251063421 75/12/25 02)6255-9875 177 83 201 4001

9415 Dann Glover 4 7503031639826 75/03/03 031)740-6388 182 70 202 4003

9511 Bill Crystal 3 7601232186327 76/01/23 055)333-6328 164 48 101 1002

9512 Nich Cage 3 7604122298371 76/04/12 051)418-9627 161 42 102 201 2002

9513 Mich Keaton 3 7609112118379 76/09/11 051)724-9618 177 55 202 4003

9514 Bill Murray 3 7601202378641 76/01/20 055)296-3784 160 58 301 101 4007

9515 Maca Culkin 3 7610122196482 76/10/12 02)312-9838 171 54 201 4001

9611 Rich Dreyfus 2 7711291186223 77/11/29 02)6788-4861 182 72 101 1002

9612 Tim Robbins 2 7704021358674 77/04/02 055)488-2998 171 70 102 2001

9613 Wesl Snipes 2 7709131276431 77/09/13 053)736-4981 175 82 201 4002

9614 Stev Martin 2 7702261196365 77/02/26 02)6175-3945 166 51 201 4003

9615 Dani Day-Lewi 2 7712141254963 77/12/14 051)785-6984 184 62 301 4007

9711 Dann Devito 1 7808192157498 78/08/19 055)278-3649 162 48 101

9712 Sean Connery 1 7801051776346 78/01/05 02)381-5440 175 63 201

9713 Chri Slater 1 7808091786954 78/08/09 031)345-5677 173 69 201

9714 Char Sheen 1 7803241981987 78/03/24 055)423-9870 179 81 102

9715 Anth Hopkins 1 7802232116784 78/02/23 02)6122-2345 163 51 103

Cartecian Product(카티션 곱) 조인 태상 테이블들의 조건이 누락되었을 때 해당 조인에 해당하는 모든 대상 행을… Continue Reading 생능_오라클SQL_04장. JOIN

생능_오라클SQL_03장. SQL 복수행 함수

GROUP 함수 emp 테이블의 내용

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17