sql 练习题及答案

时间:2020-11-30 00:10:54

--1分组统计各部门下工资>2000 的员工的平均工资
select avg(s.sal) from
(select * from emp e where e.sal>2000) s
group by deptno;

--2 统计各部门下平均工资大于 1600 的部门
select e.deptno,avg(e.sal) from emp e group by e.deptno having avg(e.sal)>1600;

--3 求出部门 30 中得到最多奖金的员工奖金
select max(e.comm) from emp e where e.deptno=30;

--4 求出部门 30 中得到最多奖金的员工姓名
select e.ename from emp e where e.comm=(select max(comm) from emp e where e.deptno=30)and deptno=30;


--5 算出每个职位的员工数和最低工资
select job,count(job),min(e.sal) from emp e group by e.job;

--6 算出每个部门的平均工资和平均奖金(平均值包括没有奖金),
--如果平均奖金大于 300,显示“奖金不错”,如果平均奖金 100 到 300,显示“奖金一般”,
--如果平均奖金小于 100,显示“基本没有奖金”,按部门编号降序,平均工资降序排列
select decode(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门') dept,round(avg(sal),0),round(avg(nvl(comm,0)),0),case
when avg(nvl(comm,0))>300 then '奖金不错'
when avg(nvl(comm,0))>100 and avg(nvl(comm,0))<300 then '奖金一般'
when avg(nvl(comm,0))<100 then '几乎没奖金'
end status
from emp
group by deptno
order by deptno desc,avg(sal) desc;

 

select avg(nvl(e.comm,0)),
case
when avg(nvl(e.comm,0))>300
then '奖金还不错'
when avg(nvl(e.comm,0))>100and avg(nvl(e.comm,0))<300
then '有点点奖金'
when avg(nvl(e.comm,0))<100
then '几乎没奖金'
end 奖金状况
from emp e group by e.deptno;

--7 列出员工表中每个部门的员工数,和部门编号
select count(e.deptno),e.deptno from emp e group by e.deptno

--8 分组统计每个部门下,每种职位的平均奖金(也要算没奖金的人)和总工资(包括奖金)
select avg(nvl(e.comm,0)),sum(sal+nvl(e.comm,0)) 总工资 from emp e group by e.deptno;

--9 得到工资大于自己部门平均工资的员工信息(提示:e1.deptno=e2.deptno)
select * from emp e1,(select avg(e.sal) ee,e.deptno from emp e group by e.deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.ee;

--10 为所有人涨工资,标准是: 10 部门涨10%; 20 部门涨15%; 30 部门涨20%其他部门涨18%(提示:set sal = decode(...)

update emp set

sal =
case
when deptno = 10 then sal*1.1
when deptno = 20 then sal*1.15
when deptno = 30 then sal*1.2
else sal*1.18 end;