关闭

Oracle常见的面试题(1)

标签: oraclejoin面试webc
363人阅读 评论(0) 收藏 举报
分类:
 

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表连接查询姓名。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:163706次
    • 积分:2964
    • 等级:
    • 排名:第11883名
    • 原创:139篇
    • 转载:5篇
    • 译文:0篇
    • 评论:24条
    最新评论