Oracle常见的面试题四（10级学员 庞丽整理）

Oracle常见的面试题：

题目：现有STUDENT（学生），COURSE（课程），SC（成绩）表，完成以下需求。（建表语句在emp.sql中，综合考察）

（1）       查询选修课程为web的学员学号和姓名

select s.sid,s.name from student s inner join sc on(s.sid=sc.sid)inner join course c on

(c.cid=sc.cid) where c.name='web';

         分析：课程与学生表没有直接联系，必须通过中间成绩表做2次连接。

（2）查询课堂编号为2的学员姓名和单位

         select s.name,s.dept from student s inner join sc on (s.sid=sc.sid) where sc.cid=2;

（3）查询不选修4号课程的学员姓名和单位

方法一：select name,dept from student where sid not in (select s.sid from student s left join sc on s.sid=sc.sid where cid=4);

方法二：select name,dept from student where not exists(select sid from sc where sc.sid=s.sid and cid=4);

分析：要点是先要查询出选修了4号课程的学员id，再从所有学员中排出这些id的学员。方法二效率较高。

（4）求出所有学生的选修的所有课程

select * from student s inner join sc on s.sid=sc.sid inner join course c on sc.cid=c.cid order by s.sid;

分析：步骤1：查询出所有课程的数目

         Select count(*) from course;

步骤2：在成绩（sc）表，按学员id分组，看每组的个数，该个数等于步骤1课程总数的sid即为选修了所有课程的学员id。

Select sid from sc group by sid having (count(*) =(select count(*) from course));

步骤3：再根据该sid查询学员的详细信息

Select s.name,s.dept from student s where sid in (select sid from sc group by sid having(count(*) = (select count(*) from course)));

（5）查询选修课程超过3门的学员姓名和单位

         select * from student where sid in(select sid from(select sid,count(*) from (select s.* from student s inner join sc on s.sid=sc.sid inner join course c on sc.cid=c.cid order by s.sid) group by sid having count(*)>3 order by sid));

（6）找出没有选修过TeacherLI 讲授课程的所有学生姓名

         select s.name from student s where sid not in (select sid from course c left join sc on(c.cid=sc.cid) where c.teacher='Teacher Li');

（7）列出两门以上（含两门）不及格课程的学生姓名及其平均成绩

         select s.*,b.avgscore from student s,

(select sc.sid,avg(score) avgscore from sc,

       (select sid from sc where score <60 group by sid having(count(*)>=2))a

               where sc.sid=a.sid group by sc.sid)b

               where s.sid=b.sid;

分析：步骤1：查询所有两门以上不及格的学员id

Select sid from sc where score < 60 group by sid having(count(*)>=2);

步骤2：步骤1结果与真实表sc做连接，算平均成绩

Select sc.sid,svg(score)  avgscore  from sc,

         (select sid from sc where score <60 group by sid having(count(*)>=2))a

                   Where sc.sid=a.sid group by sc.sid;

步骤3：步骤2结果与真实表student做连接，查学员姓名

         Select s.*,b.avgscore from student s,

                   (select sid from sc where score<60 group by sid having(count(*)>=2))a

                            Where sc.sid=a.sid group by sc.sid;

（8）列出既学过1号课程，又学过2号课程的所有学生姓名

         select s.name from student s inner join(select sc.sid from sc where sc.cid in(1,2) group by sid having(count(*)=2))a on(s.sid=a.sid);

分析：要点是不仅要学过1,2,号课程in(1,2)，并且要求同时学过此两门课count(*)=2。

（9）列出1号课程成绩比2号课成绩高的所有学生的学号，姓名和1号课和2号课的成绩

         select s.sid,s.name,sc1.score,sc2.score from sc sc1,sc sc2,student s where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid =1 and sc2.cid=2 and sc1.score>sc2.score;

分析：要点在于自连接，把成绩表拆成两张表来看，sc1中只考虑1号课，sc2中只考虑2号课且sc1.score>sc2.score;最后再考虑将结果与student表连接查询姓名。