数据库面试题答案

第一题：

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select distinct s1.s1id from

(select sc1.sid s1id,sc1.score s1score from sc sc1 where sc1.CID = '101') s1, (select sc2.sid s2id,sc2.score s2score from sc sc2 where sc2.CID = '102') s2 where s1.s1score > s2.s2score and s1.s1id = s2.s2id;

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

select sid,avg(score) from sc group by sid having avg(score) > 60;

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

select s.sid,s.sname,count(CID),nvl(sum(SCORE),0) from student s,sc sc

where s.sid = sc.sid(+) group by s.sid,s.sname;

4、查询姓“李”的老师的个数；

select count(*) from teacher where tname like '张%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

select s.sid,s.sname from student s where s.sid not in (select distinct(sc.sid) f

where sc.cid = c.cid and c.tid = t.tid and t.tname = '张三');

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select s.sid,s.sname from student s,sc sc

where s.sid = sc.sid and sc.cid = '101' and s.sid in

(select s.sid from student s,sc sc where s.sid = sc.sid and sc.cid = '102');

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select s.sid,s.sname from student s where s.sid in (

select sc.sid from cource c,teacher t,sc sc where

c.tid=t.tid and sc.cid = c.cid and t.tname = '张三' group by sc.sid having count( = (select count(*) from cource c,teacher t where c.tid = t.tid and t.tname='张三' 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； select ss1.ssid,ss1.ssname from

(select s.sid ssid,s.sname ssname,sc.score score from student s,sc sc where s.sid (select s.sid ssid,s.sname ssname,sc.score score from student s,sc sc where s.sid where ss1.ssid = ss2.ssid and ss1.score < ss2.score;

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

select * from student s where s.sid not in (select s.sid from student s,sc sc whe 10、查询没有学全所有课的同学的学号、姓名；

select s.sid,s.sname from student s,sc sc where s.sid = sc.sid group by s.sid,s.s < (select count(c.cid) from cource c);

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

select distinct s.sid,s.sname from student s,sc sc where s.sid = sc.sid and s.sid 12、查询至少学过学号为“001”同学所学课的其他同学学号和姓名；

select s.sid,s.sname,count(https://www.sodocs.net/doc/cc2728246.html,id) from (select sid ssid,cid ccid from sc where s where t.ssid = s.sid group by s.sid,s.sname

having count(https://www.sodocs.net/doc/cc2728246.html,id) = (select count(*) from sc where sid = 13 group by sid); 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.Cid=SC.Cid ) where SC.cid in (select cource.cid from cource,Teacher where cource.tid = Teacher

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

select u.sid,u.sname from student u,(select s.sid sid,count(s.cid) cn from sc s , (select cid from sc where sid =13) a where s.cid = a.cid group by s.sid) m

where u.sid = m.sid and https://www.sodocs.net/doc/cc2728246.html, = (select count(cid) from sc where sid = 13) and(sel m.sid) = (select count(cid) from sc where sid = 13) and u.sid <> 13;

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

delete from sc where sc.cid in (select c.cid from cource c,teacher t where c.tid 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程

insert into sc

select distinct sid,'102',(select avg(score) from sc where sc.cid = '102') from s where sid not in (select sc.sid from sc where sc.cid = '103');

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成select sc.sid 学生ID,

(select score from sc sc1 where sc1.sid = sc.sid and sc1.cid='101') Java,

(select score from sc sc1 where sc1.sid = sc.sid and sc1.cid='102') "C++",

(select score from sc sc1 where sc1.sid = sc.sid and sc1.cid='103') VB,

(select score from sc sc1 where sc1.sid = sc.sid and sc1.cid='104') C#,

count(sc.cid) 有效课程数,avg(sc.score) 有效平均分 from sc sc group by sc.sid order by avg(sc.score) desc;

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

select distinct sc1.cid 课程ID,sc1.score 最高分,sc2.score 最低分 from sc sc1,sc s where sc1.cid = sc2.cid

and

sc1.score = (select max(score) from sc where sc1.cid = sc.cid)

and

sc2.score = (select min(score) from sc where sc2.cid = sc.cid);

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

select cid,avg(score) from sc group by cid order by avg(score) desc,100*(sum (CAS 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（0 select

sum(case when cid = '101' then score else 0 end )/sum(case when cid = '101' then 100*sum(case when cid = '101' and score > 60 then 1 else 0 end )/sum(case when c sum(case when cid = '102' then score else 0 end )/sum(case when cid = '102' then

100*sum(case when cid = '102' and score > 60 then 1 else 0 end )/sum(case

sum(case when cid = '103' then score else 0 end )/sum(case when cid = '103' then 100*sum(case when cid = '103' and score > 60 then 1 else 0 end )/sum(case when ci from sc;

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

select max(t.tname) ,max(https://www.sodocs.net/doc/cc2728246.html,ame),avg(score) from sc sc,cource c,teacher t where 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002）， [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

select distinct s.sid 学生ID,s.sname 学生姓名,

(select score from sc where sc.sid = s.sid and sc.cid = '101') Java,

(select score from sc where sc.sid = s.sid and sc.cid = '102') "C++",

(select score from sc where sc.sid = s.sid and sc.cid = '103') VB,

(select score from sc where sc.sid = s.sid and sc.cid = '104') C#,

(select avg(score) from sc where sc.sid = s.sid) 平均成绩

from sc sc,student s where sc.sid = s.sid;

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] select c.cid 课程ID,https://www.sodocs.net/doc/cc2728246.html,ame 课程名称,

sum(case when score between 85 and 100 then 1 else 0 end) "[100-85]",

sum(case when score between 70 and 84 then 1 else 0 end) "[84-70]",

sum(case when score between 60 and 69 then 1 else 0 end) "[69-60]",

sum(case when score <60 then 1 else 0 end) "[ <60]"

from sc,cource c

where sc.cid = c.cid group by c.cid,https://www.sodocs.net/doc/cc2728246.html,ame;

24、查询学生平均成绩及其名次.

select t.ssid,rownum 名次 from (select sid ssid,avg(score) from sc group by sid o 25、查询各科成绩前三名的记录:(不考虑成绩并列情况).

select

(select score from (select score from sc where cid = '101' order by score desc) t (select t2.ts from (select t1.score ts,rownum r from (select score from sc where (select t2.ts from (select t1.score ts,rownum r from (select score from sc where (select score from (select score from sc where cid = '102' order by score desc) t (select t2.ts from (select t1.score ts,rownum r from (select score from sc where (select t2.ts from (select t1.score ts,rownum r from (select score from sc where (select score from (select score from sc where cid = '103' order by score desc) t (select t2.ts from (select t1.score ts,rownum r from (select score from sc where (select t2.ts from (select t1.score ts,rownum r from (select score from sc where from dual;

26、查询每门课程被选修的学生数

select cid,count(*) from sc group by cid;

27、查询出只选修了一门课程的全部学生的学号和姓名

select sid,sname from student where sid in (select sid from sc group by sid havin 28、查询男生、女生人数

select count(*) 男生 from student group by SSEX having ssex = '男';

29、查询姓“张”的学生名单

select * from student where sname like '张%';

30、查询同名同性学生名单，并统计同名人数

select sname,count(*) from student group by sname having count(*)>1;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where to_char(birthday,'YYYY') = '1981';

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排select cid,avg(score) from sc group by cid order by avg(score) asc,cid desc;

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select sname,sc.sid ,avg(score)

from student,sc

where student.sid=sc.sid group by sc.sid,sname having avg(score)<85;

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

select s.sname,sc.score

from student s,sc sc,cource c

where s.sid = sc.sid and sc.cid = c.cid and https://www.sodocs.net/doc/cc2728246.html,ame='Java'

and sc.score < 90;

35、查询所有学生的选课情况；

select sc.sid,sc.cid,sname,cname from sc,student,cource

where sc.sid=student.sid and sc.cid=cource.cid ;