oracle之经典查询题

发布时间:2014-10-23 23:22:52
来源:分享查询网

--01.  查询员工表所有数据 select * from emp --02.  查询职位(JOB)为'PRESIDENT'的员工的工资 select sal from emp where job='PRESIDENT' --03.  查询佣金(COMM)为0或为NULL的员工信息 select * from emp where comm=0 or comm is null --04.  查询入职日期在 1981-5-1到1981-12-31之间的所有员工信息 select * from emp where hiredate between to_date(19810501,'yyyy/mm/dd') and to_date(19811231,'yyyy/mm/dd') --05. 查询所有名字长度为4的员工的员工编号,姓名 select empno,ename from emp where length(ename)=4 --06.  显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息 select * from emp where  deptno=10 and job='MANAGER' or (deptno=20 and job='CLERK') --07.  显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息 select * from emp where ename not like '%L%' or ename like '%SM%' --08.  显示各个部门经理('MANAGER')的工资 select deptno,sal from emp where job='MANAGER' --09.  显示佣金(COMM)收入比工资(SAL)高的员工的详细信息 select * from emp where comm >sal --10.  把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数) select ename from emp where extract(month from hiredate)=extract(month from sysdate)   select ename from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm'); --11. 把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数) select ename from emp where extract(month from hiredate)=extract(month from sysdate)+1 select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm'); --12. 求1982年入职的员工(考察知识点:单行函数) select ename from emp where extract(year from hiredate)=1982 select * from emp where to_char(hiredate,'yyyy') = '1982'; --13. 求1981年下半年入职的员工(考察知识点:单行函数) select ename from emp where extract(year from hiredate)=1981 and extract(month from hiredate)>6 select * from emp where hiredate between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1; --14. 求1981年各个月入职的的员工个数(考察知识点:组函数) select count(*),extract(month from hiredate) from emp where extract(year from hiredate)='1981'  group by extract(month from hiredate)    select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm') from emp  where to_char(hiredate,'yyyy')='1981'   group by trunc(hiredate,'month')  order by trunc(hiredate,'month'); --01. 查询各个部门的平均工资 Select deptno, avg(sal) from emp group by deptno --02. 显示各种职位的最低工资 select job, min(sal) from emp group by job --03. 按照入职日期由新到旧排列员工信息 select * from emp order by hiredate desc --04. 查询员工的基本信息,附加其上级的姓名 select emp.*, t.ename managername  from emp , emp t where emp.mgr=t.empno --05. 显示工资比'ALLEN'高的所有员工的姓名和工资 select ename,sal from emp where sal>(select sal from emp where ename='ALLEN') --06. 显示与'SCOTT'从事相同工作的员工的详细信息 select * from emp where job=(select job from emp where ename='SCOTT') and ename <> 'SCOTT' --07. 显示销售部('SALES')员工的姓名 select ename from emp,dept where emp.deptno=dept.deptno and dname='SALES' --08. 显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资 select ename,sal from emp where deptno=30 and sal=(select sal from emp where ename='MARTIN') and ename <> 'MARTIN' --09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN') select ename from emp where sal>(select avg(sal) from emp) and job='SALESMAN' --10. 显示所有职员的姓名及其所在部门的名称和工资 select ename,dname,sal from emp ,dept where emp.deptno=dept.deptno --11. 查询在研发部('RESEARCH')工作员工的编号,姓名,工作部门,工作所在地 select empno,ename,dname,loc from emp,dept where emp.deptno=dept.deptno --12. 查询各个部门的名称和员工人数 select dname,countnum from dept, (select deptno,count(empno) countnum from emp group by deptno) t where dept.deptno=t.deptno select dname,c from (select count(*) c, deptno from emp group by deptno) e   inner join dept d on e.deptno = d.deptno; --13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 select count(empno) ,job from  emp where sal>(select avg(sal) from emp)group by job --14. 查询工资相同的员工的工资和姓名 select sal,ename from emp e where(select count(*) from emp where sal=e.sal group by sal)>1 --15. 查询工资最高的 3名员工信息 select * from (select * from emp order by sal desc )t where rownum<4 --16. 按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第三名继续排) select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank; --17. 求入职日期相同的(年月日相同)的员工 select * from emp e where (select count(*) from emp where e.hiredate=hiredate)>1 --18. 查询每个部门的最高工资 select deptno, max(sal) from emp group by (deptno) --19. 查询每个部门,每种职位的最高工资 select deptno,job,max(sal) from emp group by deptno,job --20. 查询每个员工的信息及工资级别(用到表 Salgrade) select emp.*,grade from emp,salgrade where sal between losal and hisal --21. 查询工资最高的第 6-10 名员工 select * from(select rownum r,ename from (select ename from emp order by sal desc))where r<=10 and r>=6 --22. 查询各部门工资最高的员工信息 select * from emp e where e.sal=(select max(sal) from emp where emp.deptno=e.deptno) --23. 查询每个部门工资最高的前 2名员工 select * from emp e where  (select count(*) from emp where sal > e.sal and e.deptno =deptno) < 2 select * from (  select rank() over (partition by deptno order by sal desc) rank, e.* from emp e ) where rank < 3; --24. 查询出有3个以上下属的员工信息 select * from emp, (select mgr,count(empno)countnum from emp group by mgr)t  where t.countnum>3 and emp.empno=t.mgr select * from emp e where  (select count(*) from emp where e.empno =mgr) >2 ; --25. 查询所有大于本部门平均工资的员工信息 select * from emp where sal>(select avg(sal)sal from emp e where deptno=emp.deptno) --26. 查询平均工资最高的部门信息 select * from dept where deptno= (select deptno from emp group by deptno having avg(sal)= (select max(avg(sal)) from emp group by deptno)) --27. 查询大于各部门总工资的平均值的部门信息 select * from dept where deptno=(select deptno from emp group by deptno having sum(sal)>(select avg(sum(sal))sal from emp group by deptno )) --28. 查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接查询) select * from emp where deptno= (select deptno from emp group by deptno having sum(sal)> (select avg(sum(sal))sal from emp group by deptno )) --29. 查询没有员工的部门信息 select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null; --显示与Blake在同一部门工作的雇员的项目和受雇日期,但是blake不包含在内。 select ename,hiredate from emp where deptno= ( select deptno from emp where ename='BLAKE') and ename <> 'BLAKE'

返回顶部
查看电脑版