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;
|
