oracle数据库增删改查练习50例 答案精.docx
《oracle数据库增删改查练习50例 答案精.docx》由会员分享,可在线阅读,更多相关《oracle数据库增删改查练习50例 答案精.docx(9页珍藏版)》请在冰豆网上搜索。
oracle数据库增删改查练习50例答案精
oracle数据库增删改查练习50例-答案
一、建表--学生表droptablestudent;createtablestudent(snovarchar2(10,snamevarchar2(10,sagedate,ssexvarchar2(10;insertintostudentvalues('C赵雷',to_date('1990/01/01','yyyy/mm/dd','男';insertintostudentvalues('02'钱电',to_date('1990/12/21','yyyy/mm/dd',男';insertintostudentvalues('03'孙风',to_date('1990/05/20','yyyy/mm/dd',男';insertintostudentvalues('04'李云',to_date('1990/08/06','yyyy/mm/dd',男';insertintostudentvalues('05'周梅',to_date('1991/12/01','yyyy/mm/dd',女';insertintostudentvalues('06'吴兰',to_date('1992/03/01','yyyy/mm/dd',女';insertintostudentvalues('07'郑竹',to_date('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'数学','01';insertintocoursevalues('03'英语','03';
--教师表droptableteacher;createtableteacher(tnovarchar2(10,tnamevarchar2(10;insertintoteachervalues('01张三';insertintoteachervalues('02'李四';insertintoteachervalues('03'王五';
--成绩表droptablesc;createtablesc(snovarchar2(10,cnovarchar2(10,scorenumber(18,1;insertintoscvalues('01','01',80.0;insertintoscvalues('01','02',90.0;insertintoscvalues('01','03',99.0;insertintoscvalues('02','01',70.0;insertintoscvalues('02','02',60.0;insertintoscvalues('02','03',80.0;insertintoscvalues('03','01',80.0;insertintoscvalues('03','02',80.0;insertintoscvalues('03','03',80.0;insertintoscvalues('04','01',50.0;insertintoscvalues('04','02',30.0;insertintoscvalues('04','03',20.0;insertintoscvalues('05','01',76.0;insertintoscvalues('05','02',87.0;insertintoscvalues('06','01',31.0;insertintoscvalues('06','03',34.0;insertintoscvalues('07','02',89.0;insertintoscvalues('07','03',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='01'andsc2.cno='02';
1.2、查询必须存在"01"课程,"02"课程可以没有的情况
selectt.*,s.score_01,s.score_02fromstudenttinnerjoin(selecta.sno,a.scorescore_01,b.scorescore_02fromscaleftjoin(select*fromscwhereeno='02'bon(a.sno=b.snowherea.cno='01'son(t.sno=s.sno;
2.1、查询同时'01'课程比'02'课程分数低的数据
selects.sno,s.sname,s.sage,s.ssex,sc1.score,sc2.scorefromstudents,scsc1,sc
sc2wheres.sno=sc1.snoands.sno=sc2.snoandsc1.cno='01'andsc2.cno='02'andsc1.score2.2、查询同时'01'课程比'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='02'bwherea.sno(+=b.snotwheres.sno=t.snoand(t.score_01t.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、查询'01''02都学过的同学的信息selectst.*fromstudentst,(select*fromscwhereeno='01'a,(select*fromscwhereeno='02'bwherest.sno=a.snoandst.sno=
b.sno
10、查询学过编号为’01但是没有学过编号为’02'的课程的同学的信息selectst.*fromstudentst,((selectsnofromscwhereeno='01'minus(selectsnofroms