1 select * from student 2 select * from score 3 --select * from grade 4 select * from course 5 select * from teacher 6 7 --1、 查询Student表中的所有记录的Sname、Ssex和Class列。 8 select sname,ssex,class from Student 9 --2、 查询教师所有的单位即不重复的Depart列。 10 select distinct depart from teacher 11 --3、 查询Student表的所有记录。 12 select * from student 13 --4、 查询Score表中成绩在60到80之间的所有记录。 14 select * from score where degree between 60 and 80 15 --5、 查询Score表中成绩为85，86或88的记录。 16 select * from score where degree in (85,86,88) 17 --6、 查询Student表中“95031”班或性别为“女”的同学记录。 18 select * from student where class='95031' or ssex='女' 19 --7、 以Class降序查询Student表的所有记录。 20 select * from student order by class desc 21 --8、 以Cno升序、Degree降序查询Score表的所有记录。 22 select * from score order by cno,degree desc 23 --9、 查询“95031”班的学生人数。 24 select COUNT(*) from student where class='95031' 25 --10、查询Score表中的最高分的学生学号和课程号。 26 select sno,cno from score where degree in (select MAX(degree) from score) 27 select top 1 sno,cno from score order by degree desc 28 --11、查询‘3-105’号课程的平均分。 29 select AVG(degree) from score where cno='3-105' 30 --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 31 select AVG(degree) from score where cno in (select cno from score group by cno having COUNT(*)>=5) and cno like '3%' 32 select AVG(degree) from score where cno like '3%' group by cno having COUNT(*)>=5 33 --13、查询最低分大于70，最高分小于90的Sno列。 34 select distinct sno from score 35 where sno in (select sno from score group by sno having MIN(degree)>70) 36 and sno in (select sno from score group by sno having MAX(degree)<90) 37 38 select sno from score group by sno having MAX(degree)<90 and MIN(degree)>70 39 --14、查询所有学生的Sname、Cno和Degree列。 40 select sname,cno,degree from student,score where student.sno=score.sno 41 --15、查询所有学生的Sno、Cname和Degree列。 42 select student.sno,cname,degree from student 43 join score on student.sno=score.sno 44 join course on score.cno=course.cno 45 --16、查询所有学生的Sname、Cname和Degree列。 46 select sname,cname,degree from student 47 join score on student.sno=score.sno 48 join course on score.cno=course.cno 49 --17、查询“95033”班所选课程的平均分。 50 select AVG(degree) from score 51 where degree in (select degree from score join student on student.sno=score.sno where class='95033') 52 --18、假设使用如下命令建立了一个grade表： 53 --create table grade(low int,upp int,rank varchar(1)) 54 --insert into grade values(90,100,'A') 55 --insert into grade values(80,89,'B') 56 --insert into grade values(70,79,'C') 57 --insert into grade values(60,69,'D') 58 --insert into grade values(0,59,'E') 59 --现查询所有同学的Sno、Cno和rank列。 60 select sno,cno,[rank] from score,grade where degree between low and upp 61 --19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。//无关子查询 62 --“109”号同学成绩 63 --select degree from score where sno='109' and cno='3-105' 64 select * from student join score on student.sno=score.sno 65 where cno='3-105' and degree > (select degree from score where sno='109' and cno='3-105') 66 --20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 67 --select sno from score group by sno having COUNT(*)>1 68 --select MAX(degree) from score group by cno 69 select * from score a 70 where sno in (select sno from score group by sno having COUNT(*)>1) 71 and 72 (degree not in (select MAX(degree) from score b where a.cno=b.cno group by cno))--相关子查询 73 74 --21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 75 select * from score where degree>(select degree from score where sno='109' and cno='3-105') 76 --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 77 select sno,sname,sbirthday from student 78 where YEAR(sbirthday)= YEAR((select sbirthday from student where sno='108')) 79 --23、查询“张旭“教师任课的学生成绩。 80 --select cno from course where course.tno=(select tno from teacher where tname='张旭') 81 select degree from score where cno=(select cno from course where course.tno=(select tno from teacher where tname='张旭')) 82 --24、查询选修某课程的同学人数多于5人的教师姓名。 83 --select cno from score group by cno having COUNT(*)>5 84 --select tno from course where cno=(select cno from score group by cno having COUNT(*)>5) 85 select tname from teacher 86 where tno=(select tno from course where cno=(select cno from score group by cno having COUNT(*)>5)) 87 --25、查询95033班和95031班全体学生的记录。 88 select student.sno,sname,ssex,sbirthday,class,course.cno,degree,cname,teacher.tno,tname,tsex,prof,depart from student 89 join score on student.sno=score.sno 90 join course on score.cno=course.cno 91 join teacher on course.tno=teacher.tno 92 where class='95033' or class='95031' order by student.sno 93 --26、查询存在有85分以上成绩的课程Cno. 94 select distinct cno from score where degree>=85 95 --27、查询出“计算机系“教师所教课程的成绩表。 96 --select tno from teacher where depart='计算机系' 97 --select cno from course where tno in (select tno from teacher where depart='计算机系') 98 select * from score 99 where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系')) 100 --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。//查关子查询 exists存在 101 select * from teacher t1 where depart='计算机系' and not exists 102 ( 103 select * from teacher t2 where depart='电子工程系' and t1.prof=t2.prof 104 ) 105 --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。 106 select cno,sno,degree from score where cno='3-105' and degree>(select MIN(degree) from score where cno='3-245') order by degree desc 107 --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.//相关子查询 108 --select cno,sno,degree from score where cno='3-105' and degree>(select MAX(degree) from score where cno='3-245') 109 --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.//相关子查询 110 select cno,sno,degree from score s1 where cno='3-105' and sno in 111 ( 112 select sno from score where cno='3-245' 113 ) 114 and degree> 115 ( 116 select degree from score s2 where cno='3-245' and sno in 117 ( 118 select sno from score where cno ='3-105' 119 ) 120 and s1.sno=s2.sno 121 ) 122 --31、查询所有教师和同学的name、sex和birthday. 123 select sname as name,ssex as sex,sbirthday as birthday from student 124 union 125 select tname,tsex,tbirthday from teacher 126 --32、查询所有“女”教师和“女”同学的name、sex和birthday. 127 select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女' 128 union 129 select tname,tsex,tbirthday from teacher where tsex='女' 130 --33、查询成绩比该课程平均成绩低的同学的成绩表。 131 select * from score where degree<(select AVG(degree) from score where cno in (select cno from score group by cno)) 132 133 --34、查询所有任课教师的Tname和Depart. 134 select tname,depart from teacher 135 --35 查询所有未讲课的教师的Tname和Depart. 136 --select cno from course where cno not in (select cno from score) 137 select tname,depart from teacher 138 where tno in (select tno from course where cno= (select cno from course where cno not in (select cno from score))) 139 140 select tname,depart from teacher 141 --36、查询至少有2名男生的班号。 142 select class from student where ssex='男' group by class having COUNT(ssex)>=2 143 --37、查询Student表中不姓“王”的同学记录。 144 select * from student where sname not like '王%' 145 --38、查询Student表中每个学生的姓名和年龄。 146 --(YEAR(GETDATE())-YEAR(sbirthday)) 147 select sname,(YEAR(GETDATE())-YEAR(sbirthday)) from student 148 --39、查询Student表中最大和最小的Sbirthday日期值。 149 select MAX(sbirthday),MIN(sbirthday) from student 150 --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 151 select * from student order by class desc,sbirthday 152 --41、查询“男”教师及其所上的课程。 153 --select tname from teacher where tsex='男' 154 --tsex='男' and course.tno in (select tno from teacher where tsex='男') 155 select tname,cname from teacher 156 join course on teacher.tno=course.tno 157 where tsex='男' and course.tno in (select tno from teacher where tsex='男') 158 --42、查询最高分同学的Sno、Cno和Degree列。 159 select sno,cno,degree from score where degree in (select MAX(degree) from score) 160 --43、查询和“李军”同性别的所有同学的Sname. 161 select sname from student where ssex=(select ssex from student where sname='李军') 162 --44、查询和“李军”同性别并同班的同学Sname.//也可以用相关子查询 163 select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军') 164 --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。 165 select * from score where cno in (select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='男') 166 --46、查询score表中分数最高的学生的信息。//多层嵌套 167 select * from student where sno in (select sno from score where degree in (select MAX(degree) from score)) 168 --47、查询score表中的平均分在80分以上的学生信息。//无关查询 169 --select sno from score group by sno having AVG(degree)>=80 170 select * from student 171 where sno in (select sno from score where score.sno in(select sno from score group by sno having AVG(degree)>=80))