oracle数据库课后习题答案,oracle数据库经典练习题及答案

最近下载了一套oracle数据库习题(无答案)，本人自己在oracle上练习得到的答案，如果不对的地方，请不要见怪。保存到博客方便自己以后查阅。

相关表：

/*学生表*/

create table student

(

sno varchar2(10) primary key not null,

sname varchar2(20),

sage number(2),

ssex varchar2(5)

)

/*教师表*/

create table teacher

(

tno varchar2(10) primary key,

tname varchar2(20)

)

/*课程表*/

create table course

(

cno varchar2(10),

cname varchar2(20),

tno varchar2(20),

constraint pk_course primary key (cno,tno)

)

/*成绩表*/

create table sc

(

sno varchar2(10),

cno varchar2(10),

score number(4,2),

constraint pk_sc primary key (sno,cno)

)

相关数据：

/*初始化学生表 student*/

insert into student values ('s001','张三',23,'男');

insert into student values ('s002','李四',23,'男');

insert into student values ('s003','吴鹏',25,'男');

insert into student values ('s004','琴沁',20,'女');

insert into student values ('s005','王丽',20,'女');

insert into student values ('s006','李波',21,'男');

insert into student values ('s007','刘玉',21,'男');

insert into student values ('s008','萧蓉',21,'女');

insert into student values ('s009','陈萧晓',23,'女');

insert into student values ('s010','陈美',22,'女');

/*初始化教师表 teacher */

insert into teacher values ('t001','刘阳');

insert into teacher values ('t002','谌燕');

insert into teacher values ('t003','胡明星');

/*初始化课程表 course*/

insert into course values ('c001','J2SE','t002');

insert into course values ('c002','Java Web','t002');

insert into course values ('c003','SSH','t001');

insert into course values ('c004','Oracle','t001');

insert into course values ('c005','SQL SERVER 2005','t003');

insert into course values ('c006','C#','t003');

insert into course values ('c007','JavaScript','t002');

insert into course values ('c008','DIV+CSS','t001');

insert into course values ('c009','PHP','t003');

insert into course values ('c010','EJB3.0','t002');

/*初始化成绩表 sc*/

insert into sc values ('s001','c001',78.9);

insert into sc values ('s002','c001',80.9);

insert into sc values ('s003','c001',81.9);

insert into sc values ('s004','c001',60.9);

insert into sc values ('s001','c002',82.9);

insert into sc values ('s002','c002',72.9);

insert into sc values ('s005','c003',78.9);

insert into sc values ('s006','c004',50.9);

insert into sc values ('s007','c005',81.9);

insert into sc values ('s008','c006',50.9);

insert into sc values ('s005','c007',42.9);

insert into sc values ('s006','c008',72.9);

insert into sc values ('s005','c009',52.9);

insert into sc values ('s006','c010',92.9);

练习题：

/*1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；*/

select distinct c.sno

from sc c

where (select a.score

from sc a

where a.sno = c.sno

and a.cno = 'c001') >

(select a.score

from sc a

where a.sno = c.sno

and a.cno = 'c002')

/* 2、查询平均成绩大于60分的同学的学号和平均成绩；*/

select *

from (select a.sno, avg(score) avgScore from sc a group by a.sno)

where avgScore > 60

/*3、查询所有同学的学号、姓名、选课数、总成绩； */

select c.sno, c.sname, nvl(d.coursenum, 0), nvl(d.totalscore, 0)

from student c

left join (select a.sno,

a.sname,

count(b.sno) coursenum,

sum(b.score) totalscore

from student a, sc b

where a.sno = b.sno

group by a.sno, a.sname) d

on c.sno = d.sno

/* 4、查询姓“刘”的老师的个数； */

select count(*) from teacher a

where a.tname like '刘%'

/* 5、查询没学过“谌燕”老师课的同学的学号、姓名； */

select b.sno, e.sname

from sc b

left join student e

on b.sno = e.sno

where b.score < 60

and b.cno in

(select c.cno

from course c

where c.tno = (select d.tno from teacher d where d.tname = '谌燕'))

/*6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；*/

select c.sno, d.sname

from (select b.sno sno, count(*) numCount

from sc b

where b.cno in ('c001', 'c002')

group by sno) c

left join student d

on c.sno = d.sno

where c.numCount = 2

/*7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；*/

select a.sno, a.sname

from student a

where a.sno in (select d.sno

from sc d

where d.cno in (select c.cno

from teacher b, course c

where b.tno = c.tno

and b.tname = '谌燕'));

/*8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；*/

select a.sno, a.sname

from student a

where a.sno in (select b.sno

from sc b, sc c

where b.cno = 'c001'

and c.cno = 'c002'

and b.sno = c.sno

and b.score > c.score);

/*9、查询所有课程成绩小于60分的同学的学号、姓名； */

select a.sno, a.sname

from student a

where a.sno in (select b.sno from sc b where b.score < 60);

/*10、查询没有学全所有课的同学的学号、姓名； */

select a.sno, a.sname

from student a

where a.sno in

(select c.sno

from (select b.sno, count(b.cno) cno_num

from student a, sc b

where a.sno = b.sno

group by b.sno) c

where c.cno_num < (select count(d.cno) from course d))

or a.sno not in (select distinct e.sno from sc e);

/*11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；*/

select * from student;

select * from teacher;

select * from course;

select * from sc;

/*12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

15、删除学习“谌燕”老师课的SC表记录；

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”      号课的平均成绩；

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

19、查询不同老师所教不同课程平均分从高到低显示

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[