数据分析面试必备，45道SQL题目，从易到难，刷题必备 个人答案

哔哩哔哩视频地址如下

https://www.bilibili.com/video/BV1Bp4y1n7Ah?p=9&vd_source=3c401e9b12aadd668c92b73995070898

缘由

本人由于今天晚上面试回答简单sql语句磕磕巴巴，被面试官通知：我没有想问的，你可以自行投简历 而导致红温，遂上b站刷sql题目，与本贴更新个人答案

建表语句

#学生表
CREATE TABLE Student(
SId VARCHAR(10),
Sname VARCHAR(10),
Sage DATETIME,
Ssex VARCHAR(10)
);
#教师表
CREATE TABLE Teacher(
TId VARCHAR(10),
Tname VARCHAR(10)
);
#课程表
CREATE TABLE Course(
CID VARCHAR(10),
Cname NVARCHAR(10),
Tid VARCHAR(10)
);
#成绩表
CREATE TABLE SC(
SId VARCHAR(10),
CID VARCHAR(10),
score DECIMAL(18,1)
);

插入语句

INSERT INTO Student VALUES('01','赵雷','1990-01-01','男');
INSERT INTO Student VALUES('02','钱电','1990-12-21','男');
INSERT INTO Student VALUES('03','孙风','1990-05-20','男');
INSERT INTO Student VALUES('04' ,'李云','1990-08-06','男');
INSERT INTO Student VALUES('05','周梅','1991-12-01','女');
INSERT INTO Student VALUES('06','吴兰','1992-03-01','女');
INSERT INTO Student VALUES('07','郑竹','1999-07-01','女');
INSERT INTO Student VALUES('09','张三','2017-12-20','女');
INSERT INTO Student VALUES('10','李四','2017-12-25','女');
INSERT INTO Student VALUES('11','李四','2017-12-30','女');
INSERT INTO Student VALUES('12','赵六','2017-01-01','女');
INSERT INTO Student VALUES('13','孙七','2018-01-01','女');

INSERT INTO Course VALUES('01','语文','02');
INSERT INTO Course VALUES('02','数学','01');
INSERT INTO Course VALUES('03','英语','03');

INSERT INTO Teacher VALUES('01','张三');
INSERT INTO Teacher VALUES('02','李四');
INSERT INTO Teacher VALUES('03','王五');

INSERT INTO SC VALUES
    ('01', '01', 80),
    ('01', '02', 90),
    ('01', '03', 99),
    ('02', '01', 70),
    ('02', '02', 60),
    ('02', '03', 80),
    ('03', '01', 80),
    ('03', '02', 80),
    ('03', '03', 80),
    ('04', '01', 50),
    ('04', '02', 30),
    ('04', '03', 20),
    ('05', '01', 76),
    ('05', '02', 87),
    ('06', '01', 31),
    ('06', '03', 34),
    ('07', '02', 89),
    ('07', '03', 98);

查询语句

SELECT * FROM course

SELECT * FROM sc

SELECT * FROM student

SELECT * FROM teacher

#面试碰到的 查询所有学生平均成绩和姓名
SELECT a.sname,AVG(b.score)
FROM 
student a
JOIN
SC b
ON 
a.SId=b.SId
GROUP BY
a.sname
#1.查询01课程比02课程成绩好的学生的信息和课程分数
SELECT a.score,b.score,c.*
FROM
sc a
JOIN
sc b
ON
a.SId=b.SId
JOIN
student c
ON
a.SId=c.SId
WHERE
a.CID="01" AND b.CID="02" AND a.score>b.score

#2.查询平均成绩大于等于60分的同学的学生编号和和学生姓名和平均成绩
SELECT b.SId,b.Sname,AVG(a.score)
FROM
sc a
JOIN
student b
ON
a.SId=b.SId
GROUP BY
b.SId

#3.查询SC表存在成绩的学生信息
SELECT *
FROM
student a
WHERE
a.SId 
IN
(SELECT DISTINCT SId FROM sc)

#4查询所有同学的学生编号，学生姓名，选课总数和所有课程的总成绩
SELECT s.sid AS "学生编号",s.sname AS "学生姓名",SUM(c.score) AS "总成绩",COUNT(c.cid) AS "选课总数"
FROM
student s
JOIN
sc c
ON
s.sid=c.sid
GROUP BY
s.sid

#5.查询李姓老师的数量
SELECT COUNT(*) AS "数量"
FROM 
teacher t
WHERE
t.tname LIKE "李%"

#6.查询学习过张三老师授课的学生的信息
SELECT s.*
FROM
student s
JOIN
sc c ON s.sid=c.sid
JOIN
course co ON co.cid=c.cid
JOIN
teacher t ON t.tid=co.tid
WHERE
t.tname="张三"

#7.查询没有学全所有课程的学生的信息
#这是合并两个查询结果的方法
SELECT s.*
FROM
student s
JOIN
sc c ON c.sid=s.sid
GROUP BY
s.sid
HAVING
COUNT(c.cid)<3
UNION
SELECT s.*
FROM
student s
WHERE
s.sid NOT IN(SELECT sid FROM sc)
#这是使用左连接的方法，没有满足s.sid=c.sid的自然就是没有选课的
SELECT s.*
FROM
student s
LEFT JOIN
sc c ON c.sid=s.sid
GROUP BY
s.sid
HAVING
COUNT(c.cid)<3

#8.查询至少有一门课程是与学号为01的同学所学相同的同学的信息
SELECT s.*
FROM student s
JOIN
sc c ON s.sid=c.sid
WHERE s.sid!="01" AND c.cid IN (SELECT cid FROM sc WHERE sid="01" )
GROUP BY s.sid

#9.查询和05号同学学习的课程完全相同的其他学生的信息
#找出有课程不在01所学课程里的学生id，not in，然后从剩下的学生中找到课程数量与01所学课程数量相同的学生
#自查询从sc表得到sid然后连接查询student表得到学生信息
SELECT s.*
FROM 
(SELECT sid FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid="05"))
GROUP BY 
sid
HAVING 
COUNT(cid)=(SELECT COUNT(cid) FROM sc WHERE sid="05")) c
JOIN
student s
ON
s.sid=c.sid

#10.查询没有学习过张三老师讲授的任意一门课程的学生的名字 这里就一门课程
#先查询张三老师教授的课程
#然后查询学习过张三老师课程的学生，然后not in就是没学习过的
SELECT c.cid
FROM teacher t
JOIN course c ON t.tid=c.tid
WHERE t.tname="张三";

SELECT *
FROM student 
WHERE sid NOT IN 
(SELECT sid
FROM sc
WHERE sc.`CID` IN (SELECT c.cid
FROM teacher t
JOIN course c ON t.tid=c.tid
WHERE t.tname="张三")
);

#11.查询两门及以上不及格课程的同学的学号、姓名和平均成绩
#sc student
#子查询两门以上不及格课程的学生id，然后主查询id in 子查询id，根据id分组，获取平均成绩

SELECT student.`SId`,student.`Sname`,AVG(sc.`score`)
FROM student,sc
WHERE sc.`SId` IN
(SELECT sid 
FROM sc
WHERE score<60
GROUP BY sid
HAVING COUNT(score)>=2) AND student.sid=sc.sid 
GROUP BY sc.sid

#12.检索01课程分数小于60，按分数降序排列的学生的信息
SELECT student.*
FROM sc,student
WHERE
sc.`CID`="01" AND sc.`score`<60 AND sc.`SId`=student.`SId`
ORDER BY sc.`score` DESC

SELECT s.*
FROM student s
JOIN sc c ON s.sid=c.sid
WHERE c.cid="01" AND c.score<60
ORDER BY c.score DESC

#13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
#子查询查出平均成绩和sid，父查询通过sid连接子查询，然后根据子查询的平均成绩排序
SELECT c.sid,c.cid,c.score,b.avg_sc
FROM sc c
JOIN
(SELECT sid,AVG(score) AS avg_sc
FROM sc
GROUP BY sid
) b ON c.sid=b.sid 
ORDER BY b.avg_sc DESC

#14.查询各科成绩的最高分，最低分，平均分，及格率，中等率，优良率，优秀率
SELECT cid,MAX(score) AS "最高分",MIN(score) AS "最低分",AVG(score) AS "平均分",
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格率",
SUM(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END)/COUNT(*) AS "中等率",
SUM(CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END)/COUNT(*) AS "优良率",
SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END)/COUNT(*) AS "优秀率"
FROM sc
GROUP BY cid 

#15.1按照各科成绩进行排序，并显示排名，score重复时不合并名次
#15.2按照各科成绩进行排序，并显示排名，score重复时合并名次
#这里使用了窗口函数
#row_number()不会出现相同排名 rank()会出现相同排名，但是跳跃排序 dense_rank()会出现相同排名，且连续排序
SELECT *,row_number() over(PARTITION BY cid ORDER BY score DESC) AS "排名"
FROM sc
SELECT *,rank() over(PARTITION BY cid ORDER BY score DESC) AS "排名"
FROM sc
SELECT *,dense_rank() over(PARTITION BY cid ORDER BY score DESC) AS "排名"
FROM sc

#16查询学生的总成绩，并且进行排名，分数重复时保留每次空缺（跳跃排名）
SELECT SUM(score) AS "总成绩",rank() over(ORDER BY SUM(score) DESC) AS "排名"
FROM sc
GROUP BY sid

结尾

持续更新，有错误欢迎指出，希望能赶在暑期前找到实习，共勉