1、SQL 练习题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 Sname, Course, Grade from ScoreWhere Grade between 60 and 805、 查询
2、Score 表中成绩为 85，86 或 88 的记录。Select Sname, Course, Grade from ScoreWhere Grade in (85, 86, 88)6、 查询 Student 表中“95031”班或性别为“女”的同学记录。Select * from StudentWhere Cno = 95031 or Sage = f7、 以 Class 降序查询 Student 表的所有记录。Select * from StudentOrder by Class DESC8、 以 Cno 升序、Degree 降序查询 Score 表的所有记录。Select * from
3、 ScoreOrder by Cno ASC, Degree DESC9、 查询“95031”班的学生人数。Select count(*) from StudentWhere Cno = 9503110、查询 Score 表中的最高分的学生学号和课程号。Select Sno, Cno from ScoreWhere max(grade)11、查询3-105号课程的平均分。Select avg(grade) from ScoreWhere Cno = 3-10512、查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。Select avg(grade) from Sco
4、reWhere count(distinct Sno) 5, Cno like(3%)13、查询最低分大于 70，最高分小于 90 的 Sno 列。select Sno from score group by Sno having min(degree)70 and max(degree)y.degree and y.sno=109and o=3-105;20、查询“张旭“教师任课的学生成绩。select cno,sno,degree from score where cno=(select o from course x,teacher y wherex.tno=y.tno and y.tn
5、ame=张旭);21、查询选修某课程的同学人数多于 5 人的教师姓名。select tname from teacher where tno in(select x.tno from course x,score y where o=ogroup by x.tno having count(x.tno)5);22、查询 95033 班和 95031 班全体学生的记录。select * from student where class in(95033,95031);23、查询存在有 85 分以上成绩的课程 Cno.select distinct cno from score where deg
6、ree in (select degree from score where degree85);24、查询出“计算机系“教师所教课程的成绩表。select * from score where cno in(select o from course x,teacher y where y.tno=x.tno andy.depart=计算机系);25、查询所有教师和同学的 name、sex 和 birthday.select tname,tsex,tbirthday from teacherunion select sname,ssex,sbirthday from student;26、查询
7、所有“女”教师和“女”同学的 name、sex 和 birthday.select tname,tsex,tbirthday from teacher where tsex=女union select sname,ssex,sbirthday from student where ssex=女;27、查询成绩比该课程平均成绩低的同学的成绩表。select * from score a where degree=2;31、查询 Student 表中不姓“王”的同学记录。select * from student where sname not like王_;32、查询 Student 表中最大和
8、最小的 Sbirthday 日期值。select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) fromstudent)33、以班号和年龄从大到小的顺序查询 Student 表中的全部记录。select class,sname,sbirthday from student order by class desc,sbirthday;34、查询“男”教师及其所上的课程。select x.tname,ame from teacher x,course y where x.tno=y.tno a
9、nd x.tsex=男;35、查询最高分同学的 Sno、Cno 和 Degree 列。select * from score where degree=(select max(degree)from score);36、查询和“李军”同性别的所有同学的 Sname.select sname from student where ssex=(select ssex from student where sname=李军);37、查询所有选修“计算机导论”课程的“男”同学的成绩表select * from score where sno in(select sno from student where ssex=男) and cno=(selectcno from coursewhere cname=计算机导论);38、求某班级学生人数多于三人的记录081002050302 章峥
