《学生选课数据库SQL语句练习题(详细分解答案)(共17页).doc》由会员分享，可在线阅读，更多相关《学生选课数据库SQL语句练习题(详细分解答案)(共17页).doc（16页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）表（四）所示，数据如表1-2的表（一）表（四）所示。用SQL语句创建四个表并完成相关题目。表1-1数据库的表结构 表（一）Student 属性名数据类型可否为空含 义SnoChar(3)否学号（主键）SnameChar(8)否学生姓名SsexChar(2)否学生性别Sbirthdaydatetime可学生出生年月ClassChar(5)可学生所在班级表（二）Course属性名数据类型可否为空含 义Cn
2、oChar(5)否课程号（主键）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数据库中的数据表（一）
3、StudentSnoSnameSsexSbirthdayclass108曾华男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-1058
4、81093-105761013-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、查询教师所有的单位即不重复的D
5、epart列。 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 studen
6、t where class = 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)
8、as avgdegree 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、查询所有学生的Sn
9、ame、Cno和Degree列。 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 S
10、o=o;- 17、查询“95033”班所选课程的平均分。 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
11、,C)insert into 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 EEN
12、D) as rank from 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 h
13、aving count(cno)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
15、：select * from 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(d
16、egree) from score 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 fro
17、m student where 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 sco
19、re where degree85;- 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
20、in -“计算机系”与“电子工程系”不同职称的教师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
21、degreeany - 大于任意一个选修编号为“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”的同学的成绩(sel
22、ect degree from 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
23、 as name,ssex 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；-
24、 38、查询Student表中不姓“王”的同学记录。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)
25、as 年龄 from student；- 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,Sbirt
26、hday - 42、查询“男”教师及其所上的课程。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.sele
27、ct sname from student where ssex= -与李军同性别的同学姓名(select ssex from student where sname=李军)-李军的性别and sname not in(李军)-从中去除李军- 45、查询和“李军”同性别并同班的同学Sname.select sname from student where -与李军同性别并同班的同学姓名ssex=(select ssex from student where sname=李军)-与李军同性别的同学姓名and class=(select class from student where sname
28、=李军)-与李军同班的同学姓名and sname not in (李军);-从中去除李军- 46、查询所有选修“计算机导论”课程的“男”同学的成绩表。方法1：select degree from score join student on student.sno=score.sno join course on o=owhere ssex=男 and cname=计算机导论方法2：select degree from score whereSno in(select sno from student where ssex=男)and cno in(select cno from course
29、where cname=计算机导论)- 47、查询出选修课程号为3-245和6-166的课程的学生学号与姓名select student.Sno,sname from student join score on student.Sno=score.snowhere cno in(3-245,6-166)- 48、查询出没有选修课程号为3-245和6-166的课程的学生学号与姓名select student.Sno,sname from student join score on student.Sno=score.snowhere cno not in(3-245,6-166)专心-专注-专业