题干:设有一数据库，包括四个表：学生表（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 9503110 6 rows selected11 12 SQL> select * from course;13 CNO CNAME TNO14 ----- ---------- ---15 3-105 计算机导论 82516 3-245 操作系统 80417 6-166 数字电路 85618 9-888 高等数学 83119 20 SQL> select * from score;21 SNO CNO DEGREE22 --- ----- ------23 103 3-245 86.024 105 3-245 75.025 109 3-245 68.026 103 3-105 92.027 105 3-105 88.028 109 3-105 76.029 101 3-105 64.030 107 3-105 91.031 108 3-105 78.032 101 6-166 85.033 107 6-166 79.034 108 6-166 81.035 12 rows selected36 37 SQL> select * from course;38 CNO CNAME TNO39 ----- ---------- ---40 3-105 计算机导论 82541 3-245 操作系统 80442 6-166 数字电路 85643 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 95033100 109 王芳 女 1975/2/10 95031101 103 陆君 男 1974/6/3 95031102 105 匡明 男 1975/10/2 95031103 6 rows selected104 105 SQL> --8、 以Cno升序、Degree降序查询Score表的所有记录。106 SQL> select * from score order by cno asc, degree desc;107 SNO CNO DEGREE108 --- ----- ------109 103 3-105 92.0110 107 3-105 91.0111 105 3-105 88.0112 108 3-105 78.0113 109 3-105 76.0114 101 3-105 64.0115 103 3-245 86.0116 105 3-245 75.0117 109 3-245 68.0118 101 6-166 85.0119 108 6-166 81.0120 107 6-166 79.0121 12 rows selected122 123 SQL> --9、 查询“95031”班的学生人数。124 SQL> select count(*) from student where class = '95031';125 COUNT(*)126 ----------127 3128 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 CNO140 --- -----141 103 3-105142 143 SQL> --11、 查询每门课的平均成绩。144 SQL> select avg(all degree) from score group by cno;145 AVG(ALLDEGREE)146 --------------147 76.33333333333148 81.66666666666149 81.5150 151 SQL> select avg(all degree), cno from score group by cno;152 AVG(ALLDEGREE) CNO153 -------------- -----154 76.33333333333 3-245155 81.66666666666 6-166156 81.5 3-105157 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.5165 166 SQL> --13、查询分数大于70，小于90的Sno列。167 SQL> select sno degree > 70 and degree < 90;168 select sno degree > 70 and degree < 90169 ORA-00923: 未找到要求的 FROM 关键字170 171 SQL> select sno from score where degree > 70 and degree < 90;172 SNO173 ---174 103175 105176 105177 109178 108179 101180 107181 108182 8 rows selected183 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 DEGREE187 -------- ----- ------188 陆君 3-245 86.0189 匡明 3-245 75.0190 王芳 3-245 68.0191 陆君 3-105 92.0192 匡明 3-105 88.0193 王芳 3-105 76.0194 李军 3-105 64.0195 王丽 3-105 91.0196 曾华 3-105 78.0197 李军 6-166 85.0198 王丽 6-166 79.0199 曾华 6-166 81.0200 12 rows selected201 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 DEGREE205 --- ---------- ------206 103 操作系统 86.0207 105 操作系统 75.0208 109 操作系统 68.0209 103 计算机导论 92.0210 105 计算机导论 88.0211 109 计算机导论 76.0212 101 计算机导论 64.0213 107 计算机导论 91.0214 108 计算机导论 78.0215 101 数字电路 85.0216 107 数字电路 79.0217 108 数字电路 81.0218 12 rows selected219 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.cno223 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 DEGREE227 -------- ---------- ------228 陆君 操作系统 86.0229 匡明 操作系统 75.0230 王芳 操作系统 68.0231 陆君 计算机导论 92.0232 匡明 计算机导论 88.0233 王芳 计算机导论 76.0234 李军 计算机导论 64.0235 王丽 计算机导论 91.0236 曾华 计算机导论 78.0237 李军 数字电路 85.0238 王丽 数字电路 79.0239 曾华 数字电路 81.0240 12 rows selected241 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.66666666666247 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 RANK251 --- ----- ----252 101 3-105 D253 109 3-245 D254 105 3-245 C255 109 3-105 C256 108 3-105 C257 107 6-166 C258 108 6-166 B259 101 6-166 B260 103 3-245 B261 105 3-105 B262 107 3-105 A263 103 3-105 A264 12 rows selected265 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 CLASS285 --- -------- ---- ----------- -----286 108 曾华 男 1977/9/1 95033287 105 匡明 男 1975/10/2 95031288 107 王丽 女 1976/1/23 95033289 103 陆君 男 1974/6/3 95031290 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 DEGREE297 --- ----- ------298 103 3-245 86.0299 103 3-105 92.0300 105 3-105 88.0301 107 3-105 91.0302 108 3-105 78.0303 101 6-166 85.0304 107 6-166 79.0305 108 6-166 81.0306 8 rows selected307 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 SBIRTHDAY313 --- ---------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 SBIRTHDAY320 --- -------- -----------321 108 曾华 1977/9/1322 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 TNO326 --- ----- ------ --- ----- ---- ----------- ------ ---------- ----- ---------- ---327 101 6-166 85.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856328 107 6-166 79.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856329 108 6-166 81.0 856 张旭 男 1969/3/12 讲师 电子工程系 6-166 数字电路 856330 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 DEGREE333 --- ----- ------334 101 6-166 85.0335 107 6-166 79.0336 108 6-166 81.0337 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 DEGREE340 ------341 85.0342 79.0343 81.0344 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 TNAME361 -----362 王萍363 王萍364 王萍365 王萍366 王萍367 王萍368 6 rows selected369 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 TNAME374 -----375 王萍376 377 SQL> --25、查询95033班和95031班全体学生的记录。378 SQL> select * from student where class in ('95033', '95031');379 SNO SNAME SSEX SBIRTHDAY CLASS380 --- -------- ---- ----------- -----381 108 曾华 男 1977/9/1 95033382 105 匡明 男 1975/10/2 95031383 107 王丽 女 1976/1/23 95033384 101 李军 男 1976/2/20 95033385 109 王芳 女 1975/2/10 95031386 103 陆君 男 1974/6/3 95031387 6 rows selected388 389 SQL> --26、 查询存在有85分以上成绩的课程Cno.390 SQL> select distinct cno from score where degree > 85;391 CNO392 -----393 3-245394 3-105395 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 DEGREE401 ------402 86.0403 75.0404 68.0405 92.0406 88.0407 76.0408 64.0409 91.0410 78.0411 9 rows selected412 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 PROF417 ------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 degree430 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 DEGREE436 ----- --- ------437 3-105 109 76.0438 3-105 108 78.0439 3-105 105 88.0440 3-105 107 91.0441 3-105 103 92.0442 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 DEGREE448 ----- --- ------449 3-105 105 88.0450 3-105 107 91.0451 3-105 103 92.0