解法一: select ename from (select ename from emp order by sal desc) where rownum=1;
解法二: select ename from emp where sal=(select max(sal) from emp);
解法三: select ename, sal from emp where sal not in (select distinct t1.sal from emp t1 inner join emp t2 on t1.sal < t2.sal);
1. 计算每位员工的年薪
select ename, sal*12+nvl(comm, 0) annualSalary from emp;
2. 统计有员工的部门的人数
select count(distinct deptno) from emp;
3. 求挣最高薪水的员工(boss除外)的姓名
select ename from emp where sal = (select max(sal) from emp where job<>upper('president'));
4. 查询薪水超过平均薪水的员工的姓名和工资
select ename, sal from emp where sal > (select avg(sal) from emp);
5. 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
select ename, t1.deptno, to_char(sal, '$9,999.9') sal, to_char(round(t2.avgsal, 1), '$9,999.9') avg from emp t1,(select deptno, avg(sal)
avgsal from emp group by deptno) t2 where t1.sal > t2.avgsal and t1.deptno = t2.deptno;
6. 查询部门中薪水最高的人姓名、工资和所在部门名称
select ename, sal, dname from emp t1
inner join
(select deptno, max(sal) maxsal from emp group by deptno) t2
on t1.deptno = t2.deptno and t1.sal = t2.maxsal
inner join
dept t3 on t3.deptno = t1.deptno;
7. 查询部门平均薪水的等级
select deptno, avg, grade from (select deptno, avg(sal) avg from emp group by deptno) t1 inner join salgrade t2 on t1.avg between
t2.losal and t2.hisal;
8. 哪些人是主管
select ename from emp
where empno in (select distinct mgr from emp);
9. 求平均薪水最高的部门的名称和平均工资
select dname, to_char(avg, '$9,999.9') avg from dept t1
inner join (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))) t2 on t1.deptno = t2.deptno;
10. 求薪水最高的前3名雇员
select * from (select ename, sal from emp order by sal desc) t where rownum <= 3;
11. 求薪水最高的第4-6名雇员
select ename, sal from (select rownum as rn, t.* from (select ename, sal from emp order by sal desc) t) where rn >= 4 and rn <= 6;
12. 求薪水最低的部门经理所在部门的名称
