Oracle复杂查询综合

-- 1、列出所有员工的年工资，按年薪从低到高排序。
select empno, ename, sal, nvl2(comm, (sal+comm)*12, sal*12) income from emp order by income;

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

-- 3、列出所有员工的姓名及其直接上级的姓名。
select e1.ename, e2.ename
from emp e1
join emp e2
on e1.mgr=e2.empno(+);

-- 4、列出受雇日期早于其直接上级的所有员工。
select e1.ename, e1.hiredate, e2.ename, e2.hiredate
from emp e1, emp e2
where e1.mgr=e2.empno
and e1.hiredate<e2.hiredate;

-- 5、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select d.deptno, d.dname, e.*
from emp e
right join dept d
on e.deptno=d.deptno;

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

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

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

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

-- 10、列出与“SCOTT”从事相同工作的所有员工。
select *
from emp
where job=(
      select job
      from emp
      where ename='SCOTT'
)
and ename<>'SCOTT';

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

-- 12、列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno, count(*), round(avg(sal)), round(avg(months_between(sysdate, hiredate)/12)) from emp group by deptno;

-- 13、列出所有员工的姓名、部门名称和工资。
select ename, dname, sal from emp natural join dept;

-- 14、列出从事同一种工作但属于不同部门的员工的一种组合。
select e1.ename, e1.job, e1.deptno, e2.ename, e2.job, e2.deptno
from emp e1, emp e2
where e1.job=e2.job
and e1.deptno<>e2.deptno
and e1.ename<e2.ename;

-- 15、列出所有部门的详细信息和部门人数。
select d.deptno, d.dname, d.loc, count(e.empno)
from emp e
right join dept d
on e.deptno=d.deptno
group by d.deptno, d.dname, d.loc
order by deptno;


-- 16、列出各种工作的最低工资。
select job, min(sal) from emp group by job;

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


-- 18、列出至少有一个员工的所有部门。
select d.*, count(e.empno)
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno, d.dname, d.loc
having count(e.empno)>0;

---