学生选课数据库SQL语句练习题详细分解答案.docx
《学生选课数据库SQL语句练习题详细分解答案.docx》由会员分享,可在线阅读,更多相关《学生选课数据库SQL语句练习题详细分解答案.docx(11页珍藏版)》请在冰豆网上搜索。
学生选课数据库SQL语句练习题详细分解答案
一、设有一数据库,包括四个表:
学生表(Student)、课程表
(Cours®、成绩表(Score)以及教师信息表(Teacher)。
四个表的结构分别如表1-1的表
(一)~表(四)所示,数据如表1-2的表
(一)~表(四)所示。
用SQL语句创建四个表并完成相关题目。
表1-1数据库的表结构
表
(一)Student
属性名
数据类型
可否为空
含义
Sno
Char(3)
否
学号(主键)
Sname
Char(8)
否
学生姓名
Ssex
Char
(2)
否
学生性别—
Sbirthday
datetime
可
学生出生年月
Class
Char(5)
可
学生所在班级
表
(二)Course
属性名
数据类型
可否为空
含义
Cno
Char(5)
否
课程号(主键)
Cname
Varchar(10)
否
课程名称
Tno
Char(3)
否
教师编号(外键)
表(三)Score
属性名
数据类型
可否为空
含义
Sno
Char(3)
否
学号(外键)
Cno
Char(5)
否
课程号(外键)
Degree
Decimal(4,1)
可
成绩
主码:
Sno+Cno
表(四)Teacher
属性名
数据类型
可否为空
含义
Tno
Char(3)
否
教师编号(主键)
Tname
Char(4)
否
教师姓名
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
103
陆君
男
1974-06
03
95031
表
(二)Course
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
表(四)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表中的所有记录的SnameSsex和Class列。
selectsname,ssex,classfromstudent;
--2、查询教师所有的单位即不重复的Depart列。
selectdistinetdepartfromTeacher;
--3、查询Student表的所有记录。
select*fromstudent;
--4、查询Score表中成绩在60到80之间的所有记录。
select*fromscorewheredegreebetween60and80;
--5、查询Score表中成绩为85,86或88的记录。
select*fromscorewheredegreein(85,86,88);
--6、查询Student表中“9503班或性别为女”的同学记录
select*fromstudentwhereclass='95031'orssex=女:
--7、以Class降序查询Student表的所有记录。
select*fromstudentorderbyclassdesc;
--&以Cno升序、Degree降序查询Score表的所有记录。
select*fromscoreorderbycno,degreedesc;
--9、查询“9503班的学生人数。
selectclass,count(*)as学生人数fromstudent
groupbyclasshavingclass二'95031:
--10、查询Score表中的最高分的学生学号和课程号。
(子查询或者排序)
selectsno,cno,degree,
(selectmax(degree)fromscore)asmaxscore计算最高分
fromscorewheredegree=(selectmax(degree)fromscore);
--11、查询-105号课程的平均分。
selectavg(degree)asavgdegree
fromscoregroupbyenohavingeno二'3-105:
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
selectavg(degree)asavgdegreefromscoregroupbyeno按照课程分组取平均值
havingeno二(selectenofromscoregroupbyenohavingcount(*)>=5)--至少有5名学生选修的课程
andenolike'3%';-以3开头的课程
--13、查询最低分大于70,最高分小于90的Sno列selectsno,max(degree)asmaxdegree,min(degree)asmindegreefrom
Scoregroupbysno
havingmax(degree)<90andmin(degree)>70
--14、查询所有学生的SnameCno和Degree列。
selectsname,cno,degreefromstudent
joinscoreon二;
--15、查询所有学生的SnoCname和Degree列。
selectsno,cname,degreefromScore
joincourseon二;
--16、查询所有学生的SnameCname和Degree列。
selectsname,cname,degreefromstudent
joinscoreon二
joincourseon二;
--17、查询“9503班所选课程的平均分。
selectavg(degree)asavgdegreefromscorewheresnoin(selectsnofromstudentwhereclass二'95033')
18、假设使用如下命令建立了一个grade表:
createtablegrade(lowint(3),uppint(3),rankchar
(1))
insertintogradevalues(90,100'A)
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'
whendegreebetween70and79thenC
whendegreebetween60and69then'D'
whendegreebetween0and59then'E'
END)asrankfromscore;
--19、查询选修“305”课程的成绩高于“109号同学成绩的所有同学的记录。
select*fromscorewherecno二'3-105'anddegree>(selectdegreefromscorewheresno二'109'andcno二'3-105');
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select*fromscorewheresnoin--选学多门课程的同学中分数为非最高分成绩的同学的全记录
(selectsnofromscoregroupbysnohavingcount(cno)>1选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin(-分数为非最高分成绩的同学
selectsnofromscorewheredegree=(selectmax(degree)fromscore)))--分数最高成绩的同学
--21、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。
方法1:
select*fromscorewheresnoin--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录
(selectsnofromscoregroupbysnohavingcount(cno)>1选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin(-非同课程分数最高成绩的同学
selectdistinetsnofromscorewheredegreein(-同课程分数最高成绩的同学
selectmax(degree)fromscoregroupbycno)))-同课程分数最高成绩
方法2:
select*fromscorewheresnoin--选学多门课程的同学中分数