最完整的sql练习+答案Word文档下载推荐.docx

1、tsex varchar（2） not null,-教工出生日期tbirthday datetime,-职称prof varchar（6）,-所在部门depart varchar（10）create table course-课程号cno varchar（5） not null primary key,-课程名称cname varchar（10） not null,tno varchar（3） references teacher（tno）create table scoresno varchar（3） not null references student（sno）,cno varchar（

2、5） not null references course（cno）,-成绩degree decimal（4,1）insert into studentvalues（108,曾华男1977-09-0195033105匡明1975-10-0295031107王丽女1976-01-23101军1976-02-20109王芳1975-02-10103陆君1974-06-03insert into teacher804诚1958-12-02副教授计算机系856旭1969-03-12讲师电子工程系825王萍1972-05-05助教831冰1958-08-14insert into course3-105

3、计算机导论3-245操作系统6-166数字电路9-888高等数学insert into score867568928876649178857981select * from studentselect * from teacherselect * from courseselect * from score -1、 查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from student-2、 查询教师所有的单位即不重复的Depart列。select distinct depart from teacher-3、 查询Stu

4、dent表的所有记录。-4、 查询Score表中成绩在60到80之间的所有记录。select * from score where degree between 60 and 80-5、 查询Score表中成绩为85，86或88的记录。select * from score where degree=or degree=-6、 查询Student表中“95031”班或性别为“女”的同学记录。select * from student where class= or ssex=-7、 以Class降序查询Student表的所有记录。select * from student order by c

5、lass desc-8、 以Cno升序、Degree降序查询Score表的所有记录。select* from score order byo ,degree desc-9、 查询“95031”班的学生人数。select count（sno） from student where class=-10、查询Score表中的最高分的学生学号和课程号。select sno,cno,degree from score where degree in（select max（degree） from score）-11、查询3-105号课程的平均分。select avg（degree） from score

6、 whereo=-12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select avg（degree） from score whereo like3% ando in （selecto from score group byo having count（cno）5）-13、查询最低分大于70，最高分小于90的Sno列。select sno from score where degree between 70 and 90-14、查询所有学生的Sname、Cno和Degree列。select sname,cno,degree from score,student whe

7、re student.sno=score.sno-15、查询所有学生的Sno、Cname和Degree列。selectame,student.sno,degree from score,student,course where student.sno=score.sno and o=o-16、查询所有学生的Sname、Cname和Degree列select sname,cname,degree from score,student,course where student.sno=score.sno and o=o-17、查询“95033”班所选课程的平均分。select 平均分=avg（de

8、gree） from course,student ,score where class= and o=o and student.sno=score.sno-18、假设使用如下命令建立了一个grade表：-create table grade（low int,upp int,rank varchar（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

9、grade values（0,59,E-现查询所有同学的Sno、Cno和rank列。select student.sno,cno,rank from score,student,grade where student.sno=score.sno and degree between low and upp -19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。/无关子查询select score.sno,sname,ssex,sbirthday,class,o,cname,degree from score,student,course where student.

10、sno=score.sno and o=o and o=and degree（select degree from score where sno=ando=-20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。select sno,cno,degree from score where degree not in （select max（degree）from score group byo） order by sno-21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select * from score where degree ando=-22

11、、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select sno,sname,sbirthday from student where year（sbirthday）=（select year（sbirthday） from student where sno=-23、查询“旭“教师任课的学生成绩。select sno,o,degree from score,course,teacher where o=o and course.tno=teacher.tno and tname=-24、查询选修某课程的同学人数多于5人的教师。select tnam

12、e from teacher,course where teacher.tno=course.tno and o in （selecto from score group byo having count（sno）-25、查询95033班和95031班全体学生的记录。 union select * from student where class=-26、查询存在有85分以上成绩的课程Cno.select distincto from score where degree85-27、查询出“计算机系“教师所教课程的成绩表。select score.sno,o,degree from teach

13、er,course ,score where teacher.tno=course.tno and o =o and depart= order by sno-28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。select tname,prof from teacher where depart= and prof not in （select prof from teacher where depart=） union -29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序

14、。selecto,sno,degree from score whereo= and degree any （select degree from score whereo=） order by degree desc-30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.all （select degree from score whereo=-31、查询所有教师和同学的name、sex和birthday.select name=tname,sex=tsex,birthday=tbirthday from teacher union s

15、elect name=sname,sex=ssex,birthday=sbirthday from student-32、查询所有“女”教师和“女”同学的name、sex和birthday.select name=tname,sex=tsex,birthday=tbirthday from teacher where tsex= union select name=sname,sex=ssex,birthday=sbirthday from student where ssex=-33、查询成绩比该课程平均成绩低的同学的成绩表。select * from score where degree=

16、2-37、查询Student表中不姓“王”的同学记录。select * from student where sname not like 王%-38、查询Student表中每个学生的和年龄。select sname,sage=（2011-year（sbirthday） from student-39、查询Student表中最大和最小的Sbirthday日期值。select max（sbirthday） from student union select min（sbirthday） from student-40、以班号和年龄从大到小的顺序查询Student表中的全部记录。select sn

17、o,sname,ssex,class,sage=（2011-year（sbirthday） from student order by class desc,（2011-sbirthday） desc-41、查询“男”教师及其所上的课程。select tname,tsex ,cname, depart from teacher,course where course.tno=teacher.tno and tsex=-42、查询最高分同学的Sno、Cno和Degree列。select student.sno,cno,degree from student, score where studen

18、t.sno=score.sno and degree in （select max（degree） from score）-43、查询和“军”同性别的所有同学的Sname.select s1.sname from student s1, student s2 where s1.ssex=s2.ssex and s2.sname=军-44、查询和“军”同性别并同班的同学Sname.and s1.class =s2.class-45、查询所有选修“计算机导论”课程的“男”同学的成绩表。select score.sno,o ,degree from course,student,score wher

19、e student.sno=score.sno and o=o and ssex= andame=-46、查询score表中分数最高的学生的信息。/多层嵌套select student.sno,sname,ssex,sbirthday,class from student, score where student.sno=score.sno and degree in （select max（degree） from score）-47、查询score表中的平均分在80分以上的学生信息。/相关查询。无关查询select sno,sname,ssex,sbirthday,class from student where sno in（ select sno from score group by sno having avg（degree）80）