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

已于 2024-05-26 09:51:44 修改
阅读量2.2k 收藏 35
点赞数 45
文章标签： 面试 职场和发展 sql
于 2024-05-21 21:08:18 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_53300365/article/details/139102278
版权

哔哩哔哩视频地址如下

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

#17统计各科成绩各分数段的人数：课程编号，课程名称，100-85，85-70，70-60，60-0人数和所占百分比

SELECT c.cname,s.cid,SUM(CASE WHEN s.score<60 THEN 1 ELSE 0 END) AS "60-0人数",SUM(CASE WHEN s.score<60 THEN 1 ELSE 0 END)/COUNT(s.score) AS "60-0占比",
SUM(CASE WHEN s.score>=60 AND s.score<70 THEN 1 ELSE 0 END) AS "70-60人数",SUM(CASE WHEN s.score>=60 AND s.score<70 THEN 1 ELSE 0 END)/COUNT(s.score) AS "70-60占比",
SUM(CASE WHEN s.score>=70 AND s.score<85 THEN 1 ELSE 0 END) AS "85-70人数",SUM(CASE WHEN s.score>=70 AND s.score<85 THEN 1 ELSE 0 END)/COUNT(s.score) AS "85-70占比",
SUM(CASE WHEN s.score>=85 AND s.score<=100 THEN 1 ELSE 0 END) AS "100-85人数",SUM(CASE WHEN s.score>=85 AND s.score<=100 THEN 1 ELSE 0 END)/COUNT(s.score) AS "100-85占比"
FROM sc s
JOIN course c ON s.cid=c.cid
GROUP BY s.cid;


#18.查询各科成绩前三名的学生信息
#sc，student
SELECT c.cid AS "课程id",s.*,c.ranking
FROM student s
JOIN 
(SELECT cid,sid,score AS "成绩",row_number() over(PARTITION BY cid ORDER BY score DESC) AS "ranking"
FROM sc) c
ON s.sid=c.sid
WHERE ranking<4
ORDER BY cid,ranking;
#先查询出带排名的cid，sid，ranking，作为子查询连接student查询，条件sid相等，查询结果排序，order by cid，ranking，最后where限制一下
#ranking1的大小，只要前三名

SELECT c.cid,s.*,c.ranking
FROM student s
JOIN 
(SELECT sid,cid,row_number() over(PARTITION BY cid ORDER BY score) ranking
FROM sc) c
ON s.sid=c.sid
WHERE ranking<4
ORDER BY c.cid,c.ranking;

#19.查询每门课程被选修的学生数量
#sc
SELECT cid AS "课程编号",COUNT(*) AS "被选修数量"
FROM sc
GROUP BY cid;

#20.查询出只选修了两名课程的学生的学号和姓名
#sc,student
#先sc表查出学号，然后student表拿姓名

SELECT s.sname,c.sid
FROM student s
JOIN
(SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(cid)=2) c
ON s.sid=c.sid;

#21.查询男生、女生人数
#student
#两种方法，前者主要是复习sum+case when then的用法，后者就是直接分组查询，前者在更加复杂的应用场景里比较好用
SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END) AS "男生人数",SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) AS "女生人数"
FROM student;

SELECT ssex,COUNT(*)
FROM student
GROUP BY ssex;


#22.查询出名字中含有 风 的学生信息
#student
#这题考察的是百分号占位符的用法
SELECT * 
FROM student
WHERE sname LIKE "%风%";

#23.查询同名同性学生名单，并且统计同名人数
#student
#写了半天也没搞明白为什么连接查询中大于等于三个同名的会出现问题，连接查询好难，，，看了眼评论区，很好的思路

SELECT Sname, COUNT(*) AS COUNT
FROM student
GROUP BY Sname
HAVING COUNT(*) > 1;

#24.查询1990年出生的学生的名单
#student
#考点是日期的转化？？第一次见
SELECT * 
FROM student
WHERE YEAR(sage)='1990'

#25.查询每门课程的平均成绩，结果按照平均成绩降序排列，平均成绩相同时，按照课程编号升序排列
SELECT cid,AVG(score) avg_score
FROM sc
GROUP BY cid
ORDER BY avg_score DESC,cid 

#26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
#思路是从sc表中找到平均成绩大于等于85的学生的学号，然后student表where in 找到的学号，然后输出#2
SELECT s.sid AS "学号",s.sname AS "姓名",c.avg_score AS "平均成绩"
FROM student s
JOIN (SELECT sid,AVG(score) avg_score FROM sc GROUP BY sid HAVING avg_score>=85 ) c ON s.sid=c.sid

#27.查询课程名称为数学，且分数低于60的学生的姓名和分数
#course,sc,student
#子查询course得到cid，in cid得到sid和平均成绩，然后连接查询sid相等 得到sname和score
SELECT s.sname,c.score
FROM student s
JOIN
(SELECT sid,score FROM sc
WHERE cid IN  
(SELECT cid FROM course WHERE cname="数学")
AND score<60) c ON s.sid=c.sid

#28.查询所有学生的课程以及分数情况，包含没选课的学生
#student，sc
#left join
SELECT s.*,c.cid,c.score
FROM student s
LEFT JOIN sc c ON s.sid=c.sid

#29.查询任何一门课程成绩都在70分以上的学生姓名、课程名称、分数
#student、course、sc
SELECT s.sname AS "学生姓名",e.cname AS "课程名称",c.score AS "分数"
FROM student s
JOIN
(SELECT sid,cid,score
FROM sc
WHERE score>70) c
ON s.sid=c.sid
JOIN course e ON c.cid=e.cid 

#30.查询不及格的课程
#很抽象的问法，也不知道是指找出有不及格学生的课程还是找出平均分数不及格的课程
#看了一下原题答案，意思是找出有不及格分数的学生的课程
#然后这个课程指的是课程信息
#sc,course
SELECT *
FROM course
WHERE cid IN (SELECT cid FROM sc WHERE score<60)

结尾

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

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值