oracle查询的实例

16、列出全部部门的具体信息和部门人数。
 select dept.*,(select count(*) from emp where emp.deptno=dept.deptno) as pop from dept;
13、列出在每个部门做事的员工数量 、均匀收入、平均做事限期。
select deptno,count(deptno) as 员工数量,avg(sal),trunc(avg((sysdate-hiredate)/365)) as year from emp group by deptno;
9、列出薪金高于公司平均薪金的全部员工。
select *from emp where sal>(select avg(sal) from emp);
18、列出各个部门的MANAGER（司理）的最低薪金。
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
27、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. 
select ename,job,sal from emp order by job desc, sal ;


26、显示所有员工姓名的前三个字符. 
select substr(ename,0,3) from emp;
30.检索出职工人数最多的公司的编号和名称
select dept.deptno,dept.dname from dept where dept.deptno=(select dept.deptno from (Select emp.deptno, count(*) cont from emp group by emp.deptno)  dept where cont=(Select max(cont) from (select emp.deptno, count(*) cont from emp group by emp.deptno) emp));


查看某一个用户，某一个表所拥有的所有的约束条件
SELECT constraint_name, table_name, r_owner, r_constraint_name 
FROM all_constraints WHERE table_name = 'EMPLOYEE' and owner = 'SCOTT'; 


select *from user_constraints where table_name='EMPLOYEE';
注意‘’里面必须注意大小写，小写不行时，换成大写试试。


从ti表跟新数据到t2表，如果t2表name字段记录在t1表中存在，就将money字段得知累加，如果不存在，将t1表的记录插入到t2表中
可以使用merge  语句
可以实现t2表中有多条记录，而t1表中有一条记录，也就是被更改的记录可以有多条，而提供的只能有一条，反之是错误的。
同时也可以实现在表t1和t2相同的情况下删掉不需要的name的值。
merge into t2
using t1 on t1.name=t2.name
when matched  then
update  set t2.sal=t2.sal+t1.sal             where t1.name=''
when not matched then
insert 
values(t1.name,t1.sal);




merge into t2
   using t1
   on (t2.name=t1.name)
   when matched then
   update  set t2.sal=t2.sal+t1.sal
   delete where t2.name='li'


With语句使用举例
1、查询出部门的总薪水大于所有部门平均总薪水的部门。
with dept_cost as(
select  emp.deptno ,sum(sal)  as zong from emp,dept where emp.deptno=dept.deptno group by emp.deptno),
dept_avg as(
select sum(zong)/count(*) as sal_avg from dept_cost)
select *from dept_cost where zong>(select sal_avg from dept_avg)
with语句用来给查询语句中的子查询命名，随后就可以在查询语句的其他地方引用这个名称。
 With使用注意事项
1. 注意语法格式
1) 在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。
2) 最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来。
3)如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误。
4)一个with子句内部不能嵌套with子句。
2.With子查询中的列应该加别名以便引用。




在emp表中查询职业是Manager，和CLerk的人数，并且其他职业的人数。
select decode(job,'MANAGER','sum_ma','CLERK','sum_cl','sum_ot') mingchen,count(*) sum_ming from emp group by decode(job,'MANAGER','sum_ma','CLERK','sum_cl','sum_ot');




select (case job when 'MANAGER' then 'sum_ma'
  2  when 'CLERK' then 'sum_cl'
  3  else 'sum_ot'
  4  end ) mingchen,
  5  count(*) from emp group by (case job
  6  when 'MANAGER' then 'sum_ma'
  7  when 'CLERK' then 'sum_cl'
  8  else 'sum_ot'
  9  end);




select (case 
when job in ('MANAGER','CLERK') then 'sum_ma'
else 'sum_ot'
end) mingchen, count(*) from emp group by (case  when job in('MANAGER','CLERK') then 'sum_ma'
else 'sum_ot'
end )
对emp表按照部门标号（不一定是部门标号，还有可能是非数字字符）从小到大排序，如果相同的话按工资从低到高排序。
select *From emp order by decode(deptno,10,1,20,2,30,3) ,sal;