学生选课数据库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)否课程号（

2、主键）CnameVarchar(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数据库中的数据表（一）StudentSnoSna

3、meSsexSbirthdayclass108曾华男1977-09-0195033105匡明男1975-10-0295031107王丽女1976-01-2395033101李军男1976-02-2095033109王芳女1975-02-1095031103陆君男1974-06-0395031 表（二）CourseCnoCnameTno3-105计算机导论8253-245操作系统8046-166数字电路8569-888高等数学831表（三）ScoreSnoCnoDegree1033-245861053-245751093-245681033-105921053-105881093-1057610

4、13-105641073-105911083-105781016-166851076-166791086-16681表（四）TeacherTnoTnameTsexTbirthdayProfDepart804李诚男1958-12-02副教授计算机系856张旭男1969-03-12讲师电子工程系825王萍女1972-05-05助教计算机系831刘冰女1977-08-14助教电子工程系- 1、查询Student表中的所有记录的Sname、Ssex和Class列。 select sname,ssex,class from student;- 2、查询教师所有的单位即不重复的Depart列。 selec

5、t distinct depart from Teacher;- 3、查询Student表的所有记录。 select * from student;- 4、查询Score表中成绩在60到80之间的所有记录。 select * from score where degree between 60 and 80;- 5、查询Score表中成绩为85，86或88的记录。 select * from score where degree in(85,86,88);- 6、查询Student表中“95031”班或性别为“女”的同学记录。 select * from student where class

6、 = 95031 or ssex=女;- 7、以Class降序查询Student表的所有记录。 select * from student order by class desc;- 8、以Cno升序、Degree降序查询Score表的所有记录。 select * from score order by cno,degree desc;- 9、查询“95031”班的学生人数。 select class,count(*) as 学生人数 from student group by class having class=95031;- 10、查询Score表中的最高分的学生学号和课程号。（子查询或

7、者排序） select sno,cno,degree, (select max(degree) from score) as maxscore-计算最高分 from score where degree= (select max(degree) from score);- 11、查询3-105号课程的平均分。 select avg(degree) as avgdegree from score group by cno having cno=3-105;- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select avg(degree) as avgdegree

8、from score group by cno -按照课程分组取平均值 having cno= (select cno from score group by cno having count(*)=5)-至少有5名学生选修的课程 and cno like 3%;-以3开头的课程- 13、查询最低分大于70，最高分小于90的Sno列。 select sno,max(degree)as maxdegree,min(degree) as mindegree from Score group by sno having max(degree)70- 14、查询所有学生的Sname、Cno和Degre

9、e列。 select sname,cno,degree from student join score on student.sno=score.sno;- 15、查询所有学生的Sno、Cname和Degree列。 select sno,cname,degree from Score join course on So=o;- 16、查询所有学生的Sname、Cname和Degree列。 select sname,cname,degree from student join score on student.sno=score.sno join course on So=o;- 17、查询“9

10、5033”班所选课程的平均分。 select avg(degree) as avgdegree from score where sno in(select sno from student where class=95033)18、假设使用如下命令建立了一个grade表：create table grade(low int(3),upp int(3),rank char(1)insert into grade values(90,100,A)insert into grade values(80,89,B)insert into grade values(70,79,C)insert int

11、o grade values(60,69,D)insert into grade values(0,59,E)-现查询所有同学的Sno、Cno和rank列。 select sno,cno, (case when degree between 90 and 100 then A when degree between 80 and 89 then B when degree between 70 and 79 then C when degree between 60 and 69 then D when degree between 0 and 59 then EEND) as rank fr

12、om score;- 19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select * from score where cno=3-105and degree(select degree from score where sno=109 and cno=3-105);- 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 select * from score where sno in-选学多门课程的同学中分数为非最高分成绩的同学的全记录 (select sno from score group by sno having count(c

13、no)1-选学多门课程的同学 intersect-取交集为选学多门课程的同学中分数为非最高分成绩的同学。 select distinct sno from score where sno not in( -分数为非最高分成绩的同学 select sno from score where degree=(select max(degree) from score)-分数最高成绩的同学 - 21、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。 方法1：select * from score where sno in-选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录

14、(select sno from score group by sno having count(cno)1-选学多门课程的同学 intersect-取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。 select distinct sno from score where sno not in(-非同课程分数最高成绩的同学 select distinct sno from score where degree in (-同课程分数最高成绩的同学 select max(degree)from score group by cno)-同课程分数最高成绩 方法2：select * fro

15、m score where sno in-选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录 (select sno from score group by sno having count(cno)1- 选学多门课程的同学 intersect - 取交集为选学多门课程的同学中分数为同课程非最高分成绩的同学 select distinct sno from score where sno not in - 选出非同课程最高分成绩的同学(select distinct sno from score as s1 where degree=(select max(degree) from s

16、core as s2 where o=o group by cno);- 使用关联子查询选出同课程最高分成绩的同学- 22、查询1975年之后出生的学生的所学课程以及成绩。 select sname,Cname,degree from student join score on student.sno=score.sno join course on o=o where sbirthday=1975-01-01;- 23、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 select sno,sname,sbirthday from student whe

17、re datepart(year,sbirthday)= (select datepart(year,sbirthday) from student where sno=107)-学号为107的同学的出生年份 and sno not in(107);-排除学号为107的同学- 24、查询“张旭”教师任课的学生成绩。 select degree from score where cno= (select cno from course join teacher on teacher.tno=course.tno where tname=张旭);-张旭老师所任课程- 25、查询选修某课程的同学人数

18、多于5人的教师姓名。 select tname from teacher join course on teacher.tno=course.tno where cno in (select cno from score group by cno having count(*)5);- 多于5名同学选修的课程- 26、查询95033班和95031班全体学生的记录。 select * from student where class in(95033,95031);- 27、查询存在有85分以上成绩的课程Cno. select distinct cno from score where degr

19、ee85;- 28、查询出“计算机系”教师所教课程的成绩表。 select o,degree from score join course on o=o where tno in (select tno from teacher where depart=计算机系);-计算机系教师的教师编号- 29、查询“计算机系”与“电子工程系”不同职称的教师的Tname和Prof。 select tname,prof from teacher where depart in(计算机系,电子工程系)-“计算机系”与“电子工程系”所有教师Tname和Profand prof not in -“计算机系”与“电

20、子工程系”不同职称的教师Prof(select prof from teacher where depart =计算机系intersectselect prof from teacher where depart =电子工程系)-“计算机系”与“电子工程系”相同职称的教师Prof- 30、查询选修编号为“3-105“课程且成绩至少高于一个选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。select cno,sno,degree from score wherecno=3-105-选修编号为“3-105”课程的同学and degreeany - 大

21、于任意一个选修编号为“3-245”的同学的成绩(select degree from score where cno=3-245)-选修编号为“3-245”的同学的成绩order by degree desc- 31、查询选修编号为“3-105“课程且成绩高于所有选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。select cno,sno,degree from score wherecno=3-105-选修编号为“3-105”课程的同学and degreeall - 大于所有选修编号为“3-245”的同学的成绩(select degree fr

22、om score where cno=3-245)-选修编号为“3-245”的同学的成绩order by degree desc- 32、查询所有教师和同学的name、sex和birthday.select sname as name,ssex as sex,sbirthday as birthday from studentunionselect tname as name,tsex as sex,tbirthday as birthday from teacher- 33、查询所有“女”教师和“女”同学的name、sex和birthday.select sname as name,ssex

23、 as sex,sbirthday as birthday from student where ssex=女unionselect tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=女- 34、查询成绩比该课程平均成绩低的同学的成绩表。select * from score as s1 where degree1；select Class,COUNT(*) from Student where Ssex=男group by Class having COUNT(*)=2；- 38、查询Student

24、表中不姓“王”的同学记录。select * from student where sname not like 王%- 39、查询Student表中每个学生的姓名和年龄。select sname,datediff(year,Sbirthday,current_timestamp)as 年龄 from student；select sname,datediff(year,Sbirthday,getdate() as 年龄 from student；select sname,datepart(year,getdate()-datepart(year,Sbirthday) as 年龄 from st

25、udent；- 40、查询Student表中最大和最小的Sbirthday日期值。select datepart(year,max(sbirthday)as max,datepart(year,min(sbirthday) as min from student;select max(year(sbirthday)as max,min(year(sbirthday) as min from student;- 41、以班号和年龄从大到小的顺序查询Student表中的全部记录。select * from student order by class desc,Sbirthday - 42、查询“

26、男”教师及其所上的课程。select tname,tsex,cname from teacher left join course on course.tno=teacher.tno where tsex=男- 43、查询最高分同学的Sno、Cno和Degree列。select student.sno,cno,degree from student join Score on Score.sno=student.snowhere degree=(select max(degree) from score);- 44、查询和“李军”同性别的所有同学的Sname.select sname from student whe