oracle数据库增删改查练习50例 答案精Word文档下载推荐.docx
《oracle数据库增删改查练习50例 答案精Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《oracle数据库增删改查练习50例 答案精Word文档下载推荐.docx(9页珍藏版)》请在冰豆网上搜索。
![oracle数据库增删改查练习50例 答案精Word文档下载推荐.docx](https://file1.bdocx.com/fileroot1/2022-10/17/8821827c-be98-4e79-9d2e-a55013f13522/8821827c-be98-4e79-9d2e-a55013f135221.gif)
02'
钱电'
1990/12/21'
,男'
03'
孙风'
1990/05/20'
男'
04'
李云'
1990/08/06'
05'
周梅'
1991/12/01'
,女'
06'
吴兰'
1992/03/01'
07'
郑竹'
1989/07/01'
女'
08'
王菊'
1990/01/20'
--课程表droptablecourse;
createtablecourse(enovarchar2(10,cnamevarchar2(10,tnovarchar2(10;
insertintocoursevalues('
0语文'
数学'
01'
英语'
--教师表droptableteacher;
createtableteacher(tnovarchar2(10,tnamevarchar2(10;
insertintoteachervalues('
01张三'
02'
李四'
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,scl.score,sc2.scorefromstudents,scsc1,scsc2wheres.sno=scl.snoands.sno=sc2.snoandsc1.cno='
andsc2.cno='
1.2、查询必须存在"
课程,"
课程可以没有的情况
selectt.*,s.score_01,s.score_02fromstudenttinnerjoin(selecta.sno,a.scorescore_01,b.scorescore_02fromscaleftjoin(select*fromscwhereeno='
bon(a.sno=b.snowherea.cno='
son(t.sno=s.sno;
2.1、查询同时'
课程比'
课程分数低的数据
selects.sno,s.sname,s.sage,s.ssex,sc1.score,sc2.scorefromstudents,scsc1,sc
sc2wheres.sno=sc1.snoands.sno=sc2.snoandsc1.cno='
andsc1.score<
sc2.score;
2.2、查询同时'
课程分数低或'
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='
a,(select*fromscwhereeno='
bwherea.sno(+=b.snotwheres.sno=t.snoand(t.score_01<
t.score_02or
t.score_01isnuII;
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_scorefrom
students,(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,teacher
te,scwherete.tno=co.tnoandco.cno=sc.cnoandsc.sno=st.snoandte.tnamefe三'
8、哪些学生没上过张三(老师)的课select*fromstudentminusselectst.*fromstudentst,courseco,teacherte,scwherete.tno=co.tnoandco.cno=sc.cnoandsc.sno=st.snoandte.tname=张三'
9、查询'
'
02都学过的同学的信息selectst.*fromstudentst,(select*fromscwhereeno='
bwherest.sno=a.snoandst.sno=
b.sno
10、查询学过编号为’01但是没有学过编号为’02'
的课程的同学的信息selectst.*fromstudentst,((selectsnofromscwhereeno='
minus(selectsnofroms