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 sc2.score; 2.2、查询同时01课程比02课程分数低或01缺考的数据select s.sno, s.sname, s.sage, s.ssex, t.score_01, t.score_02 from student s, (select b.sno, a.score score_01, b.score score_02 from (select * from sc where cno = 01) a, (select * from sc where cno = 02) b where a.sno(+) = b.sno) t where s.sno = t.sno and (t.score_01 = 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 order by sno) t where s.sno = t.sno; 4.2、包括没有考试成绩的数据select g.* from (select s.sno, s.sname, nvl(t.avg_score, 0) avg_score from student s, (select sno, round(avg(score), 2) avg_score from sc group by sno order by sno) t where s.sno = t.sno(+) g where g.avg_score 60; 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、查询所有成绩的（不含缺考的）。 select s.sno,s.sname,c.count_cno,c.sum_scorefrom student s, (select sno,count(cno) count_cno,sum(score) sum_score from sc group by sno order by sno) c where s.sno = c.sno 5.2、查询所有成绩的(包括缺考的)。select a.s_sno, a.s_sname, nvl(a.c_cno, 0), a.s_score from (select s.sno s_sno, s.sname s_sname, c.count_cno c_cno, c.sum_score s_score from student s, (select sno, count(cno) count_cno, sum(score) sum_score from sc group by sno order by sno) c where s.sno = c.sno(+) a 6、查询李姓老师的数量 （有几个老师姓李）select count(tn) from (select tno, substr(tname, 0, 1) tn from teacher where substr(tname, 0, 1) = 李) a; 7、哪些学生上过张三（老师）的课select st.* from student st, course co, teacher te, sc where te.tno = co.tno and o = o and sc.sno = st.sno and te.tname = 张三 8、哪些学生没上过张三（老师）的课select * from studentminus select st.* from student st, course co, teacher te, sc where te.tno = co.tno and o = o and sc.sno = st.sno and te.tname = 张三 9、查询 01 02都学过的同学的信息select st.* from student st, (select * from sc where cno = 01) a, (select * from sc where cno = 02) b where st.sno = a.sno and st.sno = b.sno 10、查询学过编号为01但是没有学过编号为02的课程的同学的信息 select st.* from student st, (select sno from sc where cno = 01) minus (select sno from sc where cno = 02) a where st.sno = a.sno; 11、查询没有学全所有课程的同学的信息11.1 学完所有课程的 select st.* from student st, (select sno, count(cno) from sc group by sno having count(cno) = 3) a where st.sno = a.sno; 11.2 没有学完所有课程的select st.* from student st, (select sno from student minus select sno from sc group by sno having count(cno) = 3) a where st.sno = a.sno; 12、查询至少有一门课与学号为01的同学所学相同的同学的信息 select st.* from student st, (select distinct sno from sc where cno in (select cno from sc where sno = 01) and sno != 1) a where st.sno = a.sno; 13、查询和01号的同学学习的课程完全相同的其他同学的信息select st.* from student st, (select sno from (select sno, count(cno) CNT1 from sc group by sno) a, (select count(cno) CNT2 from sc where sno = 01) b where a.CNT1 = b.CNT2 and a.sno != 01) c where st.sno = c.sno; 14、查询没学过张三老师讲授的任一门课程的学生姓名select st.* from student st, (select sno from student minus select sno from sc where cno = (select o from teacher t, course c where t.tno = c.tno and tname = 张三) a where st.sno = a.sno; 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select a.sno, st.sname, a.avg_score from student st, (select sno, count(cno), round(avg(score), 2) avg_score from (select * from sc where 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 70) 60-70, (select count(1) from sc where cno = 01 and score = 85 and score = 70 and score = 60 and score 70) 60-70, (select count(1) from sc where cno = 02 and score = 85 and score = 70 and score = 60 and score 70) 60-70, (select count(1) from sc where cno = 03 and score 60) 0-60 from dual) b where o = o; 纵向显示 1(显示存在的分数段); select o, ame, b.ff, b.c1 from course a, (select cno, c1, 0-60 ff from (select cno, count(1) c1 from sc where score = 60 and score = 70 and score = 85 and score = 100 group by cno) b where o = o 23.2 统 计 各 科 成 绩 各 分 数 段 人 数 ： 课 程 编 号 , 课 程 名称,100-85,85-70,70-60,60及所占百分比 横向显示select o, ame, bb.0-60, bb.1百分比(%), bb.60-70, bb.2百分比(%), bb.70-85, bb.3百分比(%), bb.85-100, bb.4百分比(%) from course aa, (select 01 cno, (select count(1) from sc where cno = 01 and score 60) 0-60, round(select count(1) from sc where cno = 01 and score = 60 and score = 60 and score = 70 and score = 70 and score = 85 and score = 85 and score = 100) * 100 / (select count(1) from sc where cno = 01), 2) 4百分比(%) from dual) union (select 02 cno, (select count(1) from sc where cno = 02 and score 60) 0-60, round(select count(1) from sc where cno = 02 and score = 60 and score = 60 and score = 70 and score = 70 and score = 85 and score = 85 and score = 100) * 100 / (select count(1) from sc where cno = 02), 2) 4百分比(%) from dual) union (select 03 cno, (select count(1) from sc where cno = 03 and score = 70 and n.score = 60 and n.score = 85 then 85-100 when n.score = 70 and n.score = 60 and n.score 70 then 60-70 else 0-60 end) order by o, ame, px) t1, (select o, ame, count(1) all_num from course m, sc n where o = o group by o, ame order by o, ame) t2 where o = o 24、 查询学生的平均成绩并进行排名select c.sno, st.sname, c.avg_score, c.paim from student st, (select b.*, rownum paim from (select a.sno, round(avg(score), 2) avg_score from (select s.sno sno, nvl(sc.score, 0) score from student s, sc where s.sno = sc.sno(+) a group by a.sno order by avg_score desc) b) c where st.sno = c.sno; 25、查询各科成绩前三名的记录25.1 分数重复时保留名次空缺 select st.*, o, d.score from student st, (select a.* from (select * from sc where cno = 01 order by score desc) a where rownum = 3 union all select b.* from (select * from sc where cno = 02 order by score desc) b where rownum = 3 union all select c.* from (select * from sc where cno = 03 order by score desc) c where rownum = 3) d where st.sno = d.sno 25.2 分数重复时不保留名次空缺，合并名次select st.sno,st.sname,st.sage,st.ssex,o,b.score,b.paim from student st,(select sno,cno,score,paim from (select sno, cno, score, dense_rank() over(partition by cno order by score desc) paim from sc ) awhere a.paim = 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) awhere st.sno = a.sno and o = o 39、求每门课程的学生人数select o,ame, from course c, (select cno,count(sno) cn from sc group by cno) awhere 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 sc2where 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 where st.sno = a.sno and a.paim = 2 select st.sno, st.sname, st.sage, st.ssex, o, a.paim from student st, (select sno, cno, score, rank() over(partition by cno order by score desc) paim from sc) a where st.sno = a.sno and a.paim = 2) aa where st.sno = aa.sno 46、查询各学生的年龄select st.*, (to_char(sysdate, yyyy) - to_char(st.sage, yyyy) 年龄 from student st 47、查询本周过生日的学生select st.* from student st where to_char(st.sage, mmdd) between to_char(trunc(sysdate, iw), mmdd) and to_char(trunc(sysdate, iw) + 6, mmdd) 48、查询下周过生日的学生select st.* from student st where to_char(st.sage, mmdd) between to_char(trunc(sysdate, iw)+7, mmdd) and to_char(trunc(sysdate, iw) + 13, mmdd) select to_char(trunc(sysdate, iw)+7, mmdd) from dual; select to_char(trunc(sysdate, iw)+ 13, mmdd) from dual; 49、查询本月过生日的学生select st.* from student st where to_char(st.sage, mm) = to_char(sysdate,mm) 50、查询下月过生日的学生select st.* from student st where to_char(st.sage, mm) = to_char(add_months(trunc(sysdate),1),mm)