Oracle数据库之SQL语句练习

             由于被拉去进行建模比赛，小生的sql练习计划直接被延后了三天。趁着今晚没课(逃了)将这一套

     题集进行一下练习，巩固下之前的学习。需要说明的是，练习所针对的数据都是来自上一盘文章中的

     http://blog.csdn.net/kiritor/article/details/8790214。数据不多，也就是学生、课程的相关信息。

             1、查询“C001”课程比“C002”课程成绩高的所有学生的信息。    

            ☞  在from子句后面使用子查询

select  s.*，a.cno,a.score from student s, 
 (
 select * from score where cno='C001'
 )a,
 (
  select * from score where cno='C002'
 )b
 where a.sno=b.sno and a.score>=b.score and s.SNO = a.SNO
 ;

              ☞  使用相关子查询方式实现

select * from student s, score a
   where a.cno ='C001'
   and exists
    (
       select * from score b 
       where b.cno='C002'
       and a.score >=b.score
       and a.sno = b.sno    
    )  
    and s.sno =a.sno
 ;

                 2、查询平均成绩大于60分的所有学生的信息，包括其平均成绩

                 ☞  第一种方式

select s.sno,t.sname, avg(s.score) from student t, score s
 where t.sno=s.sno 
  group by (s.sno,t.sname) 
  having avg(s.score)>60
 ; 

                ☞  第二种方式

select t.sno ,t.sname, m.avg_s from student t，
   (
     select s.sno, avg(s.score) avg_s from  score s
     group by s.sno having avg(s.score)>60
   ) m
   where m.sno = t.sno
 ;

                 3、查询所有同学的姓名、学号、总成绩、选课数

/*思路：可以知道的是选课数、总成绩可以通过
   子查询中的内置函数查询出来的*/   
--首先查询出总成绩与选课数
select sum(score) ,count(cno) from score group by sno;
--之后查询学生的学号姓名等信息就顺理成章了
select t.* ,tmp.sum_sc,tmp.sum_cn from student t,
  (
    select sno, sum(score) sum_sc ,count(cno) sum_cn from score group by sno
  ) tmp
 where t.sno = tmp.sno
 ;

                 4、查询姓为“刘”的老师的信息

 select * from teacher where tname like '刘%'; 
 select count(*) from teacher where tname like '刘%';

                5、查询学过“王燕”老师课的同学的学号、姓名

                     思考：首先查询出王燕老师教授的课程的编号

                  ☞  第一种方式

 select  t.* ,s.cno,s.score from student t, score s
 where s.cno in    
   (
     select distinct cno from course c,teacher t
     where c.tno = 
      (
     select tno from teacher where tname='王燕'
      ) 
   ) 
   and t.sno = s.sno
;

                 ☞  第二种方式

 select * from student st 
    where st.sno  in
     (
       select distinct sno from score s join course c
         on s.cno=c.cno
         join teacher t on c.tno=t.tno 
         where tname='王燕'
     )  
;

              6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

--通过连接的方式实现
select * from score s
 join score a on s.sno = a.sno 
 join student st on st.sno = s.sno
 where s.cno='C001' and a.cno = 'C002'
 and st.sno = s.sno
 ;

               7、查询课程编号‘COO2’的成绩比课程编号为'C001'的成绩低的学生的所有信息。

                 呃，是不是有种似曾相识的感觉呢，和第一题没有区别嘛，不过我们采用子查询的

                 方式来实现。

select * from student t
 join score a on t.sno = a.sno
 join score b on t.sno = b.sno
 where a.cno = 'C002' 
     and b.cno ='C001'
     and a.score <= b.score
;

                 哈哈使用连接的方式看起来更加简单吧！

                8、查询所有课程成绩都小于60分的学生的学号等信息

                  先来看看一种经常误以为是正确的查询吧！小生是在网上找的题库

                  答案什么的感觉感觉有些问题啊，还是自己推敲吧

                  错误的查询：

select st.*,s.score from student st
 join score s on st.sno=s.sno
 join course c on s.cno=c.cno
 where s.score <60

                    很容易的可以知道这个查询只要有小于60分的课程都会查到，这并不符合题目的要求

                 下一种查询方式：

                       思考所有的课程小于60，就是不存在某个学生的某门课程大于60分

 select t.*  from student t
  where  
     not  exists
     (
        select * from score s 
         where s.score >60.9  and t.sno = s.sno
     )
     and t.sno in
      (
         select sno from score
      )   
 ;  

                  9、查询没有学完所有课程的学生的信息

                      

                       思考：：

                         1、我们应该知道总共的课程数
                         2、再在score表中查询，按照sno分组、并
                               去重，添加having子句         

select  t.sno,t.sname from student t 
 left join  score on t.sno=score.sno
 group by t.sno,t.sname
 having count(score.cno)<
  (
    select count(distinct cno) from course
  )
;

                 10、查询至少有一门课与学号为‘S001’所选的课一样的

                    思路：首先查询出学号为S001学生所选
                                 的所有课程的信息，之后进行判断

select  t.sno,t.sname ,score.cno from student t 
left join score on t.sno= score.sno
where score.cno in
  (
    select distinct cno from score where sno='S001'
  )  
  and t.sno <>'S001'
;

              第一阶段的练习就到这儿了。