group function
sum() avg() max() min()
the group function will not handle null value except count(*).
count(*)　return all rows including null;
count(expr) return the not null rows.
SQL> select count(*) ,count(comm) from emp;

COUNT(*) COUNT(COMM)
---------- -----------
14 4
count(distinct expr)
select column,group_function(expr_column) from table group by column order by expr;
--group by column having group_condition order by
不使用别名或数字作为order by 的排序条件，因为有可能会导致优化器会使用磁盘排序
SQL82
非等值连接
自连接
左右外连接(+)
等值连接
SQL99
cross join
natural join,using(only one column),on
left/rigth/full outer join
self join
inner join:
select ename,dname from emp e,dept d where e.deptno=d.deptno;
select ename,dname from emp e join dept d using(deptno);
select ename,dname from emp natural join dept;
select ename,dname from emp e join dept d on (e.deptno=d.deptno);
subquery
子查询 不能为空，否则结果为空
子查询中先执行子查询再执行父查询
SQL> c/deptno/emp.deptno
1* select ename,emp.deptno,asal from emp,(select deptno,avg(sal) asal from emp group by deptno) d where emp.deptno=d.deptno and sal>asal
SQL> /

ENAME DEPTNO ASAL
---------- ---------- ----------
ALLEN 30 1566.66667
JONES 20 2175
BLAKE 30 1566.66667
SCOTT 20 2175
KING 10 2916.66667
FORD 20 2175

TOP-N
SQL> select rownum,ename,sal from (select ename,distinct sal from emp order by sal desc) a where rownum<4;

ROWNUM ENAME SAL
---------- ---------- ----------
1 KING 5000
2 SCOTT 3000
3 FORD 3000

每个部门的薪水的前三名？
SELECT employee_id,last_name,salary from hr_emp h where salary>(select avg(salary) from hr_emp) order by salary asc
SELECT department_id,employee_id,last_name from hr_emp h where exists ( select 1 from hr_emp a where a.last_name like '%u%' and a.department_id=h.department_id group by department_id) order by department_id;
select employee_id,last_name,job_title from emps,jobs,loc where emps.job_id=jobs.job_id and loc.location_id=1700;
select employee_id,last_name,salary from emps e where e.manager_id in(select employee_id from emps a where a.last_name='King')
select employee_id,last_name,job_title from emps a join jobs b on(a.job_id=b.job_id) join depts c on (a.department_id=c.department_id) and c.department_name='Executive';
集合set
union all
union
intersect
minus
除了union all,所有操作都会以第一列自动升序排列,结果以第一个查询的列名显示.
select department_id,employee_id,last_name,salary from emps a where a.salary>(select avg(salary) from emps) and exists (select 1 from emps b where b.last_name like '%u%' and a.department_id=b.department_id) order by department_id;
lab5_5.sql
select last_name,e.department_id,department_name from emps e join depts d on e.department_id=d.department_id join loc l on l.location_id=d.location_id and l.city='Toronto'
lab5_6.sql
select a.last_name "Employee",a.employee_id "emp#",b.employee_id "Mgr#",b.last_name "Manager" from emps a join emps b on (b.employee_id=a.manager_id);
lab5_7.sql
select a.last_name "Employee",a.employee_id "emp#",b.employee_id "Mgr#",b.last_name "Manager" from emps a left join emps b on (b.employee_id=a.manager_id);
lob5_11.sql

CTAS只会拷贝表结构，其他约束不会拷贝也不会拷贝注释.
sys schema:
create tigger trg_test
before create on database
begin
null;
end;
scott schema:
create procedure proc as begin execute immediate 'alter trigger sys.trg_test disable'; end;

grant create any trigger to scott;
grant alter any trigger to scott;
grant drop any trigger to scott;
grant ADMINISTER DATABASE TRIGGER to scott;

高并发环境先标记，然后在空闲时再drop.
alter table table_name set unused column column_name;
ater table table_name drop unused columns;
dba_constraints
dba_tab_comments
dba_col_comments
[@more@]