oracle scott 用户下emp,dept,bonus等表的关联查询

--查询部门下有多少个员工(如果该部门下没有员工，则记 0)
--(case when b.num is null then 0 else num end) as num
select t.deptno,t.dname,(case when b.num is null then 0 else num end) as num from dept t left join (select a.deptno,a.num from(select t.deptno,count(t.deptno) as num from emp t group by t.deptno)a)b on t.deptno=b.deptno;
select t.deptno,t.dname,nvl(b.num,0) as num from dept t left join (select a.deptno,a.num from(select t.deptno,count(t.deptno) as num from emp t group by t.deptno)a)b on t.deptno=b.deptno;

--查询除去 SALESMAN职业,平均工资超过$1500的部门
select * from(select deptno,avg(sal),job from emp t group by t.deptno,job having avg(sal) > 1500)a where a.job <> 'SALESMAN';

--查询雇员姓名为king的雇员号，雇员姓名，雇员所在的部门号，及部门地址
select a.empno,a.ename,a.deptno,d.loc from dept d join(select t.empno,t.ename,t.deptno from emp t where t.ename='KING')a on a.deptno=d.deptno;

--查询在纽约工作的员工姓名、部门号和工资信息
select t.ename,t.deptno,(nvl(t.sal,0) + nvl(t.comm,0))as sal from emp t right join(select t.deptno from dept t where t.loc='NEW YORK')a on a.deptno=t.deptno;

--查询在accounting或sales部门工作的雇员姓名、工种、工资情况
select a.dname,t.ename,t.job,(nvl(t.sal,0)+nvl(t.comm,0))as sal from emp t right join(select t.deptno,t.dname from dept t where t.dname in('ACCOUNTING','SALES'))a on a.deptno=t.deptno;

--列出至少有一个员工的所有部门。
select * from dept t where t.deptno in(select distinct t.deptno from emp t);
select t.deptno,count(0) from emp t group by t.deptno having count(t.deptno) > 1;

--列出薪金比“SMITH”多的所有员工
select * from emp t where t.sal > (select t1.sal from emp t1 where t1.ename='SMITH');

--列出所有员工的姓名及其直接上级的姓名
select t.ename,(select t1.ename from emp t1 where t1.empno=t.mgr)as mgrname from emp t;
select b.ename,a.ename as mgrname from emp a,emp b where a.empno(+)=b.mgr;

--列出受雇日期早于其直接上级的所有员工。
select * from emp t where t.hiredate > (select t1.hiredate from emp t1 where t1.empno=t.mgr);

--列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(左右链接都可)
select e.deptno,e.dname,a.ename from dept e left join emp a on e.deptno=a.deptno;
select dname,ename from emp a,dept b where a.deptno(+) = b.deptno;
select dname,ename from emp e right join dept d on d.deptno=e.deptno;

--列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename,e.job,d.dname from emp e join dept d on e.deptno=d.deptno and e.job='CLERK';
select b.ename,b.job,a.dname from dept a,emp b where a.deptno=b.deptno and job='CLERK';

--列出最低薪金大于1500的各种工作
select t.job,min(t.sal) from emp t group by t.job having min(t.sal) > 1500;

--列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select * from emp t where t.deptno=(select d.deptno from dept d where d.dname='SALES');

--列出薪金高于公司平均薪金的所有员工。
select * from emp t where t.sal > (select avg(e.sal) from emp e);

--列出与“SCOTT”从事相同工作的所有员工。
select * from emp t where t.job=(select e.job from emp e where e.ename='SCOTT');

--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select * from emp e where e.deptno<>30 and e.sal=any(select t.sal from emp t where t.deptno=30);
select * from emp e where e.sal in(select t.sal from emp t where t.deptno=30);

--列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp e where e.sal >all(select t.sal from emp t where t.deptno=30);

--列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,(select count(*) from emp where deptno=d.deptno) as 人数,
(select round(avg(sal+nvl(comm,0)),2) from emp where deptno=d.deptno)as 平均工资,
(select round(avg(to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy')))) from emp where deptno=d.deptno)as 平均服务期限
from dept d;
select deptno,count(*),
trunc(avg(sal+nvl(comm,0))) avgsal,
trunc(avg(sysdate-hiredate)) avgday
from emp group by deptno;

--列出所有员工的姓名、部门名称和工资。
select t.ename,t1.dname,t.sal + nvl(t.comm,0) as sal from emp t,dept t1 where t.deptno=t1.deptno(+);
select e.ename,d.dname,e.sal+nvl(e.comm,0) from emp e left join dept d on e.deptno=d.deptno;

--列出从事同一种工作但属于不同部门的员工的一种组合。
select distinct a.ename,a.job,a.deptno from emp a,emp b where (a.job=b.job) and (a.deptno!=b.deptno) order by a.job;

--列出所有部门的详细信息和部门人数。
select d.*,(select count(0) from emp t where t.deptno=d.deptno) as 部门人数 from dept d;

--列出各种工作的最低工资。
select e.job,min(e.sal+nvl(e.comm,0)) from emp e group by e.job;

--列出各个部门的MANAGER（经理）的最低薪金。
select e.deptno,min(e.sal) from emp e where e.job='MANAGER' group by e.deptno;

--列出所有员工的年工资,按年薪从低到高排序。
select e.ename,(e.sal+nvl(e.comm,0))*12 as yearsal from emp e order by yearsal;

--列出员工的姓名，员工的上级，员工的工资等级和员工上级的工资等级
select m.ename 员工姓名,n.ename 员工上级,m.grade 员工工资等级,n.grade 上级工资等级
from
(select mgr,ename,grade from emp,salgrade
where sal>=losal and sal<=hisal) m
left join
(select empno,ename,grade from emp,salgrade
where sal>=losal and sal<=hisal) n
on m.mgr=n.empno;

--显示各部门的平均工资、最高工资、最低工资和总工资列表，并按平均工资高低顺序排序。
select t.deptno,round(avg(t.sal),2),max(t.sal),min(t.sal),sum(t.sal) from emp t group by t.deptno order by avg(t.sal);

--查询职务和SALESMAN相同，比SCOTT雇佣时间早的雇员信息
select * from emp t where t.job='SALESMAN' and t.hiredate < (select e.hiredate from emp e where e.ename = 'SCOTT');

--查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资
select * from(select e.*,(e.sal+nvl(e.comm,0)) as salcomm from emp e)a where a.salcomm >(select max(t.sal+nvl(t.comm,0)) from emp t where t.job='SALESMAN');

--雇员表emp查询到有奖金的员工信息(假设comm字段就是奖金字段)，插入奖金表BONUS
insert into bonus(ename,job,sal,comm) values (select t.ename,t.job,t.sal,t.comm from emp t where t.comm is not null and t.comm > 0);

--为没有奖金的雇员增加100元工资
select t.*,t.sal+100 from emp t where t.comm is null or t.comm =0;

--查询到工资级别为3级的雇员编号、名字和工资
select * from(select t.empno,t.ename,t.sal+nvl(t.comm,0) as salcomm from emp t) a where a.salcomm < 2000 and a.salcomm > 1401;