oracle数据库增删改查练习50例答案精文档格式.docx
《oracle数据库增删改查练习50例答案精文档格式.docx》由会员分享,可在线阅读,更多相关《oracle数据库增删改查练习50例答案精文档格式.docx(8页珍藏版)》请在冰点文库上搜索。
04'
李云'
1990/08/06'
05'
周梅'
1991/12/01'
女'
06'
吴兰'
1992/03/01'
07'
郑竹'
1989/07/01'
08'
王菊'
1990/01/20'
--课程表droptablecourse;
createtablecourse(cnovarchar2(10,cnamevarchar2(10,tnovarchar2(10;
insertintocoursevalues('
语文'
数学'
英语'
--教师表droptableteacher;
createtableteacher(tnovarchar2(10,tnamevarchar2(10;
insertintoteachervalues('
张三'
李四'
王五'
--成绩表droptablesc;
createtablesc(snovarchar2(10,cnovarchar2(10,scorenumber(18,1;
insertintoscvalues('
80.0;
90.0;
99.0;
70.0;
60.0;
50.0;
30.0;
20.0;
76.0;
87.0;
31.0;
34.0;
89.0;
98.0;
commit;
二、查询1.1、查询同时存在"
01"
课程和"
02"
课程的情况selects.sno,s.sname,s.sage,s.ssex,sc1.score,sc2.scorefromstudents,scsc1,scsc2wheres.sno=sc1.snoands.sno=sc2.snoando='
ando='
1.2、查询必须存在"
课程,"
课程可以没有的情况
selectt.*,s.score_01,s.score_02fromstudenttinnerjoin(selecta.sno,a.scorescore_01,b.scorescore_02fromscaleftjoin(select*fromscwherecno='
bon(a.sno=b.snowhereo='
son(t.sno=s.sno;
2.1、查询同时'
课程比'
课程分数低的数据
selects.sno,s.sname,s.sage,s.ssex,sc1.score,sc2.scorefromstudents,scsc1,scsc2wheres.sno=sc1.snoands.sno=sc2.snoando='
andsc1.score<
sc2.score;
2.2、查询同时'
课程分数低或'
缺考的数据selects.sno,s.sname,s.sage,s.ssex,t.score_01,t.score_02fromstudents,(selectb.sno,a.scorescore_01,b.scorescore_02from(select*fromscwherecno='
a,(select*fromscwherecno='
bwherea.sno(+=b.snotwheres.sno=t.snoand(t.score_01<
t.score_02ort.score_01isnull;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selects.sno,s.sname,t.avg_scoreavg_scorefromstudents,(selectsno,round(avg(score,2avg_scorefromscgroupbysnohavingavg(score>
=60orderbysnotwheres.sno=t.sno;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩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_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_scorefromscgroupbysnoorderbysnoc
wheres.sno=c.sno
5.2、查询所有成绩的(包括缺考的。
selecta.s_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
6、查询"
李"
姓老师的数量(有几个老师姓李)selectcount(tnfrom(selecttno,substr(tname,0,1tnfromteacherwheresubstr(tname,0,1='
李'
a;
7、哪些学生上过张三(老师)的课selectst.*fromstudentst,courseco,teacherte,scwherete.tno=co.tnoando=oandsc.sno=st.snoandte.tname='
8、哪些学生没上过张三(老师)的课select*fromstudentminusselectst.*fromstudentst,courseco,teacherte,scwherete.tno=co.tnoando=oandsc.sno=st.snoandte.tname='
9、查询'
'
都学过的同学的信息selectst.*fromstudentst,(select*fromscwherecno='
bwherest.sno=a.snoandst.sno=b.sno
10、查询学过编号为'
但是没有学过编号为'
的课程的同学的信息selectst.*fromstudentst,((selectsnofromscwherecno='
minus(selectsnofromscwherecno='
awherest.sno=a.sno;
11、查询没有学全所有课程的同学的信息11.1学完所有课程的selectst.*fromstudentst,(selectsno,count(cnofromscgroupbysnohavingcount(cno=3awherest.sno=a.sno;
11.2没有学完所有课程的selectst.*fromstudentst,
(selectsnofromstudentminusselectsnofromscgroupbysnohavingcount(cno=3awherest.sno=a.sno;
12、查询至少有一门课与学号为'
的同学所学相同的同学的信息selectst.*fromstudentst,(selectdistinctsnofromscwherecnoin(selectcnofromscwheresno='
andsno!
=1awherest.sno=a.sno;
13、查询和'
号的同学学习的课程完全相同的其他同学的信息selectst.*fromstudentst,(selectsnofrom(selectsno,count(cnoCNT1fromscgroupbysnoa,(selectcount(cnoCNT2fromscwheresno='
bwherea.CNT1=b.CNT2anda.sno!
='
cwherest.sno=c.sno;
14、查询没学过"
张三"
老师讲授的任一门课程的学生姓名selectst.*fromstudentst,(selectsnofromstudentminusselectsnofromscwherecno=(selectofromteachert,coursecwheret.tno=c.tnoandtname='
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩selecta.sno,st.sname,a.avg_scorefromstudentst,(selectsno,count(cno,round(avg(score,2avg_scorefrom(select*fromscwherescore<
60groupbysnoawherest.sno=a.sno;
16、检索'
课程分数小于60,按分数降序排列的学生信息selectst.*,o,a.scorefromstudentst,(selectsno,cno,scorefromscwherecno='
andscore<
60orderbyscoredescawherest.sno=a.sno;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩selectst.sno,st.sname,c.score,d.score,e.score,a.avg_scorefromstudentst,(selectsno,round(avg(score,2avg_scorefromscgroupbysno
orderbyavg_scoredesca,(select*fromscwherecno='
c,(select*fromscwherecno='
d,(select*fromscwherecno='
ewherest.sno=a.sno(+andst.sno=c.sno(+andst.sno=d.sno(+andst.sno=e.sno(+
18、查询各科成绩最高分、最低分和平均分:
以如下形式显示:
课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率selecto"
课程编号"
ame"
课程名称"
b.max_score"
最高分"
c.min_score"
最低分"
d.avg_socre"
平均分"
e."
及格率"
中等率"
优良率"
优秀率"
fromcoursea,(selectcno,max(scoremax_scorefromscgroupbycnob,(selectcno,min(scoremin_scorefromscgroupbycnoc,(selectcno,round(avg(score,2avg_socrefromscgroupbycnod,(select'
cno,round((selectcount(1fromscwherecno='
andscore>
=60/(selectcount(1fromscwherecno='
*100,2"
round((selectcount(1fromscwherecno='
=70andscore<
80/(selectcount(1fromscwherecno='
=80andscore<
90/(selectcount(1fromscwherecno='
=90/(selectcount(1fromscwherecno='
fromdualunionselect'
round((selectcount(1fromscwherecno='
fromdualewhereo=oando=oando=oando=o;
19、按各科成绩进行排序,并显示排名selectsno,cno,score,rank(over(partitionbycnoorderbyscoredesc"
名次"
fromsc;
selectsno,cno,score,dense_rank(over(partitionbycnoorderbyscoredesc"
20、查询学生的总成绩并进行排名20.1查询学生的总成绩selecta.sno,a.sname,nvl(b.sum_score,0"
总成绩"
fromstudenta,(selectsno,sum(scoresum_scorefromscgroupbysnoorderbysnobwherea.sno=b.sno(+;
20.2查询学生的总成绩并进行排名。
selectc."
学生编号"
c."
学生姓名"
rank(over(orderbyc."
desc"
排名"
from(selecta.sno"
a.sname"
nvl(b.sum_score,0"
fromstudenta,(selectsno,sum(scoresum_scorefromscgroupbysnoorderbysnobwherea.sno=b.sno(+c
21、查询不同老师所教不同课程平均分从高到低显示selecta.tno,a.tname,c.avg_score"
fromteachera,courseb,(selectcno,round(avg(score,2avg_scorefromscgroupbycnocwherea.tno=b.tnoando=oorderby"
desc;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score重复时保留名次空缺select*from(selectsno,cno,score,rank(over(partitionbycnoorderbyscoredescorder_scfromscawherea.order_scin(2,3;
Score重复时合并名次select*from(selectsno,cno,score,dense_rank(over(partitionbycnoorderbyscoredescorder_scfromscawherea.order_scin