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、查询同时存在\课程和\课程的情况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 sc1.cno = '01' and sc2.cno = '02';
1.2、查询必须存在\课程，\课程可以没有的情况
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 a.cno = '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 sc1.cno = '01' and sc2.cno = '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,
(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.2、包括没有考试成绩的数据select g.* from (select s.sno, s.sname,
nvl(t.avg_score, 0 avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno order by sno t where s.sno = t.sno(+ g where g.avg_score < 60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、查询所有成绩的（不含缺考的）。select s.sno,s.sname,c.count_cno,c.sum_scorefrom student s,(select sno,count(cno count_cno,sum(score sum_score from sc group by sno order by sno c
where s.sno = c.sno
5.2、查询所有成绩的(包括缺考的。select a.s_sno, a.s_sname, nvl(a.c_cno, 0, a.s_score from (select s.sno s_sno, s.sname s_sname, c.count_cno c_cno, c.sum_score s_score from student s, (select sno, count(cno count_cno, sum(score sum_score from sc group by sno order by sno c where s.sno = c.sno(+ a
6、查询\李\姓老师的数量 （有几个老师姓李）select count(tn from (select tno, substr(tname, 0, 1 tn from teacher where substr(tname, 0, 1 = '李' a;
7、哪些学生上过张三（老师）的课select st.* from student st, course co, teacher te, sc where te.tno = co.tno and co.cno = sc.cno and sc.sno = st.sno and te.tname = '张三'
8、哪些学生没上过张三（老师）的课select * from studentminus select st.* from student st, course co, teacher te, sc where te.tno = co.tno and co.cno = sc.cno and sc.sno = st.sno and te.tname = '张三'
9、查询 '01' '02'都学过的同学的信息select st.* from student st, (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where st.sno = a.sno and st.sno = b.sno
10、查询学过编号为'01'但是没有学过编号为'02'的课程的同学的信息select st.* from student st, ((select sno from sc where cno = '01' minus (select sno from sc where cno = '02' a where st.sno = a.sno;
11、查询没有学全所有课程的同学的信息11.1 学完所有课程的select st.* from student st, (select sno, count(cno from sc group by sno having count(cno = 3 a where st.sno = a.sno;
11.2 没有学完所有课程的select st.* from student st,
(select sno from student minus select sno from sc group by sno having count(cno = 3 a where st.sno = a.sno;
12、查询至少有一门课与学号为'01'的同学所学相同的同学的信息select st.* from student st, (select distinct sno from sc where cno in (select cno from sc where sno = '01' and sno != 1 a where st.sno = a.sno;
13、查询和'01'号的同学学习的课程完全相同的其他同学的信息select st.* from student st, (select sno from (select sno, count(cno CNT1 from sc group by sno a, (select count(cno CNT2 from sc where sno = '01' b where a.CNT1 = b.CNT2 and a.sno != '01' c where st.sno = c.sno;
14、查询没学过\张三\老师讲授的任一门课程的学生姓名select st.* from student st, (select sno from student minus select sno from sc where cno = (select c.cno from teacher t, course c where t.tno = c.tno and tname = '张三' a where st.sno = a.sno;
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select a.sno, st.sname, a.avg_score from student st, (select sno, count(cno, round(avg(score, 2 avg_score from (select * from sc where score < 60 group by sno a where st.sno = a.sno;
16、检索'01'课程分数小于60，按分数降序排列的学生信息select st.*, a.cno, a.score from student st, (select sno, cno, score from sc where cno = '01' and score < 60 order by score desc a where st.sno = a.sno;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select st.sno, st.sname, c.score, d.score, e.score, a.avg_score from student st, (select sno, round(avg(score, 2 avg_score from sc group by sno
order by avg_score desc a, (select * from sc where cno = '01' c, (select * from sc where cno = '02' d, (select * from sc where cno = '03' e where st.sno = a.sno(+ and st.sno = c.sno(+ and st.sno = d.sno(+ and st.sno = e.sno(+
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率select a.cno \课程编号\课程名称\最高分\最低分\d.avg_socre \平均分\及格率\中等率\优良率\优秀率\(select cno, max(score max_score from sc group by cno b, (select cno, min(score min_score from sc group by cno c, (select cno, round(avg(score, 2 avg_socre from sc group by cno d, (select '01' cno, round((select count(1 from sc where cno = '01' and score >= 60 / (select count(1 from sc where cno = '01' * 100, 2 \及格率\count(1 from sc where cno = '01' and score >= 70 and score < 80 / (select count(1 from sc where cno = '01' * 100, 2 \中等率\score >= 80 and score < 90 / (select count(1 from sc where cno = '01' * 100, 2 \优良率\round((select count(1 from sc where cno = '01' and score >= 90 / (select count(1 from sc where cno = '01' * 100, 2 \优秀率\from sc where cno = '02' and score >= 60 / (select count(1 from sc where cno = '02' * 100, 2 \及格率\80 / (select count(1 from sc where cno = '02' * 100, 2 \中等率\