学生选课数据库SQL语句练习题

一、设有一数据库，包括四个表：学生表（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)可学生所在班级表（二）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数据库中的数据表（一）StudentSno Sname Ssex Sbirthday class95033108曾华男1977-09-01105匡明男1975-10-950310295033107王丽女1976-01-2395033101李军男1976-02-20109王芳女1975-02-950311095031103陆君男1974-06-03表（二）CourseCno Cname Tno3-105计算机导论8253-245操作系统8046-166数字电路8569-888高等数学831表（三）ScoreSno Cno Degree1033-245861053-245751093-245681033-105921053-105881093-105761013-105641073-105911083-105781016-166851076-166791086-16681表（四）TeacherTno Tname Tsex Tbirthday Prof Depart804李诚男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、查询教师所有的单位即不重复的Depart列。

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 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 studentgroup by class having class='95031';-- 10、查询Score表中的最高分的学生学号和课程号。

（子查询或者排序）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 avgdegreefrom score group by cno having cno='3-105';-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

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列。

select sno,max(degree)as maxdegree,min(degree) as mindegree from Score group by snohaving max(degree)<90 and min(degree)>70-- 14、查询所有学生的Sname、Cno和Degree列。

select sname,cno,degree from studentjoin score on =;-- 15、查询所有学生的Sno、Cname和Degree列。

select sno,cname,degree from Scorejoin course on =;-- 16、查询所有学生的Sname、Cname和Degree列。

select sname,cname,degree from studentjoin score on =join course on =;-- 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,’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 'E'END) as rank from score;-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from score where cno='3-105'and 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 having 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--选学多门课程的同学中分数为非同课程最高分成绩的同学的全记录(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：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 s1where degree=(select max(degree) from score as s2 where = group by cno)));-- 使用关联子查询选出同课程最高分成绩的同学-- 22、查询1975年之后出生的学生的所学课程以及成绩。