oracle数据库增删改查练习50例

1、一教师表teacher values C 01 张二;insert intoteacher values (J 02，李四insert into teacher values (03'王五-成绩表 drop tablesc;createtable sc (sno varchar2 (10,eno varchar2(10,score number(18, 1;insert into sc values ('01', ' 017, 80. 0; ivalues (J GT , 5 03, , 99. 0; i nsert into scnsert into sc v

2、alues ('02', ' 03', 80. 0; insert into sc values ('01', ' 02', 90. 0; i values ('02', 01', 70. 0; i nsert into sc nsert into scnsert into scvalues C 02',02',60.0;ivalues ('03', ' 017, 80. 0; i nsert into sc nsert into sc values ('04

3、', ' 01', 50. 0; i values ('04', ' 03', 20. 0; i nsert into sc nsert into sc values ('06', ' 01', 31. 0; i values ('07', ' 02', 89. 0; i nsert into scvalues ('03'02', 80. 0; i nsert into sc nsert into sc values (' 04', &

4、#39; 02' , 30. 0; i values ('05', 01', 76. 0; i nsert into sc nsert into sc values ('06', ' 03', 34. 0; i values C 07','03', 98.0;valuesC 03,/ 0380.0;i nsert into scvalues C 05，,，02，,87.0;i nsert into scoracle数据库增删改查练习50例-答案-建表一学生表droptablestudent;createta

5、blestudent(snovarchar2(10,snamevarchar2(10,sagedate,ssexvarchar2(10;insertintostudentvalues。0赵宙,to_date('1990/01/01','yyyy/mm/dd',男；insertintostudentvaluesC02'钱电,to_date('1990/12/21'yyyy/mm/dd',男'insertintostudentvaluesC03，孙风,to_date('1990/05/20','yyyy

6、/mm/dd',男；insertintostudentvaluesC04'李玄,to_date('1990/08/06,'yyyy/mm/dd',男'；insertintostudentvaluesC05周梅,to_date('1991/12/017yyyy/mm/dd',女;insertintostudentvaluesC06'吴兰,to_date('1992/03/01'yyyy/mm/dd',女'；insertintostudentvaluesC07'关B竹,to_date(&

7、#39;1989/07/01,，'yyyy/mm/dd',女'；insertintostudentvalues('08'王朱),to_date('1990/01/20','yyyy/mm/dd',女'；-课程表droptablecourse;createtablecourse(enovarchar2(10,cnamevarchar2(10,tnovarchar2(10;insertintocoursevalues('0语文，02'insertintocoursevalues('02数学

8、9;，'01'insertintocoursevalues('03'央语，03;droptableteacher;createtableteacher(tnovarchar2(10,tnamevarchar2(10;insertintocommit;二、查询1.1、查询同时存在01课程和02课程的情况selects.sno,s.sname,s.sage,s.ssex,scl.score,sc2.scorefromstudents,scscl,scsc2wheres.sno=scl.snoands.sno二o='01'o='02'1

9、.2、查询必须存在01课程，02课程可以没有的情况selectt.*,s.score_01,s.score_02fromstudenttinnerjoin(selecta.sno,a.scorescore_01,b.scorescore_02fromscaleftjoin(select*fromscwhereeno='02'bon(a.sno=o='GTson(t.sno=s.sno;1.1、 查询同时，or课程比，02,课程分数低的数据selects.sno,s.sname,s.sage,s.ssex,scl.score,sc2.scorefromstudents,s

10、cscl,scsc2wheres.sno=scl.snoands.sno=o='01'o='02'andscl.score<sc2.score;1.2、 查询同时FT课程比，02,课程分数低或01缺考的数据selects.sno,s.sname,s.sage,s.ssex,t.score_01,t.score_02fromstudents,(selectb.sno,a.scorescore_01,b. scorescore_02from(select*fromscwhereeno='01'a,(select*fromscwhereeno=&

11、#39;02'bwherea.sno(+=b.snotwheres.sno=t.snoand(t.score_01<t.score_02ort.score_01isnuII;3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selects.snos.sname,t.avg_scoreavg_scorefromstudents,(selectsno,round(avg(score,2avg_scorefromscgroupbysnohavingavg(score=60orderbysnotwheres.sno=t.sno;4、查询平均成绩小于60分的同学的学生编号和

12、学生姓名和平均成绩4.1、有考试成绩,且小于60分selects.sno,s.sname,t.avg_scoreavg_scorefromstudents,(selectsno,round(avg(score,2avg_scorefromscgroupbysnohavingavg(score<60orderbysnotwheres.sno=t.sno;4.2、包括没有考试成绩的数据selectg.*from(selects.sno,s.sname,nvl(t.avg_score,0avg_scorefromstudents,(selectsno,round(avg(score,2avg_

13、scorefromscgroupbysnoorderbysnotwheres.sno=t.sno(+gwhereg.avg_score<60;5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、查询所有成绩的（不含缺考的）。selects.sno,s.sname,c.count_cno,c.sum_scorefromstudents,(selectsno,count(cnocount_cno,sum(scoresum_scorefromscgroupbysnoorderbysnocwheres.sno=c.sno5.2、查询所有成绩的（包括缺考的。selecta.s_

14、sno,a.s_sname,nvl(a.c_cno,0,a.s_scorefrom(selects.snos_sno,s.snames_sname,c.count_cnoc_cno,c.sum_scores_scorefromstudents,(selectsno,count(cnocount_cno,sum(scoresum_scorefromscgroupbysnoorderbysnocwheres.sno=c.sno(+a&查询李姓老师的数量(有几个老师姓李)selectcount(tnfrom(selecttno,substr(tname,0,1tnfromteacherwhe

15、resubstr(tname,0,1李'a;7、哪些学生上过张三(老师)的课selectst.*fromstudentst,courseco,teacherte,scwherete.tno=o=oandsc.sno二st.snoandte.tnamefe二'8、哪些学生没上过张二(老师)的课select*fromstudentminusselectst.*fromstudentst,courseco,teacherte,scwherete.tno=o二oandsc.sno二st.snoandte.tname二张二，9、查询，017，02都学过的同学的信息selectst.*fr

16、omstudentst,(select*fromscwhereeno二'Ora,(select*fromscwhereeno='02'bwherest.sno=a.snoandst.sno=b.sno10.查询学过编号为，01但是没有学过编号为，02,的课程的同学的信息selectst.*fromstudentst,(selectsnofromscwhereeno='01'minus(selectsnofromscwhereeno='02'awherest.sno=a.sno;11、查询没有学全所有课程的同学的信息11学完所有课程的sel

17、ectst.*fromstudentst,(selectsno,count(cnofromscgroupbysnohavingcount(cno=3awherest.sno=a.sno;11.2没有学完所有课程的selectst.*fromstudentst,(selectsnofromstudentminusselectsnofromscgroupbysnohavingcount(cno=3awherest.sno=a.sno;12、查询至少有一门课与学号为，01，的同学所学相同的同学的信息selectst.*fromstudentst,(selectdistinetsnofromscwhe

18、reenoin(selectenofromscwheresno='01'andsno1_J1awherest.sno=a.sno;13、查询和，01/号的同学学习的课程完全相同的其他同学的信息selectst.*fromstudentst,(selectsnofrom(selectsno,count(cnoCNT1fromscgroupbysnoa,(selectcount(cnoCNT2fromscwheresno='01'bwherea.CNT1=b.CXT2anda.sno!='01'cwherest.sno=c.sno;select st

19、.* from stude nt st,c. c no from teacher t, course c14、查询没学过张三老师讲授的任一门课程的学生姓名(selectsnofromstudentminusselectsnofromscwhereeno=(selectwheret.tno=c.tnoandtname弓长二'awherest.sno=a.sno;15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selecta.sn。，st.sname,a.avg_scorefromstudentst,(selectsno,count(cno,round(avg(score,2

20、avg_scorefrom(select*fromscwherescore<60groupbysnoawherest.sno=a.sno;16、检索，01，课程分数小于60,按分数降序排列的学生信息selectst.*,a.enoa.scorefromstudentst,(selectsno,eno,scorefromscwhereeno='01andscore<60orderbyscoredescawherest.sno=a.sno;17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩selectst.sno,st.sname,c.score,d.score,

21、e.score,a.avg_scorefromstudentst,(selectsno,round(avg(score,2avg_scorefromscgroupbysnoorderbyavg_scoredesca,(select*fromscwhereeno二01'c,(select*fromscwhereeno二02'd,(select*fromscwhereeno='03，ewherest.sno=a.sno(+andst.sno=c.sno(+andst.snod.sno(+andst.sno=e.sno(+18、查询各科成绩最高分、最低分和平均分：以如下形式显

22、示：课程ID,课程号格name,最局分，最低分，平均分，及格率，中等率，优良率，优秀率selecta.eno课程编，ame课程名称,b.max_score最高分，c.min_score最低分，d.avg_socre平均分,e.及max (score max_score from sc c, (select eno, roun d(select coun t(1 from sc '01' * 100, 2 及格率,rou率,e.中等率,e.优良率,e.优秀率fromcoursea,(selecteno,groupbyenob,(selecteno,min(scoremin_sco

23、refromscgroupbyenod(avg(score,2avg_socrefromscgroupbyenod,(select01'eno,rounwhereeno='01'andscore>=60/(selectcount(1fromscwhereeno二nd(selectcount(1fromscwhereeno='01'andscore>=70andscore<80/(selectcount(1fromscwhereeno=01'*100,2中等率，round(selectcount(1fromscwhereeno=&

24、#39;01'andscore>=80and score < 90 / (select cou nt(1 from sc where eno'01' * 100, 2 优良率,roun d(select coun t(1from sc where eno01' and score >= 90 / (select coun t(1 from sc where eno 二'0T * 100, 2 优秀率“fromdualunionselect02'eno,round(selectcount(1fromscwhereeno='

25、02'andscore>=60/(selectcount(1fromscwhereeno='02'*100,2及格率，round(selectcount(1fromscwhereeno='02'andscore>=70andscore<80 /(select count (1 from sc where eno02' * 100, 2 中等率,roun d( (select coun t (1 from sc where eno = '02' and score >= 80 and score < 9

26、0 / (select co unt (1 from sc where eno = '02' * 100, 2 忧良率，rou nd(select cou nt (1 from sc where eno =02' andscore >= 90/ (select co un t (1 from sc where eno = '02' * 100, 2 优 秀率from dual union select '03'cn o, ro und(select coun t(1 from sc where eno = '03'

27、and score >= 60 / (select cou nt(1 from scwhere eno =F3, * 100, 2 及格率,rou nd( (select coun t (1 from sc where eno ='03' and score >= 70and score <80 / (select coun t (1 from sc where eno = ' 03' * 100,2 中等率，round (select count (1cou nt (1 from sc where eno = '03' *an

28、d score > 90 / (select coun t (1fromscwhereeno='03'andscore>=80andscore<90/(select100,2优良率，round(selectcount(1fromscwhereeno=03'fromscwhereeno='03'*100,2'优秀率fromdualewherea.eno=b.enoanda.eno=o=o=o;19、按各科成绩进行排序，并显示排名selectsno,eno,score,rank(over(partitionbyenoorderbys

29、coredesc名次fromsc;selectsno,eno,score,dense_rank(over(partitionbyenoorderbyscoredes名次fromsc;20、查询学生的总成绩并进行排名20.1查询学生的总成绩selecta.sno,a.sname,nvl(b.sum_score,0总、成绩“fromstudenta,(selectsno,sum(scoresum_scorefromscgroupbysnoorderbysnobwherea.sno=b.sno(+;20.2查询学生的总成绩并进行排名。selectc.学生编号,c.学生姓名,c.总成绩”,rank(

30、over (order by c. 总成绩desc 排名 from (selecta. sno学生编号,a. sname学生姓名，z,nvl(b.sum_score,0总成绩fromstudenta,(selectsno,sum(scoresum_scorefromscgroupbysnoorderbysnobwherea.sno=b.sno(+c21、查询不同老师所教不同课程平均分从高到低显示select a. t no, a. t name,c. avg_score平均分fromteachera,courseb,(selecteno,round(avg(score,2avg_scorefr

31、omscgroupbyenocwherea.tno=o=oorderby平均分desc;22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score重复时保留名次空缺select*from(selectsno,eno,score,rank(over(partitionbyenoorderbyscoredescorder_scfromscawherea.order_scin(2,3;Score重复时合并名次select*from(selectsno,eno,score,dense_rank(over(partitionbyenoorderbyscoredescorder_scfroms

32、cawherea.order_scin(2,3;23、统计各科成绩各分数段人数：课程编号，课程名称,100-85,85-70,70-60,0-60及所占百分比23.1统计各科成绩各分数段人数：课程编号，课程名称，100-85，85-70，70-60，o-60selecta.eno,ame,b."85-100",b.70-85,b.60-70，b.0-60fromcoursea,(select'01/eno,(selectcount(1fromscwhereeno=01'andscore>=85andscore<=10085-100，(select

33、count(1fromscwhereeno='01'andscore>=70andscore<8570-85，(selectcount(1fromscwhereeno='01'andscore>=60andscore<7060-70”,(selectcount(1fromscwhereeno='01'andscore<600-60fromdualunionselect02'eno,(selectcount(1fromscwhereeno=02'andscore>=85andscore<=1

34、0085-100，(selectcount(1fromscwhereeno='02'andscore>=70andscore<8570-85，(selectcount(1fromscwhereeno二02'andscore>=60andscore<7060-70，(selectcount(1fromscwhereeno='02'andscore<600-60fromdualunionselect'03'eno,(selectcount(1fromscwhereeno='03'andscore&

35、gt;=85andscore<=10085-100，(selectcount(1fromscwhereeno='03'andscore>=70andscore<8570-85，(selectcount(1fromscwhereeno=03'andscore>=60andscore<7060-70，(selectcount(1fromscwhereeno二'03'andscore<60。-60fromdualbwherea.eno=b.eno;纵向显示1（显示存在的分数段;o,ame,b.ff,b.clfromcours

36、ea,(selecteno,cl,0-60'fffrom(selecteno,count(1clfromscwherescore<60groupbyenounionselecteno,cl,'60-70'fffrom(selecteno,count(1clfromscwherescore>=60andscore<70groupbyenounionselecteno,cl,70-85'fffrom(selecteno,count(1clfromscwherescore>=70andscore<85groupbyenounionsele

37、cteno,cl,85T00'fffrom(selecteno,count(1clfromscwherescore=85andscore<=100o=o23.2统计各科成绩各分数段人数：课程编号,课程名称,100-85,85-70,"0-60,<60及所占百分比横向显示selectaa.eno,ame,bb.。-60，bb.1百分比（%”,bb.60-70”,bb.2百分比(%，bb.”70-85，bb.3百分比(%,bb.85-100，bb.4百分比(fromcourseaa,(select*0Teno,(selectcount(1fromscwhereeno=

38、'01'andscore<600-60，round(selectcount(1fromscwhere eno = '01'and score < 60 * 100 / (select count (1 from sc where eno2 百分比（%,(selectcount(1fromscwhereeno='01'andscore>=60andscore<7060-70，round(selectcount(1fromscwhereeno='01'andscore>=60andscore<70*

39、100/(selectcount(1fromscwhereeno二and score= 70 and score < 85 70-85, 70 and score < 85 * 100 / (select cou'01',2"2百分比(%,(selectcount(1fromscwhereeno='01'round(selectcount(1fromscwhereeno=01'andscore>=nt(1fromscwhereeno='01',2'百分比(%，(selectcount(1fromscwh

40、ereeno='01/andscore>=85andscore<=10085-100，round(selectcount(1fromscwhereeno='01'andscore>=85andscore<=100*100/(selectcount(1fromscwhereeno='01',24白分匕匕(%fromdualunion(select02'eno,(selectcount(1fromscwhereeno='02'andscore<600-60，round(selectcount(1froms

41、cwhereeno'02'andscore<60*100/(selectcount(1fromscwhereeno='02',2"百分比(，(selectcount(1fromscwhereeno='02'andscore>=60andscore<7060-70，round(selectcount(1fromscwhereeno='02'andscore>=60andscore<70*100/(selectcount(1fromscwhereeno='02',2"2

42、百分比(%，(selectcount(1fromscwhereeno='02'andscore>=70andscore<8570-85，round(selectcount(1fromscwhereeno='02'andscore>=70andscore85*100/(selectcount(1fromscwhereeno='02',2 3百分比(,(selectcount(1fromscwhereeno='02'andscore=85andscore<=10085-100，round(selectcount

43、(1fromscwhereeno='02'andscore>=85andscore<=100*100/(selectcount(1fromscwhereeno='02',24百分比(%fromdualunion(select03'eno,(selectcount(1fromscwhereeno='03'andscore<600-60，round(selectcount(1fromscwhereeno二'03'andscore<60*100/(selectcount(1fromscwhereeno=&

44、#39;03',21百分比(，(selectcount(1fromscwhereeno=03'andscore>=60andscore<7060-70，round(selectcount(1fromscwhereeno='03'andscore>=60andscore<70*100/(selectcount(1fromscwhereeno='03',2'百分比(%，(selectcount(1fromscwhereeno='03'andscore>=70andscore<8570-85，

45、round(selectcount(1fromscwhereeno='03'andscore>=70andscore<85*100/(selectcount(1fromscwhereeno='03,23百分比(%，(selectcount(1fromscwhereeno='03'andscore>=85andscore<=10085-100，round(selectcount(1fromscwhereeno='03'andscore>=85andscore<=100*100/(selectcount(1

46、fromscwhereeno='03',24百分比(%fromdualbbwhereaa.eno=bb.eno;纵向显示selecttl.*,round(tl.num/t2.all_num*100,2|'日分t匕from(o,ame,(casewhenn.score>=85then'85-100'whenn.score>=70andn.score<85then'70-85'whenn.score>=60andn.score<70then'60-70'else'0-60'enda

47、spx,count(1numfromCoursem,scno=o,m.Cname,(casewhenn.score>=85then'85-100"whenn.score>=70andn.score<85then'70-85，whenn.score>=60andn.score<70then'60-70'else0-60'endorderbym.eno,ame,pxtl,(o,ame,count(1all_numfromcoursem,o=o,o,o=o24>食网字生的平均成项开进仃琲第selectc.sno,s

48、t.sname,c.avg_score,c.paimfromstudentst,(selectb.*,rownumpaimfrom(selecta.sno,round(avg(score,2avg_scorefrom(selects.snosno,nvl(sc.score,0scorefromstudents,scwheres.sno=sc.sno(+agroupbya.snoorderbyavg_scoredescbcwherest.sno=c.sno;25、查询各科成绩前三名的记录25.1分数重复时保留名次空缺selectst.*,d. c no, d. score from stude

49、nt st, (select a. * from desc a where row num <= 3 union all select b. * from desc b where row num <= 3 union all select c. * from(select * from sc where(select * from sc where(select * from sc whereeno = ' 01' eno = ' 02' eno = '03'order by scoreorder by scoreorder by

50、scoredesccwhererownum<=3dwherest.sno=d.sno25.2分数重复时不保留名次空缺，合并名次selectst.sno,st.sname,st.sage,st.ssex,o,b.score,b.paimfromstudentst,(selectsno,eno,score,paimfrom(selectsno,eno,score,dense_rank(over(partitionbyenoorderbyscoredescpaimfromscawherea.paim<=3bwherest.sno=b.sno26、查询每门课程被选修的学生数selecten

51、o,count(1fromscgroupbyeno;27、查询出只有两门课程的全部学生的学号和姓名selectst.sno,st.snamefromstudentst,(selecta.snofrom(selectsno,count(=2bwherest.sno=b.sno;28、查询男生、女生人数select男生人数，男女情况，cnfrom(selectcount(ssexcnfromstudent where ssex二男'union allselect女生人数男女情况,cn from (select coun t (ssex cn fromstude nt where ssex女

52、29、查询名字中含有风字的学生信息select*fromstudentwheresnamelike'%风%'31、查询1990年出生的学生名单(注：Student表中Sage歹U的类型是dateselect*fromstudentwhereto_char(sage,'yyyy，=199032、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时,按课程编号升序排歹yselect c.eno , c. cname, a. avg_scorefrom course c, (select cn o, ro un d(avg (score,2 avg_score fr

53、om 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 n。，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;3

54、4、查询课程名称为数学，且分数低于60的学生姓名和分数select st. s name, a. scorefrom stude nt st, (select sno, score from sc where eno = (select eno fromcourse where cn ame =数学'and score < 60 awhere st. s no = a. s no35、查询所有学生的课程及分数情况select st. c. cname, sc. c no, st, course c, sc where st. s no = sc. s no and sc. c n

55、o = c. c nosc.score from stude nt36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；select st.*, c.c name, sc.c no, sc.score from stude nt st, course c, sc, (select sno, mi n (score from sc group by sno hav ing min( score = 70 a where st. s no = a. s no and a. s no = sc. s no and sc. c no = c. c no37、查询不及格的课程selectst. *, c. cname, a. eno, a. scorefrom student st, course c, (select *from sc where score < 60 awhere st. s no = a. s noand c. eno = a. c no38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名select st.*, c.c name, a. c no , a. scorefrom stude nt st,course c, (select sn o,c no, scorefrom sewhere eno