oracle 经典查询案例

来源：互联网发布：ios5.1.1旧版软件助手编辑：程序博客网时间：2024/05/07 01:29

第一部分

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表）

SelectORDERDATE , TOTALPRICE, ords, qty from orders ors,(Select ORDERID, QTY qty, ORDERID ords from orderitem ord,( selectPRICE from product wherePRODUCTID='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;

第三部分：

01. tmp表中有如下记录（建表 SQL见emp.sql）

RQ SHENGFU

---------- ----------

2005-05-09 WIN

2005-05-09 LOSE

2005-05-10 WIN

2005-05-10 LOSE

要求格式为：

RQ WIN LOSE

---------- ---------- ----------

2005-05-10 1 2

2005-05-09 2 2

答案：select rq, count(case when shengfu='WIN' then 'WIN' else null end) WIN, count(case when shengfu='LOSE' then 'LOSE' else null end) LOSE from tmp group by rq ;

02. 查询当前月有多少天

答案：SQL> select trunc(add_months(sysdate,1),'month') - trunc(sysdate,'month') from dual;

03. pages表有四个字段，id, url,title,body。如图：

ID URL TITLE BODY

--------- ----------- ------------------------- ------

1 http://www.baidu.com 新闻党报评事业单位发绩效工资砸铁饭碗再砸金饭碗

2 http://www.sina.com baidu新闻假唱假演奏最高罚款3000元 10月1日起施行

3 http://www.yahoo.com 搜索结果 www.baidu.com/search/url_submit.html - 网页快4 http://www.baidu.com 新闻垃圾焚烧产生致癌物专家告诫中国勿重蹈日本覆辙

要求格式为：

ID CUNT

--------------------------------------------------------------------------------

3 www.baidu.com/search/url_submit.html - 网页快照 -类似结果

2 baidu新闻

1 http://www.baidu.com

4 http://www.baidu.com

答案：select id,body cunt from pages where body like '%baidu%' union all select id,title from pages where title like '%baidu%' union all select id,url from pages where url like '%baidu%';

ID URL

--------------------------------------------------------------------------------

1 http://www.baidu.com

4 http://www.baidu.com

2 baidu新闻

3 www.baidu.com/search/url_submit.html - 网页快照 -类似结果

答案：select id,url from pages where url like '%baidu%' union all select id,title from pages where title like '%baidu%' union all select id,body cunt from pages where body like '%baidu%';

04.现有 STUDENT(学生), COURSE(课程), SC(成绩)表,完成以下需求(建表语句在emp.sql

中,综合考察)

a) 查询选修课程为 web的学员学号和姓名

答案：SQL> Select s.name,s.sid from student s, (Select sid from sc Where cid=(select cid from course where name='web' )) s1 where s.sid=s1.sid;

b) 查询课程编号为 2的学员姓名和单位

答案：select * from student s,(select sid from sc where cid=2) s1 where s.sid=s1.sid;

b) 查询不选修4号课程的学员姓名和单位

答案：SQL> select distinct s.* from student s,(Select sid from sc where cid!=4) s1 where s.sid=s1.sid;

d) 查询选修全部课程的学员姓名和单位

答案：SQL> select * from student where sid=(select sid from sc group by sid having count(*)=(select count(distinct cid) from sc));

e) 查询选修课程超过 3门的学员姓名和单位

答案：SQL> select * from student s,(select sid from sc group by sid having count(*)>3) s1 where s.sid=s1.sid;

f) 找出没有选修过 Teacher LI讲授课程的所有学生姓名

答案：select distinct s1.* from student s1 where s1.sid not in ( select s.sid from sc s,(select distinct cid from course where TEACHER='Teacher LI') c where s.cid=c.cid) ;

g) 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

答案：SQL> select s.*,sco from student s,(select distinct sid,avg(SCORE) sco from sc where score<60 group by sid) s1 where s.sid=s1.sid;

h) 列出既学过1号课程，又学过2号课程的所有学生姓名

答案：SQL> select * from student s, (select sid from sc where cid=1) s1 ,(select sid from sc where cid=2) s2 where s.sid=s1.sid and s.sid=s2.sid;

i) 列出1号课成绩比2号课成绩高的所有学生的学号，姓名和 1号课和 2号课的成

绩

答案：select * from student s, (select sid,SCORE from sc where cid=1) s1 ,(select sid,SCORE from sc where cid=2) s2 where s.sid=s1.sid and s.sid=s2.sid and s1.score>s2.score;

05.现有test表，表中数据如图所示：

a) 连续的编号要求如下格式

BEGIN END

---------- ----------

1 6

8 9

11 14

18 19

答案：Select min(id) begin,max(id) end from test group by id-rownum order by id-rownum

b) 不连续的编号要求如下格式

BEGIN END

---------- ----------

8 9

11 14

18 19

答案：SQL> Select min(id) begin,max(id) end from test group by id-rownum having id-rownum!=0 order by id-rownum;

06.（统计各部门，各职位的人数）

DEPTNO CLERK SALESMAN PRESIDENT MANAGER ANALYST

------ ---------- ---------- ---------- ---------- ----------

30 1 4 0 1 0

20 2 0 0 1 2

10 1 0 1 1 0

答案：select deptno, count(case when job='CLERK' then 'CLERK' else null end) CLERK, count(case when job = 'SALESMAN' then 'SALESMAN' else null end) SALESMAN, count(case when job='PRESIDENT' then 'PRESIDENT' else null end) PRESIDENT, count(case when job='MANAGER' then 'MANAGER' else null end) MANAGER, count(case when job='ANALYST' then 'ANALYST' else null end) ANALYST from emp group by deptno;

07.根据EMP表数据产生如下格式的报表(统计各职位,各部门的人数)(06题的变体)

Job 10 20 30

------------- ---------- ----------- ---------

0 0 1

CLERK 1 2 1

SALESMAN 0 0 4

PRESIDENT 1 0 0

MANAGER 1 1 1

ANALYST 0 2 0

答案：select job , count(case when deptno='10' then '10' else null end) as "10", count(case when deptno='20' then '20' else null end) as "20", count(case when deptno='30' then '30' else null end) as "30" from emp group by job;

08.按照如下格式显示 7369号员工的信息

Empno key value

------------ ------------ ---------------------------

7369 comm

7369 deptno 20

7369 ename smith

7369 hiredate 1980-12-17

7369 job cleak

7369 mgr 7902

7369 sal 800

答案：select empno, 'ENAME' as KEY, ename VALUE from emp where empno = 7369 union select empno, 'JOB', job from emp where empno = 7369 union select empno, 'HIREDATE', to_char(hiredate,'yyyy-mm-dd') a from emp where empno = 7369 union select empno, 'MGR', to_char(mgr) from emp where empno = 7369 union select empno, 'SAL', to_char(sal) from emp where empno = 7369 union select empno, 'COMM', to_char(comm) from emp where empno = 7369 union select empno, 'DEPTNO', to_char(deptno) from emp where empno = 7369;