数据库基础学习6— 查询练习题

最新推荐文章于 2020-06-06 10:30:38 发布
weixin_30764771
最新推荐文章于 2020-06-06 10:30:38 发布
阅读量169
点赞数
文章标签：数据库
原文链接：http://www.cnblogs.com/zyh-club/p/4660041.html
版权
  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(*)>5
104 
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.Sno
110 
111 
112 --26、  查询存在有85分以上成绩的课程Cno.
113 select * from Score
114 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 Teacher
124 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 union
126 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 Teacher
146 union
147 select Sname,Ssex,Sbirthday from Student
148 
149 --32、查询所有“女”教师和“女”同学的name、sex和birthday.
150 select Tname 'name',Tsex 'sex',Tbirthday 'birthday' from Teacher where Tsex='女'
151 union
152 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(*)>=2
168 
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 union
181 select min(Sbirthday) from student
182 
183 
184 --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
185 select Sno,sname,ssex,class,(YEAR(GETDATE())-YEAR(Sbirthday)) '年龄'  from student order by Class desc ,年龄 desc
186 
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 Score
194 select Sno,Cno,Degree from Score where Degree=(select MAX(Degree) from Score) 
195 
196 --查询总成绩最高分的
197 select Sno,Cno,Degree from Score where sno in
198 (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='男'))