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    值班报告     ......

 CREATE VIEW MYVIEW
AS
   SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
    UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
说明：数据库A的表的字段名必须和数据库B的表的字段名相同，包括数据类型等。 ......

PairWise subquery:
e.g.:
select * from wf_docsort where (ndocsortid,nmoduleinfoid)  in (select ndocsortid, nmoduleinfoid from wf_docsort where instr(cname,'文')>0)
the above sql is the same function as:
select * from wf_docsort where ndocsortid = (select ndocsortid from wf_docsort where instr(cname,'xx')>0) and nmoduleinfoid = (select nmoduleinfoid from wf_docsort where instr(cname,'xx')>0)
A scalar subquery expression is a subquery that: returns exactly one column value from one row. Scalar subqueries can be used in: 1) The condition and expression part of DECODE and CASE 2) All clauses of SELECT except GROUP BY 3) The SET clause and WHERE clause of an UPDATE statement
e.g.:
select * from wf_docsort
results:
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    ......