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 exists102 (103     select * from teacher t2 where depart='電子工程系' and t1.prof=t2.prof104 )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 desc107 --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 in111 (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 in117     (118         select sno from score where cno ='3-105'119     )120     and s1.sno=s2.sno121 )122 --31、查詢所有教師和同學的name、sex和birthday.123 select sname as name,ssex as sex,sbirthday as birthday from student124 union 125 select tname,tsex,tbirthday from teacher126 --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 teacher135 --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)>=2143 --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 student148 --39、查詢Student表中最大和最小的Sbirthday日期值。149 select MAX(sbirthday),MIN(sbirthday) from student150 --40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。151 select * from student order by class desc,sbirthday152 --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.//也可以用相關子查詢1