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

1、oracle数据库增删改查练习50例-答案一、建表-学生表 drop table student;create table student (sno varchar2(10,sname varchar2(10,sage date,ssex varchar2(10;i nsert into stude nt values('0赵雷 ',to_date('1990/01/01','yyyy/mm/dd',男'in sert into stude nt values('02 '钱电 ',to_date('1990

2、/12/21','yyyy/mm/dd'，男'in sert into stude nt values('03 '孙风 ',to_date('1990/05/20','yyyy/mm/dd',男'in sert into stude nt values('04 '李云 ',to_date('1990/08/06','yyyy/mm/dd',男'in sert into stude nt values('05 '周梅 &#

3、39;,to_date('1991/12/01','yyyy/mm/dd'，女'in sert into stude nt values('06 '吴兰 ',to_date('1992/03/01','yyyy/mm/dd'，女'in sert into stude nt values('07 '郑竹 ',to_date('1989/07/01','yyyy/mm/dd',女'in sert into stude nt valu

4、es('08'王菊 ',to_date('1990/01/20','yyyy/mm/dd',女'-课程表 drop table course;create table course (eno varchar2(10,c name varchar2(10,t no varchar2(10;i nsert into course values ('0 语文 ','02'i nsert into course values ('02'数学','01'i nsert

5、into course values ('03 '英语','03'-教师表 drop table teacher;create table teacher (tno varchar2(10,tname varchar2(10;i nsert into teacher values('01 张三'i nsert into teacher values('02 '李四 insert into teacher values('O3'王五'-成绩表 drop table sc;create table sc

6、 (sno varchar2(10,cno varchar2(10,score number(18,1;i nsert into sc values('01','01',80.0;i nsert into sc values('01','02',90.0;i nsert into sc values('01','03',99.0;i nsert into sc values('02','01',70.0;i nsert into sc values('02&#

7、39;,'02',60.0;i nsert into sc values('02','03',80.0;i nsert into scvalues('03','01',80.0;i nsert into sc values('03','02',80.0;i nsert into sc values('03','03',80.0;i nsert into sc values('04','01',50.0;i nsert i

8、nto sc values('04','02',30.0;i nsert into sc values('04','03',20.0;i nsert into sc values('05','01',76.0;i nsert into sc values('05','02',87.0;i nsert into sc values('06','01',31.0;i nsert into sc values('06',

9、9;03',34.0;i nsert into sc values('07','02',89.0;i nsert into sc values('07','03',98.0;commit;二、查询1.1、查询同时存在"01"课程和"02"课程的情况select s.sno, s.sname, s.sage, s.ssex, scl.score, sc2.score from stude nt s, sc sc1, sc sc2 where s.s no = scl.s no and

10、s.s no = sc2.s no and scl.c no = '01' and sc2.c no = '02'1.2、查询必须存在"01"课程，"02"课程可以没有的情况select t.*, s.score_01, s.score_02 from stude nt t inner join (select a.s no, a.score score_01, b.score score_02 from sc a left join (select * from sc where eno = '02' b

11、 on (a.s no = b.s no where a.c no = '01' s on (t.s no = s.s no;2.1、查询同时'01'课程比'02'课程分数低的数据select s.s no, s.s name, s.sage, s.ssex, sc1.score, sc2.score from stude nt s, sc sc1, sc sc2 where s.s no = sc1.s no and s.s no = sc2.s no and sc1.c no = '01' and sc2.c no = 

12、9;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 stude nt s, (select b.s no, a.score score_01,b. score score_02 from (select * from sc where eno = '01' a, (select * from sc where eno

13、 = '02' b where a.s no(+ = b.s no t where s.s no = t.s no and (t.score_01 < t.score_02 or t.score_01 is nu II;3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 select s.s no, s.s name, t.avg_score avg_score from stude nt s, (select sno, roun d(avg(score, 2 avg_score from sc group by sno hav ing avg(score

14、 >= 60 order by sno t where s.s no = t.s no;4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩4.1、有考试成绩，且小于 60 分 select s.sno, s.sname, t.avg_score avg_score from student s,(select sno, roun d(avg(score, 2 avg_score from sc group by sno hav ing avg(score < 60 order by sno t where s.s no = t.s no;4.2、包括没有考试成绩的数据

15、select g.* from (select s.sno, s.sname, n vl(t.avg_score, 0 avg_score from stude nt s, (select sno, roun d(avg(score, 2 avg_score from sc group by sno order by sno t where s.s no = t.s no(+ g where g.avg_score < 60;5.1、5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩查询所有成绩的(不含缺考的)。select s.s no ,s.s name,c.co un

16、t_cno ,c.sum_scorefrom stude nt s,(select sno ,co un t(c no count_cno ,sum(score sum_score from sc group by sno order by sno cwhere s.s no = c.s no5.2、查询所有成绩的(包括缺考的。select a.s_sno, a.s_sname, nvl(a.c_cno, 0, a.s_score from (select s.s no s_sno, s.s name s_sn ame, c.co unt_cno c_cno, c.sum_score s_sc

17、ore from stude nt s, (select sno, coun t(c no count_cno, sum(score sum_score from sc group by sno order by sno c where s.s no = c.s no(+ a&查询"李"姓老师的数量 (有几个老师姓李)select count(tn from (select tno, substr(t name, 0, 1 tn from teacher where substr(t name, 0, 1 李'a;7、哪些学生上过张三(老师)的课select

18、 st.* from stude nt st, course co, teacher te, sc where te.t no = co.t no and co.c no = sc.c no and sc.s no = st.s no and te.t namefe三'8、哪些学生没上过张三(老师)的课 select * from stude ntmi nus select st.* from stude nt st, course co, teacher te, sc where te.t no = co.t no and co.c no = sc.c no and sc.s no

19、= st.s no and te.t name =张三'9、查询'01' '02都学过的同学的信息 select st.* from student st, (select * from sc where eno = '01' a, (select * from sc where eno = '02' b where st.s no = a.s no and st.s no = b.s no10、查询学过编号为'01但是没有学过编号为02'的课程的同学的信息select st.* from stude nt st,

20、 (select sno from sc where eno = '01' minus (select sno from sc where eno = '02' a where st.s no = a.s no;11、查询没有学全所有课程的同学的信息 11.1学完所有课程的select st.* from stude nt st, (select sno, coun t(c no from sc group by sno hav ing coun t(c no = 3 a where st.s no = a.s no;11.2 没有学完所有课程的 select

21、st.* from student st,(select sno from stude nt minus select sno from sc group by sno hav ing coun t(c no = 3 a where st.s no = a.s no;12、查询至少有一门课与学号为'01'的同学所学相同的同学的信息select st.* from stude nt st, (select disti net sno from sc where eno in (select eno from sc where sno = '01' and sno

22、匸 1 a where st.s no = a.s no;13、查询和'01'号的同学学习的课程完全相同的其他同学的信息select st.* from stude nt st, (select sno from (select sno, coun t(c no CNT1 from sc group by sno a, (select coun t(c no CNT2 from sc where sno = '01' b where a.CNT1 = b.CNT2 and a.s no != '01' c where st.s no = c.sn

23、o;14、查询没学过"张三"老师讲授的任一门课程的学生姓名select st.* from stude nt st, (select sno from stude nt minus select sno from sc where eno = (select c.c no from teacher t, course c where t.t no = c.t no and tn ame 张三'a where st.s no = a.s no;15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select a.s no, st.s name, a.avg_

24、score from stude nt st, (select sno, coun t(c no, roun d(avg(score, 2 avg_score from (select * from sc where score < 60 group by sno a where st.s no = a.s no;16、检索'01'课程分数小于60,按分数降序排列的学生信息 select st.*, o.a.score from stude nt st, (select sno, eno, score from sc where eno = '01' an

25、d score < 60 order by score desc a where st.s no = a.s no;17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select st.s no, st.s name, c.score, d.score, e.score, a.avg_score from stude nt st, (select sno, roun d(avg(score, 2 avg_score from sc group by snoorder by avg_score desc a, (select * from sc where eno = 

26、9;01' c, (select * from sc where eno = '02' d, (select * from sc where eno = '03' e where st.s no = a.s no(+ and st.s no =c.sno(+ and st.s no = d.s no(+ and st.s no = e.s no(+18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name,最高分，最低分，平均分，及格率，中等率，优良率，优秀率 select o "课程编号", ame "

27、;课程名称", b.max_score 最高分", c.min_score "最低分", d.avg_socre平均分",e."及格率",e."中等率",e."优良率",e."优秀率"from course a, (select eno, max(score max_score from sc group by eno b, (select eno, min( score min _score from sc group by eno c, (select eno,

28、 roun d(avg(score, 2 avg_socre from sc group by eno d, (select '01' eno, roun d(select coun t(1 from sc where eno = '01' and score >= 60 / (select cou nt(1 from sc where eno = '01' * 100, 2 及格率",rou nd(select coun t(1 from sc where eno = '01' and score >= 7

29、0 and score < 80 / (select coun t(1 from sc where eno = '01' * 100, 2 "中等率", round(select count(1 from sc where eno = '01' and score >= 80 and score < 90 / (select cou nt(1 from sc where eno = '01' * 100, 2优良率", roun d(select coun t(1 from sc where eno

30、 = '01' and score >= 90 / (select coun t(1 from sc where eno = '01' * 100, 2 "优秀率"from dual union select '02' cno, round(select count(1 from sc where eno = '02' and score >= 60 / (select coun t(1 from sc where eno = '02' * 100,2 "及格率"

31、,ro un d(select coun t(1 from sc where eno = '02' and score >= 70 and score <80 / (select count(1 from sc where eno = '02' * 100, 2 中等率",roun d(select coun t(1 from sc where eno = '02' and score >= 80 and score < 90 / (select co un t(1 from sc where eno = 

32、9;02' * 100, 2 忧良率", rou nd(select cou nt(1 from sc where eno = '02' and score >= 90 / (select co un t(1 from sc where eno = '02' * 100, 2优 秀率"from dual union select '03' cn o, ro un d(select coun t(1 from sc where eno = '03' and score >= 60 / (sel

33、ect cou nt(1 from sc where eno = '03' * 100, 2 及格率",rou nd(select coun t(1 from sc where eno = '03' and score >= 70 and score < 80 / (select coun t(1 from sc where eno = '03' * 100, 2 "中等率", round(select count(1 from sc where eno = '03' and score

34、>= 80 and score < 90 / (select cou nt(1 from sc where eno = '03' * 100, 2优良率", roun d(select coun t(1 from sc where eno = '03' and score > 90 / (select coun t(1 from scwhere eno = '03' * 100, 2 '优秀率"from dual e where o = o and o = o and a.c no = d.c no

35、and a.c no = e.c no;19、按各科成绩进行排序，并显示排名select sno, eno, score,rank( over( partiti on by eno order by score desc 名次"from sc;select sno, eno, score, den se_ra nk( over( partiti on by eno order by score des名 次"from sc;20、查询学生的总成绩并进行排名 20.1查询学生的总成绩select a.sno, a.sname, n vl(b.sum_score, 0 总、成绩

36、"from stude nt a, (select sno, sum(score sum_score from sc group by sno order by sno b where a.s no = b.s no(+;20.2查询学生的总成绩并进行排名。select c."学生编号",c."学生姓名",c."总成绩",rank( over (order by c."总成绩"desc "排名"from (select a.sno "学生编号", a.sname &

37、quot;学生姓名",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.s no = b.s no(+ c21、查询不同老师所教不同课程平均分从高到低显示select a.t no, a.t name,c. avg_score 平均分"from teacher a, course b, (select eno, roun d(avg(score, 2 avg_score f

38、rom sc group by eno c where a.t no = b.t no and b.c no = c.c no order by平均分"desc;22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score重复时保留名次空缺 select * from (select sno, eno, score, rank( over(partition by eno order by score desc order_sc from sc a where a.order_sc in (2, 3;Score重复时合并名次 select * from (select sn

39、o, eno, score, den se_ra nk( over( partiti on by eno 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&q

40、uot;,"0-60"select o, ame, b."85- 100", b."70-85", b."60-70", b."0-60" from course a, (select '01' cno, (select cou nt(1 from sc where cno = '01' and score >= 85 and score <= 100 "85-100", (select coun t(1 from sc where

41、 cno = '01' and score >= 70 and score < 85 "70-85", (select coun t(1 from sc where cno = '01' and score >= 60 and score < 70 "60-70", (select coun t(1 from sc where cno = '01' and score < 60 "0-60" from dual un io n select '02&

42、#39; cno, (select co un t(1 from scwhere cno = '02' and score >= 85 and score <= 100 "85-100", (select cou nt(1 from sc where cno = '02' and score >= 70 and score < 85 "70-85", (select coun t(1 from sc where cno = '02' and score >= 60 and sc

43、ore < 70 "60-70", (select coun t(1 from sc where cno = '02' and score < 60 "0-60" from dual union select '03' cno, (select coun t(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", (select co un t(1 from sc where cn

44、o = '03' and score >= 70 and score < 85 "70-85", (select coun t(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", (select coun t(1 from sc where cno = '03' and score < 60 "0-60" from dual b where o = o;纵向显示1（显示存在的分

45、数段;select a.c no, a.c name, b.ff, b.c1 from course a, (select cno, c1, '0-60' ff from (select cno, coun t(1 c1 from sc where score < 60 group by cno union select cno, c1, '60-70' ff from (select cno, coun t(1 c1 from sc where score >= 60 and score < 70 group by cno union sel

46、ect eno, c1,70-85' ff from (select eno, coun t(1 c1 from sc where score >= 70 and score < 85 group by eno union select eno, c1, '85-100' ff from (select eno, coun t(1 c1 from sc where score >= 85 and score <= 100group by eno b where a.c no = b.c no23.2统计各科成绩各分数段人数：课程编号,课程名称,&

47、quot;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, (

48、select '01' eno, (select co un t(1 from sc where eno = '01' and score < 60 "0-60", roun d(select cou nt(1 from sc where eno = '01' and score < 60 * 100 / (select count(1 from sc where eno = '01', 2 "百分比(%", (select coun t(1 from sc where eno

49、= '01' and score >= 60 and score < 70 "60-70", ro un d(select coun t(1 from sc where eno = '01' and score >= 60 and score < 70 * 100 / (select count(1 from sc where eno = '01', 2 "2百分比(%", (select count(1 from sc where eno = '01' and sc

50、ore >= 70 and score < 85 "70-85", ro un d(select coun t(1 from sc where eno = '01' and score >= 70 and score < 85 * 100 / (select cou nt(1 from sc where eno = '01', 2 '百 分比(%", (select co un t(1 from sc where eno = '01' and score >= 85 and sc

51、ore <= 100 "85-100", roun d(select coun t(1 from sc where eno = '01' and score >= 85 and score <= 100 * 100 /(select count(1 from sc where eno = '01', 2 "4百分比(%" from dual union (select '02' eno, (select coun t(1 from scwhere eno = '02' an

52、d score < 60 "0-60", ro un d(select coun t(1 from sc where eno ='02' and score < 60 * 100 / (select count(1 from sc where eno = '02', 2 "百分比(%", (select coun t(1 from sc where eno = '02' and score >= 60 and score < 70 "60-70", roun d(

53、select coun t(1 from sc where eno = '02' and score >= 60 and score < 70 * 100 / (select count(1 from sc where eno = '02', 2 "2百分比(", (select count(1 from sc where eno = '02' and score >= 70 and score < 85 "70-85", ro un d(select coun t(1 from s

54、c where eno = '02' and score >= 70 and score < 85 * 100 / (select coun t(1 from sc where eno = '02',2 "3 百分比(", (select count(1 from sc where eno = '02' and score >= 85 and score <=100 "85-100", roun d(select cou nt(1 from sc where eno = '0

55、2' and score >= 85 and score <=100 * 100 / (select count(1 from sc where cno = '02', 2 "4百分比(%" from dual union (select '03' cno, (select co un t(1 from sc where cno = '03' and score < 60 "0-60", roun d(select cou nt(1 from sc where cno = '

56、;03' and score < 60 * 100 / (select co un t(1 from sc where cno = '03', 2 "1 百分比(%", (select count(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", ro un d(select coun t(1 from sc where cno = '03' and score >= 60 and score

57、 < 70 * 100 / (select cou nt(1 from sc where cno = '03', 2 '百 分比(%",(select coun t(1 from sc where cno = '03' and score >= 70 and score < 85 "70-85", roun d(select coun t(1 from sc where cno = '03' and score >= 70 and score < 85 * 100 / (sele

58、ct count(1 from sc where cno = '03', 2 "3百分比(%", (select count(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", ro un d(select cou nt(1 from sc where cno = '03' and score >= 85 and score <= 100 * 100 / (select coun t(1 from

59、 sc where cno = '03', 2 "4百分比(%" from dual bb where o = o;纵向显示select t1.*, ro un d(t1. num / t2.all_num * 100, 2 | '%'百分比 from (select m.c no, m.c name, (case whe n n. score >= 85 the n '85-100' whe n n. score >= 70 and n. score < 85 then '70-85' wh

60、e n n .score >= 60 and n .score < 70 then '60-70' else '0-60' end as px, coun t(1 num from Course m, sc n where m.c no = o group by m.c no, m.C name, (case whe n n. score >= 85 the n '85-100' whe n n. score >= 70 and n. score < 85 the n '70-85' whe n n.

61、 score >= 60 and n. score < 70 the n '60-70' else '0-60' end order by o, ame, px t1, (select m.c no, m.c name, coun t(1 all_ num from course m, sc n where m.c no = o group by m.c no, m.c name order by m.c no, m.c name t2 where t1.c no = t2.c no24、查询学生的平均成绩并进行排名 select c.sno, st

62、.sname, c.avg_score, c.paimfrom stude nt st, (select b.*, row num p aim from (select a.s no, roun d(avg(score, 2 avg_score from (select s.s no sno, n vl(sc.score, 0 score from stude nt s, sc where s.s no = sc.s no(+ a group by a.s no order by avg_score desc b c where st.s no = c.s no;25、查询各科成绩前三名的记录

63、25.1分数重复时保留名次空缺select st.*,d. c no, d.score from stude nt st, (select a.* from (select * from sc where eno = '01' order by score desc a where row num <= 3 union all select b.* from (select * from sc where cno = '02' order by score desc b where row num <= 3 union all select c.*

64、from (select * from sc where eno = '03' order by score desc c where row num <= 3 d where st.s no = d.s no25.2分数重复时不保留名次空缺，合并名次select st.s no ,st.s name,st.sage,st.ssex,b.c no ,b.score,b .p aimfrom stude nt st,(select sno,cno ,score ,p aim from (select sno, eno, score, den se_ra nk( over(

65、partiti on by eno order by score desc p aim from sc awhere a.p aim <= 3 bwhere st.s no = b.s no26、查询每门课程被选修的学生数 select cno ,cou nt(1 from sc group by cno;27、查询出只有两门课程的全部学生的学号和姓名select st.s no, st.s name from stude nt st, (select a.s no from (select sno, coun t(c no cn from sc group by sno a where

66、 a.c n =2 b where st.s no = b.s no;28、查询男生、女生人数 select男生人数'"男女情况", cn from (select count(ssex cn from student where ssex =男'union allselect 女生人数'"男女情况", cn from (select coun t(ssex cn from stude nt where ssex 女'29、查询名字中含有"风"字的学生信息select * from student wh

67、ere sname like '%风%'31、查询1990年出生的学生名单（注：Student表中Sage列的类型是dateselect * from stude nt where to_char(sage,'yyyy' = '1990'32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时,按课程编号升序排歹y select o ,ame,a.avg_scorefrom course c,(select cn o,ro un d(avg(score,2 avg_score from sc group by eno awhere c.c no = a.c no order by a.avg_score desc ,c.c no;33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select st.s no, st.s name, a.avg_score from stude nt st, (select sno, roun d(avg(score, 2 avg_score from sc group by sno hav ing roun d(avg(score, 2 >= 85 a where st.s no = a.s no;34、查询课程名称为"数