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

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

2、生所在班级 表（二） Course 属性名 数据类型 可否为空 含 义 Cno Char(5) 否 课程号（主键 ） Cname Varchar(10) 否 课程名称 Tno Char(3) 否 教师 编号（外 键 ） 表（三） Score 属性名 数据类型 可否为空 含 义 Sno Char(3) 否 学号（外键 ） Cno Char(5) 否 课程号（外数据库 wzm 键 ） Degree Decimal(4,1) 可 成绩 主码： Sno+ Cno 表（四） Teacher 属性名 数据类型 可否为空 含 义 Tno Char(3) 否 教 师 编号（主 键 ） Tname Char(4

3、) 否 教 师 姓名 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 10

4、3 陆君 男 1974-06-03 95031 表（二） Course 数据库 wzm Cno Cname Tno 3-105 计算机导论 825 3-245 操作系统 804 6-166 数字电路 856 9-888 高等数学 831 表（三） Score Sno Cno 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

5、 表（四） Teacher Tno Tname Tsex Tbirthday Prof Depart 804 李诚 男 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; 数据库 wzm - 2、查询教师所有的单位即不重复的 Depart列。 select distinct

6、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

7、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; 数据库 wzm - 9、查询 “95031”班的学生人数。 select class,count(*) as 学生人数 from student group by class having class=95031; - 10、查询 S

8、core表中的最高分的学生学号和课程号。（子查询或者排序） 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开头的课程的平均分数。

9、select avg(degree) 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列。 数据库 wzm select sno,max(degree)as maxdegree,min(degree) as mindegree from Score group by sno h

10、aving max(degree)70 - 14、查询所有学生的 Sname、 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 sc

11、ore on student.sno=score.sno join course on So=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 i

12、nto grade values(80,89, B ) 数据库 wzm insert into grade values(70,79, 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 the

13、n C when degree between 60 and 69 then D when degree between 0 and 59 then E END) 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

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

15、21、查询 score中选学多门课程的同学中分数为非同课程最高分成绩的记录。 方法 1： 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 fro

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

17、where sno not in - 选出非同课程最高分成绩的同学 数据库 wzm (select distinct sno from score as s1 where degree=(select max(degree) 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=

18、o where sbirthday=1975-01-01; - 23、查询和学号为 107的同学同年出生的所有学生的 Sno、Sname和 Sbirthday列。 select sno,sname,sbirthday from student where datepart(year,sbirthday)= (select datepart(year,sbirthday) from student where sno=107)-学号为 107的同学的出生年份 and sno not in(107);-排除学号为 107的同学 - 24、查询 “张旭 ”教师任课的学生成绩。 select degr

19、ee from score where cno= (select cno from course join teacher on teacher.tno=course.tno where tname=张旭 );-张旭老师所任课程 数据库 wzm - 25、查询选修某课程的同学人数多于 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名同学选修的课程 -

20、26、查询 95033班和 95031班全体学生的记录。 select * from student where class in(95033,95031); - 27、查询存在有 85分以上成绩的课程 Cno. select distinct cno from score 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和 Prof