Describe 首頁/2020-11-22 here.

* OCP Day2

Operator

SQL> select distinct job from emp where deptno=20;

JOB
------------------
CLERK
ANALYST
MANAGER

  1  select distinct job from emp where deptno=20
  2  UNION
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
ANALYST
CLERK
MANAGER
SALESMAN

  1  select distinct job from emp where deptno=20
  2  UNION all
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
CLERK
ANALYST
MANAGER
SALESMAN
CLERK
MANAGER

6 rows selected.

  1  select distinct job from emp where deptno=20
  2  minus
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
ANALYST

Managing Tables Using DMS Statements

DML: insert update delete

SQL> select * from dept;

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


SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> insert into dept values(50,'NETWORK','BEJING');

1 row created.

SQL> insert into dept values(70,null,null);

1 row created.

SQL> insert into dept values(null,null,null);
insert into dept values(null,null,null)
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")


SQL> select * from dept;

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

6 rows selected.

SQL> create table dept1 
  2  as
  3  select * from dept
  4  where 1=2;

Table created.


SQL> insert into dept1
  2  select * from dept;

6 rows created.

SQL> select * from dept1;

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

6 rows selected.


SQL> truncate table dept1;

Table truncated.

SQL> select * from dept1;

no rows selected

update

SQL> update emp set sal=sal+200 where ename='SMITH';

1 row updated.


SQL> update emp set sal=sal+300 where sal>2000;

6 rows updated.

  1  select ename,sal from emp
  2* fetch next 3 rows only
SQL> /

ENAME                       SAL
-------------------- ----------
SMITH                      1100
ALLEN                      1700
WARD                       1350


SQL> select sal from emp where ename='KING';

       SAL
----------
      5000

  1* select * from emp where ename='SMITH'
SQL> /

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

delete

DML delete by condition or line. is able to rollback before commit

SQL> delete from emp;


14 rows deleted.

SQL> SQL> select * from emp;

no rows selected

SQL> rollback;
Rollback complete.

truncate

• delete all rows

cannot easy to undone

LOCK

• Terminal 1

SQL> update dept set loc='BEIJIN' where deptno=70;

1 row updated.


  1* select * from dept
SQL> /

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70                              BEIJIN

6 rows selected.

• Terminal 2

  1* select * from dept
SQL> /

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

6 rows selected.

SQL> update dept set loc='SHANGHAI' where deptno=70;

will waiting...

• Terminal 3

SQL> update dept set loc='GUANGZHOU' where deptno=60; 

1 row updated.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        60                              GUANGZHOU

7 rows selected.

DEPTNO 70(Related) not commit

savepoint

SQL> rollback;
Rollback complete.

SQL> select * from dept;

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

6 rows selected.

SQL> savepoint a;

Savepoint created.

SQL> insert into dept(deptno) values(73);

1 row created.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        73

7 rows selected.

SQL> insert into dept(deptno) values(71);

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> insert into dept(deptno) values(72);

1 row created.

SQL> savepoint c;

Savepoint created.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        73
        71
        72

9 rows selected.

SQL> rollback to a;

Rollback complete.

SQL> select * from dept;

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

6 rows selected.

table

create table department (
dept_id number(2),
name char(20)
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)

create table department (
dept_id number(2),
name char(20),
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)
SQL> /

Table created.

SQL> select * from department;

no rows selected

SQL> desc department;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPT_ID                                            NUMBER(2)
 NAME                                               CHAR(20)
 MASTER                                    NOT NULL CHAR(10)
 LOCATION                                  NOT NULL VARCHAR2(100)

create table student (
id number(4)
name char(10) constraint stu_nu

read only table

ALTER TABLE emp READ ONLY;

-- perform table maintenance and then
-- return table back to read/write mode

ALTER TABLE emp READ WRITE;

rename table

rename student to stu

• dba_tables

  1  select table_name,OWNER from dba_tables
  2* fetch next 5 rows only
SQL> /

TABLE_NAME                     OWNER
------------------------------ ------------------------------
ICOL$                          SYS
COL$                           SYS
IND$                           SYS
TAB$                           SYS
CLU$                           SYS