由于被拉去进行建模比赛,小生的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'
;
第一阶段的练习就到这儿了。