学生选课数据库SQL语句练习题

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

2、nameVarchar(10)否课程名称TnoChar(3)否教师编号（外键）表（三）Score属性名数据类型可否为空含义SnoChar(3)否学号（外键）CnoChar(5)否课程号（外键）DegreeDecimal(4,1)可成绩主码：Sno+Cno表（四）Teacher属性名数据类型可否为空含义TnoChar(3)否教师编号（主键）TnameChar(4)否教师姓名TsexChar(2)否教师性别Tbirthdaydatetime可教师出生年月ProfChar(6)可职称DepartVarchar(10)否教师所在部门表1-2数据库中的数据SnoSnameSsexSbirthdaycla

3、ss108曾华男1977-090195033105匡明男1975-100295031107王丽女1976-012395033101李军男1976-022095033109土芳女1975-021095031103陆君男1974-060395031表（一）Student表（二）CourseCnoCnameTno3-105计算机导论8253-245操作系统8046-166数字电路8569-888高等数学831表（三）ScoreSnoCnoDegree1033-245861053-245751093-245681033-105921053-105881093-105761013-105641073-1

4、05911083-105781016-166851076-166791086-16681表（四）TeacherTnoTnameTsexTbirthdayProfDepart804李诚男1958-1202副教授计算机系856张旭男1969-0312讲师电子工程系825王萍女1972-0505助教计算机系831刘冰女1977-08-14助教电子工程系-1、查询Student表中的所有记录的SnameSsex和Class列。selectsname,ssex,classfromstudent;-2、查询教师所有的单位即不重复的Depart列。selectdistinetdepartfromTeache

5、r;-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表的所有记录

6、。select*fromstudentorderbyclassdesc;-8、以Cno升序、Degree降序查询Score表的所有记录。select*fromscoreorderbycno,degreedesc;-9、查询“95031”班的学生人数。selectclass,count(*)as学生人数fromstudentgroupbyclasshavingclass二'95031：-10、查询Score表中的最高分的学生学号和课程号。(子查询或者排序)selectsno,cno,degree,(selectmax(degree)fromscore)asmaxscore-计算最高分fr

7、omscorewheredegree=(selectmax(degree)fromscore);-11、查询3-105'号课程的平均分。selectavg(degree)asavgdegreefromscoregroupbyenohavingeno二'3-105：-12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。selectavg(degree)asavgdegreefromscoregroupbyeno-按照课程分组取平均值havingeno二(selectenofromscoregroupbyenohavingcount(*)>=5)-至少有5

8、名学生选修的课程andenolike'3%'-以3开头的课程-13、查询最低分大于70，最高分小于90的Sno列selectsno,max(degree)asmaxdegree,min(degree)asmindegreefromScoregroupbysnohavingmax(degree)<90andmin(degree)>70-14、查询所有学生的SnameCno和Degree列。selectsname,cno,degreefromstudentjoinscoreon二；-15、查询所有学生的SnoCname和Degree列。selectsno,cname,d

9、egreefromScorejoincourseon二；-16、查询所有学生的SnameCname和Degree列。selectsname,cname,degreefromstudentjoinscoreon二joincourseon二；-17、查询“95033”班所选课程的平均分。selectavg(degree)asavgdegreefromscorewheresnoin(selectsnofromstudentwhereclass二'95033')18、假设使用如下命令建立了一个grade表：createtablegrade(lowint(3),uppint(3),ran

10、kchar(1)insertintogradevalues(90,100,insertintogradevalues(80,89,'B')insertintogradevalues(70,79,'C)insertintogradevalues(60,69,'D)insertintogradevalues(0,59,'E)-现查询所有同学的Snc、Cno和rank列。selectsno,cno,(casewhendegreebetween90and100then'A'whendegreebetween80and89then'B

11、9;whendegreebetween70and79thenCwhendegreebetween60and69then'D'whendegreebetween0and59then'E'END)asrankfromscore;-19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。select*fromscorewherecno二'3-105'anddegree>(selectdegreefromscorewheresno二'109'andcno二'3-105');-20、查询scor

12、e中选学多门课程的同学中分数为非最高分成绩的记录。select*fromscorewheresnoin-选学多门课程的同学中分数为非最高分成绩的同学的全记录(selectsnofromscoregroupbysnohavingcount(cno)>1-选学多门课程的同学intersect-取交集为选学多门课程的同学中分数为非最高分成绩的同学。selectdistinctsnofromscorewheresnonotin(-分数为非最高分成绩的同学selectsnofromscorewheredegree=(selectmax(degree)fromscore)-分数最高成绩的同学-21、

13、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。方法1:select*fromscorewheresnoin-选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录(selectsnofromscoregroupbysnohavingcount(cno)>1-选学多门课程的同学intersect-取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。selectdistinctsnofromscorewheresnonotin(-日非同课程分数最高成绩的同学selectdistinctsnofromscorewheredegreein(-同课程分数最高成绩的

14、同学selectmax(degree)fromscoregroupbyeno)-同课程分数最高成绩选学多门课程的方法2：select*fromscorewheresnoin-同学中分数为非同课程最高分成绩的同学的全记录(selectsnofromscoregroupbysnohavingcount(cno)>1-选学多门课程的同学intersect-取交集为选学多门课程的同学中分数为同课程非最高分成绩的同学选出非selectdistinetsnofromscorewheresnonotin-同课程最高分成绩的同学(selectdistinctsnofromscoreassiwherede

15、gree=(selectmax(degree)fromscoreass2where=groupbycno);-使用关联子查询选出同课程最高分成绩的同学-22、查询1975年之后出生的学生的所学课程以及成绩。selectsname,Cname,degreefromstudentjoinscoreon二joincourseon二wheresbirthday>='1975-01-01：-23、查询和学号为107的同学同年出生的所有学生的SnoSname和Sbirthday列。selectsno,sname,sbirthdayfromstudentwheredatepart(year,s

16、birthday)=(selectdatepart(year,sbirthday)fromstudentwheresno二'107')-学号为107的同学的出生年份andsnonotin('107');-排除学号为107的同学-24、查询“张旭”教师任课的学生成绩selectdegreefromscorewhereeno二jointeacheron二wheretname='张旭');-张旭老师所任课程-25、查询选修某课程的同学人数多于5人的教师姓名。selecttnamefromteacherjoincourseon二whereenoin(se

17、lectenofromscoregroupbycnohavingcount(*)>5);-多于5名同学选修的课程-26、查询95033班和95031班全体学生的记录。select*fromstudentwhereclassin('95033','95031');-27、查询存在有85分以上成绩的课程Cno.selectdistinetcnofromscorewheredegree>85;-28、查询出“计算机系”教师所教课程的成绩表。select,degreefromscorejoincourseon二wheretnoin(selecttnofrom

18、teacherwheredepart二'计算机系');-计算机系教师的教师编号-29、查询“计算机系”与“电子工程系”不同职称的教师的Tname和Prof。selecttname,proffromteacherwheredepartin('计算机系','电子工程系')-Tname和Prof“计算机系”与“电子工程系”所有教师andprofnotin-师Prof“计算机系”与“电子工程系”不同职称的教(selectproffromteacherwheredepart='计算机系'intersectselectproffromteac

19、herwheredepart='电子工程系')-“计算机系”与“电子工程系”相同职称的教师Prof-30、查询选修编号为“3-105“课程且成绩至少高于一个选修编号为“3-245”的同学的Cnc、Sno和Degree,并按Degree从高到低次序排序。selectcno,sno,degreefromscorewherecno二'3-105'-选修编号为“3-105”课程的同学anddegree>any-大于任意一个选修编号为“3-245”的同学的成绩(selectdegreefromscorewhereeno二'3-245')-选修编号为“

20、3-245”的同学的成绩orderbydegreedesc-31、查询选修编号为“3-105“课程且成绩高于所有选修编号为“3-245”的同学的Cnc、Sno和Degree,并按Degree从高到低次序排序。selectcno,sno,degreefromscorewherecno二'3-105'-选修编号为“3-105”课程的同学anddegree>all-大于所有选修编号为“3-245”的同学的成绩(selectdegreefromscorewhereeno二'3-245')-选修编号为“3-245”的同学的成绩orderbydegreedesc-32

21、、查询所有教师和同学的namesex和birthday.selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudentunionselecttnameasname,tsexassex,tbirthdayasbirthdayfromteacher-33、查询所有“女”教师和“女”同学的namesex和birthday.selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudentwheressex='女'unionselecttnameasname,tsexassex,tb

22、irthdayasbirthdayfromteacherwheretsex='女'-34、查询成绩比该课程平均成绩低的同学的成绩表。select*fromscoreassiwheredegree<(selectavg(degree)fromscoreass2groupbyenohaving=-35、查询所有任课教师的Tname和Depart.selecttname,departfromteacherwheretnoin(selecttnofromcourse)-课程表中存在的教师编号-36、查询所有未讲课的教师的Tname和Depart.selecttname,depar

23、tfromteacherwheretnoin(selecttnofromcoursewhereenonotin(selectenofromcoursegroupbyeno)备注：如果课程表中课程确定不为空，也可以如下编写：selectTnameQepartfromTeacherwhereTnonotin(selectTnofromCourse)-37、查询至少有2名男生的班号。selectclass,ssex,count(ssex)as男生人数fromstudentgroupbyclass,ssexhavingssex='男'andcount(ssex)>1selectC

24、lass,COUNT(*)fromStudentwhereSsex='男'groupbyClasshavingCOUNT(*)>=2；-38、查询Student表中不姓“王”的同学记录。select*fromstudentwheresnamenotlike'王%'-39、查询Student表中每个学生的姓名和年龄。selectsname,datediff(year,Sbirthday,current_timestamp)as年龄fromstudent:selectsname,datediff(year,Sbirthday,getdate()as年龄from

25、student;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,cnamefromteacherleftjoincourseon二wheretsex='男'-43、查询最高分同学的SnoCno和Degree列。select,cno,degreefromstudentjoinScoreon二wheredegree=(selectmax(degree)fromscore);-44、查询和“李军”同性