oracle数据库增删改查练习50例-答案.pdf

oracle 数据库增删改查练习50例-答案 一、建表 -学生表 drop table student; create table student (sno varchar2(10),sname varchar2(10),sage date,ssex varchar2(10); insert into student values(01,赵雷,to_date(1990/01/01,yyyy/mm/dd),男); insert into student values(02,钱电,to_date(1990/12/21,yyyy/mm/dd),男); insert into student values(03,孙风,to_date(1990/05/20,yyyy/mm/dd),男); insert into student values(04,李云,to_date(1990/08/06,yyyy/mm/dd),男); insert into student values(05,周梅,to_date(1991/12/01,yyyy/mm/dd),女); insert into student values(06,吴兰,to_date(1992/03/01,yyyy/mm/dd),女); insert into student values(07,郑竹,to_date(1989/07/01,yyyy/mm/dd),女); insert into student values(08,王菊,to_date(1990/01/20,yyyy/mm/dd),女); -课程表 drop table course; create table course (cno varchar2(10),cname varchar2(10),tno varchar2(10); insert into course values (01,语文,02); insert into course values (02,数学,01); insert into course values (03,英语,03); -教师表 drop table teacher; create table teacher (tno varchar2(10),tname varchar2(10); insert into teacher values(01,张三); insert into teacher values(02,李四); insert into teacher values(03,王五); -成绩表 drop table sc; create table sc (sno varchar2(10),cno varchar2(10),score number(18,1); insert into sc values(01,01,80.0); insert into sc values(01,02,90.0); insert into sc values(01,03,99.0); insert into sc values(02,01,70.0); insert into sc values(02,02,60.0); insert into sc values(02,03,80.0); insert into sc values(03,01,80.0); insert into sc values(03,02,80.0); insert into sc values(03,03,80.0); insert into sc values(04,01,50.0); insert into sc values(04,02,30.0); insert into sc values(04,03,20.0); insert into sc values(05,01,76.0); insert into sc values(05,02,87.0); insert into sc values(06,01,31.0); insert into sc values(06,03,34.0); insert into sc values(07,02,89.0); insert into sc values(07,03,98.0); commit; 二、查询 1.1、查询同时存在“01“课程和“02“课程的情况 select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and o = 01 and o = 02; 1.2、查询必须存在“01“课程，“02“课程可以没有的情况 select t.*, s.score_01, s.score_02 from student t inner join (select a.sno, a.score score_01, b.score score_02 from sc a left join (select * from sc where cno = 02) b on (a.sno = b.sno) where o = 01) s on (t.sno = s.sno); 2.1、查询同时01课程比02课程分数低的数据 select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and o = 01 and o = 02 and sc1.score = 60 order by sno) t where s.sno = t.sno; 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 4.1、有考试成绩，且小于60分 select s.sno, s.sname, t.avg_score avg_score from student s, (select sno, round(avg(score), 2) avg_score from sc group by sno having avg(score) = 60) / (select count(1) from sc where cno = 01) * 100, 2) “及格率“, round(select count(1) from sc where cno = 01 and score = 70 and score = 80 and score = 90) / (select count(1) from sc where cno = 01) * 100, 2) “优秀率“ from dual union select 02 cno, round(select count(1) from sc where cno = 02 and score = 60) / (select count(1) from sc where cno = 02) * 100, 2) “及格率“, round(select count(1) from sc where cno = 02 and score = 70 and score = 80 and score = 90) / (select count(1) from sc where cno = 02) * 100, 2) “优秀率“ from dual union select 03 cno, round(select count(1) from sc where cno = 03 and score = 60) / (select count(1) from sc where cno = 03) * 100, 2) “及格率“, round(select count(1) from sc where cno = 03 and score = 70 and score = 80 and score 90) / (select count(1) from sc where cno = 03) * 100, 2) “优秀率“ from dual) e where o = o and o = o and o = o and o = o; 19、按各科成绩进行排序，并显示排名 select sno, cno, score, rank() over(partition by cno order by score desc) “名次“ from sc; select sno, cno, score, dense_rank() over(partition by cno order by score desc) “名次“ from sc; 20、查询学生的总成绩并进行排名 20.1 查询学生的总成绩 select a.sno, a.sname, nvl(b.sum_score, 0) “总成绩“ from student a, (select sno, sum(score) sum_score from sc group by sno order by sno) b where a.sno = b.sno(+); 20.2 查询学生的总成绩并进行排名。 select c.“学生编号“,c.“学生姓名“,c.“总成绩“,rank() over (order by c.“总成绩“ desc) “排名“ from (select a.sno “学生编号“, a.sname “学生姓名“, nvl(b.sum_score, 0) “总成绩“ from student a, (select sno, sum(score) sum_score from sc group by sno order by sno) b where a.sno = b.sno(+) c 21、查询不同老师所教不同课程平均分从高到低显示 select a.tno, a.tname, c.avg_score “平均分“ from teacher a, course b, (select cno, round(avg(score), 2) avg_score from sc group by cno) c where a.tno = b.tno and o = o order by “平均分“ desc; 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 Score 重复时保留名次空缺 select * from (select sno, cno, score, rank() over(partition by cno order by score desc) order_sc from sc) a where a.order_sc in (2, 3); Score 重复时合并名次 select * from (select sno, cno, score, dense_rank() over(partition by cno order by score desc) order_sc from sc) a where a.order_sc in (2, 3); 23 、 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名 称,“100-85“,“85-70“,“70-60“,“0-60“及所占百分比 23.1 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名 称,“100-85“,“85-70“,“70-60“,“0-60“ select o, ame, b.“85-100“, b.“70-85“, b.“60-70“, b.“0-60“ from course a, (select 01 cno, (select count(1) from sc where cno = 01 and score = 85 and score = 70 and score = 60 and score = 85 and score = 70 and score = 60 and score = 85 and score = 70 and score = 60 and score = 60 and score = 70 and score = 85 and score = 60 and score = 60 and score = 70 and score = 70 and score = 85 and score = 85 and score = 60 and score = 60 and score = 70 and score = 70 and score = 85 and score = 85 and score = 60 and score = 60 and score = 70 and score = 70 and score = 85 and score = 85 and score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score = 85) a where st.sno = a.sno; 34、查询课程名称为“数学“，且分数低于60的学生姓名和分数 select st.sname,a.score from student st, (select sno, score from sc where cno = (select cno from course where cname = 数学) and score = 70) a where st.sno = a.sno and a.sno = sc.sno and o = o 37、查询不及格的课程 select st.*,ame,o,a.score from student st, course c, (select * from sc where score = 80) a where st.sno = a.sno and o = o 39、求每门课程的学生人数 select o,ame, from course c, (select cno,count(sno) cn from sc group by cno) a where o = o 40、查询选修“张三“老师所授课程的学生中，成绩最高的学生信息及其成绩 select st.*, ame, o, b.score from student st, course c, (select * from sc where score = (select max(score) from (select * from sc where cno = (select cno from course where tno = (select tno from teacher where tname = 张三) a) and cno = (select cno from course where tno = (select tno from teacher where tname = 张三) b where st.sno = b.sno and o = o 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select distinct sc1.sno, o, sc1.score from sc sc1,sc sc2 where sc1.score = sc2.score and o != o 42、查询每门功成绩最好的前两名 select st.sno, st.sname, st.sage, st.ssex, o, a.paim from student st, (select sno, cno, score, dense_rank() over(partition by cno order by score desc) paim from sc) a wher