1、题1、 查询 Student 表中的所有记录的 Sname、Ssex 和 Class 列。2、 查询教师所有的单位即不重复的 Depart 列。3、 查询 Student 表的所有记录。4、 查询 Score 表中成绩在 60 到 80 之间的所有记录。5、 查询 Score 表中成绩为 85，86 或 88 的记录。6、 查询 Student 表中“95031”班或性别为“女”的同学记录。7、 以 Class 降序查询 Student 表的所有记录。8、 以 Cno 升序、Degree 降序查询 Score 表的所有记录。9、 查询“95031”班的学生人数。10、查询 Score 表中的最
2、高分的学生学号和课程号。11、查询3-105号课程的平均分。12、查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。13、查询最低分大于 70，最高分小于 90 的 Sno 列。14、查询所有学生的 Sname、Cno 和 Degree 列。15、查询所有学生的 Sno、Cname 和 Degree 列。16、查询所有学生的 Sname、Cname 和 Degree 列。17、查询“95033”班所选课程的平均分。18、假设使用如下命令建立了一个 grade 表：create table grade(low numeric(3,0),upp numeric(3),r
3、ank 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 列。19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。20、查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录。21、查询成绩高于学号为“1
4、09”、课程号为“3-105”的成绩的所有记录。22、查询和学号为 108 的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列。23、查询“张旭“教师任课的学生成绩。24、查询选修某课程的同学人数多于 5 人的教师姓名。25、查询 95033 班和 95031 班全体学生的记录。26、查询存在有 85 分以上成绩的课程 Cno.27、查询出“计算机系“教师所教课程的成绩表。28、查询“计算机系”与“电子工程系“不同职称的教师的 Tname 和 Prof。29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的 Cno、Sno 和 Degree
5、,并按 Degree 从高到低次序排序。30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno 和 Degree.31、查询所有教师和同学的 name、sex 和 birthday.32、查询所有“女”教师和“女”同学的 name、sex 和 birthday.33、查询成绩比该课程平均成绩低的同学的成绩表。34、查询所有任课教师的 Tname 和 Depart.35 查询所有未讲课的教师的 Tname 和 Depart.36、查询至少有 2 名男生的班号。37、查询 Student 表中不姓“王”的同学记录。38、查询 Student 表中每个学生的
6、姓名和年龄。39、查询 Student 表中最大和最小的 Sbirthday 日期值。40、以班号和年龄从大到小的顺序查询 Student 表中的全部记录。41、查询“男”教师及其所上的课程。42、查询最高分同学的 Sno、Cno 和 Degree 列。43、查询和“李军”同性别的所有同学的 Sname.44、查询和“李军”同性别并同班的同学 Sname.45、查询所有选修“计算机导论”课程的“男”同学的成绩表下面是参考答案：SQL 语句练习题参考答案1.select sname,ssex,class from student;2.select distinct(depart) from te
7、acher;orselect distinct depart from teacher;3.select * from student; 4. select * from score where degree between 60 and 80; or select * from score where degree=60 and degree5;or select cno,avg(degree) from score where cno like 3% group by cno having count(*)5;13.select sno from score group by sno ha
8、ving min(degree)70 and max(degree)(select degree from score where sno=109 and cno=3-105);or select x.* from score x,score y where o=3-105 and x.degreey.degree and y.sno=109 and o=3-105;20.分析：1.成绩非本科最高 select * from score where degree not in (select max(degree) from score group by cno)选学一门以上的学生成绩：sel
9、ect sno from score group by sno having count(*)1;2.查询成绩非本科最高并且选 1 门以上的学生的成绩:select * from score where degree not in(select max(degree) from score group by cno) group by sno having count(*)1;or select * from (select * from score where degree not in(select max(degree) from score group by cno) as aa gr
10、oup by sno having count(*)=2;通用答案：select sno from( select * from score where degree not in(select max(degree) from score group by cno) as aa group by sno having count(*)=2;21. select * from score where degree(select degree from score where sno=109 and cno=3-105);or select x.* from score x,score y wh
11、ere x.degreey.degree and y.sno=109 and o=3-105;22.select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno=108);23. select * from score where cno in(select cno from course where tno=(select tno from teacher where tname=张旭);or select cno,sno,degree
12、from score where cno=(select o from course x,teacher y where x.tno=y.tno and y.tname=张旭);24. select tname from teacher where tno in (select x.tno from course x,score y where o=o and o in (select cno from score group by cno having count(*)5);or select tname from teacher where tno in(select tno from c
13、ourse where cno in(select cno from score group by cno having count(*)5);or select tname from teacher where tno in(select x.tno from course x,score y where o=o group by x.tno having count(x.tno)5);25. select * from student where class in (95033,95031);or select * from student where class=95033 or cla
14、ss=95031;26.select distinct cno from score where degree in (select degree from score where degree85);27. select * from score where cno in (select cno from course where tno in (select tno from teacher where depart=计算机系);or select * from score where cno in(select o from course x,teacher y where y.tno=
15、x.tno and y.depart=计算机系);28.select tname,prof from teacher where depart=计算机系 and prof not in (select prof from teacher where depart=电子工程系);29. select * from score where cno=3-105 and degree(select min(degree) from score where cno=3-245) order by degree desc;or select * from score where cno=3-105and
16、degreeany(select degree from score where cno=3-245) order by degree desc;30. select * from score where cno=3-105 and degree(select max(degree) from score where cno=3-245);orselect * from score where cno=3-105 and degreeall(select degree from score where cno=3-245);31.select sname as name,ssex as sex
17、,sbirthday as birthday from studentunionselect tname,tsex,tbirthday from teacher;32.select sname as name,ssex as sex,sbirthday as birthday from student where ssex=女unionselect tname,tsex,tbirthday from teacher where tsex=女;33.select * from score a where degree=2;37. select * from student where sname
18、 not like 王%;38.select sname as 姓名,2010-year(sbirthday) as 年龄 from student;39. select sbirthday from student where sbirthday in (select min(sbirthday) from student)unionselect sbirthday from student where sbirthday in (select max(sbirthday) from student);or select sbirthday from student where sbirth
19、day=(select min(sbirthday) from student) or sbirthday=(select max(sbirthday) from student);40.select * from student order by class desc,sbirthday;41.select cname,tname from course,teacher where course.tno=teacher.tno and tsex=男;orselect ame,teacher.tname from course,teacher where course.tno=teacher.
20、tno and tsex=男;orselect cname,tname from course x,teacher y where x.tno=y.tno and y.tsex=男;orselect x.tname,ame from teacher x,course y where x.tno=y.tno and x.tsex=男;42. select * from score where degree in (select max(degree) from score);43.select sname from student where ssex=(select ssex from stu
21、dent where sname=李军);44. select sname from student where ssex=(select ssex from student where sname=李军) and class=(select class from student where sname=李军);45.select * from score where sno in(select sno from student where ssex=男) and cno in(select cno from course where cname=计算机导论);注意：20 题的前两个答案在 sql server 中不支持，在 mysql 中支持如果题答案中有错误，请记得及时通知我让我纠正错误！我的邮箱地址：chuxue_
