Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id having min(salary)>(select mn(salary) from employees where department_id = 50)
* min salary in department_id=50
* list department_id, min(salary) group by department_id and min(salary)> (min salary in departmentid=50).
2. select department_id, min(salary) from employees group by department_id having min(salary)>(select min(salary) from employees group by department_id);
* this sentense is wrong. has the subquery operates multi results.
Multi-row subqueires: any, all, in
IN:     equals to any member in the list
any:   1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to each value in a list or returned by a query. 3) evaluates to ......

constraint Example:
1. grammer:
 create table [schema.]table
      (column datatype [DEFAULT expr]
     [column_constraint], ...
     [table_constraint] [,......]);
2. example of a column_level constraint:
create table employees(employee_id number(6) constraint emp_emp_id_pk primiary key, first_name varchar2(20), ...);
3. example of a table-level constraint:
create table employees(employee_id number(6), first_name varchar2(20), ... job_id varchar2(20) not null, constraint emp_emp_id_pk primary key(employee_id));
4. UNIQUE example
create table employees(employee_id number(6), last_name varchar2(20) not null,..., constraint emp_email_uk UNIQUE(email));
5. FOREIGN KEY constraint
create table employees(employee_id number(6), last_name varchar2(20),... constraint emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), constraint emp_email_uk UNIQUE(email));
** foreign key constaint:
* ......

GRANT
Name
GRANT -- 定义访问权限
Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON FUNCTION funcname ([type, ...]) [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE langname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schemanam ......

Confirming granted privileges
Data Dictionary View                Description
ROLE_SYS_PRIVS                System privileges granted to roles
ROLE_TAB_PRIVS                Table privileges granted to roles
USER_ROLE_PRIVS             Roles accessible by the user
USER_SYS_PRIVS               System privileges granted to the user
USER_TAB_PRIVS_MADE     Object privileges granted on the user's objects
USER_TAB_PRIVS_RECD     Object privileges granted to the user
USER_ROLE_PRIVS_MADE  Object privileges granted on the columns of the user's objects
USER_ROLE_PRIVS_RECD:  Object privileges granted to the user on specific columns
Revoking Object Privileges
g ......

Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE,  INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation.
set constraints dept2_id_pk immediate // changing a specific constraint attribute.
alter session set constraints=immediate // changing all constraints for a session.
Difference between initially deferrable and initially immediate
initially deferred: waits to check the constraints until the transaction ends.
initially immediate: checks the constraint at the end of the statement execution.
Dropping a constraint:
1. Removing the manager constraint from the EMP2 table:
alter table emp2 drop constraint emp_mgr_fk;
2. Removing the primary key constraint on the dept2 table and drop the associate foreign key constraint on the emp2.department_id column:
alter table dept2 drop primary key c ......

Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in data warehousing applications.
syntax:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
e.g.: table: temp_t1:
results:
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     ......