Oracle 查询习题练习

查询所有员工的年工资、所在部门的名称、按年薪从低往高排序

select sal*12+NVL(comm,0) 年薪,dname 部门名称 from emp,dept
where emp.deptno=dept.deptno
order by 年薪 asc;

查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年工资降序

select e.empno 员工编号,e.ename 员工姓名,m.empno 上级领导编号,m.ename 上级领导姓名 
from emp e left outer join emp m on e.mgr=m.empno
order by m.sal*12+NVL(m.comm,0) desc;

select e.empno 员工编号,e.ename 员工姓名,m.empno 上级领导编号,m.ename 上级领导姓名 
from emp e,emp m where e.mgr=m.empno(+)
order by m.sal*12+NVL(m.comm,0) desc;

查询非销售人员的工作名称，以及从事同一工作员工的月工资之和，要求月工资之和大于5000，输出结果按月工资之和降序排列。

select * from(
select job 工作名称,sum(sal) 同一工作的月薪总和 from emp
where job!='SALESMAN'
group by job
order by 同一工作的月薪总和 desc)
where 同一工作的月薪总和>5000;

select job 工作名称,sum(sal) 同一工作的月薪总和 from emp
where job!='SALESMAN'
having sum(sal)>5000
group by job
order by 同一工作的月薪总和 desc;

查询所有领取奖金和不领取奖金的员工人数、平均工资。

• UNION(并集)：返回各个查询的所有记录，不包括重复记录
• UNION ALL（并集）:返回各个查询的所有记录，包括重复记录。
• INTERSECT（交集）：返回两个查询共有的记录。
• MINUS（补集）：返回包含在第一个查询中，但不包含在第二个查询中的记录。

select count(*) 人数,avg(sal) 平均工资 from emp
where comm is not null and comm>0
union
select count(*) 人数,avg(sal) 平均工资 from emp
where comm is null or comm=0;

查询每种工作的最低工资，以及该员工的姓名。

select e.ename,t.job,t.sal
from emp e,(select min(sal) sal,job from emp group by job) t
where t.sal=e.sal and t.job=e.job;

select ename 员工姓名,job 工作,sal 工资 from emp where sal in(
select min(sal) 工资 from emp
group by job
);

查询出工资不超过2500的人数最多的部门名称。

select d.deptno,d.dname from dept d,emp e
where d.deptno=e.deptno and e.sal<=2500
group by d.deptno,d.dname
having count(*)=(select max(count(*)) from emp where sal<=2500 group by deptno
);

查询出管理员工人数最多的人的名字和他管理的人的名字。

select b.ename 领导名,e.ename 员工名
from emp e inner join emp b on e.mgr=b.empno
where e.mgr=(
select mgr from emp group by mgr
having count(mgr)=(select max(n) from(
select count(mgr) n from emp
group by mgr)))
;

查询总工资、各个部门的总工资、各个部门中各个工作的总工资

select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,NULL,sum(sal) from emp group by deptno
union
select NULL,NULL,sum(sal) from emp;

使用增强的group by

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

统计各个年份的入职人数、以及总入职人数。

select count(*) 总人数, sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),1987,1,0)) "1987" 
from emp;

select count(*) 入职人数, to_char(hiredate,'yyyy') 年份 from emp
group by to_char(hiredate,'yyyy')
union all
select distinct count(*) ,'总入职人数' from emp;

select count(*) 入职人数, to_char(hiredate,'yyyy') 年份 from emp
group by rollup(to_char(hiredate,'yyyy'));

select count(*) 入职人数, to_char(hiredate,'yyyy') 年份 from emp
group by grouping sets((), (to_char(hiredate,'yyyy')))