第一部分
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;
第三部分：
01. tmp表中有如下记录（建表 SQL见emp.sql）
RQ SHENGFU
---------- ----------
2005-05-09 WIN
2005-05-09 WIN
2005-05-09 LOSE
2005-05-09 LOSE
2005-05-10 WIN
2005-05-10 LOSE
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;
541
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
