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],[ <60]   21、查詢各科成績前三名的記錄:(不考慮成績並列情況)     22、查詢每門課程被選修的學生數   23、查詢出只選修了一門課程的全部學生的學號和姓名   24、查詢男生、女生人數   25、查詢姓“張”的學生名單    26、查詢同名同性學生名單，並統計同名人數   27、1981年出生的學生名單(註：Student表中Sage列的類型是number)    28、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列    29、查詢平均成績大於85的所有學生的學號、姓名和平均成績   30、查詢課程名稱為“資料庫”，且分數低於60的學生姓名和分數   31、查詢所有學生的選課情況；   32、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；   33、查詢不及格的課程，並按課程號從大到小排列   34、查詢課程編號為c001且課程成績在80分以上的學生的學號和姓名；   35、求選了課程的學生人數 37、查詢各個課程及相應的選修人數   38、查詢不同課程成績相同的學生的學號、課程號、學產生績   39、查詢每門功成績最好的前兩名   40、統計每門課程的學生選修人數（超過10人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列    41、檢索至少選修兩門課程的學生學號   42、查詢全部學生都選修的課程的課程號和課程名   43、查詢沒學過“諶燕”老師講授的任一門課程的學生姓名   44、查詢兩門以上不及格課程的同學的學號及其平均成績   45、檢索“c004”課程分數小於60，按分數降序排列的同學學號   46、刪除“s002”同學的“c001”課程的成績 */