自己做得练习题,我的写法可能不完全正确,读者学得时候参考别人的写法
学生表
student(S#,sname,sage,ssex)课程表
Course(C#,cname,T#)
成绩表
SC(S#,score,C#)
教师表
Teacher(T#,tname)
1.001课程比002课程成绩高的所有的学生的学号
select S# from
(select score from SC where c#='001') a,
(select score from SC where c#='002') b
where a.score>b.score and a.S#=b.S#
2.查询平均成绩大于60分的同学的学号与平均成绩
我的写法:select S#,avg(score) from sc where avg(score)>60 group by S#
别人的写法:select S#,avg(score) from sc group by S# having avg(score)>60
3.查询所有同学的学号,姓名,选课书,总成绩
我的写法:select s#,sname count(C#),sum(score) from student a,sc b
where a.s#=b.s# group by s#
别人的写法:select a.s#,a.sname count(b.C#),sum(score) from student a left join sc b
on a.s#=b.s# group by a.s#
4.查询姓李的老师的个数
select count(T#) from Teacher where tname like "李%"
5.查询没有学过 叶平 老师课的学生
首先考虑的是学过叶平老师的课的学生学号 去掉重复
select s#,sname from student
where S# not in
(select distinct(sc.s#) from sc,course,teacher
where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='叶平');
疑问:sc.c#=course.c# and teacher.t#=course.t# 这个条件是否可以改成
sc.t#=teacher.t#
误区:在成绩表sc 中没有t# 这个字段所以我采用sc.c#=course.c# and teacher.t#=course.t#
来表示同一个意思
6.查学过001 也学过002 课程的同学学号,姓名
别人的写法
select Student.S#,Student.Snamefrom Student,SC where Student.S#=SC.S# and SC.C#='001'
and
exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
我的答案:
select s#,sname from student where s#=(
select dictinct(s#) from sc where c# in ('001','002')
);
7.查询学过叶平老师所有课的同学的学号、姓名
说明:根据课程数来做判断
select s#,sname from student
where s# in
(selec s# from sc,course,teacher
where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='叶平'
group by s# having count(sc.c#)=
(select count(c#) from course,teacher
where teacher.t# =course=t# and teacher.tname='叶平')
)
8.查询编号002的成绩比编号001的课程低的所有的同学的姓名学号
我的答案:
select s#,sname from teacher
where s# in
( select s# from
(select s#,score from sc where c#='001') sc1,
(select s#,score from sc where c#='002') sc2
where sc1.s#=sc2.s# and sc1.score>sc2.score
);
别人的写法:
Select S#,Sname from
(select Student.S#,Student.Sname,score ,
(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001'
)
S_2 where score2 <score;
9.查询所有课程成绩小于60分的同学的学号,姓名
理解说明:只要有一门成绩大于60分就行
我的写法:
select s#,sname from student
where s# not in
( select distinct(s#) from sc where score>60
);
别人的写法:
select S#,Sname
from Student
where S# not in
(select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10.查询没有学全所有课的同学的学号,姓名
理解说明:根据课程数量来判断 与题7写法类似
我的写法:
select s#,sname from student where s# in(
select distinct(s#) from sc group by s# having count(c#)<
(
select count(c#) from course //所有的课程数
)
)
别人的写法:
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname
having count(C#) <(select count(C#) from Course);
11.查询至少有一门课与学号为'1001'的同学所学相同的同学的学号与姓名
理解说明:根据课程名来判断
select s#,sname from student,sc
where student.s#=sc.s# and sc.c# in
(
select c# from sc where s# ='1001'
)
12.查询至少学过学号为001同学所有一门课的其他同学的学号和姓名
13.把成绩表中 叶平老师课的成绩都改成这个课程的平均成绩
update sc set sc.score=avg(score)
where c# =(select course.c# from course,teacher
where course.t#=techear.t# and teacher.tname='叶平'
)
group by c#
别人的写法:.....
update sc set score=(
select avg(sc.score) from sc sc2
where sc2.c#=sc.s#
)
14.查询出于1002号同学学习课程完全相同的其他同学的学号与姓名
我的理解:课程数相同,课程相同
错(只单一考虑了学习的课程数相等):select s#,sname from student
where s# in(
select s# from
(select c# count(c#)from sc where s3!='1002') sc1,
(select c# count(c#)from sc where s3='1002') sc2
where
sc1.s#!=sc2.s# and sc1.count(c#)=sc2.count(c#
);
别人的写法:
select s# ,sname from student
where s# in(
select s# from sc where c# in(select c# from sc where s#='1002') //查询出学习了1002学号课程的同学,但是不满足全部都学习了
group by s# having count(s#)=(select count(s#) from sc where s#='1002') //满足学得课程数跟1002学生一样
)
15.删除学习 叶平老师课的成绩表sc 记录
delete * from sc
where c# in
(select c# from course,teacher
where teacher.t#=course.t# and teacher.tname='叶平'
)
16.向sc表中插入一些记录,符合条件:没有上过编号为003课程的同学的同学编号 2号课程的平均成绩
insert into sc select s#,'002',
(select avg(score) from sc where c#='002')
from student where s# not in (select s# from sc where c# ='003')
)