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;