2022/3/17日课后作业
12、返回工资高于30部门所有员工工资水平的员工信息。#第一步算出30部门的最高工资
(select max(p.sal)'maxgrade'
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where p.deptno=30)2850
#第二步算出大于'maxgrade' 的人员
select *
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where p.deptno<>30 and p.sal>(select max(p.sal)'maxgrade'
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where p.deptno=30)
#18、工资处于第四级别的员工的姓名。
条件：工资水平为4
结果：员工姓名
select p.ename
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where grade = 4
#19、工资为二等级的职员名字、部门所在地、最低工资、最高工资
#
select p.ename,d.loc,p.sal,s.grade
from dept d
left join emp p
on d.deptno=p.deptno
left join salgrade s
on p.sal between s.losal and s.hisal
where s.grade=2 and p.job='职员' and ((select min(p.sal)
fromemp p
left join salgrade s
on p.sal between s.losal and s.hisal) or (select max(p.sal)
fromemp p
left join salgrade s
on p.sal between s.losal and s.hisal))
#20.工资等级多于smith的员工信息。
#关键词：工资等级大于smith的等级员工信息为*
#1、求出smith员工的工资等级
(select s.grade
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where p.ename = 'smith' group by s.grade) #1
#2,工资等级大于1的所有员工信息
select *
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where s.grade > (select s.grade
from emp p
left join salgrade s
on p.sal between s.losal and s.hisal
where p.ename = 'smith' group by s.grade)
页:
[1]