Oracle自带表(EMP)SQL语句练习

- 日理万妓 2022-09-17 12:29 203阅读 0赞

0. 查询最高工资及其对应员工  
  
  
解法一: 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. 求薪水最低的部门经理所在部门的名称