SQL 练习及解答

1. --求部门中那些人的薪水最高

select ename, sal from emp
join (select max(sal) max, deptno from emp group by deptno) t

2. --求部门平均薪水的等级

select deptno, avg, grade from
(select deptno, avg(sal) avg from emp group by deptno) t
join salgrade s on (t.avg between s.losal and s.hisal )

3. --求部门平均薪水等级

select deptno, avg, grade from salgrade s
join (select deptno, avg(sal) avg from emp group by deptno) t
on (t.avg between s.losal and s.hisal)

4. --雇员中有哪些人是经理人

方法1:

select distinct e1.mgr ,e2.ename from emp e1
join emp e2 on (e1.mgr = e2.empno)

方法2:

select empno, ename from emp where empno in (select mgr from emp)

5. --不准用组函数,求薪水的最高值

select sal from emp 
where sal not in 
(
select e1.sal from emp e1
join emp e2
on (e1.sal < e2.sal)
)

6. --求平均薪水最高部门的部门编号

select deptno, avg from (select deptno,avg(sal) avg from emp group by deptno) 
where avg = 
(
select max(avg) from (select deptno,avg(sal) avg from emp group by deptno)
)

7. --求平均薪水最高部门的部门名称

select deptno, dname from dept 
where deptno = 
(
select deptno from (select deptno,avg(sal) avg from emp group by deptno) 
where avg = 
(
select max(avg) from (select deptno,avg(sal) avg from emp group by deptno)
)
)

8. --求平均薪水的等级最低的部门的部门名称

select d.dname, d.deptno, g.grade, g.avg from dept d
join
(	select grade, avg, deptno from salgrade s
	join (select avg(sal) avg, deptno from emp group by deptno) t
	on (t.avg between s.losal and s.hisal)
) g
on (d.deptno = g.deptno)
where grade = 
(
	select min(grade) from
	(	select grade, avg, deptno from salgrade s
		join (select avg(sal) avg, deptno from emp group by deptno) t
		on (t.avg between s.losal and s.hisal)
	)
)

9. --求部门经理人中平均薪水最低的部门名称

select d.dname, d.deptno, t.avg from dept d join
(
	select avg(sal) avg, deptno from 
	(
select distinct e2.sal, e2.ename, e2.deptno from emp e1 
		join emp e2 
		on (e1.mgr = e2.empno)
) group by deptno
) t
on (d.deptno = t.deptno)
where t.avg = 
(
	select min(avg) from 
	(
		select avg(sal) avg, deptno from 
		(
select distinct e2.sal, e2.ename, e2.deptno from emp e1 
			join emp e2 
			on (e1.mgr = e2.empno)
) group by deptno
	)
)

10. --求比普通员工的最高薪水还要高的经理人的名称.

select ename from emp
where empno in (select distinct mgr from emp where mgr is not null) 
and sal > 
(
	select max(sal) from emp 
	where empno not in (select distinct mgr from emp where mgr is not null)
)

11. --求薪水最高的前5名雇员

select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5

12. --求薪水最高的第六到第十名雇员

select ename, sal from 
(
	select ename, sal, rownum r from (select ename, sal from emp order by sal desc)
)

13. --最后入职的5名员工

select ename, hiredate from
(select ename, hiredate from emp order by hiredate desc)
where rownum <= 5