Oracle 数据库查询语句练习

1、查询平均薪水最低的部门名称，要求：只有领导才参加统计

//1，将各部门的经理和董事长的薪水表拿出来
SELECT DEPTNO,SAL FROM emp where job='MANAGER' OR JOB='PRESIDENT' ;
//2，将各个部门的薪水进行平均数并升序排序
SELECT DEPTNO,AVG(SAL) A FROM emp where job='MANAGER' OR JOB='PRESIDENT' GROUP BY DEPTNO order by A ASC ;
//3. 利用升序第一个找出最低薪水数的部门号
SELECT deptno,A FROM (SELECT DEPTNO,AVG(SAL) A FROM emp where job='MANAGER' OR JOB='PRESIDENT' GROUP BY DEPTNO order by A ASC )where ROWNUM=1;

//4.根据部门号找到部门名称
select DNAME 部门名称,deptno 部门号 from DEPT WHERE DEPTNO=(SELECT deptno FROM (SELECT DEPTNO,AVG(SAL) A FROM emp where job='MANAGER' OR JOB='PRESIDENT' GROUP BY DEPTNO order by A ASC )where ROWNUM=1);

2、查询比普通员工的最高薪水还要高的领导者姓名

//1.查询非领导者的薪水表
select *from emp where not job='MANAGER' and not JOB='PRESIDENT';
//2.查询非领导者的最高薪水
select max(sal) from emp where not job='MANAGER' and not JOB='PRESIDENT';
//3.查询比普通员工的最高薪水还要高的领导者姓名
select ename,sal from emp where sal>(select max(sal) from emp where not job='MANAGER' and not JOB='PRESIDENT');

3、找出薪水最高的五个人
select * from(select * from emp order by sal DESC) where rownum<=5;

4、查询第2到第7名的员工，按薪水降序排列
select *from(select rownum row1,ename,job,sal from emp where rownum<=7) where row1>=2;

5、查询最后入职的5名员工
select * from(select *from emp order by HIREDATE desc) where rownum<=5;

6.不能使用组函数，查询薪水的最高值
select * from(select * from emp order by sal DESC) where rownum=1;

7.查询所有员工的姓名，要求所有员工的姓名显示成小写，雇用日期显示为"yyyy-mm-dd" 这种格式
select lower(ename),to_char(hiredate,'yyyy-mm-dd') from emp;

8.查询20部门的所有员工的员工姓名，实际收入
select ename,sal+nvl(comm,0) 实际收入,comm from emp where deptno=20;

9.求薪水最高的员工姓名
select ename,sal  from emp where sal=(select max(sal) from emp);

10.把雇员按部门分组，求最高薪水，部门号 要求过滤掉名字中第二个字母是’A’的员工,并且部门的平均薪水 > 2000,按照部门编号倒序排列
//1,过滤名字中第二个字母是A的员工
select *from emp where not ename like '_A%'
//2.最高薪水
select max(sal) 最高薪水 ,deptno from emp where ename not like '_A%' group by deptno;
//3.加上限制平均薪水大于2000
select max(sal) 最高薪水 ,deptno from emp where ename not like '_A%'  group by deptno having avg(sal)>2000 ;

11.每页显示5条。显示第一页内容
select rownum as "序列(虚列)",emp.* from emp where rownum<=5;

12.查询10号部门的员工在整个公司中所占的比例
select round((select count(deptno) from emp where deptno=10)/(select count(*) from emp),2)from dual;

13.查询'SALES'部门平均薪水的等级
//1.查询'SALES'部门的平均薪水
select round(avg(sal)) from emp where deptno=(select deptno from dept where dname='SALES')
//2.根据薪水差等级
select grade from SALGRADE where (select round(avg(sal)) from emp where deptno=(select deptno from dept where dname='SALES')) between LOSAL and HISAL;

14.查询在纽约工作的所有员工
select * from emp where deptno=(select deptno from dept where loc ='NEW YORK');

select * from emp,dept  where dept.deptno=emp.deptno  and  loc in ('NEW YORK');

15.查询平均工资比10部门低的部门编号
//1,各部门的平均工资
select deptno ,round(avg(sal))from emp group by deptno;
//2,10部门的平均工资
select round(avg(sal)) from emp where deptno=10;
//3.整合（先分组后限制条件，，先限制条件再分组会出错）
select  deptno from  emp  GROUP BY DEPTNO HAVING avg(sal)<(select avg(sal) from emp where deptno=10);
16.查询不是领导的员工信息
SELECT * FROM emp where not job='MANAGER' and not JOB='PRESIDENT' ;

select * from emp where job not in ('manager','president');

17.查询各部门最高薪水的员工信息
select max(sal),deptno from emp group by deptno;

select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

18.查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周
select round((SYSDATE-(select HIREDATE from emp where ename='SMITH'))/7) from dual;

SELECT ROUND((SYSDATE-HIREDATE)/7,0)  from emp where ENAME='SMITH';

19.查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列
select ename 姓名, empno 编号,sal 薪水,sal*12 年收入 from emp where sal*12>10000 order by sal desc;

20.查询所有81年之后入职的员工信息
select *from emp where hiredate>'1-1月-81';