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

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;