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.