学生选课数据库SQL语句练习题详细分解答案.docx
《学生选课数据库SQL语句练习题详细分解答案.docx》由会员分享，可在线阅读，更多相关《学生选课数据库SQL语句练习题详细分解答案.docx（13页珍藏版）》请在冰豆网上搜索。
学生选课数据库SQL语句练习题详细分解答案
一、设有一数据库，包括四个表：
学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。
四个表的结构分别如表1-1的表
（一）~表（四）所示，数据如表1-2的表
（一）~表（四）所示。
用SQL语句创建四个表并完成相关题目。
表1-1数据库的表结构
表
（一）Student
属性名
数据类型
可否为空
含义
Sno
Char（3）
否
学号（主键）
Sname
Char（8）
否
学生XX
Ssex
Char
（2）
否
学生性别
Sbirthday
datetime
可
学生出生年月
Class
Char（5）
可
学生所在班级
表
（二）Course
属性名
数据类型
可否为空
含义
o
Char（5）
否
课程号（主键）
ame
Varchar（10）
否
课程名称
Tno
Char（3）
否
教师编号（外键）
表（三）Score
属性名
数据类型
可否为空
含义
Sno
Char（3）
否
学号（外键）
o
Char（5）
否
课程号（外键）
Degree
Decimal（4,1）
可
成绩
主码：
Sno+o
表（四）Teacher
属性名
数据类型
可否为空
含义
Tno
Char（3）
否
教师编号（主键）
Tname
Char（4）
否
教师XX
Tsex
Char
（2）
否
教师性别
Tbirthday
datetime
可
教师出生年月
Prof
Char（6）
可
职称
Depart
Varchar（10）
否
教师所在部门
表1-2数据库中的数据
表
（一）Student
Sno
Sname
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
o
ame
Tno
3-105
计算机导论
825
3-245
操作系统
804
6-166
数字电路
856
9-888
高等数学
831
表（三）Score
Sno
o
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
Tname
Tsex
Tbirthday
Prof
Depart
804
李诚
男
1958-12-02
副教授
计算机系
856
X旭
男
1969-03-12
讲师
电子工程系
825
王萍
女
1972-05-05
助教
计算机系
831
X冰
女
1977-08-14
助教
电子工程系
--1、查询Student表中的所有记录的Sname、Ssex和Class列。
selectsname,ssex,classfromstudent;
--2、查询教师所有的单位即不重复的Depart列。
selectdistinctdepartfromTeacher;
--3、查询Student表的所有记录。
select*fromstudent;
--4、查询Score表中成绩在60到80之间的所有记录。
select*fromscorewheredegreebetween60and80;
--5、查询Score表中成绩为85，86或88的记录。
select*fromscorewheredegreein（85,86,88）;
--6、查询Student表中“95031”班或性别为“女”的同学记录。
select*fromstudentwhereclass='95031'orssex='女';
--7、以Class降序查询Student表的所有记录。
select*fromstudentorderbyclassdesc;
--8、以o升序、Degree降序查询Score表的所有记录。
select*fromscoreorderbyo,degreedesc;
--9、查询“95031”班的学生人数。
selectclass,count（*）as学生人数fromstudent
groupbyclasshavingclass='95031';
--10、查询Score表中的最高分的学生学号和课程号。
（子查询或者排序）
selectsno,o,degree,
（selectmax（degree）fromscore）asmaxscore--计算最高分
fromscorewheredegree=（selectmax（degree）fromscore）;
--11、查询‘3-105’号课程的平均分。
selectavg（degree）asavgdegree
fromscoregroupbyohavingo='3-105';
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
selectavg（degree）asavgdegreefromscoregroupbyo--按照课程分组取平均值
havingo=（selectofromscoregroupbyohavingcount（*）>=5）--至少有5名学生选修的课程
andolike'3%';--以3开头的课程
--13、查询最低分大于70，最高分小于90的Sno列。
selectsno,max（degree）asmaxdegree,min（degree）asmindegreefromScoregroupbysno
havingmax（degree）<90andmin（degree）>70
--14、查询所有学生的Sname、o和Degree列。
selectsname,o,degreefromstudent
joinscoreonstudent.sno=score.sno;
--15、查询所有学生的Sno、ame和Degree列。
selectsno,ame,degreefromScore
joincourseonScore.o=course.o;
--16、查询所有学生的Sname、ame和Degree列。
selectsname,ame,degreefromstudent
joinscoreonstudent.sno=score.sno
joincourseonScore.o=course.o;
--17、查询“95033”班所选课程的平均分。
selectavg（degree）asavgdegreefromscorewheresnoin（selectsnofromstudentwhereclass='95033'）
18、假设使用如下命令建立了一个grade表：
createtablegrade（lowint（3）,uppint（3）,rankchar
（1））
insertintogradevalues（90,100,’A’）
insertintogradevalues（80,89,’B’）
insertintogradevalues（70,79,’C’）
insertintogradevalues（60,69,’D’）
insertintogradevalues（0,59,’E’）
--现查询所有同学的Sno、o和rank列。
selectsno,o,
（casewhendegreebetween90and100then'A'
whendegreebetween80and89then'B'
whendegreebetween70and79then'C'
whendegreebetween60and69then'D'
whendegreebetween0and59then'E'
END）asrankfromscore;
--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select*fromscorewhereo='3-105'anddegree>（selectdegreefromscorewheresno='109'ando='3-105'）;
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select*fromscorewheresnoin--选学多门课程的同学中分数为非最高分成绩的同学的全记录
（selectsnofromscoregroupbysnohavingcount（o）>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin（--分数为非最高分成绩的同学
selectsnofromscorewheredegree=（selectmax（degree）fromscore）））--分数最高成绩的同学
--21、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。
方法1：
select*fromscorewheresnoin--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录
（selectsnofromscoregroupbysnohavingcount（o）>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin（--非同课程分数最高成绩的同学
selectdistinctsnofromscorewheredegreein（--同课程分数最高成绩的同学
selectmax（degree）fromscoregroupbyo）））--同课程分数最高成绩
方法2：
select*fromscorewheresnoin--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录
（selectsnofromscoregroupbysnohavingcount（o）>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为同课程非最高分成绩的同学
selectdistinctsnofromscorewheresnonotin--选出非同课程最高分成绩的同学
（selectdistinctsnofromscoreass1
wheredegree=（selectmax（degree）fromscoreass2wheres1.o=s2.ogroupbyo）））;--使用关联子查询选出同课程最高分成绩的同学
--22、查询1975年之后出生的学生的所学课程以及成绩。
selectsname,ame,degreefromstudent
joinscoreonstudent.sno=score.sno
joincourseonscore.o=course.o
wheresbirthday>='1975-01-01';
--23、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
selectsno,sname,sbirthdayfromstudentwheredatepart（year,sbirthday）=
（selectdatepart（year,sbirthday）fromstudentwheresno='107'）--学号为107的同学的出生年份
andsnonotin（'107'）;--排除学号为107的同学
--24、查询“X旭”教师任课的学生成绩。
selectdegreefromscorewhereo=
（selectofromcourse
jointeacheronteacher.tno=course.tno
wheretname='X旭'）;--X旭老师所任课程
--25、查询选修某课程的同学人数多于5人的教师XX。
selecttnamefromteacher
joincourseonteacher.tno=course.tno
whereoin（selectofromscore
groupbyohavingcount（*）>5）;--多于5名同学选修的课程
--26、查询95033班和95031班全体学生的记录。
select*fromstudentwhereclassin（'95033','95031'）;
--27、查询存在有85分以上成绩的课程o.
selectdistinctofromscorewheredegree>85;
--28、查询出“计算机系”教师所教课程的成绩表。
selectscore.o,degreefromscore
joincourseonscore.o=course.o
wheretnoin
（selecttnofromteacherwheredepart='计算机系'）;--计算机系教师的教师编号
--29、查询“计算机系”与“电子工程系”不同职称的教师的Tname和Prof。
selecttname,proffromteacherwheredepartin（'计算机系','电子工程系'）--“计算机系”与“电子工程系”所有教师Tname和Prof
andprofnotin--“计算机系”与“电子工程系”不同职称的教师Prof
（selectproffromteacherwheredepart='计算机系'
intersect
selectproffromteacherwheredepart='电子工程系'）--“计算机系”与“电子工程系”相同职称的教师Prof
--30、查询选修编号为“3-105“课程且成绩至少高于一个选修编号为“3-245”的同学的o、Sno和Degree,并按Degree从高到低次序排序。
selecto,sno,degreefromscorewhere
o='3-105'--选修编号为“3-105”课程的同学
anddegree>any--大于任意一个选修编号为“3-245”的同学的成绩
（selectdegreefromscorewhereo='3-245'）--选修编号为“3-245”的同学的成绩
orderbydegreedesc
--31、查询选修编号为“3-105“课程且成绩高于所有选修编号为“3-245”的同学的o、Sno和Degree,并按Degree从高到低次序排序。
selecto,sno,degreefromscorewhere
o='3-105'--选修编号为“3-105”课程的同学
anddegree>all--大于所有选修编号为“3-245”的同学的成绩
（selectdegreefromscorewhereo='3-245'）--选修编号为“3-245”的同学的成绩
orderbydegreedesc
--32、查询所有教师和同学的name、sex和birthday.
selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudent
union
selecttnameasname,tsexassex,tbirthdayasbirthdayfromteacher
--33、查询所有“女”教师和“女”同学的name、sex和birthday.
selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudentwheressex='女'
union
selecttnameasname,tsexassex,tbirthdayasbirthdayfromteacherwheretsex='女'
--34、查询成绩比该课程平均成绩低的同学的成绩表。
select*fromscoreass1wheredegree<
（selectavg（degree）fromscoreass2groupbyohavings1.o=s2.o）
--35、查询所有任课教师的Tname和Depart.
selecttname,departfromteacherwheretnoin
（selecttnofromcourse）--课程表中存在的教师编号
--36、查询所有未讲课的教师的Tname和Depart.
selecttname,departfromteacherwheretnoin
（selecttnofromcoursewhereonotin（selectofromcoursegroupbyo））
备注：
如果课程表中课程确定不为空，也可以如下编写：
selectTname,DepartfromTeacherwhereTnonotin
（selectTnofromCourse）
--37、查询至少有2名男生的班号。
selectclass,ssex,count（ssex）as男生人数fromstudentgroupbyclass,ssexhavingssex='男'andcount（ssex）>1；
selectClass,COUNT（*）fromStudentwhereSsex='男'groupbyClasshavingCOUNT（*）>=2；
--38、查询Student表中不姓“王”的同学记录。
select*fromstudentwheresnamenotlike'王%'
--39、查询Student表中每个学生的XX和年龄。
selectsname,datediff（year,Sbirthday,current_timestamp）as年龄fromstudent；
selectsname,datediff（year,Sbirthday,getdate（））as年龄fromstudent；
selectsname,datepart（year,getdate（））-datepart（year,Sbirthday）as年龄fromstudent；
--40、查询Student表中最大和最小的Sbirthday日期值。
selectdatepart（year,max（sbirthday））asmax,datepart（year,min（sbirthday））asminfromstudent;
selectmax（year（sbirthday））asmax,min（year（sbirthday））asminfromstudent;
--41、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select*fromstudentorderbyclassdesc,Sbirthday
--42、查询“男”教师及其所上的课程。
selecttname,tsex,amefromteacherleftjoincourseoncourse.tno=teacher.tnowheretsex='男'
--43、查询最高分同学的Sno、o和Degree列。
selectstudent.sno,o,degreefromstudent
joinScoreonScore.sno=student.sno
wheredegree=（selectmax（degree）fromscore）;
--44、查询和“李军”同性别的所有同学的Sname.
selectsnamefromstudentwheressex=--与李军同性别的同学XX
（selectssexfromstudentwheresname='李军'）--李军的性别
andsnamenotin（'李军'）--从中去除李军
--45、查询和“李军”同性别并同班的同学Sname.
selectsnamefromstudentwhere--与李军同性别并同班的同学XX
ssex=（selectssexfromstudentwheresname='李军'）----与李军同性别的同学XX
andclass=（selectclassfromstudentwheresname='李军'）----与李军同班的同学XX
andsnamenotin（'李军'）;--从中去除李军
--46、查询所有选修“计算机导论”课程的“男”同学的成绩表。
方法1：
selectdegreefromscore
joinstudentonstudent.sno=score.sno
joincourseoncourse.o=score.o
wheressex='男'andame='计算机导论'
方法2：
selectdegreefromscorewhere
Snoin（selectsnofromstudentwheress
升级会员 