送你直通大厂企业之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 = student.sid 
      and sc_2.cid = '002') as score2 
      from student,sc 
      where student.sid=sc.sid and cid = '001') s_2
where score2<score;

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

select sid,sname
from student
where sid not in 
(select s.sid 
from student s,sc 
where s.sid=sc.sid and score>60 );
 
select sid,sname
from student s
where not EXISTS (
select s.sid from sc 
where sc.sid = s.sid and sc.score>60);

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

select s.sid,s.sname
from student s ,sc sc 
where s.sid = sc.sid
group by s.sid,s.sname
having count(sc.cid)<(
select count(cid) 
from course);
 
select s.sid,s.sname
from student s 
right join sc sc on s.sid = sc.sid
group by s.sid,s.sname
having count(sc.cid)<
(select count(cid) from course);

以上就是“送你直通大厂企业之sql面试题及答案”，你能回答上来吗?如果想要了解更多的相关内容，可以关注极悦Java官网。