一些简单的sql练习
- 准备数据
CREATE TABLE students (sno VARCHAR(3) not null, sname VARCHAR(3) not null, ssex VARCHAR(2) not null, sbirthday datetime, class VARCHAR(5) ) CREATE table courses( cno VARCHAR(5) not null, cname VARCHAR(10) not null, tno VARCHAR(10)not null ) CREATE TABLE scores( sno VARCHAR(3) not null, cno VARCHAR(5) not null, degree NUMERIC(10,1)not null ) CREATE TABLE teachers( tno VARCHAR(3) not null, tname VARCHAR(4) not null, tsex VARCHAR(2) not null, tbirthday datetime not null , prof varchar(6), depart VARCHAR(10) not null ) 插入数据 INSERT students (sno,sname,ssex,sbirthday,class)VALUES (108,'曾华','男','1977-09-01',95033); INSERT students (sno,sname,ssex,sbirthday,class)VALUES (105,'匡明','男','1975-10-02',95031); INSERT students (sno,sname,ssex,sbirthday,class)VALUES (107,'王丽','女','1976-01-23',95033); INSERT students (sno,sname,ssex,sbirthday,class)VALUES (101,'李军','男','1977-02-20',95033); INSERT students (sno,sname,ssex,sbirthday,class)VALUES (109,'王芳','女','1977-02-10',95031); INSERT students (sno,sname,ssex,sbirthday,class)VALUES (103,'陆君','男','1977-06-03',95031); INSERT courses(cno,cname,tno)values ('3-105','计算机导论',825); INSERT courses(cno,cname,tno)values ('3-245','操作系统',804); INSERT courses(cno,cname,tno)values ('6-166','数据电路',856); INSERT courses(cno,cname,tno)values ('9-888','高等数学',100); insert scores(sno,cno,degree)VALUES(103,'3-245',86); insert scores(sno,cno,degree)VALUES(105,'3-245',75); insert scores(sno,cno,degree)VALUES(109,'3-245',68); insert scores(sno,cno,degree)VALUES(103,'3-105',92); insert scores(sno,cno,degree)VALUES(105,'3-105',88); insert scores(sno,cno,degree)VALUES(109,'3-105',76); insert scores(sno,cno,degree)VALUES(101,'3-105',64); insert scores(sno,cno,degree)VALUES(107,'3-105',91); insert scores(sno,cno,degree)VALUES(108,'3-105',78); insert scores(sno,cno,degree)VALUES(101,'6-166',85); insert scores(sno,cno,degree)VALUES(107,'6-166',79); insert scores(sno,cno,degree)VALUES(108,'6-166',81); -- 多行插入 INSERT teachers(tno,tname,tsex,tbirthday,prof,depart)VALUES (804,'李诚','男','1958-12-02','副教授','计算机系'), (256,'张旭','男','1969-03-12','讲师','电子工程系'), (825,'王萍','女','1972-08-14','助教','计算机系'), (931,'刘冰','女','1977-05-05','助教','电子工程系');
- 题目及答案
1 1.-- 查询students表中的所有记录sname、ssex和class列。 2 SELECT sname,ssex,class from students; 3 4 2.-- 查询教师所有的单位即不重复的depart列 5 SELECT distinct depart from teachers; 6 7 distinct 不寻常的有区别的 8 3.-- 查询student表中的所有记录 9 SELECT *from students; 10 11 4.-- 查询score表中成绩在60到80之间的所有记录 12 SELECT *from scores WHERE degree BETWEEN 60 and 80; 13 14 between 在...之间 15 5.-- 查询score表中成绩为85,86或88的记录 16 SELECT *FROM scores where degree in (85,86,88); 17 18 6.-- 查询student表中'95031'班或性别为'女'的同学记录 19 SELECT *FROM students WHERE class='95031' or ssex='女'; 20 21 SELECT * from students WHERE ssex='女' 22 UNION 23 SELECT * from students WHERE class='95031' 24 25 7.-- 以class降序查询student表中的所有记录 26 select *from students ORDER BY class DESC; 27 28 order 顺序,次序 29 8.-- 以cno升序 degree降序查询score表的所有记录 30 SELECT *from scores ORDER BY cno asc,degree DESC; 31 32 9.-- 查询'95031'班的人数 33 SELECT COUNT(1) as stunum from students where class='95031'; 34 35 count 数,计数 36 count(1)计算一共有多少符合条件的行 37 10.-- 查询score表中的最高分的学生学号和课程号. 38 SELECT sno,cno from scores ORDER BY degree desc LIMIT 1; 39 40 -- 11.查询'3-105'号课程的平均分 41 SELECT AVG(degree) FROM scores WHERE cno='3-105'; 42 43 -- 12.查询score表中至少有5名学生选修的并以3 44 开头的课程的平均数 45 SELECT cno,avg(degree) 46 FROM scores 47 WHERE cno LIKE '3%' 48 GROUP BY cno 49 HAVING COUNT(*)>=5; 50 51 -- 13.查询最低分大于70,最高分小于90的sno列 52 SELECT sno 53 FROM scores 54 GROUP BY sno 55 HAVING MAX(degree)<90 AND min(degree)>70; 56 57 -- 14.查询所有学生的sname.cno和degree列 58 select sname,cno,degree 59 from students join scores 60 on students.sno=scores.sno 61 ORDER BY sname; 62 63 -- 15.查询所有学生的sno.cname和degree列 64 SELECT sno,cname,degree 65 FROM scores JOIN courses 66 on scores.cno=courses.cno 67 ORDER BY sno; 68 69 -- 16.查询所有的学生的sname.cname和degree列 70 SELECT sname,cname,degree 71 FROM scores JOIN courses 72 ON scores.cno=courses.cno JOIN students 73 ON students.sno=scores.sno 74 ORDER BY sname; 75 76 -- 17.查询'95033'班的所选课程的平均分 77 SELECT cname,AVG(degree) 78 FROM scores JOIN students #scores和students 建立连接 79 ON scores.sno=students.sno #条件 学生学号相同 80 JOIN courses #再与courses建立连接 81 ON scores.cno=courses.cno #课程的编号 82 WHERE class='95033' #条件student 95033 班的学生学号建立连接获取成绩参与平均分的算法,学生scores中课程编号与课程中编号对应的课程名字返回 83 GROUP BY courses.cno #以95033班所选的课程分组 84 ORDER BY cname; 85 86 -- 18 87 88 -- 19.查询选修'3-105'课程的成绩高于109号同学成绩的所有记录 89 SELECT s1.sno,s1.degree 90 FROM scores s1 INNER JOIN scores s2 91 on s1.cno =s2.cno AND s1.degree>s2.degree 92 WHERE s1.cno='3-105' AND s2.sno='109' 93 ORDER BY s1.sno 94 95 -- 20.查询score中选学一门以上课程同学中分数为非最高分成绩的记录 96 select * 97 FROM scores 98 GROUP BY sno 99 HAVING COUNT(cno)>1 AND degree!=MAX(degree) 100 101 -- 21.查询成绩高于学号为109 课程号为3-105的成绩的所有记录 102 SELECT s1.sno,s1.degree 103 FROM scores s1 JOIN scores s2 104 on s1.cno=s2.cno AND s1.degree>s2.degree 105 WHERE s1.cno='3-105' AND s2.sno='109' 106 ORDER BY s1.sno 107 108 -- 22.查询和学号为108的同学同年同月出售的所有学生的 109 -- sno.sname和sbirthday列 110 SELECT s1.sno,s1.sname,s1.sbirthday 111 FROM students s1 JOIN students s2 112 on YEAR(s1.sbirthday)=YEAR(s2.sbirthday) 113 WHERE s2.sno='108' 114 115 -- 23.查询'王萍'教师任课的学生成绩 116 SELECT sno,degree 117 FROM scores inner JOIN courses 118 on scores.cno=courses.cno 119 INNER JOIN teachers 120 on courses.tno=teachers.tno 121 WHERE teachers.tname='王萍' 122 -----另一种写法 123 SELECT sno,degree 124 FROM teachers ,courses,scores 125 WHERE teachers.tname='王萍' 126 127 -- 24.查询选修某课程的同学人数多于5人的教师姓名 128 SELECT DISTINCT tname 129 FROM scores INNER JOIN courses 130 on scores.cno=courses.cno 131 JOIN teachers 132 on courses.tno=teachers.tno 133 WHERE courses.cno in (SELECT cno FROM scores GROUP BY cno HAVING COUNT(sno)>5) 134 ------ 135 SELECT DISTINCT tname 136 FROM teachers,courses,scores 137 WHERE courses.cno=scores.cno AND courses.tno=teachers.tno AND 138 courses.cno = (SELECT cno FROM scores GROUP BY cno HAVING COUNT(*)>5) 139 ====== 140 SELECT DISTINCT tname ,cname 141 FROM scores JOIN courses 142 on scores.cno=courses.cno 143 JOIN teachers 144 on courses.tno=teachers.tno 145 GROUP BY courses.cname 146 HAVING COUNT(sno)>5 147 148 -- 25.查询95033班和95031班全体学生的记录 149 SELECT * 150 FROM students 151 WHERE class in ('95033','95031') 152 ORDER BY class 153 154 -- 26.查询存在有85分以上成绩的课程cno 155 SELECT DISTINCT cno 156 FROM scores 157 WHERE degree>85 158 ---- 159 SELECT DISTINCT courses.cname 160 FROM scores ,courses 161 WHERE scores.cno=courses.cno AND degree>85 162 163 -- 27.查询出计算机系教师所教课程的成绩表 164 SELECT tname,cname,sname,degree 165 FROM teachers JOIN courses 166 on teachers.tno=courses.tno 167 JOIN scores 168 on courses.cno=scores.cno 169 JOIN students 170 on scores.sno=students.sno 171 WHERE teachers.depart='计算机系' 172 ORDER BY tname,cname,degree DESC 173 174 -- 28.查出计算机系与电子工程系不同职称的教师的tname和prof 175 SELECT tname,prof 176 FROM teachers 177 WHERE depart='计算机系' AND prof NOT in( 178 SELECT DISTINCT prof 179 FROM teachers 180 WHERE depart='电子工程系') 181 182 -- 29.查询选修编号为3-105 课程且成绩至少高于任意选修编号为3-245的同学的成绩的cno.sno和degree,并按degree从高到低次序排序 183 SELECT cno,sno,degree 184 FROM scores 185 WHERE cno='3-105' and degree >any( 186 SELECT degree 187 FROM scores 188 WHERE cno='3-245') 189 ORDER BY degree DESC 190 ----写完自己就看不懂了的写法 191 SELECT DISTINCT s1.cno,s1.sno,s1.degree 192 FROM scores s1 JOIN scores s2 193 on s1.degree>s2.degree and 194 s1.cno='3-105' and s2.cno='3-245' 195 ORDER BY degree desc 196 197 -- 30.查询选修编号为3-105 且成绩高于所有选修编号为3-245课程的同学的cno,sno和degree 198 SELECT cno,sno,degree 199 FROM scores 200 WHERE cno='3-105' AND degree>ALL( 201 SELECT degree 202 FROM scores 203 WHERE cno='3-245') 204 ORDER BY degree desc 205 any关键词可以理解为“对于子查询返回的列中的任一数值,如果比较结果为true,则返回true”。 206 all的意思是“对于子查询返回的列中的所有值,如果比较结果为true,则返回true” 207 208 -- 31.查询所有教师和同学的name sex 和birthday 209 SELECT sname ,ssex ,sbirthday 210 FROM students 211 UNION 212 SELECT tname,tsex,tbirthday 213 FROM teachers 214 215 -- 32.查询所有女教师和女同学的name.sex和birthday 216 SELECT sname,ssex,sbirthday 217 FROM students 218 WHERE ssex='女' 219 UNION 220 SELECT tname,tsex,tbirthday 221 FROM teachers 222 WHERE tsex='女' 223 224 -- 33查询成绩比该课程平均成绩低的同学的成绩表 225 SELECT s1.* 226 FROM scores as s1 inner JOIN( 227 SELECT cno,AVG(degree) as adegree 228 FROM scores 229 GROUP BY cno) s2 230 on (s1.cno=s2.cno and s1.degree<s2.adegree) 231 232 -- 34查询所有任课教师的tname和depart 233 SELECT tname,depart 234 FROM teachers 235 WHERE tno in ( 236 SELECT tno 237 FROM courses 238 ) 239 240 -- 35查询所有未讲课的教师的tname和depart 241 SELECT tname,depart 242 FROM teachers 243 WHERE tno NOT in( 244 SELECT tno 245 FROM courses) 246 247 -- 36查询至少有2名男生的班号 248 SELECT class,COUNT(1) as boyCount 249 FROM students 250 WHERE ssex='男' 251 GROUP BY class 252 HAVING boyCount>=2 253 254 -- 37.查询student表中不姓王的同学的记录 255 SELECT * 256 FROM students 257 WHERE sname not LIKE '王%' 258 259 -- 38.查询student表中每个学生的姓名和年龄 260 SELECT sname,YEAR(NOW())-YEAR(sbirthday) as sage 261 FROM students 262 263 -- 39.查询student表中最大和最小的sbirthday日期值 264 SELECT min(sbirthday),max(sbirthday) 265 FROM students 266 267 -- 40.以班号和年龄从大到小的顺序查询student表中的全部记录 268 SELECT * 269 FROM students 270 ORDER BY class DESC ,sbirthday ASC; 271 272 -- 41.查询男教师及其所上的课程 273 SELECT teachers.tname,courses.cname 274 FROM teachers JOIN courses 275 ON teachers.tno=courses.tno 276 WHERE teachers.tsex='男' 277 278 -- 42.查询最高分同学的sno.cno和degree列 279 SELECT sno,cno,degree 280 FROM scores 281 GROUP BY cno 282 HAVING degree=max(degree) 283 284 -- 43.查询和李军同性别的所有同学的sname 285 SELECT s1.sname 286 FROM students s1 JOIN students s2 287 on s1.ssex=s2.ssex 288 WHERE s2.sname='李军' 289 290 -- 44.查询和李军同性别并同班的同学sname 291 SELECT s1.sname 292 FROM students as s1 JOIN students as s2 293 on s1.ssex=s2.ssex AND s1.class=s2.class 294 WHERE s2.sname='李军' 295 296 -- 45.查询所有选项计算机导论课程的男同学的成绩表 297 SELECT * 298 FROM scores 299 WHERE sno in( 300 SELECT sno 301 FROM students 302 WHERE ssex='男') AND 303 cno in( 304 SELECT cno 305 FROM courses 306 WHERE cname='计算机导论') 307 ====other 308 SELECT students.sno,students.sname,scores.degree,courses.cname,students.class 309 from students JOIN scores 310 on students.sno=scores.sno 311 JOIN courses 312 on scores.cno=courses.cno 313 WHERE courses.cname='计算机导论' and students.ssex='男' 314 ORDER BY students.sno
signature:祸兮福所倚,福兮祸所伏