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

--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)