50道SQL数据库面试题以及答案！面试前一定看一下

Student(Sid,Sname,Sage,Ssex)学生表
Sid：学号
Sname：学生姓名
Sage：学生年龄
Ssex：学生性别
Course(Cid,Cname,T#)课程表
Cid：课程编号
Cname：课程名称
Tid：教师编号
SC(Sid,Cid,score)成绩表
Sid：学号
Cid：课程编号
score：成绩
Teacher(Tid,Tname)教师表
Tid：教师编号：
Tname：教师名字
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号
select a.sid from
(select sid,score from sc where cid=‘001’)a,
(select sid,score from sc where cid=‘002’)b
where a.sid = b.sid and a.score>b.score;

2、查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score) from sc
group by sid
having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩
select s.sid,s.sname,count_cid as 选课数,
sum_score as 总成绩
from student s
left join
(select sid,count(cid) as count_cid,sum(score) as sum_score
from sc group by sid )sc
on s.sid = sc.sid;

4、查询姓‘李’的老师的个数：
select count(tname)
from teacher
where tname like ‘李%’;

5、查询没有学过“叶平”老师可的同学的学号、姓名：
select s.sid,s.sname
from student as s
where s.sid not in (
select DISTINCT sid
from sc as sc
where sc.cid in (
select cid
from course as c
left join teacher as t on c.tid = t.tid
where t.tname = ‘叶平’)
);

6、查询学过“叶平”老师所教的所有课的同学的学号、姓名：
select s.sid,s.sname
from student as s
where s.sid in (
select distinct sc.sid
from sc as sc
where sc.cid in (
select cid
from course as c
left join teacher as t on c.tid = t.tid
where t.tname = ‘叶平’)
group by sc.sid
HAVING count(cid)=
(select count(cid)
from course as c left join teacher as t on c.tid = t.tid
where t.tname = ‘叶平’)
);

7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：
SELECT s.sid,s.sname
from student as s
left join sc as sc on s.sid = sc.sid
where sc.cid = ‘001’
and EXISTS(
select * from sc as sc_2
where sc.sid = sc_2.sid
and sc_2.cid=‘002’);

select s.sid,s.sname
from student as s
left join sc as sc
on sc.sid = s.sid
where sc.cid = ‘001’
and s.sid in (
select sid from sc as sc_2
where sc_2.cid=‘002’
and sc_2.sid = sc.sid);

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：
select sid,sname
from (select student.sid,student.sname,score,
(select score from sc as sc_2
where sc_2.sid =