1 --1、 查询Student表中的所有记录的Sname、Ssex和Class列。  2 select Sname,Ssex,Class from Student  3   4 --2、 查询教师所有的单位即不重复的Depart列。  5   6 --select COUNT(*) from Teacher group by Depart  7 select distinct Depart from  Teacher  8   9 --3、 查询Student表的所有记录。 10 select *from Student 11 select Sno,Sname,Ssex,Sbirthday,Class from Student 12  13 --4、 查询Score表中成绩在60到80之间的所有记录。 14 select * from Score where Degree between 60 and 80 15  16 --5、 查询Score表中成绩为85，86或88的记录。 17 select * from Score where Degree in(85,86,88)  18  19 --6、 查询Student表中“95031”班或性别为“女”的同学记录。 20 select * from Student where Class='95031' or Ssex='女' 21  22 --7、 以Class降序查询Student表的所有记录。 23 select * from Student order by Class desc 24  25 --8、 以Cno升序、Degree降序查询Score表的所有记录。 26 select * from Score order by Cno asc, Degree  desc 27  28 --9、 查询“95031”班的学生人数。 29 select count(class) from Student group by Class having Class='95031' 30 select COUNT(*) from student where class='95031'--优 31  32 --10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序） 33 select * from Score where Degree=(select MAX(Degree) from Score) 34 select top 1 *from Score order by Degree desc 35  36 --11、 查询每门课的平均成绩。 37 select Cno,AVG(Degree) from score group by Cno-- 38  39  40 --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数 41 select Cno,AVG(Degree) from Score group by cno having (COUNT(*)>5) and (cno like '3%') 42  43  44 --13、查询分数大于70，小于90的Sno列。 45 select Sno from Score where Degree>70 and Degree<90 46 select Sno from Score where Degree between 70 and 90--包含临界值 47  48 --14、查询所有学生的Sname、Cno和Degree列。 49 select Sname,Cno,Degree,Class from Student,Score where Student.Sno=Score.Sno 50  51  52 --15、查询所有学生的Sno、Cname和Degree列。 53 select Sno,Cname,Degree from Course,Score where Course.Cno=Score.Cno 54  55  56 --16、查询所有学生的Sname、Cname和Degree列。 57 select Sname,Cname,Degree from Student join score on Student.Sno=Score.Sno join  Course on Course.Cno=Score.Cno 58  59 --17、 查询“95033”班学生的平均分。 60 select cno,avg(Degree) from Student,Score where (Student.Sno=Score.Sno) and (Student.Class='95033') group by Score.Cno 61  62 select avg(Degree)  from Student,Score where (Student.Sno=Score.Sno) and (Student.Class='95033') 63  64  65 --18、 假设使用如下命令建立了一个grade表：现查询所有同学的Sno、Cno和rank列。 66 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 90 and 100)and grade.rank='A') 67 union  68 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 80 and 89)and grade.rank='B') 69 union 70 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 70 and 79)and grade.rank='C') 71 union  72 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 60 and 69)and grade.rank='D') 73 union  74 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 0 and 59)and grade.rank='E') 75  76 select Sno,Cno,rank from Score,grade where  Degree between low and upp 77  78  79  80 --19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 81 select * from Score 82 select * from Score where Cno='3-105' and Degree>(select degree from Score where Sno='109'and Cno='3-105') 83 --select * from Score where (degree>(select Degree from Score where Sno=109)) in (Cno='3-105') 84  85 --20、查询score中选学多门课程的同学,这些同学中分数为非最高分成绩的记录。---- 86 select * from score where degree not in (select MAX(Degree) from Score where  Sno  in (select Sno from Score group by sno having COUNT(*)>1 )) and (Sno in (select sno from Score group by sno having COUNT(*)>1)) 87  88  89 --21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 90 select * from Score where Degree>(select degree from Score where Sno='109'and Cno='3-105') 91  92 --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 93 select Sno,Sname,Sbirthday from Student s1 where (YEAR(s1.Sbirthday) in 94 (select YEAR(s2.Sbirthday) from Student s2 where Sno='108' ))and(Sno!='108') 95  96  97 --23、查询“张旭“教师任课的学生成绩。 98 select *from Score s3 where s3.Cno=(select Cno from Course s2 where s2.Tno=(select Tno from Teacher s1 where Tname='张旭')) 99 100 101 --24、查询选修某课程的同学人数多于5人的教师姓名。102 select Tname from Teacher s1 where Tno in (select Tno from Course s2 where cno in (select Cno from Score group by Cno having COUNT(*)>5))103 --select Tname from (Teacher join Course on Teacher.Tno=Course.Tno join Score on Course.Cno=Score.Cno) group by score.Cno having count(*)>5104 105 106 --25、查询95033班和95031班全体学生的记录。107 select *from Student where Class in (95033,95031)108 109 select * from Teacher  join Course on Course.Tno=Teacher.Tno join  Score on Course.Cno=Score.Cno join (select *from Student where Class in (95033,95031)) s1 on Score.Sno=s1.Sno110 111 112 --26、  查询存在有85分以上成绩的课程Cno.113 select * from Score114 select cno from Score group by Cno having MAX(Degree)>85--分组115 116 select distinct Cno from Score where Degree>85--distinct消除重复行117 118 --27、查询出“计算机系“教师所教课程的成绩表。119 select * from Score where Cno in(select Cno from Course where Tno in (select Tno from Teacher where Depart='计算机系'))120 121 122 --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。123 select * from Teacher124 select Tname,Prof from Teacher s1 where Depart='计算机系' and not exists(select Prof from Teacher s2 where Depart='电子工程系' and s1.Prof=s2.Prof)--加上and 条件目的就是让前面的一记录一条一条的判定，而不是等着前面的条件执行完了，再跟后面的判断！125 union126 select Tname,Prof from Teacher s1 where Depart='电子工程系' and not exists(select Prof from Teacher s2 where Depart='计算机系' and s1.Prof=s2.Prof)--exists 意思 就是（）里只要返回数据就是真！不返回数据 空表就false！127 128 129 --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。130 131 select Cno,Sno,Degree from Score where Cno='3-105' and Degree >any (select Degree from Score where Cno='3-245')132 select Cno,Sno,Degree from Score where Cno='3-105' and Degree >(select min(Degree) from Score where Cno='3-245')133 134 135 136 --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.137 select * from Score 138 select Cno,Sno,Degree from Score where Cno='3-105' and Degree >(select max(Degree) from Score where Cno='3-245')139 select Cno,Sno,Degree from Score where Cno='3-105' and Degree >all (select Degree from Score where Cno='3-245')140 141 142 143 144 --31、 查询所有教师和同学的name、sex和birthday.145 select Tname 'name',Tsex 'sex',Tbirthday 'birthday' from Teacher146 union147 select Sname,Ssex,Sbirthday from Student148 149 --32、查询所有“女”教师和“女”同学的name、sex和birthday.150 select Tname 'name',Tsex 'sex',Tbirthday 'birthday' from Teacher where Tsex='女'151 union152 select Sname,Ssex,Sbirthday from Student where Ssex='女'153 154 --33、 查询成绩比该课程平均成绩低的同学的成绩表。155 select *from Score s1 where Degree <( select AVG(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno )---相关子查询156  157 --34、 查询所有任课教师的Tname和Depart.158 select Tname,Depart from Teacher where Tno in( select Tno from Course where Cno in ( select Cno from Score group by Cno))159 160 161 162 --35 、 查询所有未讲课的教师的Tname和Depart. 163 select Tname from Teacher where Tno in( select Tno from Course where Cno not in ( select Cno from Score group by Cno))164 165 166 --36、查询至少有2名男生的班号。167 select s1.Class from (select Class from Student where Ssex='男') s1 group by s1.Class having COUNT(*)>=2168 169 170 --37、查询Student表中不姓“王”的同学记录。171 select *from Student where Sname not like '王%'172 173 174 --38、查询Student表中每个学生的姓名和年龄。175 select Sname,(YEAR(GETDATE())-YEAR(Sbirthday)) '年龄'  from student 176 177 178 --39、查询Student表中最大和最小的Sbirthday日期值。179 select MAX(Sbirthday) from student 180 union181 select min(Sbirthday) from student182 183 184 --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。185 select Sno,sname,ssex,class,(YEAR(GETDATE())-YEAR(Sbirthday)) '年龄'  from student order by Class desc ,年龄 desc186 187 188 189 --41、查询“男”教师及其所上的课程。190 select * from  Course,Teacher where Teacher.Tno=Course.Tno and  Teacher.Tsex='男'191 192 --42、查询最高分同学的Sno、Cno和Degree列。193 select *from Score194 select Sno,Cno,Degree from Score where Degree=(select MAX(Degree) from Score) 195 196 --查询总成绩最高分的197 select Sno,Cno,Degree from Score where sno in198 (select top 1 Sno  from Score group by Sno order by SUM(Degree) desc)199 200 --43、查询和“李军”同性别的所有同学的Sname.201 select * from Student 202 select Sname from Student where Ssex =(select Ssex from Student where Sname='李军') and (Sname!='李军')203 204 205 --44、查询和“李军”同性别并同班的同学Sname.206 select Sname from Student where Class=(select Class from Student where Sname='李军') and Ssex =(select Ssex from Student where Sname='李军')and(Sname!='李军')207 208 --相关子查询--关键是分析出哪个是关键点209 select * from Student 210 select Class,sname from student s1 where ssex=(select class,sname,ssex from student s2 where sname='李军' and s2.class= s1.class)211 212 --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。213 select Degree from Score where 214 cno=(select Cno from course s1 where Cname='计算机导论') and( Sno in (select Sno from Student where Ssex='男'))