题干:设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。
建表后数据如下:
1 SQL> select * from student; 2 SNO SNAME SSEX SBIRTHDAY CLASS 3 --- -------- ---- ----------- ----- 4 108 曾华 男 1977/9/1 95033 5 105 匡明 男 1975/10/2 95031 6 107 王丽 女 1976/1/23 95033 7 101 李军 男 1976/2/20 95033 8 109 王芳 女 1975/2/10 95031 9 103 陆君 男 1974/6/3 95031 10 6 rows selected 11 12 SQL> select * from course; 13 CNO CNAME TNO 14 ----- ---------- --- 15 3-105 计算机导论 825 16 3-245 操作系统 804 17 6-166 数字电路 856 18 9-888 高等数学 831 19 20 SQL> select * from score; 21 SNO CNO DEGREE 22 --- ----- ------ 23 103 3-245 86.0 24 105 3-245 75.0 25 109 3-245 68.0 26 103 3-105 92.0 27 105 3-105 88.0 28 109 3-105 76.0 29 101 3-105 64.0 30 107 3-105 91.0 31 108 3-105 78.0 32 101 6-166 85.0 33 107 6-166 79.0 34 108 6-166 81.0 35 12 rows selected 36 37 SQL> select * from course; 38 CNO CNAME TNO 39 ----- ---------- --- 40 3-105 计算机导论 825 41 3-245 操作系统 804 42 6-166 数字电路 856 43 9-888 高等数学 831
以下为题目及解答:
1 Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 2 Connected as TEST1@ORCL 3 4 SQL> --1、 查询Student表中的所有记录的Sname、Ssex和Class列。 5 SQL> select sname, ssex, class from student; 6 SNAME SSEX CLASS 7 -------- ---- ----- 8 曾华 男 95033 9 匡明 男 95031 10 王丽 女 95033 11 李军 男 95033 12 王芳 女 95031 13 陆君 男 95031 14 6 rows selected 15 16 SQL> --2、 查询教师所有的单位即不重复的Depart列。 17 SQL> select distinct depart from teacher; 18 DEPART 19 ---------- 20 电子工程系 21 计算机系 22 23 SQL> --3、 查询Student表的所有记录。 24 SQL> select * from student; 25 SNO SNAME SSEX SBIRTHDAY CLASS 26 --- -------- ---- ----------- ----- 27 108 曾华 男 1977/9/1 95033 28 105 匡明 男 1975/10/2 95031 29 107 王丽 女 1976/1/23 95033 30 101 李军 男 1976/2/20 95033 31 109 王芳 女 1975/2/10 95031 32 103 陆君 男 1974/6/3 95031 33 6 rows selected 34 35 SQL> --4、 查询Score表中成绩在60到80之间的所有记录。 36 SQL> select * from score; 37 SNO CNO DEGREE 38 --- ----- ------ 39 103 3-245 86.0 40 105 3-245 75.0 41 109 3-245 68.0 42 103 3-105 92.0 43 105 3-105 88.0 44 109 3-105 76.0 45 101 3-105 64.0 46 107 3-105 91.0 47 108 3-105 78.0 48 101 6-166 85.0 49 107 6-166 79.0 50 108 6-166 81.0 51 12 rows selected 52 53 SQL> select * from score where degree between 60 and 80; 54 SNO CNO DEGREE 55 --- ----- ------ 56 105 3-245 75.0 57 109 3-245 68.0 58 109 3-105 76.0 59 101 3-105 64.0 60 108 3-105 78.0 61 107 6-166 79.0 62 6 rows selected 63 64 SQL> --5、 查询Score表中成绩为85，86或88的记录。 65 SQL> select * from score where in (85, 86, 88); 66 select * from score where in (85, 86, 88) 67 ORA-00936: 缺失表达式 68 69 SQL> select * from score where degree in (85, 86, 88); 70 SNO CNO DEGREE 71 --- ----- ------ 72 103 3-245 86.0 73 105 3-105 88.0 74 101 6-166 85.0 75 76 SQL> --6、 查询Student表中“95031”班或性别为“女”的同学记录。 77 SQL> select * from student where class = '95031' or ssex = '女'; 78 SNO SNAME SSEX SBIRTHDAY CLASS 79 --- -------- ---- ----------- ----- 80 105 匡明 男 1975/10/2 95031 81 107 王丽 女 1976/1/23 95033 82 109 王芳 女 1975/2/10 95031 83 103 陆君 男 1974/6/3 95031 84 85 SQL> --7、 以Class降序查询Student表的所有记录。 86 SQL> select * from student where class dest; 87 select * from student where class dest 88 ORA-00920: 无效的关系运算符 89 90 SQL> select * from student where class desc; 91 select * from student where class desc 92 ORA-00920: 无效的关系运算符 93 94 SQL> select * from student order by class desc; 95 SNO SNAME SSEX SBIRTHDAY CLASS 96 --- -------- ---- ----------- ----- 97 108 曾华 男 1977/9/1 95033 98 107 王丽 女 1976/1/23 95033 99 101 李军 男 1976/2/20 95033 100 109 王芳 女 1975/2/10 95031 101 103 陆君 男 1974/6/3 95031 102 105 匡明 男 1975/10/2 95031 103 6 rows selected 104 105 SQL> --8、 以Cno升序、Degree降序查询Score表的所有记录。 106 SQL> select * from score order by cno asc, degree desc; 107 SNO CNO DEGREE 108 --- ----- ------ 109 103 3-105 92.0 110 107 3-105 91.0 111 105 3-105 88.0 112 108 3-105 78.0 113 109 3-105 76.0 114 101 3-105 64.0 115 103 3-245 86.0 116 105 3-245 75.0 117 109 3-245 68.0 118 101 6-166 85.0 119 108 6-166 81.0 120 107 6-166 79.0 121 12 rows selected 122 123 SQL> --9、 查询“95031”班的学生人数。 124 SQL> select count(*) from student where class = '95031'; 125 COUNT(*) 126 ---------- 127 3 128 129 SQL> --10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序） 130 SQL> select sno, cno from score where degree = max(degree); 131 select sno, cno from score where degree = max(degree) 132 ORA-00934: 此处不允许使用分组函数 133 134 SQL> select sno, cno from score where degree = max(select degree from score); 135 select sno, cno from score where degree = max(select degree from score) 136 ORA-00934: 此处不允许使用分组函数 137 138 SQL> select sno, cno from score where degree = (select max(degree) from score); 139 SNO CNO 140 --- ----- 141 103 3-105 142 143 SQL> --11、 查询每门课的平均成绩。 144 SQL> select avg(all degree) from score group by cno; 145 AVG(ALLDEGREE) 146 -------------- 147 76.33333333333 148 81.66666666666 149 81.5 150 151 SQL> select avg(all degree), cno from score group by cno; 152 AVG(ALLDEGREE) CNO 153 -------------- ----- 154 76.33333333333 3-245 155 81.66666666666 6-166 156 81.5 3-105 157 158 SQL> --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 159 SQL> select cno, avg(degree) from score where cno in (select cno from score group by cno having count(1) >= 5) and cno like 160 161 '3%' group by cno; 162 CNO AVG(DEGREE) 163 ----- ----------- 164 3-105 81.5 165 166 SQL> --13、查询分数大于70，小于90的Sno列。 167 SQL> select sno degree > 70 and degree < 90; 168 select sno degree > 70 and degree < 90 169 ORA-00923: 未找到要求的 FROM 关键字 170 171 SQL> select sno from score where degree > 70 and degree < 90; 172 SNO 173 --- 174 103 175 105 176 105 177 109 178 108 179 101 180 107 181 108 182 8 rows selected 183 184 SQL> --14、查询所有学生的Sname、Cno和Degree列。 185 SQL> select st.sname, sc.cno, sc.degree from student st, score sc where st.sno = sc.sno; 186 SNAME CNO DEGREE 187 -------- ----- ------ 188 陆君 3-245 86.0 189 匡明 3-245 75.0 190 王芳 3-245 68.0 191 陆君 3-105 92.0 192 匡明 3-105 88.0 193 王芳 3-105 76.0 194 李军 3-105 64.0 195 王丽 3-105 91.0 196 曾华 3-105 78.0 197 李军 6-166 85.0 198 王丽 6-166 79.0 199 曾华 6-166 81.0 200 12 rows selected 201 202 SQL> --15、查询所有学生的Sno、Cname和Degree列。 203 SQL> select sc.sno, co.cname, sc.degree from course co, score sc where co.cno = sc.cno; 204 SNO CNAME DEGREE 205 --- ---------- ------ 206 103 操作系统 86.0 207 105 操作系统 75.0 208 109 操作系统 68.0 209 103 计算机导论 92.0 210 105 计算机导论 88.0 211 109 计算机导论 76.0 212 101 计算机导论 64.0 213 107 计算机导论 91.0 214 108 计算机导论 78.0 215 101 数字电路 85.0 216 107 数字电路 79.0 217 108 数字电路 81.0 218 12 rows selected 219 220 SQL> --16、查询所有学生的Sname、Cname和Degree列。 221 SQL> select st.sname, co.cname, sc.degree from student st, course co, score sc where st.sno = sc.sno, sc.cno = co.cno; 222 select st.sname, co.cname, sc.degree from student st, course co, score sc where st.sno = sc.sno, sc.cno = co.cno 223 ORA-00933: SQL 命令未正确结束 224 225 SQL> select st.sname, co.cname, sc.degree from student st, course co, score sc where st.sno = sc.sno and sc.cno = co.cno; 226 SNAME CNAME DEGREE 227 -------- ---------- ------ 228 陆君 操作系统 86.0 229 匡明 操作系统 75.0 230 王芳 操作系统 68.0 231 陆君 计算机导论 92.0 232 匡明 计算机导论 88.0 233 王芳 计算机导论 76.0 234 李军 计算机导论 64.0 235 王丽 计算机导论 91.0 236 曾华 计算机导论 78.0 237 李军 数字电路 85.0 238 王丽 数字电路 79.0 239 曾华 数字电路 81.0 240 12 rows selected 241 242 SQL> --17、 查询“95033”班学生的平均分。 243 SQL> select avg(sc.degree) from student st, score sc where st.sno = sc.sno and st.class = '95033'; 244 AVG(SC.DEGREE) 245 -------------- 246 79.66666666666 247 248 SQL> --18,现查询所有同学的Sno、Cno和rank列。 249 SQL> select sc.sno, sc.cno, gr.rank from score sc, grade gr where sc.degree between gr.low and gr.up; 250 SNO CNO RANK 251 --- ----- ---- 252 101 3-105 D 253 109 3-245 D 254 105 3-245 C 255 109 3-105 C 256 108 3-105 C 257 107 6-166 C 258 108 6-166 B 259 101 6-166 B 260 103 3-245 B 261 105 3-105 B 262 107 3-105 A 263 103 3-105 A 264 12 rows selected 265 266 SQL> --19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 267 SQL> select st.* from student st, score sc where (sc.sno = st.sno) and degree > (select degree from score where sno = 268 269 '109'); 270 select st.* from student st, score sc where (sc.sno = st.sno) and degree > (select degree from score where sno = '109') 271 ORA-01427: 单行子查询返回多个行 272 273 SQL> select st.* from student st, score sc where (sc.sno = st.sno) and (cno = '3-105') and degree > (select degree from 274 275 score where sno = '109'); 276 select st.* from student st, score sc where (sc.sno = st.sno) and (cno = '3-105') and degree > (select degree from score 277 278 where sno = '109') 279 ORA-01427: 单行子查询返回多个行 280 281 SQL> select st.* from student st, score sc where (sc.sno = st.sno) and (cno = '3-105') and degree > all (select degree from 282 283 score where sno = '109'); 284 SNO SNAME SSEX SBIRTHDAY CLASS 285 --- -------- ---- ----------- ----- 286 108 曾华 男 1977/9/1 95033 287 105 匡明 男 1975/10/2 95031 288 107 王丽 女 1976/1/23 95033 289 103 陆君 男 1974/6/3 95031 290 291 SQL> --20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 292 SQL> selet * from score where (select sno from score group by sno having count(1)) > 1; 293 SQL> 294 SQL> --21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 295 SQL> select * from score where degree > (select degree from score where sno = '109' and cno = '3-105'); 296 SNO CNO DEGREE 297 --- ----- ------ 298 103 3-245 86.0 299 103 3-105 92.0 300 105 3-105 88.0 301 107 3-105 91.0 302 108 3-105 78.0 303 101 6-166 85.0 304 107 6-166 79.0 305 108 6-166 81.0 306 8 rows selected 307 308 SQL> --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 309 SQL> select sno, sname sbirthday from student where extract(year from sbirthday) = (select extract(year from sbirthday) 310 311 from student where sno = 108 ); 312 SNO SBIRTHDAY 313 --- --------- 314 108 曾华 315 316 SQL> select sno, sname, sbirthday from student where extract(year from sbirthday) = (select extract(year from sbirthday) 317 318 from student where sno = 108 ); 319 SNO SNAME SBIRTHDAY 320 --- -------- ----------- 321 108 曾华 1977/9/1 322 323 SQL> --23、查询“张旭“教师任课的学生成绩。 324 SQL> select * from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.tname = '张旭'; 325 SNO CNO DEGREE TNO TNAME TSEX TBIRTHDAY PROF DEPART CNO CNAME TNO 326 --- ----- ------ --- ----- ---- ----------- ------ ---------- ----- ---------- --- 327 101 6-166 85.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856 328 107 6-166 79.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856 329 108 6-166 81.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856 330 331 SQL> select sc.* from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.tname = '张旭'; 332 SNO CNO DEGREE 333 --- ----- ------ 334 101 6-166 85.0 335 107 6-166 79.0 336 108 6-166 81.0 337 338 SQL> select degree from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.tname = '张旭'; 339 DEGREE 340 ------ 341 85.0 342 79.0 343 81.0 344 345 SQL> --24、查询选修某课程的同学人数多于5人的教师姓名。 346 SQL> select tname from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and (select sno from score 347 348 group by sno having count(1)) > 5; 349 SQL> select tname from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.cno = (select cno 350 351 from score group by cno having count(1) > 5); 352 select tname from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.cno = (select cno from 353 354 score group by cno having count(1) > 5) 355 ORA-00904: "TE"."CNO": 标识符无效 356 357 SQL> select tname from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and co.cno = (select cno 358 359 from score group by cno having count(1) > 5); 360 TNAME 361 ----- 362 王萍 363 王萍 364 王萍 365 王萍 366 王萍 367 王萍 368 6 rows selected 369 370 SQL> select distinct tname from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and co.cno = 371 372 (select cno from score group by cno having count(1) > 5); 373 TNAME 374 ----- 375 王萍 376 377 SQL> --25、查询95033班和95031班全体学生的记录。 378 SQL> select * from student where class in ('95033', '95031'); 379 SNO SNAME SSEX SBIRTHDAY CLASS 380 --- -------- ---- ----------- ----- 381 108 曾华 男 1977/9/1 95033 382 105 匡明 男 1975/10/2 95031 383 107 王丽 女 1976/1/23 95033 384 101 李军 男 1976/2/20 95033 385 109 王芳 女 1975/2/10 95031 386 103 陆君 男 1974/6/3 95031 387 6 rows selected 388 389 SQL> --26、 查询存在有85分以上成绩的课程Cno. 390 SQL> select distinct cno from score where degree > 85; 391 CNO 392 ----- 393 3-245 394 3-105 395 396 SQL> --27、查询出“计算机系“教师所教课程的成绩表。 397 SQL> select degree from score sc, teacher te, course co where sc.cno = co.cno and co.tno = te.tno and te.depart = '计算机系 398 399 '; 400 DEGREE 401 ------ 402 86.0 403 75.0 404 68.0 405 92.0 406 88.0 407 76.0 408 64.0 409 91.0 410 78.0 411 9 rows selected 412 SQL> --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。 413 SQL> select prof from teacher where depart = '计算机系' and prof not in (select prof from teacher where depart = '电子工 414 415 程系'); 416 PROF 417 ------ 418 副教授 419 420 SQL> --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序 421 422 。 423 SQL> --select 424 SQL> select cno, sno, degree from score where cno = '3-105' and degree > any (select degree from score where cno = '3-245') 425 426 and order by degree; 427 select cno, sno, degree from score where cno = '3-105' and degree > any (select degree from score where cno = '3-245') and 428 429 order by degree 430 ORA-00936: 缺失表达式 431 432 SQL> select cno, sno, degree from score where cno = '3-105' and degree > any (select degree from score where cno = '3-245') 433 434 order by degree; 435 CNO SNO DEGREE 436 ----- --- ------ 437 3-105 109 76.0 438 3-105 108 78.0 439 3-105 105 88.0 440 3-105 107 91.0 441 3-105 103 92.0 442 443 SQL> --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree. 444 SQL> select cno, sno, degree from score where cno = '3-105' and degree > all (select degree from score where cno = '3- 445 446 245'); 447 CNO SNO DEGREE 448 ----- --- ------ 449 3-105 105 88.0 450 3-105 107 91.0 451 3-105 103 92.0