oracle常见的经典查询语句（一）

01.       查询员工表所有数据

 

 select * from emp;

 

02.  查询职位(JOB)为'PRESIDENT'的员工的工资

 

select sal from emp where job='PRESIDENT';

 

03.  查询佣金(COMM)为0或为NULL的员工信息

 

 SQL> select * from emp where nvl(comm,0)=0;

 

04.  查询入职日期在 1981-5-1到1981-12-31之间的所有员工信息

 

select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD') and to_date('1981-12-31','YYYY-MM-DD');

 

select * from emp where hiredate between to_date('1/5月/1981') and to_date('31/12月/1981');

 

05.  查询所有名字长度为4的员工的员工编号,姓名

 

select empno,ename from emp where length(ename)=4;

 

06.  显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息

 

SQL> select * from emp where (job='MANAGER' and deptno=10) or (deptno=20 and job='CLERK');

 

07.  显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息 

 

select * from emp where ename not like '%L%' or ename like '%SM%';

 

08.  显示各个部门经理('MANAGER')的工资

 

select sal from emp where job='MANAGER';

 

09.  显示佣金(COMM)收入比工资(SAL)高的员工的详细信息

 

select * from emp where nvl(comm,0)>nvl(sal,0);

 

10.  把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)

 

Select * from emp where to_char( hiredate,'mm')=to_char( sysdate,'mm');

 

 

11.  把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)

 

SQL>  select * from emp where to_char( hiredate,'mm')=to_char( add_months(sysdate,1),'mm');

 

12.  求1982年入职的员工(考察知识点:单行函数)

 

      select * from emp where to_char( hiredate,'YYYY')=1982;

 

13.  求1981年下半年入职的员工(考察知识点:单行函数)

 

SQL>  select * from emp where  to_char( hiredate,'YYYY-MM-DD') between '1981-07-01' and '1981-12-31' ;

 

14.  求1981年各个月入职的的员工个数(考察知识点:组函数)

 

SQL>  select count(*),trunc(hiredate,'month')  from emp where to_char( hiredate,'YYYY')='1981' group by trunc(hiredate,'month') ;

 

 

01.       查询各个部门的平均工资

 

select deptno,avg(nvl(sal,0)) from emp group by deptno ;

select deptno,sum(nvl(sal,0)+nvl(comm,0))/count(*) from emp group by deptno;

 

 

02.  显示各种职位的最低工资

 

 select job,min(nvl(sal,0)) from emp  group by job;

 

 03.  按照入职日期由新到旧排列员工信息

 

SQL> select * from emp order by hiredate desc;

 

04.  查询员工的基本信息，附加其上级的姓名

 

SQL> select t1.empno,t1.ename,t1.job,t2.ename,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.mgr=t2.empno;

 

05.  显示工资比'ALLEN'高的所有员工的姓名和工资

 

SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on nvl(t1.sal,0)> nvl

(t2.sal,0) where t2.ename='ALLEN';

 

06.  显示与'SCOTT'从事相同工作的员工的详细信息

 

 SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.job=t2.job where t2.ename='SCOTT';

 

07.  显示销售部('SALES')员工的姓名   

 

select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';

 

08.  显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资

 

SQL> select e.* from emp e join emp e1 on e.sal=e1.sal where e.deptno=30 and e1.ename='MARTIN';

 

09.  查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')

 

Select * from emp where sal>(select avg(nvl(sal,0)) from emp ) and job='SALESMAN';

 

10.  显示所有职员的姓名及其所在部门的名称和工资

 

 SQL> select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno ;

 

11.  查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地

 

 SQL> select e.*, dname,loc from emp e join dept d on e.deptno=d.deptno where d.dname='RESEARCH';

 

12.  查询各个部门的名称和员工人数

 

               Select deptno,count(*) from emp group by deptno;

 

13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位 

 

 Select count(*),job from emp where sal>(select avg(nvl(sal,0)) from emp ) group by job;

 

14.  查询工资相同的员工的工资和姓名

 

SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.sal=t2.sal where t1.empno!=t2.empno ;

 

15.  查询工资最高的 3名员工信息

 

 select   *   from   (select   *   from   emp   order   by   nvl(sal,0)   desc)   where   rownum<=3;

 

16.  按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第三名继续排)

 

SQL> select e.*, (select count(*) from emp where nvl(sal,0)> nvl(e.sal,0))+1 rank from emp e order by rank;

 

17.  求入职日期相同的(年月日相同)的员工

 

SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.hiredate=t2.hiredate where t1.empno!=t2.empno ;

 

18.  查询每个部门的最高工资

 

 Select max(nvl(sal,0)),deptno from emp group by deptno;

 

19.  查询每个部门,每种职位的最高工资

 

select deptno, job, max(nvl(sal,0)) from emp group by deptno,job;

 

20.  查询每个员工的信息及工资级别(用到表 Salgrade)

 

SQL> select GRADE,e.* from salgrade , (Select * from  emp  ) e where  LOSAL <= nvl(e.sal,0) and nvl(e.sal,0)<=HISAL;

 

21.  查询工资最高的第 6-10 名员工

 

SQL> select * from (select e.*,rownum as rowno from (select   *   from   emp   order   by   nvl(sal,0)   desc) e) where rowno between 6 and 10;

 

22.  查询各部门工资最高的员工信息

 

Select * from emp e,(Select deptno,max(nvl(sal,0)) sa from emp group by deptno) s where e.sal=s.sa;

 

23.  查询每个部门工资最高的前 2名员工

 

SQL> select * from emp e where  (select count(*) from emp where nvl(sal,0) > nvl(e.sal,0) and e.deptno = deptno) < 2  order by deptno, nvl(sal,0) desc;

 

 

 

 

24.  查询出有3个以上下属的员工信息

 

 

 

SQL> select * from emp,(select mgr from (select mgr,count(*) from emp  group by mgr having count(*)>=3)) s where empno =s.mgr  ;

 

select * from emp e where  (select count(*) from emp where e.empno = mgr) > 2;

 

25.  查询所有大于本部门平均工资的员工信息() 

 

select * from emp e where sal > (select avg(sal) from emp where e.deptno=deptno) ;

 

26.  查询平均工资最高的部门信息

 

Select * from dept d,(Select deptno from (Select avg(nvl(sal,0)) sal ,deptno from emp group by deptno order by sal desc ) where   rownum<=1) e where d.deptno= e.deptno;

 

27.  查询大于各部门总工资的平均值的部门信息

 

 

select d.*,sumsal from dept d, (select sum(nvl(sal,0)) sumsal, deptno from emp group by deptno) se where se.sumsal >(select avg(sum(nvl(sal,0))) from emp group by deptno) and se.deptno = d.deptno;

 

28.  查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接查询)

 

 select e.*,sumsal from emp e, (select sum(nvl(sal,0)) sumsal, deptno from emp group by deptno) se where se.sumsal >(select avg(sum(nvl(sal,0))) from emp group by deptno) and se.deptno = e.deptno;

 

29.  查询没有员工的部门信息

 

SQL> select * from dept where deptno not in(select deptno from emp);

 

30.  查询用户（users表）huxz 所下所有订单编号，下单日期，总价格(orders表)，并包括订单中的商品数量（orderitem表），名称（product 表），价格（product表）

 

Select pr.PRODUCTNAME,qty,pr.PRODUCTID,orei,od,tp  from product pr, (Select QTY qty,PRODUCTID,ore.ORDERID orei,od,tp from orderitem  oi, (Select ORDERDATE od, ORDERID  , TOTALPRICE tp from orders where username=(select username from users where username='huxz') ) ore where ore.ORDERID= oi.ORDERID) ori where ori.PRODUCTID=pr.PRODUCTID;

 

31.  查询100001号商品被哪些顾客（users表）购买过，下单日期(orders表)，每人购买的数量（orderitem表），购买时的价格（product表）

 

Select ORDERDATE , TOTALPRICE, ords, qty   from  orders ors,(Select  ORDERID, QTY qty, ORDERID ords from  orderitem ord,( select PRICE from product where PRODUCTID='100001') where ord.PRODUCTID='100001' )  where ors.ORDERID= ords;

 

32.  查询出哪些商品从未被订购过

 

select * from product pro left join (select distinct PRODUCTID from orderitem) ord on pro.PRODUCTID=ord.PRODUCTID where ord.PRODUCTID is null;

 

33.  查询出被订购过 2次以上的商品信息

 

select p.* from product p where (select count(*) from orderitem where productid = p. productid) >= 2;