Oracle数据库之SQL语句练习

发表于:2013-05-31来源:Csdn作者:一个小菜仔点击数: 标签:oracle
由于被拉去进行建模比赛,小生的sql练习计划直接被延后了三天。趁着今晚没课(逃了)将这一套 题集进行一下练习,巩固下之前的学习。需要说明的是,练习所针对的数据都是来自上一盘文章中的

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

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

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

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

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

  [sql] view plaincopyprint?

  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 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

  ;

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

  [sql] view plaincopyprint?

  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

  ;

  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分的所有学生的信息,包括其平均成绩

  ☞ 第一种方式

  [sql] view plaincopyprint?

  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 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

  ;

  ☞ 第二种方式

  [sql] view plaincopyprint?

  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

  ;

  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、查询所有同学的姓名、学号、总成绩、选课数

  [sql] view plaincopyprint?

  /*思路:可以知道的是选课数、总成绩可以通过

  子查询中的内置函数查询出来的*/

  --首先查询出总成绩与选课数

  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

  ;

  /*思路:可以知道的是选课数、总成绩可以通过

  子查询中的内置函数查询出来的*/

  --首先查询出总成绩与选课数

  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、查询姓为“刘”的老师的信息

  [sql] view plaincopyprint?

  select * from teacher where tname like '刘%';

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

  select * from teacher where tname like '刘%';

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

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

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

  ☞ 第一种方式

  [sql] view plaincopyprint?

  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='王燕'

原文转自:http://blog.csdn.net/kiritor/article/details/8805310