《SQL 练习题 (2).docx》由会员分享，可在线阅读，更多相关《SQL 练习题 (2).docx（6页珍藏版）》请在金锄头文库上搜索。
1、-1、查询Student表中的所有记录的Sname、Ssex和Class列。use studb goselect Sname,Ssex,Class from STUDENT -2、查询教师所有的单位即不重复的Depart列。use studb goselect Distinct Depart from TEACHER -3、查询Student表的所有记录。use studb goselect * from STUDENT-4、查询Score表中成绩在到之间的所有记录。use studb goselect *from SCORE where DEGREE between 60 and 80-5、
2、查询Score表中成绩为，或的记录。use studb goselect *from SCORE where DEGREE in (85,86,88)-6、查询Student表中“”班或性别为“女”的同学记录。use studb goselect * from STUDENTwhere CLASS =95007 or SSEX =女-7、以Class降序查询Student表的所有记录。use studb goselect * from STUDENTorder by CLASS desc-8、以Cno升序、Degree降序查询Score表的所有记录。use studb goselect * f
3、rom SCOREorder by CNO ,DEGREE desc-9、查询“”班的学生人数。use studb goselect (COUNT(SNO)from STUDENTwhere class =95031-10、查询Score表中的最高分的学生学号和课程号。use studb goselect sno,cno from score where DEGREE=(select MAX(DEGREE)from SCORE)-11、查询-105号课程的平均分。use studb goselect AVG (DEGREE)from SCORE where CNO =3-105-12、查询Sc
4、ore表中至少有名学生选修的并以开头的课程的平均分数。use studb goselect AVG(DEGREE)from SCOREwhere cno in(select cNO from SCORE group by CNO having COUNT(sno)=5)and CNO like 3% -13、查询最低分大于，最高分小于的Sno列。use studb goselect sno from SCOREwhere SNO in(select SNO from score group by SNO having MIN(DEGREE)70 and MAX (DEGREE)y.DEGREE
5、 and y.SNO =109and y.CNO=3-105-20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。USE studb GOselect * from score jwhere j.SNO in (select SNO from SCORE group by SNO having count(*)=2) and j.DEGREE y.DEGREE and y.SNO =109and y.CNO=3-105-22、查询和学号为的同学同年出生的所有学生的Sno、Sname和Sbirthday列。use studb goselect SNO ,SNAME ,SBIR
6、THDAY from STUDENT where year(SBIRTHDAY) =(select year(SBIRTHDAY ) from STUDENT where SNO =108)-23、查询“张旭“教师任课的学生成绩。use studb goselect a.SNO ,a.DEGREE from SCORE a inner join course b on a.CNO =b.CNO inner join TEACHER c on b.TNO =c.TNO where tname=张旭-24、查询选修某课程的同学人数多于人的教师姓名。use studb goselect TNAME
7、from TEACHER where TNO in(select x.TNO from COURSE x inner join SCORE y on x.CNO =y.CNO group by x.TNO having count(x.TNO )5) -25、查询班和班全体学生的记录。use studb goselect * from STUDENT where CLASS =95033 or CLASS =95031-26、查询存在有分以上成绩的课程Cno.use studb goselect CNO from SCORE where DEGREE =85-27、查询出“计算机系“教师所教课
8、程的成绩表。use studb goselect * from SCORE where CNO in(select COURSE .CNO from COURSE ,TEACHER where TEACHER .TNO = COURSE .TNO and TEACHER .DEPART =计算机系) -28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。 use studb goselect TNAME,PROF from TEACHERwhere DEPART =计算机系 and PROF not in(select PROF from TEACHER where DEPART =电子工程系) -29、查询选修编号为“-105“课程且成绩至少高于选修编号为“-245”的同学的 Cno、Sno和Degree,并按Degree从高到低次序排序。 use studb go select * from SCORE where CNO =3-105 and DEGREE any (select DEGREE from SCORE where CNO =3-245) order by DEGREE desc
