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

mhfztlyfwo

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;
 
 
 
附件中为数据库文件在上一篇中oracle经典查询案例中忘记上传数据库文件了