[Data Base] 2019.06.22. 기록. 실습내용

SQL*Plus: Release 11.2.0.2.0 Production on 토 6월 22 09:05:33 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn hr/hr
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> select salary
  2  from employees
  3  where employee_id=141
  4  /

    SALARY
----------
      3500

SQL>
SQL>
SQL> select
  2
SQL>
SQL> ed
Wrote file afiedt.buf

  1  select salary
  2  from employees
  3* where employee_id=141
SQL> ;
  1  select salary
  2  from employees
  3* where employee_id=141
SQL> ed
Wrote file afiedt.buf

  1  select salary
  2  from employees
  3* where employee_id=141
SQL>
SQL> /

    SALARY
----------
      3500

SQL>
SQL>
SQL>
SQL>
SQL> update employees set salary =20000
  2  update employees set salary =20000
  3
SQL> ed
Wrote file afiedt.buf

  1  update employees
  2  set salary =20000
  3* wher employee_id=141
SQL> ;
  1  update employees
  2  set salary =20000
  3* wher employee_id=141
SQL> ;
  1  update employees
  2  set salary =20000
  3* wher employee_id=141
SQL> /
wher employee_id=141
*
ERROR at line 3:
ORA-00933: SQL command not properly ended


SQL> ed
Wrote file afiedt.buf

  1  update employees
  2  set salary =20000
  3* where employee_id=141
SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> select salary
  2  from employees
  3  where employee_id=141
  4  /

    SALARY
----------
     20000

SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> update employees
  2  set salary =20000
  3  where employee_id=141
  4  /

1 row updated.

SQL>
SQL>
SQL> r
  1  update employees
  2  set salary =20000
  3* where employee_id=141

1 row updated.

SQL>
SQL>
SQL> rollback
  2  ;

Rollback complete.

SQL> ed
Wrote file afiedt.buf

  1  update employees
  2  set salary =20000
  3* where employee_id=142
  4  ;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> 55



__________________________________________________________________________

SQL*Plus: Release 11.2.0.2.0 Production on 토 6월 22 09:06:15 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn hr/hr
Connected.
SQL> select salary
  2  from employees
  3  where employee_id=141;

    SALARY
----------
      3500

SQL> ed
Wrote file afiedt.buf

  1  select salary
  2  from employees
  3* where employee_id=141
SQL> /

    SALARY
----------
      3500

SQL> /

    SALARY
----------
      3500

SQL> ed
Wrote file afiedt.buf

  1  update employees
  2  set salary =30000
  3* where employee_id=141
  4  /

1 row updated.

SQL> rordr
SP2-0042: unknown command "rordr" - rest of line ignored.
SQL>
SQL>
SQL> rollback
  2
SQL> ;
  1* rollback
SQL> rollback
  2  ;

Rollback complete.

SQL>
SQL> update employees
  2  set salary =20000
  3  where employee_id=141
  4  /

1 row updated.

SQL> rollback
  2  ;

Rollback complete.

SQL>
SQL>
SQL>
SQL> desc department
ERROR:
ORA-04043: object department does not exist


SQL> desc departments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

SQL> create table dept
  2  (deprno nember(2),
  3  dmant varchar2(14),
  4  loc varchar2(13)
  5  );
(deprno nember(2),
              *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> ed
Wrote file afiedt.buf

  1  create table dept
  2  (deptno nember(2),
  3  dmant varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> ;
  1  create table dept
  2  (deptno nember(2),
  3  dmant varchar2(14),
  4  loc varchar2(13)
  5* )
SQL>
SQL>
SQL> /
(deptno nember(2),
              *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> ed
Wrote file afiedt.buf

  1  create table dept
  2  (deptno nember(2),
  3  dmane varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> ;
  1  create table dept
  2  (deptno nember(2),
  3  dmane varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> /
(deptno nember(2),
              *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> ed
Wrote file afiedt.buf

  1  create table dept
  2  (deptno nember(2),
  3  dmane varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> ;
  1  create table dept
  2  (deptno nember(2),
  3  dmane varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> /
(deptno nember(2),
              *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL>
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

  1  create table dept
  2  (deptno number(2),
  3  dmane varchar2(14),
  4  loc varchar2(13)
  5* )
SQL> /

Table created.

SQL> create taber dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7  where department_id = 80 ;
create taber dept80
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL> ed
Wrote file afiedt.buf

  1  create table dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7* where department_id = 80
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  create table dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7* where department_id = 80
SQL>
SQL>
SQL> /
create table dept80
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> ed
Wrote file afiedt.buf

  1  create table dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7* where department_id = 80
SQL> ;
  1  create table dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7* where department_id = 80
SQL> ;
  1  create table dept80
  2  as
  3  select employee_id ,last_name,
  4  salary*12 annsal,
  5  hire_date
  6  from employees
  7* where department_id = 80
SQL> /
create table dept80
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> ed
Wrote file afiedt.buf

  1  create table dept80
  2  as
  3  select employee_id ,last_name, salary*12 annsal, hire_date
  4  from employees
  5* where department_id = 80
SQL> /
create table dept80
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> ed
Wrote file afiedt.buf

  1  create table dept80
  2  as
  3  select employee_id ,last_name, salary*12 annsal, hire_date
  4  from employees
  5* where department_id = 80
SQL> /
create table dept80
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> desc dept80
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 ANNSAL                                             NUMBER
 HIRE_DATE                                 NOT NULL DATE

SQL> create table dtest
  2  as
  3  select
  4
SQL> ed
Wrote file afiedt.buf

  1  create table dtest
  2  as
  3  select department_id as deptno , sum(salary) as sumsalary
  4  from employees
  5* group by
SQL>
SQL> /
group by
        *
ERROR at line 5:
ORA-00936: missing expression


SQL> ed
Wrote file afiedt.buf

  1  create table dtest
  2  as
  3  select department_id as deptno , sum(salary) as sumsalary
  4  from employees
  5* group by
SQL> alter table dept80
  2  add (job_id varchar2(9))
  3  ;

Table altered.

SQL>
SQL> desc dept80
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 ANNSAL                                             NUMBER
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                             VARCHAR2(9)

SQL>
SQL> select dept80
  2  from dept80
  3  ;
select dept80
       *
ERROR at line 1:
ORA-00904: "DEPT80": invalid identifier


SQL> select *
  2  from dept80
  3  ;

EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        145 Russell                                                168000
04/10/01

        146 Partners                                               162000
05/01/05

        147 Errazuriz                                              144000
05/03/10


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        148 Cambrault                                              132000
07/10/15

        149 Zlotkey                                                126000
08/01/29

        150 Tucker                                                 120000
05/01/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        151 Bernstein                                              114000
05/03/24

        152 Hall                                                   108000
05/08/20

        153 Olsen                                                   96000
06/03/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        154 Cambrault                                               90000
06/12/09

        155 Tuvault                                                 84000
07/11/23

        156 King                                                   120000
04/01/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        157 Sully                                                  114000
04/03/04

        158 McEwen                                                 108000
04/08/01

        159 Smith                                                   96000
05/03/10


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        160 Doran                                                   90000
05/12/15

        161 Sewall                                                  84000
06/11/03

        162 Vishney                                                126000
05/11/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        163 Greene                                                 114000
07/03/19

        164 Marvins                                                 86400
08/01/24

        165 Lee                                                     81600
08/02/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        166 Ande                                                    76800
08/03/24

        167 Banda                                                   74400
08/04/21

        168 Ozer                                                   138000
05/03/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        169 Bloom                                                  120000
06/03/23

        170 Fox                                                    115200
06/01/24

        171 Smith                                                   88800
07/02/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        172 Bates                                                   87600
07/03/24

        173 Kumar                                                   73200
08/04/21

        174 Abel                                                   132000
04/05/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        175 Hutton                                                 105600
05/03/19

        176 Taylor                                                 103200
06/03/24

        177 Livingston                                             100800
06/04/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        179 Johnson                                                 74400
08/01/04


34 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select *
  2* from dept80
  3  /

EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        145 Russell                                                168000
04/10/01

        146 Partners                                               162000
05/01/05

        147 Errazuriz                                              144000
05/03/10


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        148 Cambrault                                              132000
07/10/15

        149 Zlotkey                                                126000
08/01/29

        150 Tucker                                                 120000
05/01/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        151 Bernstein                                              114000
05/03/24

        152 Hall                                                   108000
05/08/20

        153 Olsen                                                   96000
06/03/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        154 Cambrault                                               90000
06/12/09

        155 Tuvault                                                 84000
07/11/23

        156 King                                                   120000
04/01/30


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        157 Sully                                                  114000
04/03/04

        158 McEwen                                                 108000
04/08/01

        159 Smith                                                   96000
05/03/10


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        160 Doran                                                   90000
05/12/15

        161 Sewall                                                  84000
06/11/03

        162 Vishney                                                126000
05/11/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        163 Greene                                                 114000
07/03/19

        164 Marvins                                                 86400
08/01/24

        165 Lee                                                     81600
08/02/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        166 Ande                                                    76800
08/03/24

        167 Banda                                                   74400
08/04/21

        168 Ozer                                                   138000
05/03/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        169 Bloom                                                  120000
06/03/23

        170 Fox                                                    115200
06/01/24

        171 Smith                                                   88800
07/02/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        172 Bates                                                   87600
07/03/24

        173 Kumar                                                   73200
08/04/21

        174 Abel                                                   132000
04/05/11


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        175 Hutton                                                 105600
05/03/19

        176 Taylor                                                 103200
06/03/24

        177 Livingston                                             100800
06/04/23


EMPLOYEE_ID LAST_NAME                                              ANNSAL
----------- -------------------------------------------------- ----------
HIRE_DAT JOB_ID
-------- ------------------
        179 Johnson                                                 74400
08/01/04


34 rows selected.

SQL> column last_name format a15
SQL> /

EMPLOYEE_ID LAST_NAME           ANNSAL HIRE_DAT JOB_ID
----------- --------------- ---------- -------- ------------------
        145 Russell             168000 04/10/01
        146 Partners            162000 05/01/05
        147 Errazuriz           144000 05/03/10
        148 Cambrault           132000 07/10/15
        149 Zlotkey             126000 08/01/29
        150 Tucker              120000 05/01/30
        151 Bernstein           114000 05/03/24
        152 Hall                108000 05/08/20
        153 Olsen                96000 06/03/30
        154 Cambrault            90000 06/12/09
        155 Tuvault              84000 07/11/23

EMPLOYEE_ID LAST_NAME           ANNSAL HIRE_DAT JOB_ID
----------- --------------- ---------- -------- ------------------
        156 King                120000 04/01/30
        157 Sully               114000 04/03/04
        158 McEwen              108000 04/08/01
        159 Smith                96000 05/03/10
        160 Doran                90000 05/12/15
        161 Sewall               84000 06/11/03
        162 Vishney             126000 05/11/11
        163 Greene              114000 07/03/19
        164 Marvins              86400 08/01/24
        165 Lee                  81600 08/02/23
        166 Ande                 76800 08/03/24

EMPLOYEE_ID LAST_NAME           ANNSAL HIRE_DAT JOB_ID
----------- --------------- ---------- -------- ------------------
        167 Banda                74400 08/04/21
        168 Ozer                138000 05/03/11
        169 Bloom               120000 06/03/23
        170 Fox                 115200 06/01/24
        171 Smith                88800 07/02/23
        172 Bates                87600 07/03/24
        173 Kumar                73200 08/04/21
        174 Abel                132000 04/05/11
        175 Hutton              105600 05/03/19
        176 Taylor              103200 06/03/24
        177 Livingston          100800 06/04/23

EMPLOYEE_ID LAST_NAME           ANNSAL HIRE_DAT JOB_ID
----------- --------------- ---------- -------- ------------------
        179 Johnson              74400 08/01/04

34 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select *
  2* from dept80
SQL>
SQL>
SQL>
SQL>
SQL> save table.sql
Created file table.sql
SQL>
SQL>
SQL>
SQL> create table dept (
  2  DEPTNO
  3  NUMBER(2)
  4
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

  1  create table dept (
  2  DEPTNO NUMBER(2), constraint deptno_a1_PK, primary key(deptno)
  3  DNAME VARCHAR2(14) ,
  4  LOC VARCHAR2(13)
  5* )
  6  .
SQL> ed
Wrote file afiedt.buf

  1  create table dept (
  2  DEPTNO NUMBER(2), constraint deptno_a1_PK, primary key(deptno)
  3  DNAME VARCHAR2(14) ,
  4  LOC VARCHAR2(13)
  5* )
SQL> desc table
ERROR:
ORA-00931: missing identifier


SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DMANE                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> desc emp
ERROR:
ORA-04043: object emp does not exist


SQL> desc emp
ERROR:
ORA-04043: object emp does not exist


SQL> @table

Table dropped.

drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 7:
ORA-00904: : invalid identifier


mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


(
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL> @table dept
drop table dept cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 7:
ORA-00904: : invalid identifier


mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


(
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @table dept
drop table dept cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 7:
ORA-00904: : invalid identifier


mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


(
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL> @table
drop table dept cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 7:
ORA-00904: : invalid identifier


mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


(
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @table
drop table dept cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 7:
ORA-00904: : invalid identifier


mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


(
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL> @table
drop table dept cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


        add constraint emp_deptno_FK foreigny key(deptno)
                                                  *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @table

Table dropped.

drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

mar numver(4),
          *
ERROR at line 6:
ORA-00907: missing right parenthesis


        add constraint emp_deptno_FK foreigny key(deptno)
                                                  *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL>
SQL>
SQL>
SQL>
SQL> @table

Table dropped.

drop table emp cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


Table created.

        add constraint emp_deptno_FK foreigny key(deptno)
                                                  *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> @table

Table dropped.


Table dropped.


Table created.


Table created.


Table altered.

SQL>