생계형 개발자 "투지파파"

오라클 기초 실습내용 3/3

기본 sql 사용과 해당문법의 설명을 확인하면서 기초를 다시 확인할수있을듯함..

워낙 오래전 자료다 보니... 지우는것보단...이렇게라도 .... 가끔보게되면

뭔가 새로운 기분이 들지않을까..해서......

UNIX(r) System V Release 4.0 (xx)

login: team4
Password:

Sun Microsystems Inc.   SunOS 5.5.1     Generic May 1996
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

공지사항 : Oracle Login 변경(oracle실습은 Team계정으로 login 하여 사용하세요)
           - from : tschang (1999/06/14)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
SQL*Plus: Release 8.0.6.0.0 - Production on Fri Feb 18 09:47:54 2000
 
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Objects option
PL/SQL Release 8.0.6.0.0 - Production
 
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ATTEND                         TABLE
BONUS                          TABLE
DEPT                           TABLE
DUMMY                          TABLE
HYO_S                          TABLE
IMSIC                          TABLE
MAST                           TABLE
MASTER36                       TABLE
MASTERC                        TABLE
MP                             TABLE
M_4                            TABLE
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
OOK                            TABLE
OPENC                          TABLE
P                              TABLE
PAL                            TABLE
S                              TABLE
S2                             TABLE
SALGRADE                       TABLE
SLAVE36                        TABLE
SP                             TABLE
SS                             TABLE
SS2                            TABLE
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SSS                            TABLE
STUDENTC                       TABLE
S_IN                           TABLE
VIEWPC                         VIEW
WANP                           TABLE
WANS                           TABLE
WANSP                          TABLE
설강                           TABLE
설강36                         TABLE
수강                           TABLE
수강36                         TABLE
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
학생                           TABLE
학생36                         TABLE
 
35 rows selected.
 
SQL> select * from sp;
 
S_ P_        QTY
-- -- ----------
S1 P1        300
S1 P2        200
S1 P3        400
S1 P4        200
S1 P5        100
S1 P6        100
S2 P1        300
S2 P2        400
S3 P2        200
S4 P2        200
S4 P4        300
 
S_ P_        QTY
-- -- ----------
S4 P5        400
 
12 rows selected.
 
SQL> select p_id
  2  from sp
  3  group by p_id
  4  having count(*) >1; // 테이블sp에서 p_id 그룹으로하고 조건은 p_id의 합이 1보다 큰것을 출력.
                         // group by ... + having(조건) ,count(*) :
P_
--
P1
P2
P4
P5
 
SQL> select sname
  2  from s, sp   
  3  where s.s_id = sp.s_id and sp.p_id = 'P2';
 
SNAME
--------------------
홍길동
손오공
박문수
신수동
 
SQL> select sname
  2  from s
  3  where s_id in
  4    (select s_id
  5     from sp
  6      where sp.p_id = 'P2');
 
SNAME
--------------------
홍길동
손오공
박문수
신수동
 
SQL> select sname
  2  from s
  3  where exists
  4  (select *
  5  from sp
  6  where s.s_id=sp.s_id and sp.p_id='P2');
 
SNAME
--------------------
홍길동
손오공
박문수
신수동
 
SQL> select sname
  2  from s
  3  where s_id not in
  4      (select s_id
  5       from sp
  6       where sp.p_id='P2');
 
SNAME
--------------------
반월동
 
SQL> select p_id
  2  from p
  3  where weight >16
  4  union              //합집합
  5  select p_id
  6  from sp
  7  where p_id='S2'; 
 
P_
--
P2
P3
P6
 
SQL> l     // l =list =엘.
  1  select p_id
  2  from p
  3  where weight >16
  4  union
  5  select p_id
  6  from sp
  7* where p_id='S2'
 
SQL> l 4
  4* union
 
SQL> c /union/union all    //문자바꿈. union 과  union all의 차이
  4* union all

SQL> /                     //바로앞의 실행
 
P_
--
P2
P3
P6
 
SQL> l
  1  select p_id
  2  from p
  3  where weight >16
  4  union all
  5  select p_id
  6  from sp
  7* where p_id='S2'

SQL> l 7
  7* where p_id='S2'     //문장이 제대로 수행안된 이유 s_id 를 p_id로 잘못사용.....

SQL> c /p/s
  7* where s_id='S2'

SQL> /
 
P_        //union all..................................
--
P2
P3
P6
P1
P2
 
SQL> l
  1  select p_id
  2  from p
  3  where weight >16
  4  union all
  5  select p_id
  6  from sp
  7* where s_id='S2'

SQL> l 4
  4* union all

SQL> c /union all/union
  4* union

SQL> /
 
P_
--     //union.........................................union all과의 차이점 ?????
P1
P2
P3
P6
 
SQL> select s_id
  2  from s
  3  where city =
  4       (select city
  5        from s
  6        where s_id='S1');
 
S_
--
S1
S4
 
SQL> select s_id     // s_id를 테이블 s에서 가져와 출력을 하는데....
  2  from s       
  3  where city in   // city가  다음과 같아야한다.=........
  4        (select city     // city를 s테이블에서 가져오는데 조건은 s_id가 's1'이어야한다.
  5         from s          // ( => s_id가 's1'인 것의 city를 검색한다)
  6  where s_id='S1');
 
S_
--
S1
S4
 
SQL> select * from p;
 
P_ PNAME                COLOR          WEIGHT CITY
-- -------------------- ---------- ---------- ----------
P1 너트                 빨강               12 서울
P2 볼트                 초록               17 부산
P3 스크류               파랑               17 광주
P4 스크류               빨강               14 서울
P5 캠                   파랑               12 부산
P6 콕                   빨강               19 서울
 
6 rows selected.
 
SQL> update p
  2  set color='노랑',
  3      weight=weight+5,
  4      city=NULL
  5  where p_id='P2';
update p
       *
ERROR at line 1:
ORA-01407: cannot update ("TEAM4"."P"."CITY") to NULL
 
 
SQL> desc p;
 Name                            Null?    Type
 ------------------------------- -------- ----
 P_ID                            NOT NULL CHAR(2)
 PNAME                           NOT NULL VARCHAR2(20)
 COLOR                                    VARCHAR2(10)
 WEIGHT                          NOT NULL NUMBER(3)
 CITY                            NOT NULL VARCHAR2(10)
 
SQL> desc s;
 Name                            Null?    Type
 ------------------------------- -------- ----
 S_ID                            NOT NULL CHAR(2)
 SNAME                           NOT NULL VARCHAR2(20)
 STATUS                          NOT NULL NUMBER(3)
 CITY                            NOT NULL VARCHAR2(10)
 
SQL> desc sp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 S_ID                            NOT NULL CHAR(2)
 P_ID                            NOT NULL CHAR(2)
 QTY                             NOT NULL NUMBER(3)
 
SQL> select * from sp;
 
S_ P_        QTY
-- -- ----------
S1 P1        300
S1 P2        200
S1 P3        400
S1 P4        200
S1 P5        100
S1 P6        100
S2 P1        300
S2 P2        400
S3 P2        200
S4 P2        200
S4 P4        300
 
S_ P_        QTY
-- -- ----------
S4 P5        400
 
12 rows selected.
 
SQL> select * from s;
 
S_ SNAME                    STATUS CITY
-- -------------------- ---------- ----------
S1 홍길동                       20 서울
S2 손오공                       10 부산
S3 박문수                       30 부산
S4 신수동                       20 서울
S5 반월동                       30 경주
 
SQL> select * from pp;

P_ PNAME      COLOR     WEIGHT CITY
-- ---------- ----- ---------- ----------
p1 너트       빨강          12 서울
p2 볼트       초록          17 부산
p3 스크류     파랑          17 광주
p4 스크류     빨강          14 서울
p5 캠         파랑          12 부산
p6 콕         빨강          19 서울

6 rows selected.

SQL> desc pp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 P_ID                            NOT NULL CHAR(2)
 PNAME                           NOT NULL VARCHAR2(10)
 COLOR                           NOT NULL VARCHAR2(5)
 WEIGHT                          NOT NULL NUMBER(3)
 CITY                                     VARCHAR2(10)

SQL>select sp  * from sspp;

S_ P_        QTY
-- -- ----------
s1 p1        300
s1 p2        200
s1 p3        400
s1 p4        200
s1 p5        100
s1 p6        100
s2 p1        300
s2 p2        400
s3 p2        200
s4 p2        200
s4 p4        300

S_ P_        QTY
-- -- ----------
s4 p5        400

12 rows selected.

SQL> desc sspp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 S_ID                            NOT NULL CHAR(2)
 P_ID                            NOT NULL CHAR(2)
 QTY                             NOT NULL NUMBER(3)

SQL> connect scott/tiger
Connected.

SQL> selcect * from emp;

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK                      7902 17-DEC-80        800
                   20

      7499 ALLEN      SALESMAN                   7698 20-FEB-81       1600
       300         30

      7521 WARD       SALESMAN                   7698 22-FEB-81       1250
       500         30

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7566 JONES      MANAGER                    7839 02-APR-81       2975
                   20

      7654 MARTIN     SALESMAN                   7698 28-SEP-81       1250
      1400         30

      7698 BLAKE      MANAGER                    7839 01-MAY-81       2850
                   30

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7782 CLARK      MANAGER                    7839 09-JUN-81       2450
                   10

      7788 SCOTT      ANALYST                    7566 09-DEC-82       3000
                   20

      7839 KING       PRESIDENT                       17-NOV-81       5000
                   10

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7844 TURNER     SALESMAN                   7698 08-SEP-81       1500
         0         30

      7876 ADAMS      CLERK                      7788 12-JAN-83       1100
                   20

      7900 JAMES      CLERK                      7698 03-DEC-81        950
                   30

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7902 FORD       ANALYST                    7566 03-DEC-81       3000
                   20

      7934 MILLER     CLERK                      7782 23-JAN-82       1300
                   10

      7035 smith      timer

15 rows selected.

SQL> select empno, ename, sal,  //마지막의 ,는 있어도 되고 없어도 되고,,,,,,,
  2  from emp
  3  where deptno=&deptno;      //조건을 deptno로 주는데 deptno를 변수로 설정해서 입력값을 받아서 조건 결정
Enter value for deptno: 10
old   3: where deptno=&deptno
new   3: where deptno=10

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300

SQL> select empno,ename , sal,deptno //deptno를 추가
  2  from emp
  3  where deptno=&deptno;
Enter value for deptno: 30
old   3: where deptno=&deptno
new   3: where deptno=30

  //-----생략----//

SQL> /           //재실행
Enter value for deptno: 10
old   3: where deptno=&deptno
new   3: where deptno=10

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10

SQL> /
Enter value for deptno: 20
old   3: where deptno=&deptno
new   3: where deptno=20

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH             800         20
      7566 JONES            2975         20
      7788 SCOTT            3000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20

SQL> /
Enter value for deptno: 30
old   3: where deptno=&deptno
new   3: where deptno=30

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30

6 rows selected.

SQL> select * from ss;

S_ SNAME          STATUS CITY
-- ---------- ---------- ----------
s1 홍길동             20 서울
s2 손오공             10 부산
s3 박문수             30 부산
s4 신수동             20 서울
s5 반월성             30 경주

SQL> select * from sspp
  2  ;

S_ P_        QTY
-- -- ----------
s1 p1        300
s1 p2        200
s1 p3        400
s1 p4        200
s1 p5        100
s1 p6        100
s2 p1        300
s2 p2        400
s3 p2        200
s4 p2        200
s4 p4        300

S_ P_        QTY
-- -- ----------
s4 p5        400

12 rows selected.

SQL> select * fromm  tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A11                            TABLE
B11                            TABLE
C11                            TABLE
MAST                           TABLE
PP                             TABLE
SLAVE1                         TABLE
SS                             TABLE
SSPP                           TABLE

8 rows selected.

SQL> column sal format $99,999  //칼럼 표현형식 => I-26 page

SQL> select * from emp;

     EMPNO ENAME      JOB                         MGR HIREDATE       SAL
---------- ---------- -------------------- ---------- --------- --------
      COMM     DEPTNO
---------- ----------
      7566 JONES      MANAGER                    7839 02-APR-81   $2,975
                   20

      7654 MARTIN     SALESMAN                   7698 28-SEP-81   $1,250
      1400         30

      7698 BLAKE      MANAGER                    7839 01-MAY-81   $2,850
 ..................이하생략!!
15 rows selected.

SQL> column sal clear  //칼럼형식 해제.

SQL> slelect * from enp  mp;  //엘리어스

     EMPNO ENAME      JOB                         MGR HIREDATE         SAL
---------- ---------- -------------------- ---------- --------- ----------
      COMM     DEPTNO
---------- ----------
      7369 SMITH      CLERK                      7902 17-DEC-80        800
                   20

      7499 ALLEN      SALESMAN                   7698 20-FEB-81       1600
       300         30

      7521 WARD       SALESMAN                   7698 22-FEB-81       1250
 ...........이하생략
 15 rows selected.

SQL> select  &1, &&2 from emp  //SQL변수(& , &&)입력!
   2  where &&2 =&3;           
Enter value for 1: SAL        //1의값 입력
Enter value for 2: JOB        //2의값 입력
old   1: select &1, &&2 from emp
new   1: select SAL, JOB from emp
Enter value for 3: 'SALESMAN'
old   2: where &&2 =&3
new   2: where JOB ='SALESMAN'

       SAL JOB
---------- --------------------
       800 SALESMAN
      1100 SALESMAN
       950 SALESMAN
      1300 SALESMAN

SQL> /
Enter value for 1: SAL              //1의값 입력
old   1: select &1, &&2 from emp    //2의값은 앞에서 입력한값을 그대로 사용
new   1: select SAL, JOB from emp   //2=JOB
Enter value for 3: 'MANAGER'
old   2: where &&2 =&3
new   2: where JOB ='MANAGER'

       SAL JOB
---------- --------------------
      2975 MANAGER
      2850 MANAGER
      2450 MANAGER

SQL> /
Enter value for 1: saL
old   1: select &1, &&2 from emp
new   1: select saL, JOB from emp
Enter value for 3: 'SALESMAN'
old   2: where &&2 =&3
new   2: where JOB ='SALESMAN'

       SAL JOB
---------- --------------------
      1600 SALESMAN
      1250 SALESMAN
      1250 SALESMAN
      1500 SALESMAN

SQL> select ename  , sal, sal/22, round(sal/22.0), trunc(sal/22.2)   //SQL함수 I-29 page 책이 조금 틀림
  2  from   emp
  3  where deptno=30;

ENAME             SAL     SAL/22 ROUND(SAL/22.0) TRUNC(SAL/22.2)
---------- ---------- ---------- --------------- ---------------
ALLEN            1600 72.7272727              73              72
WARD             1250 56.8181818              57              56
MARTIN           1250 56.8181818              57              56
BLAKE            2850 129.545455             130             128
TURNER           1500 68.1818182              68              67
JAMES             950 43.1818182              43              42

6 rows selected.

SQL> select ename, sum(comm)
  2  from emp
  3  group  by ename;

ENAME       SUM(COMM)
---------- ----------
ADAMS
ALLEN             300
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN           1400
MILLER
SCOTT

ENAME       SUM(COMM)
---------- ----------
SMITH
TURNER              0
WARD              500
smith

15 rows selected.

SQL> select avg(sal), max(sal), min(comm)*100, count(comm), sum(sal)
  2  from emp
  3  where job='SALESMAN';

  AVG(SAL)   MAX(SAL) MIN(COMM)*100 COUNT(COMM)   SUM(SAL)
---------- ---------- ------------- ----------- ----------
      1400       1600             0           4       5600

SQL> select ename, sum(comm)
  2  from emp
  3  group by ename
  4  order by ename desc;

ENAME       SUM(COMM)
---------- ----------
smith
WARD              500
TURNER              0
SMITH
SCOTT
MILLER
MARTIN           1400
KING
JONES
JAMES
FORD

ENAME       SUM(COMM)
---------- ----------
CLARK
BLAKE
ALLEN             300
ADAMS

15 rows selected.

SQL> select deptno, sum(comm)
  2  from emp
  3  group by deptno;

    DEPTNO  SUM(COMM)
---------- ----------
        10
        20
        30       2200

SQL> select count(job)
  2  from emp;

COUNT(JOB)            // 칼럼 job 이 15개가 있다. 중복과는 상관없다.
----------
        15

SQL> select count(distinct job)   //distinct:중복되 칼럼을 간단하게
  2  from emp;

COUNT(DISTINCTJOB)
------------------
                 6

SQL> select deptno, job, count(*)
  2  from emp
  3  where  deptno in(10,20)  //in = or
  4  group by deptno, job;    //deptno와 job의 순서로...group 만약deptno하나에 대해서만 group by해주면 에러

    DEPTNO JOB                    COUNT(*)
---------- -------------------- ----------
        10 CLERK                         1
        10 MANAGER                       1
        10 PRESIDENT                     1
        20 ANALYST                       2
        20 CLERK                         2
        20 MANAGER                       1

6 rows selected.

SQL> select deptno from  emp;

    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20

    DEPTNO
----------
        30
        20
        10

15 rows selected.

SQL> select deptno, job
  2  from emp;

    DEPTNO JOB
---------- --------------------
        20 CLERK
        30 SALESMAN
        30 SALESMAN
        20 MANAGER
        30 SALESMAN
        30 MANAGER
        10 MANAGER
        20 ANALYST
        10 PRESIDENT
        30 SALESMAN
        20 CLERK

    DEPTNO JOB
---------- --------------------
        30 CLERK
        20 ANALYST
        10 CLERK
           timer

15 rows selected.

SQL> select deptno, sum(sal), avg(sal) //sal의 합계와 평균.
  2  from emp                          //
  3  where job <> 'MANAGER'            // job != 'MANAGER'
  4  group by deptno                   //
  5  having avg(sal)> 1500;            //그룹화하는 조건으로 sal의 평균이 1500이상

    DEPTNO   SUM(SAL)   AVG(SAL)
---------- ---------- ----------
        10       6300       3150
        20       7900       1975

SQL> select  deptno, sal
  2  from emp;

    DEPTNO        SAL
---------- ----------
        20        800
        30       1600
        30       1250
        20       2975
        30       1250
        30       2850
        10       2450
        20       3000
        10       5000
        30       1500
        20       1100

    DEPTNO        SAL
---------- ----------
        30        950
        20       3000
        10       1300

15 rows selected.

SQL> select deptno  , sum(sal)                    //1.job이 manager나 president가 아닌 조건을
  2  from emp                                       만족하는
  3  where job not in('MANAGER','PRESIDENT')         .....
  4  group by deptno                              //
  5  having count(*)>2 and sum(sal) >16000        //그룹화하는 조건을 나열
  6  order by sum(sal) desc;                      //정렬하는 칼럼과 방식.내림차순

    DEPTNO   SUM(SAL)
---------- ----------
        20       7900
        30       6550
  
SQL> select initcap(ename)   //출력시 ename의 첫글자를 대문자로한다.
  2  from emp;

INITCAP(EN
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams

INITCAP(EN
----------
James
Ford
Miller
Smith

15 rows selected.

===>여기서는 모르겠다.......  책의 실습 I-34 page

SQL> select inse lect * from dept;

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

SQL> select instr(loc, 'A Y\ ^H   X')
  2  from dept;

INSTR(LOC,'X')
--------------
             0
             0
             0
             0

SQL> select inse rt  tr(loc, C 'C')
  2  for  rom dept;

INSTR(LOC,'C')
--------------
             0
             0
             1
             0

SQL> select instr(loc, '" L')
  2  from m dept;

INSTR(LOC,'L')
--------------
             0
             3
             0
             0

SQL> select length(eanme    name)
  2  from emp;

LENGTH(ENAME)
-------------
            5
            5
            4
            5
            6
            5
            5
            5
            4
            6
            5

LENGTH(ENAME)
-------------
            5
            4
            6
            5

15 rows selected.

SQL> select lower(eanme    name)
  2  from emp;

LOWER(ENAM
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams

LOWER(ENAM
----------
james
ford
miller
smith

15 rows selected.

SQL> su  select substr(grade,     rde  ade                   select substr(grade, 1,2)
  2  from    frmoo   om emp;
select substr(grade, 1,2)
              *
ERROR at line 1:
ORA-00904: invalid column name

SQL> l 1
  1* select substr(grade, 1,2)
SQL> c grad    /gradd e/ename
  1* select substr(ename, 1,2)
SQL> /

SUBS
----
SM
AL
WA
JO
MA
BL
CL
SC
KI
TU
AD

SUBS
----
JA
FO
MI
sm

15 rows selected.

SQL> select ename, job
  2  (decodd e, (job, 'CLERK', 0.2, 'MANAGER', 0.5 15, 'SALESMAN', 0.3, 0.2)*SAL)
  3  from emp
  4  where deptno=10;
(decode, (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
             *
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> l 2
  2* (decode, (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
SQL> c /decode,/decode
  2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
SQL> /
select ename, job
              *
ERROR at line 1:
ORA-00904: invalid column name

SQL> l
  1  select ename, job
  2  (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
  3  from emp
  4* where deptno=10

SQL> l 2
  2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)

SQL> c /sla   SAL/bonus
  2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'bonusESMAN', 0.3, 0.2)*SAL)

SQL> roo llback
  2  ;

Rollback complete.

SQL> l
  1  rollback
  2*

SQL> seec  lect enma^?             select ename, job
  2  (decoed  de(job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SLAESMAN',0.3,0.2)*SAL) BONUS
  3  FLR   from emp
  4  where deptno =10;
select ename, job
              *
ERROR at line 1:
ORA-00904: invalid column name

SQL> l 1
  1* select ename, job

SQL> c /job/job,
  1* select ename, job,

SQL> /

ENAME      JOB                       BONUS
---------- -------------------- ----------
CLARK      MANAGER                     490
KING       PRESIDENT                  1000
MILLER     CLERK                       260

SQL>

오라클 기초 실습내용 2/3

기본 sql 사용과 해당문법의 설명을 확인하면서 기초를 다시 확인할수있을듯함..

워낙 오래전 자료다 보니... 지우는것보단...이렇게라도 .... 가끔보게되면

뭔가 새로운 기분이 들지않을까..해서......

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

   ---------------------------------------------------------------------
login: XXXXXX
Password:
Last login: Thu Feb 17 11:29:40 from 210.119.58.174
Sun Microsystems Inc.   SunOS 5.5.1     Generic May 1996
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
공지사항 : Oracle Login 변경(oracle실습은 Team계정으로 login 하여 사용하세요)
           - from : XXXXX (1999/06/14)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

tjaiit% sqlplus team4/team4p

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Feb 17 11:33:15 2000

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Objects option
PL/SQL Release 8.0.6.0.0 - Production

SQL> create table insi( name number(8) not null, age number(2) ) ;
 Table created.

SQL> desc insi;   //desc : 생성된 테이블에 대한 데이타타입보여줌 내림차순
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL NUMBER(8)
 AGE                                      NUMBER(2)

SQL> alter table insi         //alter : 테이블 변경(modify)
  2  modify name varchar2(8); //alter : 테이블 변경(modify)
Table altered.

SQL> desc insi;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(8)  //number(8)가 varchar2(8)로 데이타타입이 바뀐다.
 AGE                                      NUMBER(2)

SQL> alter table insi           //alter : 테이블 변경(modify)
  2  modify name varchar2(10);  //alter : 테이블 변경(modify)

Table altered.

SQL> desc insi;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(10) //varchar(8)가 varchar2(10)로 데이타타입이 바뀐다.
 AGE                                      NUMBER(2)

SQL> alter table insi
  2  modify name varchar2(8); //테이블내에 자료가 없을때 데이타형의 자리수 축소...가능

Table altered.

SQL> desc insi;
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(8) //varchar2(10)이 varchar2(8)로 데이타타입이 바뀐다.
 AGE                                      NUMBER(2)

SQL> alter table insi          //테이블내에 추가
  2  add address varchar2(15);

Table altered.   //테이블 추가 끝~~

SQL> desc insi;  //확인.
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(8)
 AGE                                      NUMBER(2)
 ADDRESS                                  VARCHAR2(15)  //새로 추가된 칼럼

SQL> insert into insi             //------------------테이블내에 자료 입력.
  2  values('&name',&age,'&address');
Enter value for name: aaaa
Enter value for age: 23
Enter value for address: 서울 종로구
old   2: values('&name',&age,'&address')
new   2: values('aaaa',23,'서울 종로구')

1 row created.

SQL> select * from insi;

NAME            AGE ADDRESS
-------- ---------- ---------------
aaaa             23 서울 종로구

SQL> alter table insi
  2  modify age char(2);  //age의 데이타타입을 number(2)에서 char(2)로 수정한다.
modify age char(2)        //  기
       *                  //      러
ERROR at line 2:          //            나 !! error 다. 왜!  이미 데이터가 들어가있는 상태이기땜시..
ORA-01439: column to be modified must be empty to change datatype

SQL> alter table insi
  2  modify age number(4); //age number(2)를 number(4)로 늘림.
 
Table altered.             // 수정 성공!

SQL> select * from insi;   //table 확인

NAME            AGE ADDRESS
-------- ---------- ---------------
aaaa             23 서울 종로구

SQL> desc insi              //table insi의 자료형 확인.
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(8)
 AGE                                      NUMBER(4)  //수정된사항 반영!
 ADDRESS                                  VARCHAR2(15)

SQL> alter table insi
  2  modify age number(2);  //반대로 age number(4)를 number(2)로 수정 (=줄임).
modify age number(2)
       *                       // 역시
ERROR at line 2:               //      error가 난다!
ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> alter table insi         //insi 테이블내에 phone이란 칼럼 추가.
  2  add phone varchar2(8);

Table altered.

SQL> select * from insi;             
                                      //추가....
NAME            AGE ADDRESS         PHONE
-------- ---------- --------------- --------
aaaa             23 서울 종로구

SQL> alter table insi
  2  modify phone varchar2(8) not null; //phone의 속성에다 not null을 추가한다....
modify phone varchar2(8) not null       // 긴데...
       *                                // Error 가
ERROR at line 2:                        //          난다.... not null뗌시..
ORA-02296: cannot enable (TEAM4.) - null values found

SQL> drop table insi; //테이블 삭제

Table dropped.

SQL> select * from tab;  //전체 테이블 목록 보여줌
  ///생략///

SQL> rollback;           //복구...

Rollback complete.

SQL> select * from insi;  //drop으로 삭제했기때문에 복구 불가.
select * from insi
              *
ERROR at line 1:
ORA-00942: table or view does not exist

경고: 이제는 ORACLE 에 연결되어 있지 않습니다. // connect ....

SQL> connect scott/tiger                       // id/pw
연결되었습니다.

SQL> select * from emp;                        //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
     7876  ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> desc emp;                   //데이터 타입 보여줌
 명칭                            널?      유형
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> desc
                사용법: DESCRIBE [user.]object[.subobject|@db_link] [column]

SQL> select * from tab;  //전체의 테이블

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ---------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
MASTER                         TABLE
S                              TABLE
SALGRADE                       TABLE

6 개의 행이 선택되었습니다.

SQL> desc emp;         
 명칭                            널?      유형
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> select * from emp;         //  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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> select empno, ename, sal, comm   // 칼럼명
  2  from emp                         // 테이블 emp에서 가져온다.
  3  where is null;                   // 조건이 null
where is null                         // Error -> Null이 들어가있는 칼럼이 지정안됨
      *
라인 3 에 오류:
ORA-00936: 식이 없습니다

SQL> select empno, ename, sal, comm
  2  from emp
  3  where comm is null;            //칼럼 comm이 Null인 조건.

    EMPNO ENAME            SAL      COMM
--------- ---------- --------- ---------
     7369 SMITH            800
     7566 JONES           2975
     7698 BLAKE           2850
     7782 CLARK           2450
     7788 SCOTT           3000
     7839 KING            5000
     7876 ADAMS           1100
     7900 JAMES            950
     7902 FORD            3000
     7934 MILLER          1300
10 개의 행이 선택되었습니다.

SQL> select empno, ename, sal, comm
  2  from emp
  3  where comm is not null;    //위와 반대.

    EMPNO ENAME            SAL      COMM
--------- ---------- --------- ---------
     7499 ALLEN           1600       300
     7521 WARD            1250       500
     7654 MARTIN          1250      1400
     7844 TURNER          1500         0

SQL> select empno, ename, sal, comm
  2  from emp
  3  order by comm desc;

    EMPNO ENAME            SAL      COMM
--------- ---------- --------- ---------
     7369 SMITH            800
     7566 JONES           2975
     7782 CLARK           2450
     7698 BLAKE           2850
     7788 SCOTT           3000
     7839 KING            5000
     7900 JAMES            950
     7934 MILLER          1300
     7902 FORD            3000
     7876 ADAMS           1100
     7654 MARTIN          1250      1400
     7521 WARD            1250       500
     7499 ALLEN           1600       300
     7844 TURNER          1500         0

14 개의 행이 선택되었습니다.

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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> desc emp
 명칭                            널?      유형
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> select ename, job, deptno
  2  from emp
  3  where job = 'manager'  //소문자.
  4  and deptno <> 30;

선택된 레코드가 없습니다.

SQL> select ename, job, deptno
  2  from emp
  3  where job='MANAGER'   //대문자.
  4  and deptno <> 30;
                                 //출력--------/
ENAME      JOB          DEPTNO
---------- --------- ---------
JONES      MANAGER          20
CLARK      MANAGER          10

SQL> select ename, job, deptno
  2  from emp
  3  where job='MANAGER'
  4  and deptno ^= 30;        //<>와 ^=는 같다.

ENAME      JOB          DEPTNO
---------- --------- ---------
JONES      MANAGER          20
CLARK      MANAGER          10

SQL> select ename, job, sal
  2  from emp
  3  where sal between 2000 and 3000;   // between조건: 책 실습 1-12 page

ENAME      JOB             SAL
---------- --------- ---------
JONES      MANAGER        2975
BLAKE      MANAGER        2850
CLARK      MANAGER        2450                                 ||
SCOTT      ANALYST        3000        
FORD       ANALYST        3000    
                                                               결과는 같다.
SQL>  select ename, job, sal
  2   from emp
  3   where sal >=2000 and sal <=3000;  // between 의 수학적 표현

ENAME      JOB             SAL
---------- --------- ---------
JONES      MANAGER        2975
BLAKE      MANAGER        2850
CLARK      MANAGER        2450
SCOTT      ANALYST        3000
FORD       ANALYST        3000

SQL> select ename deptno       //칼럼명ename 이 deptno로 엘리어스된다. ','가 없어서
  2  from emp
  3  where ename like 'S%';    //emp테이블중 ename이 대문자 S로 시작하는 것을 찾는다. like는 대소문자 구분

DEPTNO
----------
SMITH
SCOTT

SQL> select ename deptno
  2  from emp
  3  where ename like '%K';

DEPTNO
----------
CLARK

SQL> select ename deptno
  2  from emp
  3  where ename like 'F___'; //f로 시작하고 자리수가 4개인것

DEPTNO
----------
FORD

SQL> select ename deptno
  2  from emp
  3  where ename like '%M%';  //ename중 M이 들어간것 모두....

DEPTNO
----------
SMITH
MARTIN
ADAMS
JAMES
MILLER

SQL> select ename, job
  2  from emp
  3  where job in('CLERK', 'ANALYST');

ENAME      JOB
---------- ---------
SMITH      CLERK
SCOTT      ANALYST
ADAMS      CLERK
JAMES      CLERK
FORD       ANALYST
MILLER     CLERK

6 개의 행이 선택되었습니다.

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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> select distinct deptno   //distinct중복된 칼럼을 없앤다.
  2  from emp;

   DEPTNO
---------
       10
       20
       30

SQL> select ename,deptno
  2  from emp
  3  where ename like 'S%';
 
ENAME          DEPTNO
---------- ----------
SMITH              20
SCOTT              20
 
SQL> c /'S%'/'s%'   //대문자에서 소문자로 변경
  3* where ename like 's%'

SQL> /              //위 명령 재 실행....
 
ENAME          DEPTNO
---------- ----------
smith
 
SQL> select ename deptno  //엘리어스
  2  from emp
  3  where ename like 'S%';
 
DEPTNO
----------
SMITH
SCOTT
 
SQL> c /'S%'/'s%'
  3* where ename like 's%'
SQL> /
 
DEPTNO
----------
smith
 
SQL> select ename 이름, deptno "Emp No"  //여기도 엘리어스....
  2  from emp
  3  where deptno=10;  //조건

이름          Emp No
---------- ---------
CLARK             10
KING              10
MILLER            10

SQL> select ename 이름, deptno "Emp No" //엘리어스 Emp No가 문자열이고 공백이 있기때문에 " "으로 .....
  2  from emp e                        //엘리어스
  3  where deptno=10;

이름          Emp No
---------- ---------
CLARK             10
KING              10
MILLER            10

SQL> select * from emp e;                         //------------엘리어스 시작----

    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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.
                                                   //-----------엘리어스 끝----
SQL> select * from e
  2  ;
select * from e
              *
라인 1 에 오류:
ORA-00942:  테이블 또는 뷰가 존재하지 않습니다     //--당근!!

SQL> select empno, ename, job, emp.deptno, dname
  2  from emp, dept
  3  where emp,deptno = dept.deptno;           //에러 .->, 으로
where emp,deptno = dept.deptno
         *
라인 3 에 오류:
ORA-00920: 관계 연산자가 부적합합니다

SQL> select empno, ename, job, emp.deptno, dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno;

    EMPNO ENAME      JOB          DEPTNO DNAME
--------- ---------- --------- --------- --------------
     7369 SMITH      CLERK            20 RESEARCH
     7499 ALLEN      SALESMAN         30 SALES
     7521 WARD       SALESMAN         30 SALES
     7566 JONES      MANAGER          20 RESEARCH
     7654 MARTIN     SALESMAN         30 SALES
     7698 BLAKE      MANAGER          30 SALES
     7782 CLARK      MANAGER          10 ACCOUNTING
     7788 SCOTT      ANALYST          20 RESEARCH
     7839 KING       PRESIDENT        10 ACCOUNTING
     7844 TURNER     SALESMAN         30 SALES
     7876 ADAMS      CLERK            20 RESEARCH
     7900 JAMES      CLERK            30 SALES
     7902 FORD       ANALYST          20 RESEARCH
     7934 MILLER     CLERK            10 ACCOUNTING

14 개의 행이 선택되었습니다.

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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> select x.ename, x.sal, x.job, y.ename, y.sal, y.job
  2  from emp x, emp y
  3  where x.sal > y.sal
  4  and y.ename = 'JONES';                         //연구해봐....

ENAME            SAL JOB       ENAME            SAL JOB
---------- --------- --------- ---------- --------- ---------
SCOTT           3000 ANALYST   JONES           2975 MANAGER
KING            5000 PRESIDENT JONES           2975 MANAGER
FORD            3000 ANALYST   JONES           2975 MANAGER

SQL> select * from dept;

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

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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> select ename, dept.deptno, loc
  2  from emp, dept
  3  where emp.deptno(+) = dept.deptno;       //emp.deptno(+): 자식  dept.deptno :부모
                                              //조건은 부모만 가진 필드값을 위주로 emp.deptno(+) = dept.deptno을 만족하는
ENAME         DEPTNO LOC                      //값을 출력
---------- --------- -------------
CLARK             10 NEW YORK
KING              10 NEW YORK
MILLER            10 NEW YORK
SMITH             20 DALLAS
ADAMS             20 DALLAS
FORD              20 DALLAS
SCOTT             20 DALLAS
JONES             20 DALLAS
ALLEN             30 CHICAGO
BLAKE             30 CHICAGO
MARTIN            30 CHICAGO
JAMES             30 CHICAGO
TURNER            30 CHICAGO
WARD              30 CHICAGO
                  40 BOSTON

15 개의 행이 선택되었습니다.

SQL> select ename, empno, dept.deptno, loc
  2  from emp, dept
  3  where emp.deptno(+) = dept.deptno;

ENAME          EMPNO    DEPTNO LOC
---------- --------- --------- -------------
CLARK           7782        10 NEW YORK
KING            7839        10 NEW YORK
MILLER          7934        10 NEW YORK
SMITH           7369        20 DALLAS
ADAMS           7876        20 DALLAS
FORD            7902        20 DALLAS
SCOTT           7788        20 DALLAS
JONES           7566        20 DALLAS
ALLEN           7499        30 CHICAGO
BLAKE           7698        30 CHICAGO
MARTIN          7654        30 CHICAGO
JAMES           7900        30 CHICAGO
TURNER          7844        30 CHICAGO
WARD            7521        30 CHICAGO
                            40 BOSTON

15 개의 행이 선택되었습니다.

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
     7876 ADAMS      CLERK          7788 87/05/23              1100                  20
     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

14 개의 행이 선택되었습니다.

SQL> select worker.ename, manager.ename manager
  2  from emp worker, emp manager
  3  where worker.mgr = manager.empno;  //뜻 ->  일하는사람  : 고용주.

ENAME      MANAGER
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

13 개의 행이 선택되었습니다.

SQL>  select  deptno, sum(sal)
  2   from emp
  3  group by deptno;

   DEPTNO  SUM(SAL)
--------- ---------
       10      8750
       20     10875
       30      9400

오라클 기초 실습내용 1/3

기본 sql 사용과 해당문법의 설명을 확인하면서 기초를 다시 확인할수있을듯함..

워낙 오래전 자료다 보니... 지우는것보단...이렇게라도 .... 가끔보게되면

뭔가 새로운 기분이 들지않을까..해서......

  /# pc-oracle              #/
  /##########################/

=============================================
sql>connect system/manager

sql>create user 계정 identified by pass번호

sql>grant connect, resource to 계정
=============================================
시스템 계정으로 접속
SQL> connect system/manager
연결되었습니다.
=============================================
SQL> create table ss
  2   (s_id char(2) not null,
  3   sname varchar2(10) not null,
  4   status number(3) not null,
  5   city varchar2(10),
  6   constraint ss_s_id_pk primary key (s_id));  //주키

 ss 테이블이 생성

=============================================
SQL> create table pp
  2   (p_id char(2) not null,
  3   pname varchar2(10) not null,
  4   color varchar2(5) not null,
  5   weight number(3) not null,
  6   city varchar2(10),
  7   constraint pp_p_id_pk primary key (p_id));

  pp 테이블이 생성

=============================================
SQL> create table sspp
  2   (s_id char(2) not null,
  3    p_id char(2) not null,
  4    qty number(3) not null,
  5    constraint  sspp_s_id_pk primary key (s_id,p_id),
  6    constraint ss_s_id_fk foreign key (s_id)
  7     references ss on delete cascade,
  8   constraint pp_p_id_fk foreign key (p_id)
  9     references pp on delete cascade);

  sspp 테이블이 생성

=============================================
 SQL> Insert into ss
  2  values('s1','홍길동',20,'서울');

1 개의 행이 작성되었습니다.

=============================================
SQL> select *
  2  from ss;
- - - - - - - - - - - - - - - - - - - -
S_ SNAME         STATUS CITY
-- ---------- --------- ----------
s1 홍길동            20 서울

=============================================
SQL> Insert into ss
  2   values('&s_id','&sname',&status,'&city');
s_id 의 값을 입력하십시오: s2
sname 의 값을 입력하십시오: 손오공
status 의 값을 입력하십시오: 10
city 의 값을 입력하십시오: 부산
구   2:  values('&s_id','&sname',&status,'&city')
신   2:  values('s2','손오공',10,'부산')

1 개의 행이 작성되었습니다.

=============================================
SQL> select *
  2  from ss;

S_ SNAME         STATUS CITY
-- ---------- --------- ----------
s1 홍길동            20 서울
s2 손오공            10 부산
s3 박문수            30 부산
s4 신수동            20 서울
s5 반월성            30 경주
- - - - - - - - - - - - - - - - - - - - -
ss테이블 완성 최종
=============================================

SQL> create table 학생             //테이블의 생성.
  2   ( 학번   char(5)  not null,
  3     이름  varchar2(20),
  4     학년  char(1),
  5    신장  number(3));

Table created.                    //테이블 생성 완료!

SQL> select * from tab;

 TNAME                          TABTYPE  CLUSTERID
 ------------------------------ ------- ----------
 A                              TABLE
 AA                             TABLE           //중간생략!
 BABO                           VIEW
 BB                             TABLE
 WATERMASTER                    TABLE
 우수공급자                     VIEW
 학생                           TABLE
        49 rows selected.

SQL> desc 학생                              //방금 생성한 테이블의 데이타타입을 본다.
 Name                            Null?    Type
 ------------------------------- -------- ----
 학번                            NOT NULL CHAR(5)
 이름                                     VARCHAR2(20)
 학년                                     CHAR(1)
 신장                                     NUMBER(3)

SQL> select * from 학생;       //생성된 테이블을 본다.
   no rows selected             //내용이 없다.

SQL> alter table 학생          //테이블내에 칼럼 추가.         _______________________(page_IV-7)
  2  add (신장2  number(4));   //ADD(칼럼명  테이터 타입)
   Table altered.              //테이블내에 칼럼 추가 성공

SQL> desc 학생
 Name                            Null?    Type
 ------------------------------- -------- ----
 학번                            NOT NULL CHAR(5)
 이름                                     VARCHAR2(20)
 학년                                     CHAR(1)
 신장                                     NUMBER(3)
 신장2                                    NUMBER(4)    <== 추가된 칼럼

SQL> alter table 학생                          //칼럼 변경  |형|
  2   modify ( 신장2  varchar2(10));           //           |식|
       Table altered.

SQL> desc 학생
 Name                            Null?    Type
 ------------------------------- -------- ----
 학번                            NOT NULL CHAR(5)
 이름                                     VARCHAR2(20)
 학년                                     CHAR(1)
 신장                                     NUMBER(3)
 신장2                                    VARCHAR2(10)   <==칼럼의 데이타타입이 NUMBER에서 VARCHAR2로 변경!

SQL> alter table 학생
  2  modify(신장2  not null);    
       Table altered.

SQL> desc 학생
 Name                            Null?    Type
 ------------------------------- -------- ----
 학번                            NOT NULL CHAR(5)
 이름                                     VARCHAR2(20)
 학년                                     CHAR(1)
 신장                                     NUMBER(3)
 신장2                           NOT NULL VARCHAR2(10)    <----

SQL> alter table  학생
  2  modify(신장2  null);
    Table altered.

SQL> desc 학생;
 Name                            Null?    Type
 ------------------------------- -------- ----
 학번                            NOT NULL CHAR(5)
 이름                                     VARCHAR2(20)
 학년                                     CHAR(1)
 신장                                     NUMBER(3)
 신장2                                    VARCHAR2(10)    <----

SQL> drop table 학생;    //테이블의 삭제.
    Table dropped.       //테이블 삭제 성공

SQL>select * from tab;   /확인!

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
AA                             TABLE
BABO                           VIEW
BB                             TABLE
WATERMASTER                    TABLE         //이하생략
우수공급자                     VIEW
     50 rows selected.