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

  1 --drop database mydb
  2 
  3 create database myDB
  4 
  5 use myDB
  6 
  7 create table  Student--学生表
  8 (
  9     Sno char(3) primary key not null,--学号（主键）
 10     Sname char(8) not null,--学生姓名
 11     Ssex char(2) not null,--学生性别
 12     Sbirthday datetime,--学生出生年月
 13     Class char(5)--学生所在班级
 14 )
 15 go
 16 create table Teacher--教师表
 17 (
 18     Tno char(3) not null primary key,--教工编号（主码）
 19     Tname    Char(4)    not null,--教工姓名
 20     Tsex    Char(2)    not null,--教工性别
 21     Tbirthday    datetime,--教工出生年月    
 22     Prof    Char(6),--职称
 23     Depart    Varchar(10)    not null--教工所在部门
 24 )
 25 go
 26 create table Course--课程表
 27 (
 28     Cno char(5) primary key not null,--课程号（主键）
 29     Cname varchar(10) not null,--课程名称
 30     Tno char(3) not null references Teacher(Tno)--教工编号（外键）
 31 )
 32 go
 33 create table Score--成绩表
 34 (
 35     primary key (Sno,Cno),
 36     Sno char(3) not null references Student(Sno),--学号（外键）
 37     Cno char(5) not null references Course(Cno),--课程号（外键）
 38     Degree decimal(4,1)--成绩
 39 )
 40 go
 41 create table grade--等级表
 42 (
 43     low  int,
 44     upp  int,
 45     rank  char(1)
 46 )
 47 
 48 insert into grade values(90,100,‘A‘)
 49 insert into grade values(80,89,‘B‘)
 50 insert into grade values(70,79,‘C‘)
 51 insert into grade values(60,69,‘D‘)
 52 insert into grade values(0,59,‘E‘)
 53 select *from grade
 54 
 55 
 56 insert into Student values(‘108‘,‘曾华‘,‘男‘,‘1977-09-01‘,‘95033‘)
 57 insert into Student values(‘105‘,‘匡明‘,‘男‘,‘1975-10-02‘,‘95031‘)
 58 insert into Student values(‘107‘,‘王丽‘,‘女‘,‘1976-01-23‘,‘95033‘)
 59 insert into Student values(‘101‘,‘李军‘,‘男‘,‘1976-02-20‘,‘95033‘)
 60 insert into Student values(‘109‘,‘王芳‘,‘女‘,‘1975-02-10‘,‘95031‘)
 61 insert into Student values(‘103‘,‘陆君‘,‘男‘,‘1974-06-03‘,‘95031‘)
 62 
 63 update Student set Class=‘95031‘ where Sno=‘103‘
 64 update Student set Class=‘95033‘ where Sno=‘108‘
 65 update Student set Class=‘95031‘ where Sno=‘109‘
 66 update Student set Class=‘95031‘ where Sno=‘105‘
 67 
 68 
 69 select *from Student
 70 
 71 insert into Teacher values(‘804‘,    ‘李诚‘,    ‘男‘,    ‘1958-12-02‘,    ‘副教授‘,    ‘计算机系‘)  
 72 insert into Teacher values(‘856‘,    ‘张旭‘,    ‘男‘,    ‘1969-03-12‘,    ‘讲师‘,    ‘电子工程系‘)  
 73 insert into Teacher values(‘825‘,    ‘王萍‘,    ‘女‘,    ‘1972-05-05‘,    ‘助教‘,    ‘计算机系‘)  
 74 insert into Teacher values(‘831‘,    ‘刘冰‘,    ‘女‘,    ‘1977-08-14‘,    ‘助教‘,    ‘电子工程系‘)  
 75 
 76 select *from  Teacher 
 77 
 78 insert into Course values(‘3-105‘,‘计算机导论‘,‘825‘)  
 79 insert into Course values(‘3-245‘,‘操作系统‘,‘804‘) 
 80 insert into Course values(‘6-166‘,‘数字电路‘,‘856‘) 
 81 insert into Course values(‘9-888‘,‘高等数学‘,‘831‘) 
 82 
 83 select *from Course
 84 
 85 insert into Score values(‘103‘,    ‘3-245‘,    86) 
 86 insert into Score values(‘105‘,    ‘3-245‘,    75) 
 87 insert into Score values(‘109‘,    ‘3-245‘,    68) 
 88 insert into Score values(‘103‘,    ‘3-105‘,    92) 
 89 insert into Score values(‘105‘,    ‘3-105‘,    88) 
 90 insert into Score values(‘109‘,    ‘3-105‘,    76) 
 91 insert into Score values(‘101‘,    ‘3-105‘,    64) 
 92 insert into Score values(‘107‘,    ‘3-105‘,    91) 
 93 insert into Score values(‘108‘,    ‘3-105‘,    78) 
 94 insert into Score values(‘101‘,    ‘6-166‘,    85) 
 95 insert into Score values(‘107‘,    ‘6-166‘,    79) 
 96 insert into Score values(‘108‘,    ‘6-166‘,    81) 
 97 
 98 insert into Score values(‘103‘,    ‘6-166‘,    81) 
 99 insert into Score values(‘105‘,    ‘6-166‘,    81) 
100 insert into Score values(‘109‘,    ‘6-166‘,    81) 
101 
102 delete from Score where Sno=109 and cno=‘6-166‘
103 
104 
105 
106 
107 select *from Score
108 update Score set degree=‘64‘ where Sno=101 and cno=‘3-105‘
109 update Score set degree=‘85‘ where Sno=101 and cno=‘6-166‘
110 delete from Score where Sno=107 and cno=‘6-166‘
111 
112 
113 
114 --1、 查询Student表中的所有记录的Sname、Ssex和Class列。
115 select Sname,Ssex,Class from Student
116 
117 --2、 查询教师所有的单位即不重复的Depart列。
118 
119 --select COUNT(*) from Teacher group by Depart
120 select distinct Depart from  Teacher
121 
122 --3、 查询Student表的所有记录。
123 select *from Student
124 select Sno,Sname,Ssex,Sbirthday,Class from Student
125 
126 --4、 查询Score表中成绩在60到80之间的所有记录。
127 select * from Score where Degree between 60 and 80
128 
129 --5、 查询Score表中成绩为85，86或88的记录。
130 select * from Score where Degree in(85,86,88) 
131 
132 --6、 查询Student表中“95031”班或性别为“女”的同学记录。
133 select * from Student where Class=‘95031‘ or Ssex=‘女‘
134 
135 --7、 以Class降序查询Student表的所有记录。
136 select * from Student order by Class desc
137 
138 --8、 以Cno升序、Degree降序查询Score表的所有记录。
139 select * from Score order by Cno asc, Degree  desc
140 
141 --9、 查询“95031”班的学生人数。
142 select count(class) from Student group by Class having Class=‘95031‘
143 select COUNT(*) from student where class=‘95031‘--优
144 
145 --10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
146 select * from Score where Degree=(select MAX(Degree) from Score)
147 select top 1 *from Score order by Degree desc
148 
149 --11、 查询每门课的平均成绩。
150 select Cno,AVG(Degree) from score group by Cno--
151 
152 
153 --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
154 select Cno,AVG(Degree) from Score group by cno having (COUNT(*)>5) and (cno like ‘3%‘)
155 
156 
157 --13、查询分数大于70，小于90的Sno列。
158 select Sno from Score where Degree>70 and Degree<90
159 select Sno from Score where Degree between 70 and 90--包含临界值
160 
161 --14、查询所有学生的Sname、Cno和Degree列。
162 select Sname,Cno,Degree,Class from Student,Score where Student.Sno=Score.Sno
163 
164 
165 --15、查询所有学生的Sno、Cname和Degree列。
166 select Sno,Cname,Degree from Course,Score where Course.Cno=Score.Cno
167 
168 
169 --16、查询所有学生的Sname、Cname和Degree列。
170 select Sname,Cname,Degree from Student join score on Student.Sno=Score.Sno join  Course on Course.Cno=Score.Cno
171 
172 --17、 查询“95033”班学生的平均分。
173 select cno,avg(Degree) from Student,Score where (Student.Sno=Score.Sno) and (Student.Class=‘95033‘) group by Score.Cno
174 
175 select avg(Degree)  from Student,Score where (Student.Sno=Score.Sno) and (Student.Class=‘95033‘)
176 
177 
178 --18、 假设使用如下命令建立了一个grade表：现查询所有同学的Sno、Cno和rank列。
179 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 90 and 100)and grade.rank=‘A‘)
180 union 
181 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 80 and 89)and grade.rank=‘B‘)
182 union
183 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 70 and 79)and grade.rank=‘C‘)
184 union 
185 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 60 and 69)and grade.rank=‘D‘)
186 union 
187 (select Sno,Cno,rank from Score,grade where  (Score.Degree between 0 and 59)and grade.rank=‘E‘)
188 
189 select Sno,Cno,rank from Score,grade where  Degree between low and upp
190 
191 
192 
193 --19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
194 select * from Score
195 select * from Score where Cno=‘3-105‘ and Degree>(select degree from Score where Sno=‘109‘and Cno=‘3-105‘)
196 --select * from Score where (degree>(select Degree from Score where Sno=109)) in (Cno=‘3-105‘)
197 
198 --20、查询score中选学多门课程的同学,这些同学中分数为非最高分成绩的记录。----
199 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))
200 
201 
202 --21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
203 select * from Score where Degree>(select degree from Score where Sno=‘109‘and Cno=‘3-105‘)
204 
205 --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
206 select Sno,Sname,Sbirthday from Student s1 where (YEAR(s1.Sbirthday) in
207 (select YEAR(s2.Sbirthday) from Student s2 where Sno=‘108‘ ))and(Sno!=‘108‘)
208 
209 
210 --23、查询“张旭“教师任课的学生成绩。
211 select *from Score s3 where s3.Cno=(select Cno from Course s2 where s2.Tno=(select Tno from Teacher s1 where Tname=‘张旭‘))
212 
213 
214 --24、查询选修某课程的同学人数多于5人的教师姓名。
215 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))
216 --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
217 
218 
219 --25、查询95033班和95031班全体学生的记录。
220 select *from Student where Class in (95033,95031)
221 
222 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
223 
224 
225 --26、  查询存在有85分以上成绩的课程Cno.
226 select * from Score
227 select cno from Score group by Cno having MAX(Degree)>85
228 
229 --27、查询出“计算机系“教师所教课程的成绩表。
230 select * from Score where Cno in(select Cno from Course where Tno in (select Tno from Teacher where Depart=‘计算机系‘))
231 
232 
233 --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
234 select * from Teacher
235 select Tname,Prof from Teacher s1 where Depart=‘计算机系‘ and not exists(select Prof from Teacher s2 where Depart=‘电子工程系‘ and s1.Prof=s2.Prof)--加上and 条件目的就是让前面的一记录一条一条的判定，而不是等着前面的条件执行完了，再跟后面的判断！
236 union
237 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！
238 
239 
240 --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
241 
242 select Cno,Sno,Degree from Score where Cno=‘3-105‘ and Degree >any (select Degree from Score where Cno=‘3-245‘)
243 select Cno,Sno,Degree from Score where Cno=‘3-105‘ and Degree >(select min(Degree) from Score where Cno=‘3-245‘)
244 
245 
246 
247 --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
248 select * from Score 
249 select Cno,Sno,Degree from Score where Cno=‘3-105‘ and Degree >(select max(Degree) from Score where Cno=‘3-245‘)
250 select Cno,Sno,Degree from Score where Cno=‘3-105‘ and Degree >all (select Degree from Score where Cno=‘3-245‘)
251 
252 
253 
254 
255 --31、 查询所有教师和同学的name、sex和birthday.
256 select Tname ‘name‘,Tsex ‘sex‘,Tbirthday ‘birthday‘ from Teacher
257 union
258 select Sname,Ssex,Sbirthday from Student
259 
260 --32、查询所有“女”教师和“女”同学的name、sex和birthday.
261 select Tname ‘name‘,Tsex ‘sex‘,Tbirthday ‘birthday‘ from Teacher where Tsex=‘女‘
262 union
263 select Sname,Ssex,Sbirthday from Student where Ssex=‘女‘
264 
265 --33、 查询成绩比该课程平均成绩低的同学的成绩表。
266 select *from Score s1 where Degree <( select AVG(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno )
267  
268 --34、 查询所有任课教师的Tname和Depart.
269 select Tname,Depart from Teacher where Tno in( select Tno from Course where Cno in ( select Cno from Score group by Cno))
270 
271 
272 
273 --35 、 查询所有未讲课的教师的Tname和Depart. 
274 select Tname from Teacher where Tno in( select Tno from Course where Cno not in ( select Cno from Score group by Cno))
275 
276 
277 --36、查询至少有2名男生的班号。
278 select s1.Class from (select Class from Student where Ssex=‘男‘) s1 group by s1.Class having COUNT(*)>=2
279 
280 
281 --37、查询Student表中不姓“王”的同学记录。
282 select *from Student where Sname not like ‘王%‘
283 
284 
285 --38、查询Student表中每个学生的姓名和年龄。
286 select Sname,(YEAR(GETDATE())-YEAR(Sbirthday)) ‘年龄‘  from student 
287 
288 
289 --39、查询Student表中最大和最小的Sbirthday日期值。
290 select MAX(Sbirthday) from student 
291 union
292 select min(Sbirthday) from student
293 
294 
295 --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
296 select Sno,sname,ssex,class,(YEAR(GETDATE())-YEAR(Sbirthday)) ‘年龄‘  from student order by Class desc ,年龄 desc
297 
298 
299 
300 --41、查询“男”教师及其所上的课程。
301 select * from  Course,Teacher where Teacher.Tno=Course.Tno and  Teacher.Tsex=‘男‘
302 
303 --42、查询最高分同学的Sno、Cno和Degree列。
304 select *from Score
305 select Sno,Cno,Degree from Score where Degree=(select MAX(Degree) from Score) 
306 
307 --查询总成绩最高分的
308 select Sno,Cno,Degree from Score where sno in
309 (select top 1 Sno  from Score group by Sno order by SUM(Degree) desc)
310 
311 --43、查询和“李军”同性别的所有同学的Sname.
312 select Sname from Student where Ssex =(select Ssex from Student where Sname=‘李军‘) and (Sname!=‘李军‘)
313 
314 
315 --44、查询和“李军”同性别并同班的同学Sname.
316 select Sname from Student where Class=(select Class from Student where Sname=‘李军‘) and Ssex =(select Ssex from Student where Sname=‘李军‘)and(Sname!=‘李军‘)
317 
318 
319 --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
320 select Degree from Score where 
321 cno=(select Cno from course s1 where Cname=‘计算机导论‘) and( Sno in (select Sno from Student where Ssex=‘男‘))