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

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

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

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

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

5、 select 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

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

7、询或者排序） select sno,o,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 o having o=3-105;- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select avg(degree) as avgdegree from

8、 score group by o -按照课程分组取平均值 having o= (select o from score group by o having count(*)=5)-至少有5名学生选修的课程 and o 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、o和Degree列。 select sname

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

10、33”班所选课程的平均分。 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 into

11、grade values(60,69,D)insert into grade values(0,59,E)-现查询所有同学的Sno、o和rank列。 select sno,o, (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 from sco

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

13、同学 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-选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录 (select sno

14、from score group by sno having count(o)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 o)-同课程分数最高成绩 方法2：select * from score where sn

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

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

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

18、XX。 select tname from teacher join course on teacher.tno=course.tno where o in (select o from score group by o having count(*)5);- 多于5名同学选修的课程- 26、查询95033班和95031班全体学生的记录。 select * from student where class in(95033,95031);- 27、查询存在有85分以上成绩的课程o. select distinct o from score where degree85;- 28、查询出“计算机

19、系”教师所教课程的成绩表。 select score.o,degree from score join course on score.o=course.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”的同学的o、Sno和Degree,并按Degree从高到低次序排序。select o,sno,degree from score whereo=3-105-选修编号为“3-105”课程的同学and degreeany - 大于任意一

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

22、re o=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 as sex,sbirth

23、day 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表中不姓“王”的同学记录。s

24、elect * from student where sname not like 王%- 39、查询Student表中每个学生的XX和年龄。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 student；- 40、查询S

25、tudent表中最大和最小的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、查询“男”教师及其所上的课程。se

26、lect tname,tsex,ame from teacher left join course on course.tno=teacher.tno where tsex=男- 43、查询最高分同学的Sno、o和Degree列。select student.sno,o,degree from student join Score on Score.sno=student.snowhere degree=(select max(degree) from score);- 44、查询和“李军”同性别的所有同学的Sname.select sname from student where ssex=

27、 -与李军同性别的同学XX(select ssex from student where sname=李军)-李军的性别and sname not in(李军)-从中去除李军- 45、查询和“李军”同性别并同班的同学Sname.select sname from student where -与李军同性别并同班的同学XXssex=(select ssex from student where sname=李军)-与李军同性别的同学XXand class=(select class from student where sname=李军)-与李军同班的同学XXand sname not in (李军);-从中去除李军- 46、查询所有选修“计算机导论”课程的“男”同学的成绩表。方法1：select degree from score join student on student.sno=score.sno join course on course.o=score.owhere ssex=男 and ame=计算机导论方法2：select degree from score whereSno in(select sno from student where ss