学生选课数据库SQL语句练习题详细分解答案.docx
- 文档编号:28796862
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:13
- 大小:18.67KB
学生选课数据库SQL语句练习题详细分解答案.docx
《学生选课数据库SQL语句练习题详细分解答案.docx》由会员分享,可在线阅读,更多相关《学生选课数据库SQL语句练习题详细分解答案.docx(13页珍藏版)》请在冰豆网上搜索。
学生选课数据库SQL语句练习题详细分解答案
一、设有一数据库,包括四个表:
学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。
四个表的结构分别如表1-1的表
(一)~表(四)所示,数据如表1-2的表
(一)~表(四)所示。
用SQL语句创建四个表并完成相关题目。
表1-1数据库的表结构
表
(一)Student
属性名
数据类型
可否为空
含义
Sno
Char(3)
否
学号(主键)
Sname
Char(8)
否
学生XX
Ssex
Char
(2)
否
学生性别
Sbirthday
datetime
可
学生出生年月
Class
Char(5)
可
学生所在班级
表
(二)Course
属性名
数据类型
可否为空
含义
o
Char(5)
否
课程号(主键)
ame
Varchar(10)
否
课程名称
Tno
Char(3)
否
教师编号(外键)
表(三)Score
属性名
数据类型
可否为空
含义
Sno
Char(3)
否
学号(外键)
o
Char(5)
否
课程号(外键)
Degree
Decimal(4,1)
可
成绩
主码:
Sno+o
表(四)Teacher
属性名
数据类型
可否为空
含义
Tno
Char(3)
否
教师编号(主键)
Tname
Char(4)
否
教师XX
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
o
ame
Tno
3-105
计算机导论
825
3-245
操作系统
804
6-166
数字电路
856
9-888
高等数学
831
表(三)Score
Sno
o
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
X旭
男
1969-03-12
讲师
电子工程系
825
王萍
女
1972-05-05
助教
计算机系
831
X冰
女
1977-08-14
助教
电子工程系
--1、查询Student表中的所有记录的Sname、Ssex和Class列。
selectsname,ssex,classfromstudent;
--2、查询教师所有的单位即不重复的Depart列。
selectdistinctdepartfromTeacher;
--3、查询Student表的所有记录。
select*fromstudent;
--4、查询Score表中成绩在60到80之间的所有记录。
select*fromscorewheredegreebetween60and80;
--5、查询Score表中成绩为85,86或88的记录。
select*fromscorewheredegreein(85,86,88);
--6、查询Student表中“95031”班或性别为“女”的同学记录。
select*fromstudentwhereclass='95031'orssex='女';
--7、以Class降序查询Student表的所有记录。
select*fromstudentorderbyclassdesc;
--8、以o升序、Degree降序查询Score表的所有记录。
select*fromscoreorderbyo,degreedesc;
--9、查询“95031”班的学生人数。
selectclass,count(*)as学生人数fromstudent
groupbyclasshavingclass='95031';
--10、查询Score表中的最高分的学生学号和课程号。
(子查询或者排序)
selectsno,o,degree,
(selectmax(degree)fromscore)asmaxscore--计算最高分
fromscorewheredegree=(selectmax(degree)fromscore);
--11、查询‘3-105’号课程的平均分。
selectavg(degree)asavgdegree
fromscoregroupbyohavingo='3-105';
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
selectavg(degree)asavgdegreefromscoregroupbyo--按照课程分组取平均值
havingo=(selectofromscoregroupbyohavingcount(*)>=5)--至少有5名学生选修的课程
andolike'3%';--以3开头的课程
--13、查询最低分大于70,最高分小于90的Sno列。
selectsno,max(degree)asmaxdegree,min(degree)asmindegreefromScoregroupbysno
havingmax(degree)<90andmin(degree)>70
--14、查询所有学生的Sname、o和Degree列。
selectsname,o,degreefromstudent
joinscoreonstudent.sno=score.sno;
--15、查询所有学生的Sno、ame和Degree列。
selectsno,ame,degreefromScore
joincourseonScore.o=course.o;
--16、查询所有学生的Sname、ame和Degree列。
selectsname,ame,degreefromstudent
joinscoreonstudent.sno=score.sno
joincourseonScore.o=course.o;
--17、查询“95033”班所选课程的平均分。
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’)
--现查询所有同学的Sno、o和rank列。
selectsno,o,
(casewhendegreebetween90and100then'A'
whendegreebetween80and89then'B'
whendegreebetween70and79then'C'
whendegreebetween60and69then'D'
whendegreebetween0and59then'E'
END)asrankfromscore;
--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select*fromscorewhereo='3-105'anddegree>(selectdegreefromscorewheresno='109'ando='3-105');
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select*fromscorewheresnoin--选学多门课程的同学中分数为非最高分成绩的同学的全记录
(selectsnofromscoregroupbysnohavingcount(o)>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin(--分数为非最高分成绩的同学
selectsnofromscorewheredegree=(selectmax(degree)fromscore)))--分数最高成绩的同学
--21、查询score中选学多门课程的同学中分数为非同课程最高分成绩的记录。
方法1:
select*fromscorewheresnoin--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录
(selectsnofromscoregroupbysnohavingcount(o)>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为非同课程最高分成绩的同学。
selectdistinctsnofromscorewheresnonotin(--非同课程分数最高成绩的同学
selectdistinctsnofromscorewheredegreein(--同课程分数最高成绩的同学
selectmax(degree)fromscoregroupbyo)))--同课程分数最高成绩
方法2:
select*fromscorewheresnoin--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录
(selectsnofromscoregroupbysnohavingcount(o)>1--选学多门课程的同学
intersect--取交集为选学多门课程的同学中分数为同课程非最高分成绩的同学
selectdistinctsnofromscorewheresnonotin--选出非同课程最高分成绩的同学
(selectdistinctsnofromscoreass1
wheredegree=(selectmax(degree)fromscoreass2wheres1.o=s2.ogroupbyo)));--使用关联子查询选出同课程最高分成绩的同学
--22、查询1975年之后出生的学生的所学课程以及成绩。
selectsname,ame,degreefromstudent
joinscoreonstudent.sno=score.sno
joincourseonscore.o=course.o
wheresbirthday>='1975-01-01';
--23、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
selectsno,sname,sbirthdayfromstudentwheredatepart(year,sbirthday)=
(selectdatepart(year,sbirthday)fromstudentwheresno='107')--学号为107的同学的出生年份
andsnonotin('107');--排除学号为107的同学
--24、查询“X旭”教师任课的学生成绩。
selectdegreefromscorewhereo=
(selectofromcourse
jointeacheronteacher.tno=course.tno
wheretname='X旭');--X旭老师所任课程
--25、查询选修某课程的同学人数多于5人的教师XX。
selecttnamefromteacher
joincourseonteacher.tno=course.tno
whereoin(selectofromscore
groupbyohavingcount(*)>5);--多于5名同学选修的课程
--26、查询95033班和95031班全体学生的记录。
select*fromstudentwhereclassin('95033','95031');
--27、查询存在有85分以上成绩的课程o.
selectdistinctofromscorewheredegree>85;
--28、查询出“计算机系”教师所教课程的成绩表。
selectscore.o,degreefromscore
joincourseonscore.o=course.o
wheretnoin
(selecttnofromteacherwheredepart='计算机系');--计算机系教师的教师编号
--29、查询“计算机系”与“电子工程系”不同职称的教师的Tname和Prof。
selecttname,proffromteacherwheredepartin('计算机系','电子工程系')--“计算机系”与“电子工程系”所有教师Tname和Prof
andprofnotin--“计算机系”与“电子工程系”不同职称的教师Prof
(selectproffromteacherwheredepart='计算机系'
intersect
selectproffromteacherwheredepart='电子工程系')--“计算机系”与“电子工程系”相同职称的教师Prof
--30、查询选修编号为“3-105“课程且成绩至少高于一个选修编号为“3-245”的同学的o、Sno和Degree,并按Degree从高到低次序排序。
selecto,sno,degreefromscorewhere
o='3-105'--选修编号为“3-105”课程的同学
anddegree>any--大于任意一个选修编号为“3-245”的同学的成绩
(selectdegreefromscorewhereo='3-245')--选修编号为“3-245”的同学的成绩
orderbydegreedesc
--31、查询选修编号为“3-105“课程且成绩高于所有选修编号为“3-245”的同学的o、Sno和Degree,并按Degree从高到低次序排序。
selecto,sno,degreefromscorewhere
o='3-105'--选修编号为“3-105”课程的同学
anddegree>all--大于所有选修编号为“3-245”的同学的成绩
(selectdegreefromscorewhereo='3-245')--选修编号为“3-245”的同学的成绩
orderbydegreedesc
--32、查询所有教师和同学的name、sex和birthday.
selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudent
union
selecttnameasname,tsexassex,tbirthdayasbirthdayfromteacher
--33、查询所有“女”教师和“女”同学的name、sex和birthday.
selectsnameasname,ssexassex,sbirthdayasbirthdayfromstudentwheressex='女'
union
selecttnameasname,tsexassex,tbirthdayasbirthdayfromteacherwheretsex='女'
--34、查询成绩比该课程平均成绩低的同学的成绩表。
select*fromscoreass1wheredegree<
(selectavg(degree)fromscoreass2groupbyohavings1.o=s2.o)
--35、查询所有任课教师的Tname和Depart.
selecttname,departfromteacherwheretnoin
(selecttnofromcourse)--课程表中存在的教师编号
--36、查询所有未讲课的教师的Tname和Depart.
selecttname,departfromteacherwheretnoin
(selecttnofromcoursewhereonotin(selectofromcoursegroupbyo))
备注:
如果课程表中课程确定不为空,也可以如下编写:
selectTname,DepartfromTeacherwhereTnonotin
(selectTnofromCourse)
--37、查询至少有2名男生的班号。
selectclass,ssex,count(ssex)as男生人数fromstudentgroupbyclass,ssexhavingssex='男'andcount(ssex)>1;
selectClass,COUNT(*)fromStudentwhereSsex='男'groupbyClasshavingCOUNT(*)>=2;
--38、查询Student表中不姓“王”的同学记录。
select*fromstudentwheresnamenotlike'王%'
--39、查询Student表中每个学生的XX和年龄。
selectsname,datediff(year,Sbirthday,current_timestamp)as年龄fromstudent;
selectsname,datediff(year,Sbirthday,getdate())as年龄fromstudent;
selectsname,datepart(year,getdate())-datepart(year,Sbirthday)as年龄fromstudent;
--40、查询Student表中最大和最小的Sbirthday日期值。
selectdatepart(year,max(sbirthday))asmax,datepart(year,min(sbirthday))asminfromstudent;
selectmax(year(sbirthday))asmax,min(year(sbirthday))asminfromstudent;
--41、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select*fromstudentorderbyclassdesc,Sbirthday
--42、查询“男”教师及其所上的课程。
selecttname,tsex,amefromteacherleftjoincourseoncourse.tno=teacher.tnowheretsex='男'
--43、查询最高分同学的Sno、o和Degree列。
selectstudent.sno,o,degreefromstudent
joinScoreonScore.sno=student.sno
wheredegree=(selectmax(degree)fromscore);
--44、查询和“李军”同性别的所有同学的Sname.
selectsnamefromstudentwheressex=--与李军同性别的同学XX
(selectssexfromstudentwheresname='李军')--李军的性别
andsnamenotin('李军')--从中去除李军
--45、查询和“李军”同性别并同班的同学Sname.
selectsnamefromstudentwhere--与李军同性别并同班的同学XX
ssex=(selectssexfromstudentwheresname='李军')----与李军同性别的同学XX
andclass=(selectclassfromstudentwheresname='李军')----与李军同班的同学XX
andsnamenotin('李军');--从中去除李军
--46、查询所有选修“计算机导论”课程的“男”同学的成绩表。
方法1:
selectdegreefromscore
joinstudentonstudent.sno=score.sno
joincourseoncourse.o=score.o
wheressex='男'andame='计算机导论'
方法2:
selectdegreefromscorewhere
Snoin(selectsnofromstudentwheress
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 学生 选课 数据库 SQL 语句 练习题 详细 分解 答案
![提示](https://static.bdocx.com/images/bang_tan.gif)