Oracle数据库scoot用户经典查询语句

select*from recyclebin//orcle回收站


purge recyclebin清空回收站


flashback table 表名 to before drop


alter user scott acountt unlock


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;