oracle常见的经典查询语句（二）
?
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 ;
?
?
?
答案：SQL> select trunc(add_months(sysdate,1),'month') - trunc(sysdate,'month') from dual;
?
?
?
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
中,综合考察)
?
答案：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;
?
?
?
答案：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;
?
?
?
附件中为数据库文件在上一篇中oracle经典查询案例中忘记上传数据库文件了
