看了不会后悔的——SQL 面试题库

SQL 必备面试题库

导读
最近在加强训练SQL 语句，颇有体会，想把我的感悟分享给大家
抽空间在阅读《百年孤独》，为什么选这本书呢？ 是因为看完了《霍乱时期的爱情》，觉得作者加西亚·马尔克斯 写的还不错，想多看看他的作品。希望有读者可以推荐推荐一些好书。

[读书可以经历一千种人生，不读书的人只能活一次]————多看题能拓展思维

试题开始

学生表 Student

课程表 Course

老师表 Teacher

成绩表 SC

下面展示 试题及答案

在数据库中建立以上 4 表，并通过 SQL 语句完成数据插入

create table student(Sno int,Sname varchar(50) not null,Sage int,Ssex varchar(10) not null);
insert into student values(1001,'张宇',12,'男'),(1002,'张梅',18,'女'),(1003,'王鑫',16,'男'),(1004,'马东东',13,'男'),
(1005,'孙子涵',12,'女'),(1006,'钱一',12,'男'),(1007,'赵贺',15,'男'),(1008,'周雪',16,'女');
select * from student;
--Teacher
create table teacher(tno int,tname varchar(50) not null);
insert into teacher values(1,'李雷'),(2,'李纯'),(3,'胡悦悦'),(4,'朱清时'),
(5,'赛鸿飞'),(6,'宋三东');
select * from teacher;
--course
create table course(cno varchar(255),cname varchar(50) not null,tno int);
insert into course values('001','PHP',1),('002','C',1),('003','C++',2),('004','JAVA',3),
('005','Python',4),('006','R',5),('007','HTML',6);
select * from course;
--SC
select * into SC from 成绩表_二阶段;  ---无法为你们提供SC的数据
select * from sc;

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

select sc_1.sno from sc sc_1,sc sc_2 where sc_1.sno=sc_2.sno and sc_1.cno='001' and sc_2.cno='002' and sc_1.grade > sc_2.grade;

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

select sno,avg(grade) from sc group by sno having avg(grade)>60;

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

select sc.sno,st.sname,count(cno),sum(grade)from sc,student st where sc.sno = st.sno group by sc.sno,st.sname;

查询姓“李”的老师的个数

select count(tno) from teacher where tname like '李%';

查询学过“李纯”老师课的同学的学号、姓名 推荐第二种

法一：
SELECT sc.sno,st.sname from sc,student st WHERE sc.sno = st.sno and sc.cno in
(SELECT cno FROM	course c ,teacher t WHERE c.tno=t.tno AND t.tname='李纯');
法二： 
SELECT st.sno,sname FROM course c LEFT JOIN teacher t on t.tno=c.tno 
LEFT JOIN sc on sc.cno = c.cno 
LEFT JOIN student st on st.sno = sc.sno 
WHERE t.tname ='李纯' ;  

查询没学过“李雷”老师课的同学的学号、姓名

SELECT sno ,sname FROM student where sno not in(
SELECT DISTINCT sc.sno from sc where sc.cno IN(
SELECT cno FROM	course c ,teacher t WHERE c.tno=t.tno AND t.tname='李雷'));

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

SELECT sno,Sname FROM student WHERE sno in (SELECT t1.sno FROM
(SELECT * FROM sc WHERE cno = '001')t1,
(SELECT * FROM sc WHERE cno = '002')t2 WHERE t1.sno = t2.sno);

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

SELECT sno,Sname FROM student where sno in( SELECT t1.sno FROM (SELECT * from sc where cno ='002')t1,
(SELECT * from sc where cno ='001')t2 where t1.sno =t2.sno AND t1.grade >t2.grade);

查询学过“李雷”老师所教的所有课的同学的学号、姓 名

select DISTINCT st.sno, st.sname from student st, sc
where st.sno = sc.sno and sc.cno in
(select c.cno from course c, teacher t where c.tno = t.tno and t.tname = '李雷');

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

SELECT cno ,MAX(grade),MIN(grade) FROM sc GROUP BY cno;

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

SELECT st.sno,st.sname
FROM student st,sc
WHERE st.sno = sc.sno
GROUP BY st.sno,st.sname
HAVING COUNT(sc.cno) < (SELECT COUNT(*) FROM course c);

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

select sno,sname from student where sno in(select sno from sc where cno in(select cno from sc where sno = '1001') and sno!='1001');

查询至少学过学号为“1001”同学所学的一门课的其他 同学学号和姓名

select sno,sname from student where sno in(select sno from sc where cno in(select cno from sc where sno = '1001') and sno!='1001');

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

SELECT sc.cno AS 课程号,
max(c.cname)AS 课程名,
isnull(AVG(grade),0) AS 平均成绩,
100 * SUM(CASE WHEN isnull(grade,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc, course c
where sc.cno = c.cno
GROUP BY sc.cno
ORDER BY 平均成绩,及格率 desc

删除学习“李纯”老师课的 SC 表记录

delete from sc where cno in(
select cno from teacher t,course cs where t.tno=cs.tno and t.tname = '李纯'
);

把成绩表中朱清时老师教的课的成绩都改为此课程的平时成绩

update sc set grade = (select avg(sc_2.grade) from sc sc_2 where sc_2.cno=sc.cno)
from course c,teacher t where c.cno=sc.cno and c.tno=t.tno and t.tname='朱清时';

查询不同课程成绩相同的同学的学号、课程号、学生成绩(学号相同，学号不同两种情况)

SELECT distinct sc1.Sno,sc1.Cno,sc1.grade,sc2.Sno,sc2.Cno,sc2.grade
FROM	course c 
LEFT JOIN  sc sc1 on c.cno = sc1.cno 
LEFT JOIN sc sc2 on sc1.grade =sc2.grade
LEFT JOIN student st on st.sno = sc2.sno WHERE sc1.grade = sc2.grade 
AND sc1.sno <> sc2.sno AND  sc1.cno <> sc2.cno;（学号不同）
 
SELECT distinct sc1.Sno,sc1.Cno,sc1.grade,sc2.Sno,sc2.Cno,sc2.grade
FROM	course c 
LEFT JOIN  sc sc1 on c.cno = sc1.cno 
LEFT JOIN sc sc2 on sc1.grade =sc2.grade
LEFT JOIN student st on st.sno = sc2.sno WHERE sc1.grade = sc2.grade 
AND sc1.sno = sc2.sno AND  sc1.cno <> sc2.cno;（学号相同）

每位老师教课学生中成绩最好的学生姓名，学号，课程 名，老师名
还有其他方法

SELECT st.sno,sname,cname,tname FROM	course c LEFT JOIN teacher t on t.tno=c.tno  
LEFT JOIN  (SELECT cno,max(grade) as 最高分 from sc GROUP BY cno) sc1 on c.cno = sc1.cno 
LEFT JOIN sc sc2 on sc1.cno = sc2.cno AND sc1.[最高分] = sc2.grade
LEFT JOIN student st on st.sno = sc2.sno;

统计出选修人数最多的课程，需要包含课程名，课程编号，学生名称，分数四个字段
推荐第二种

法一：
SELECT  DISTINCT cs.Cname,cs.Cno,st.Sname,SC.grade FROM course cs,student st,SC 
where Sc.Sno=st.Sno and Sc.Cno=cs.Cno and Sc.Cno in  (select Cno from (select Cno,count(*) cnt from SC group by Cno) a 
where a.cnt = (select max(cnt) from (select Cno,count(*) cnt from SC group by Cno) a));


法二：
SELECT cname,c.cno,sname,grade from course c
LEFT JOIN sc on c.cno=sc.cno
LEFT JOIN (SELECT cno,COUNT(sno)cnt from sc GROUP BY cno) sc1 on c.cno = sc1.cno
inner JOIN (SELECT max(cnt) 最大 FROM (SELECT cno,COUNT(sno)cnt from sc GROUP BY cno)a) sc2 on sc2.最大=sc1.cnt
LEFT JOIN student st on st.sno = sc.sno;

按性别统计出每门课程男女选修的人数及成绩的平均 值，需要包含性别，课程名，人 数，总成绩，平均成绩 5 字段

SELECT st.ssex,cname,count(*) cnt,sum(grade) 总成绩,avg(grade) 平均成绩 from course c
LEFT JOIN sc on sc.cno= c.cno
LEFT JOIN  student st on st.sno = sc.sno
group by st.ssex,cname

总结

我觉得这样的面试题给你，公司主要考察你的 **关联**掌握情况，希望我这份文档对你有用