oracle 数据库增删改查练习50例-答案
一、建表--学生表drop table student;create table student (sno varchar2(10,sname varchar2(10,sage date,ssex varchar2(10;insert into student values('01','赵雷
',to_date('1990/01/01','yyyy/mm/dd','男';insert into student values('02','钱电
',to_date('1990/12/21','yyyy/mm/dd','男';insert into student values('03','孙风
',to_date('1990/05/20','yyyy/mm/dd','男';insert into student values('04','李云
',to_date('1990/08/06','yyyy/mm/dd','男';insert into student values('05','周梅
',to_date('1991/12/01','yyyy/mm/dd','女';insert into student values('06','吴兰
',to_date('1992/03/01','yyyy/mm/dd','女';insert into student values('07','郑竹
',to_date('1989/07/01','yyyy/mm/dd','女';insert into student values('08','王菊
',to_date('1990/01/20','yyyy/mm/dd','女';--课程表drop table course;create table course (cno varchar2(10,cname varchar2(10,tno varchar2(10;insert into course values ('01','语文','02';insert into course values ('02','数学','01';insert into course values ('03','英语','03';
--教师表drop table teacher;create table teacher (tno varchar2(10,tname
varchar2(10;insert into teacher values('01','张三';insert into teacher values('02','李四';insert into teacher values('03','王五';
--成绩表drop table sc;create table sc (sno varchar2(10,cno varchar2(10,score number(18,1;insert into sc values('01','01',80.0;insert into sc values('01','02',90.0;insert into sc values('01','03',99.0;insert into sc values('02','01',70.0;insert into sc
values('02','02',60.0;insert into sc values('02','03',80.0;insert into sc
values('03','01',80.0;insert into sc values('03','02',80.0;insert into sc
values('03','03',80.0;insert into sc values('04','01',50.0;insert into sc
values('04','02',30.0;insert into sc values('04','03',20.0;insert into sc
values('05','01',76.0;insert into sc values('05','02',87.0;insert into sc
values('06','01',31.0;insert into sc values('06','03',34.0;insert into sc
values('07','02',89.0;insert into sc values('07','03',98.0;
commit;
二、查询1.1、查询同时存在"01"课程和"02"课程的情况select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and o = '01' and o = '02';
1.2、查询必须存在"01"课程，"02"课程可以没有的情况
select t.*, s.score_01, s.score_02 from student t inner join (select a.sno, a.score score_01, b.score score_02 from sc a left join (select * from sc where cno = '02' b on (a.sno = b.sno where o = '01' s on (t.sno = s.sno;
2.1、查询同时'01'课程比'02'课程分数低的数据
select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and o = '01' and o = '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 student s, (select b.sno, a.score score_01,
b.score score_02 from (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where a.sno(+ = b.sno t where s.sno = t.sno and (t.score_01 < t.score_02 or
t.score_01 is null;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select s.sno, s.sname, t.avg_score avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno having avg(score >= 60 order by sno t where s.sno = t.sno;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩4.1、有考试成绩，且小于60分select s.sno, s.sname, t.avg_score avg_score from student s,
