学生选课数据库SQL语句练习题(详细分解答案)

1、、设有一数据库，包括四个表：学生表（Student）、课程表 （Course ）、成绩表（Score ）以及教师信息表（Teacher）。 四个表的结构分别如表1-1的表（一）表（四）所示，数据如表 的表（一）表（四）所示。用SQL语句创建四个表并完 成相 关题目。 表数据库的表结构 表()Student 属性名 数据类型 可否为 空 含义 Sno Char(3) 否 学号（主 键） Sn ame Char(8) 否 学生姓名 Ssex Char(2) 否 学生性别 Sbirthday datetime 可 学生出生年 月 Class Char(5) 可 学生所在班 级 表（二）Course

2、属性名 数据类型 可否为 空 含义 Cno Char(5) 否 课程号（主 键） Cn ame Varchar(10) 否 课程名称 Tno Char(3) 否 教师编号 （外键） 表（三）Score 属性名 数据类型 可否为 空 含义 Sno Char(3) 否 学键 Cno Char(5) 否 课程号（外 键） Degree Decimal(4,1) 可 成绩 主码：Sno+Cno 表（四）Teacher 属性名 数据类型 可否为 空 含义 Tno Char(3) 否 教师编号 （主键） Tn ame Char(4) 否 教师姓名： Tsex Char(2) 否 教师性别一 Tbirthd

3、ay datetime 可 教师出生年 月 Prof Char(6) 可 职称 Depart Varchar(10) 否 教师所在部 门 表数据库中的数据 表Stude ()nt Sno Sn ame Ssex Sbirthday class 108 曾华 男 1977-09 01 95033 105 匡明 男 1975-10 02 95031 107 王丽 女 1976-01 23 95033 101 李军 男 1976-02 20 95033 109 王芳 女 1975-02 10 95031 103 陆君 男 1974-06 03 95031 表（二）Course Cno Cn ame

4、Tno 3-105 计算机导论 825 3-245 操作系统 804 6-166 数字电路 856 9-888 高等数学 831 表（三）Score Sno Cno Degree 103 3-245 86 105 3-245 75 109 3-245 68 103 3-105 92 105 3-105 88 109 3-105 76 101 3-105 64 107 3-105 91 108 3-105 78 101 6-166 85 107 6-166 79 108 6-166 81 表（四）Teacher Tno Tn ame Tsex Tbirthday Prof Depart 804

5、李诚 男 1958-12 02 副教 授 计算机系 856 张旭 男 1969-03 12 讲师 电子工程 系 825 王萍 女 1972-05 05 助教 计算机系 831 刘冰 女 1977-08 14 助教 电子工程 系 -1V查询Student表中的所有记录的Sname、Ssex和Class歹ij。 select sn ame,ssex,class from stude nt; -2v查询教师所有的单位即不重复的Depart列。 select disti net depart from Teacher; -3、查询Student表的所有记录。 select * from stude n

6、t; -4s查询Score表中成绩在60到80之间的所有记录。 select * from score where degree betwee n 60 and 80; -5、查询Score表中成绩为85，86或88的记录。 select * from score where degree in(85,86,88); -6、查询Student表中“95031班或性别为女”的同学记录 select * from stude nt where class = 95031 or ssex 二女: -7、以Class降序查询Student表的所有记录。 select * from stude nt o

7、rder by class desc; -8、以Cno升序、Degree降序查询Score表的所有记录V select * from score order by cno degree desc; -9、查询“95031班的学生人数。 select class,count(*) as 学生人数 from student group by class hav ing class 二95031： -10、查询Score表中的最高分的学生学号和课程号。（子查询或 者排 序） select sno,cno ,degree, (select max(degree) from score) as maxs

8、core- 计算最高分 from score where degree= (select max(degree) from score); -11、查询305,号课程的平均分。 select avg(degree) as avgdegree from score group by eno hav ing eno 二3105： -12x查询Score表中至少有5名学生选修的并以3开头的课程的 平均 分数。 select avg(degree) as avgdegree from score group by eno -按照 课 程分组取平均值 hav ing eno 二(select eno f

9、rom score group by eno hav ing count(*)=5)-至少有5名学生选修的课程 and eno like *3%*;-以3开头的课程 -13v查询最低分大于70,最高分小于90的Sno列 select sno, max(degree)as maxdegree, min( degree) as min degree from Score group by sno hav ing max(degree)70 一 14、查询所有学生的Snamev Cno和Degree列。 select sn ame,c no degree from stude nt join sco

10、re on stude nt.s no 二 score.s no; 一 15、查询所有学生的Snox Cname和Degree列。 select sno,cn ame,degree from Score join course on Score.c no 二 course.c no; 一 16、查询所有学生的Snamev Cname和Degree列。 select sn ame,c name,degree from stude nt join score on stude nt.s no 二 score.s no join course on Score.c no 二 course.c no;

11、 -17v查询“95033班所选课程的平均分。 select avg(degree) as avgdegree from score where sno in (select sno from stude nt where class 二95033) 18、假设使用如下命令建立了一个grade表： create table grade(low in t(3),upp in t(3),ra nk char(1) insert into grade values(90,100,* A) insert into grade values(80,89,1 B*) insert into grade v

12、alues(70,79,1 C*) insert into grade values(60,69, D) insert into grade values(0,59, E) -现查询所有同学的Snov Cno和rank列。 select sno,cno, (case whe n degree betwee n 90 and 100 the n TV whe ndegree between80 and89 thenB whe ndegree between70 and79 thenC whe ndegree between60 and69 thenD whe ndegree between0 a

13、nd 59 the n END) as rank from score; -19.查询选修“305”课程的成绩高于“ 109号同学成绩的所有同学的 记录。 select * from score where eno 二05a nd degree(select degree from score where sno 二109 and eno 二3-105); “20、查询score中选学多门课程的同学中分数为非最高分成绩的 记 录。 select * from score where sn o i n-选学多门课程的同学中分数为非 最 高分成绩的同学的全记录 (select sno from s

14、core group by sno hav ing coun t(c no )1-选学多 门课程的同学 in tersect-取交集为选学多门课程的同学中分数为非最高分成绩的同 学。 select disti net sno from score where sno not in(-分数为非最高分 成绩 的同学 select sno from score where degree=(seleet max(degree) from score)- 分数最高成绩的同学 -2K查询score中选学多门课程的同学中分数为非同课程最高分成绩 的记录。 方法1: select * from score w

15、here sno in-选学多门课程的同学中 分数 为非同课程最高分成绩的同学的全记录 (select sno from score group by sno hav ing coun t(c no )1- 选学多 门课程的同学 in tersect-取交集为选学多门课程的同学中分数为非同课程最高分成绩 的同学。 select disti net sno from score where sno not in(- 非同课程分数最 高成绩的同学 select disti net sno from score where degree in (- 同课程分数最高 成绩的同学 select max(

16、degree)from score group by eno)-同课程分数最高成 绩 方法2： select * from score where sno in-选学多门课程的同学中 分 数为非同课程最高分成绩的同学的全记录 (select sno from score group by sno hav ing eoun t(c no )1- 选学 多门课程的同学 in tersect -取交集为选学多门课程的同学中分数为同课程非最高分成绩 的同学 select disti net sno from score where sno not in - 选出非同课程最 高分成绩的同学 (selec

17、t dist inct sno from score as si where degree=(select max(degree) from score as s2 where sl.c no=s2.c no group by cn o);-使用关联子查询选出同课程最 高分成绩的同学 -22x查询1975年之后出生的学生的所学课程以及成绩。 select sn ame,C name,degree from stude nt join score on stude nt.s no 二 score.s no join course on score.c no 二 course.c no where

18、 sbirthday=,1975-01-01: -23、查询和学号为107的同学同年出生的所有学生的Sno、 Sname 和 Sbirthday 列。 select sno,sn ame,sbirthday from stude nt where datepart(year,sbirthday)= (select datepart(year,sbirthday) from stude nt where sno 二107)一 学 号为107的同学的出生年份 and sno not排除学号为107的同学 -24、查询 张旭”教师任课的学生成绩。 select degree from score w

19、here eno 二 (select eno from course join teacher on teacher.t no 二 course.t no where tname 二张旭);一张 旭老师所任课程 -25.查询选修某课程的同学人数多于5人的教师姓名 select tn ame from teacher 11/16 joi n course on teacher.t no 一 course.t no where eno in (select eno from score group by eno having count(*)5);-多于5名同学选修的课程 -26x查询95033班

20、和95031班全体学生的记录。 select * from stude nt where class in (95033；95031); -27v查询存在有85分以上成绩的课程Cno. select disti net cno from score where degree85; -2-计算机系教师 的教师编号 -29、查询 计算机系”与电子工程系”不同职称的教师的Tname和 Pro仁 select tname,prof from teacher where depart in(计算机系T电子工 程系计算机系”与电子工程系”所有教师Tname和Prof and prof not in -计算

21、机系”与 电子工程系”不同职称的教师Prof (select prof from teacher where depart =计算机系 in tersect select prof from teacher where depart =* 电子工程系)-计算机系”与 电 子工程系”相同职称的教师Prof -30、查询选修编号为“305课程且成绩至少高于一个选修编号为“345” 的同学的Cnox Sno和Degree,并按Degree从高到低次 序排序。 select eno,sno ,degree from score where eno二？105-选修编号为“305”课程的同学 and de

22、greeany -大于任意一个选修编号为345的同学的成绩 (select degree from score where eno 二3245)- 选修编号为345” 的同学的成绩 order by degree desc -31、查询选修编号为“305课程且成绩高于所有选修编号为“3 245”的同学的CnOsSno和Degree,并按Degree从高到低次序扫E序。 select eno,sno ,degree from score where eno二?3-105-选修编号为“305”课程的同学 and degreeall -大于所有选修编号为“345”的同学的成绩 (select deg

23、ree from score where eno 22*3-245)-选修编号为345” 的同学的成绩 order by degree desc 14 / -32x查询所有教师和同学的name、sex和birthday. select sn ame as n ame,ssex as sex,sbirthday as birthday from stude nt union select tn ame as n ame,tsex as sex,tbirthday as birthday from teacher -33x查询所有 女”教师和 女乃同学的names sex和birthday. se

24、lect sn ame as n ame,ssex as sex,sbirthday as birthday from student where ssex=* 女 union select tn ame as n ame,tsex as sex,tbirthday as birthday from teacher where tsex=* 女 -34、查询成绩比该课程平均成绩低的同学的成绩表。 select * from score as si where degree1 ； select Class,COUNT(*) from Stude nt where Ssex=男group by C

25、lass havi ng COUNT(*)=2 ； -3 select max(year(sbirthday)as max, min( year(sbirthday) as min from stude nt; -41、以班号和年龄从大到小的顺序查询Student表中的全部记 录。 select * from stude nt order by class desc,Sbirthday -42、查询男”教师及其所上的课程。 select tn ame,tsex,c name from teacher left join course on course.t no 二 teacher.t no

26、where tsex= 男 一43、查询最高分同学的Snox Cno和Degree列。 select stude nt.s no,cno ,degree from stude nt join Score on Score.s no 二 stude nt.s no where degree=(select max(degree) from score); -44x查询和李军”同性别的所有同学的Sname. select sn ame from stude nt where ssex=-与李军同性别的同学姓名 (select ssex from stude nt where sn ame=* 李军)一李军的性另 U and sname not in(李军)