sql数据查询练习题

一、设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）表（四）所示，数据如表1-2的表（一）表（四）所示。用SQL语句创建四个表并完成相关题目。表1-1数据库的表结构表（一）Student (学生表)属性名数据类型可否为空含义Snovarchar (20)否学号（主码）Snamevarchar (20)否学生姓名Ssexvarchar (20)否学生性别Sbirthdaydatetime可学生出生年月Classvarchar (20)可学生所在班级表（二）Course（课程表）属性名数据类型可否为空含义Cnovarchar (20)否课程号（主码）Cnamevarchar (20)否课程名称Tnovarchar (20)否教工编号（外码）表（三）Score(成绩表)属性名数据类型可否为空含义Snovarchar (20)否学号（外码）Cnovarchar (20)否课程号（外码）DegreeDecimal(4,1)可成绩主码：Sno+ Cno表（四）Teacher(教师表)属性名数据类型可否为空含义Tnovarchar (20)否教工编号（主码）Tnamevarchar (20)否教工姓名Tsexvarchar (20)否教工性别Tbirthdaydatetime可教工出生年月Profvarchar (20)可职称Departvarchar (20)否教工所在部门表1-2数据库中的数据表（一）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-105881093-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 Student2、查询教师所有的单位即不重复的Depart列。select distinct Depart from teacher3、查询Student表的所有记录。select * from student4、查询Score表中成绩在60到80之间的所有记录。select * from Score where Degree between 60and 805、查询Score表中成绩为85，86或88的记录。select * from Score where Degree=85 orDegree=86 or Degree=886、查询Student表中“95031”班或性别为“女”的同学记录。select * from student where Class=95031or Ssex=女7、以Class降序查询Student表的所有记录。select * from student order by Class desc8、以Cno升序、Degree降序查询Score表的所有记录。select * from Score order by cno asc,Degree desc9、查询“95031”班的学生人数。select count(*) from student whereclass=9503110、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）select Cno,sno from Score where degree in(select MAX(Degree)from Score)11、查询每门课的平均成绩。select avg(degree), cno from Score group bycno12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select AVG(Degree) from Score group by cnohaving count(cno)5 and Cno like 3%13、查询分数大于70，小于90的Sno列。Select Sno from Score where Degree between70 and 9014、查询所有学生的Sname、Cno和Degree列。15、查询所有学生的Sno、Cname和Degree列。16、查询所有学生的Sname、Cname和Degree列。17、查询“95033”班学生的平均分。select avg(degree) from score,student wherescore.sno=student.sno and class=9503318、假设使用如下命令建立了一个grade表：create table grade(low int(3),upp int(3),rankchar(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列。19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。select * from Score where cno=3-105 andDegree(select degree from Score where cno=3-105 and Sno=109)20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。select * from score a where sno in (Selectsno from score group by sno having count(sno)1) and degree(select degree from Score where Sno=109 and cno=3-105)22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select Sno,Sname,Sbirthday from Studentwhere YEAR(Sbirthday)=(select YEAR(sbirthday)from Student where Sno=108)23、查询“张旭“教师任课的学生成绩。Select Degree from score where cno=( Selectcno from course where tno=(Select tno from teacher where tname=张旭)24、查询选修某课程的同学人数多于5人的教师姓名。Select tname from teacher where Tno=(selectTno from Course where Cno=(select Cno from Score group by Cno havingCOUNT(*)5)25、查询95033班和95031班全体学生的记录。select * from Student where Class=95033or Class=9503126、查询存在有85分以上成绩的课程Cno.select distinct cno from score whereDegree8527、查询出“计算机系“教师所教课程的成绩表。select degree from score where Cnoin(select cno from Course where Tno in(select tno from Teacher where Depart=计算机系)28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。select Tname,Prof from teacher where profnot in (select prof from teacher where depart = 计算机系and prof in (select prof from teacher where depart =电子工程系)29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。select Cno,Sno,Degree from Score whereCno=3-105 and Degree(select MAX(degree)from Score where Cno=3-245)order by Degree desc30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.select Cno,Sno,Degree from Score whereCno=3-105 and Degree(select MAX(degree)from Score where Cno=3-245)31、查询所有教师和同学的name、sex和birthday.select sname as name,ssex as sex,sbirthdayas birthday from Studentunionselect tname ,tsex ,tbirthday from teacher32、查询所有“女”教师和“女”同学的name、sex和birthday.select sname as name,ssex as sex,sbirthdayas birthday from Student where Ssex=女unionselect tname ,tsex ,tbirthday from teacherwhere tsex=女33、查询成绩比该课程平均成绩低的同学的成绩表。Select degree from score a wheredegree=237、查询Student表中不姓“王”的同学记录。select * from student where sname not like王%38、查询Student表中每个学生的姓名和年龄。Selectsname,YEAR(GETDATE()-YEAR(sbirthday) as年龄 from student39、查询Student表中最大和最小的Sbirthday日期值。select MAX(Sbirthday),MIN(Sbirthday) fromStudent40、以班号和年龄从大到小的顺序查询Student表中的全部记录。select * from Student order by Class desc,Sbirthday ASC41、查询“男”教师及其所上的课程。Select tname,cname from teacher join courseon teacher.tno=course.tno and teacher.tsex=男42、查询最高分同学的Sno、Cno和Degree列。select Sno,Cno,Degree from Score whereDegree in(select MAX(Degree) from Score)43、查询和“李军”同性别的所有同学的Sname.Select sname from student wheressex=(select ssex from student where sname=李军)44、查询和“李军”同性别并同班的同学Sname.Select sname from student wheressex=(select ssex from student where sname=李军) and Class=(select class from student wheresname=李军)45、查询所有选修“计算机导论”课程的“男”同学的成绩表。 select degree from Score where Snoin(select Sno from Student where Ssex=男)and Cno in (select Cno from Course where cname=计算机导论)