SQL面试题（1-10）oracle写的

Student(SID, Sname, Sage, Ssex) 学生表 
Course(CID, Cname, TID) 课程表 
SC(SID, CID, score) 成绩表 
Teacher(TID, Tname) 教师表

student学生表：

create table student
(
      SID char(7) primary key not null,
      Sname varchar(11) not null,
      Sage number not null,
      Ssex char(2) not null
);

course课程表

create table course
(
       CID char(3) primary key not null,
       Cname varchar(20) not null,
       TID varchar(10) not null
);

sc成绩表

create table SC
(
       SID char(7) not null,
       CID char(3) not null,
       score number,
       primary key(SID,CID)
);

teacher教师表

create table Teacher
(
       TID char(5) primary key not null,
       Tname varchar(10) not null
);

插入数据：

insert into student values('0000001','张三',18,'男');
insert into student values('0000002','张四',17,'男');
insert into student values('0000003','王五',18,'女');
insert into student values('0000004','李六',20,'男');

insert into course values('001','Java','00001');
insert into course values('002','C++','00002');
insert into course values('003','C','00003');
insert into course values('004','JavaScript','00004');
insert into course values('005','python','00001');
insert into course values('006','C#','00001');
insert into course values('007','SqlServer','00001');
insert into course values('008','Oracle','00001');

insert into SC values('0000001','001',80);
insert into SC values('0000001','002',80);
insert into SC values('0000001','003',88);
insert into SC values('0000001','006',80);
insert into SC values('0000001','007',80);
insert into SC values('0000001','008',88);
insert into SC values('0000001','005',58);
insert into SC values('0000002','001',21);
insert into SC values('0000002','002',55);
insert into SC values('0000002','003',39);
insert into SC values('0000002','004',58);
insert into SC values('0000002','007',31);
insert into SC values('0000002','005',45);
insert into SC values('0000002','006',55);
insert into SC values('0000003','001',48);
insert into SC values('0000003','003',83);
insert into SC values('0000003','004',90);
insert into SC values('0000003','005',88);
insert into SC values('0000003','007',70);
insert into SC values('0000004','001',45);
insert into SC values('0000004','002',65);
insert into SC values('0000004','004',61);
insert into SC values('0000004','007',78);
insert into SC values('0000003','006',68);

insert into teacher values('00001','叶平');
insert into teacher values('00002','叶凡');
insert into teacher values('00003','李平');
insert into teacher values('00004','王平');

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

select SC1.SID from SC SC1,SC SC2
where SC1.SID = SC2.SID 
and SC1.CID = '001' 
and SC2.CID = '002'
and SC1.score > Sc2.score;

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

select SID,avg(score) from SC
group by SID  
having avg(score) > 60 

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

select S.SID,S.Sname,count(*) 选课数,sum(score) 总成绩 from student S,SC
where S.SID = SC.SID 
group by S.SID,S.Sname

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

select count(*) 姓李老师的个数 from Teacher
where Tname like '李%'

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

select SID, Sname
from student
where SID not in
(
        select distinct sid
        from sc
        where CID in
        (      
              select CID
              from course
              where TID = (select TID from teacher where Tname = '叶平')
        )
);

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

select SID,Sname
from student
where SID in
(
      select SC1.SID
      from SC SC1,SC SC2
      where SC1.SID = SC2.SID
      and SC1.CID = '001'
      and SC2.CID = '002'
); 

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

select student.SID,student.sname 
from student,
(    
	select sid,count(*) cnt from sc
     where cid  in
     (     select cid from course 
           where tid = 
           (select tid from teacher where Tname ='叶平')
     )
     group by sid
) c
where student.sid = c.sid
and c.cnt = 
(
    select count(*) from course 
    where tid = (select tid from teacher where Tname ='叶平')
)

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select sid,sname from student
where sid in 
(    
      select sc1.sid from sc sc1,sc sc2
      where sc1.sid = sc2.sid and sc1.cid = '001'
      and sc2.cid = '002' and sc1.score < sc2.score
)

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

select sid,sname from student
where sid not in
(
      select distinct sid from sc 
      where score >= 60
);  

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

select sid,sname from student
where sid in
(
      select sc.sid from sc,(select sid,count(*) cnt from sc group by sid) c
      where sc.sid = c.sid and c.cnt <(select count(*) from course)
);

select t.sid,t.sname from 
(      select student.sid sid,student.sname sname,count(distinct(sc.sid)) cnt 
       from student,sc
       where student.sid = sc.sid
       group by student.sid,student.sname       
) t
where t.cnt <(select count(distinct(cid)) from sc)