【sql学习】经典面试50题-PostgreSQL语句练习
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【sql学习】经典面试50题-PostgreSQL语句练习，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含17892字，纯文字阅读大概需要26分钟。
内容图文
以下语句由PostgreSQL数据库语法编写，都全部调试过，欢迎探讨～部分语句结尾未添加分号，时间关系大小写不统一，格式不是特别整洁，强迫症求放过～
先创建四张表格
create table student(
s_id varchar(10),
s_name varchar(20),
s_age date,
s_sex varchar(10)
);
create table course(
c_id varchar(10),
c_name varchar(20),
t_id varchar(10)
);
create table teacher (
t_id varchar(10),
t_name varchar(20)
);
create table score (
s_id varchar(10),
c_id varchar(10),
score integer ,
);
插入数据
insert into student (s_id, s_name, s_age, s_sex)
values ('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
insert into course (c_id, c_name, t_id)
values ('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
insert into teacher (t_id, t_name)
values ('01' , '张三'),
('02' , '李四'),
('03' , '王五');
insert into score (s_id, c_id, score)
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);
值得注意的是，学生的名单跟成绩名单相比，学生的id是多的，也就是说8号学生是没有成绩的，另外也发现有的学生的部分课程没有成绩，这都是需要注意的，
1、查询"01"课程比"02"课程成绩高的学生的学号及课程分数（重点）
解题思路：
第一步：score中分别查询出01课程的成绩和02的课程的成绩进行inner join，
(select s_id, score as score1 from score where c_id='01') as a inner join (select s_id, score as score2 from score where c_id='02') as b
on a.s_id =b.s_id
第二步：以01课程成绩>02课程成绩进行条件筛选
where score1 > score2
第三步：选出需要的字段
select course.s_id， score1，score2
按照语句书写顺序将以上语句组织为：
select a.s_id as s_id,score1,score2 from (select s_id, score as score1 from score where c_id='01') a inner join (select s_id, score as score2 from score where c_id='02') b on a.s_id=b.s_id where score1>score2;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select * from (select s_id, score as score1 from score where c_id='01') a inner join (select s_id, score as score2 from score where c_id='02') b on a.s_id=b.s_id
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select s_id, score as score1 from score where c_id='01') a left join (select s_id, score as score2 from score where c_id='02') b on a.s_id=b.s_id
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from (select s_id, score as score1 from score where c_id='01') a right join (select s_id, score as score2 from score where c_id='02') b on a.s_id=b.s_id where score1 is null
2、查询平均成绩大于60分的学生的学号和平均成绩（重点）
SELECT s_id,AVG(score) FROM score GROUP BY s_id having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩
简洁的写法
SELECT t1.s_id,t1.s_name,count(t1.s_id),sum(t2.score) FROM student AS t1 LEFT JOIN score as t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id,t1.s_name
优化版： SELECT t1.s_id,t1.s_name,count(t1.s_id),sum(case when t2.score is NULL then 0 else t2.score end) FROM student AS t1 LEFT JOIN score as t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id,t1.s_name
注意：这和代码有bug。王菊同学的选课数目应该为0
一个冗长的答案：
SELECT t1.s_id,t1.s_name,t2.cnt,t2.total_score FROM (SELECT s_id,s_name FROM student ) AS t1 LEFT JOIN (SELECT score.s_id,COUNT(*) AS cnt,SUM(score) AS total_score FROM score GROUP BY score.s_id) AS t2 ON t1.s_id = t2.s_id
解题思路：先将student表和score表进行联结，这里选左联结，确保找到所有学生的信息，然后对新表进行汇总和计算，group by中只能出现select中出现的字段哟～
第四题：查询姓“李”的老师的个数；
SELECT count(*) from teacher where t_name like '李%'
第五题：查询没学过“张三”老师课的学生的学号、姓名（重点）
思路：
SELECT DISTINCT s_id,s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM score as d LEFT JOIN course AS b ON d.c_id = b.c_id LEFT JOIN teacher AS c ON b.t_id = c.t_id WHERE c.t_name = '张三')
常见的错误示范：，相当于把张三老师的记录去掉了，实际上会选出错误的结果
select distinct a.s_id,a.s_name from student as a LEFT JOIN score as d on a.s_id = d.s_id left join course as b on d.c_id = b.c_id left join teacher as c on b.t_id = c.t_id WHERE c.t_name <> '张三'
第六题：查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
select distinct a.s_id,a.s_name from student as a LEFT JOIN score as d on a.s_id = d.s_id left join course as b on d.c_id = b.c_id left join teacher as c on b.t_id = c.t_id WHERE c.t_name = '张三'
SELECT DISTINCT s_id,s_name
FROM student
WHERE s_id IN
(SELECT s_id
FROM score as d
LEFT JOIN course AS b ON d.c_id = b.c_id
LEFT JOIN teacher AS c ON b.t_id = c.t_id
WHERE c.t_name = '张三')
SELECT s_id,s_name
from student
where s_id in(
SELECT s_id
from score
where c_id in(SELECT c_id
from course
where t_id in (SELECT
t_id from teacher where t_name = '张三')))
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
SELECT DISTINCT s_id,s_name
FROM student
WHERE s_id IN
(SELECT DISTINCT t1.s_id
FROM
(SELECT s_id,score
FROM score
WHERE c_id = '01') as t1
JOIN
(SELECT s_id,score
FROM score
WHERE c_id = '02') as t2
ON t1.s_id = t2.s_id
)
解题思路：在这里首先是利用score表字查询的自联结将原来表中的列变成了行
8、查询课程编号为“02”的总成绩（不重点）
SELECT sum(score)
from score
where c_id = '02'
9、查询所有课程成绩小于60分的学生的学号、姓名（同第2题，要注意存在成绩为null的情况，因此得采用not in）
思路解读：因为这样子查询中的结果不会包含null的id，整体-不符合要求 = 符合要求的+null
select s_id,s_name from student where s_id not in (select s_id from score GROUP BY s_id having max(score)>= 60);
错误写法：从score中直接选id的话，这种未包含全部学生的id，成绩为null的情况
select s_id,s_name
from student
where s_id in (select s_id from score GROUP BY s_id having max(score) < 60);
10、查询没有学全所有课的学生的学号、姓名(重点)
select a.s_id,a.s_name from student as a left join score as d on a.s_id = d.s_id left join course as b on b.c_id = d.c_id GROUP BY a.s_id,a.s_name having count (a.s_id)<(SELECT count(distinct c_id) from course)
注意：此处不用distinct也可
错误的写法：从score中选会漏掉选课结果为null的王菊，但是因为course表没有跟student表直接管理，因此最好的办法是join
select s_id，s_name from student where s_id in (SELECT s_id,count(s_id) from score GROUP BY s_id having count(s_id) < SELECT count(*) from course)）
11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
inner join的效率比in高一些
一种复杂的写法：
SELECT t1.s_id,t1.s_name from student as t1 right join (SELECT DISTINCT s_id from score where c_id in (SELECT c_id from score where s_id = '01' )and s_id != '01') as t2 on t1.s_id = t2.s_id
注意；通过一对多方式来选择时会出现很多重复数据，这里指，一个s_id 对应多个c_id，一对一联结是比较好的，根据c_id来选s_id的时候，必然会发生重复，去重用distinct
SELECT s_id,s_name from student where s_id in (SELECT DISTINCT s_id from score where c_id in (SELECT c_id from score where s_id = '01') and s_id != '01')
12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
复杂写法
select s_id from score where c_id = ( SELECT c_id from score where s_id = '01') and s_id != '01' GROUP BY s_id having COUNT(s_id) = (SELECT COUNT(*) from score where s_id = '01')
简洁写法：
SELECT s_id from score group by s_id having (count(s_id) = (SELECT COUNT(*) from score where s_id = '01') ) and (s_id != '01')
注意：本道题因为课程一共有三项，1号同学正好有三项课程，因此指判断课程数是否相同即可，但是如果总课程数大于3以上方法则不适用,上述方法存在bug，以下应该是正确写法
注意：双重否定的用法，not in （a,b,c） 的意思跟 in（全部集合中除去（a.b.c）的集合）的意思一样
如果用集合的概念来解释就是 如果两个不同的集合a和b相加等于全集U，那么针对全集中的元素来说not in a = in b
SELECT s_id from score where s_id not in ( select s_id from score where c_id not in ( SELECT c_id from score where s_id = '01') )and s_id != '01'GROUP BY s_id having COUNT(s_id) = (SELECT COUNT(*) from score where s_id = '01')
13.查询没学过"张三"老师讲授的任一门课程的学生姓名(重点)
SELECT DISTINCT s_id,s_name from student where s_id not in (select s_id from score where c_id in (SELECT c_id from course where t_id in (select t_id from teacher where t_name = '张三')))
14.
15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select a.s_id,a.s_name,t.avg_score from student as a INNER JOIN (SELECT s_id,AVG(score)as avg_score from score GROUP BY s_id having COUNT(*) >=2 and max(score)< 60) as t on a.s_id = t.s_id
16.检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
SELECT * from student where s_id in
(SELECT s_id from score where c_id = '01' and score < 60 ORDER BY score);
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点)
注意；这里的max起到取出数据的作用，没有实际的max功能，分别进行操作的时候，可以用case when
SELECT s_id, max (case when c_id = '02' then score else null end) as "数学", max (case when c_id = '01' then score else null end) as "语文", max (case when c_id = '03' then score else null end) as "英语", AVG(score) from score GROUP BY s_id ORDER BY AVG(score) desc
错误示范：这种形式不是最优解
SELECT a.s_id,d.score,AVG(d.score) FROM student as a LEFT JOIN score as d on a.s_id = d.s_id GROUP BY a.s_id,d.score ORDER BY AVG(d.score) desc
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
SELECT c_id,max (score),min (score),avg(case when score is not null then score else 0 end), AVG(case when score >= 70 and score < 80 then 1 else 0 end) as middle, AVG(case when score >= 80 and score < 90 then 1 else 0 end) as good, AVG(case when score >= 90 then 1 else 0 end) as great from score GROUP BY c_id;
19、按各科成绩进行排序，并显示排名(重点dens_rank(）over（order by 列）
SELECT s_id,c_id,score, DENSE_RANK()OVER(PARTITION BY c_id ORDER BY score desc) AS ranking from score
窗口函数的用法看这里：看这里
20、查询学生的总成绩并进行排名（不重点）
SELECT s_id,sum(score) from score group by s_id order by sum(score) desc
21、查询不同老师所教不同课程平均分从高到低显示(不重点)
SELECT c.t_name,d.c_id,avg(d.score) from score as d inner join course as b on d.c_id = b.c_id inner join teacher as c on c.t_id = b.t_id group by d.c_id,c.t_name order by avg(d.score)desc
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
SELECT c_id,s_name,score from student as a INNER join (SELECT *, DENSE_RANK()OVER(PARTITION BY c_id ORDER BY score desc) AS ranking from score) as t on a.s_id = t.s_id where ranking = 2 or ranking = 3 ORDER BY c_id,score des
23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)
select co.c_id ,c_name, sum (case when score < 60 then 1 else 0 end ) as D, sum (case when score >= 60 and score < 70 then 1 else 0 end ) as C, sum (case when score >= 70 and score < 85 then 1 else 0 end ) as B, sum (case when score >= 85 and score < 100 then 1 else 0 end ) as A from score as sc left join course as co on co.c_id = sc.c_id GROUP BY co.c_id ,c_name
24、查询学生平均成绩及其名次（同19题，重点）
注意在：DENSE_RANK()OVER( ORDER BY avg_score desc) AS ranking中对成绩按照desc进行排序
在结尾对ranking升序
SELECT * , DENSE_RANK()OVER( ORDER BY avg_score desc) AS ranking from (SELECT s_id,AVG(case when score is not null then score else 0 end) as avg_score FROM score GROUP BY s_id) as t ORDER BY ranking
考虑成绩并列可以用dense_rank（）,不考虑成绩并列可以用row_number（）
25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
SELECT c_id,s_name,score from student as a INNER join (SELECT *, ROW_NUMBER()OVER(PARTITION BY c_id ORDER BY score desc) AS ranking from score) as t on a.s_id = t.s_id where ranking = 1 or ranking = 2 or ranking = 3 ORDER BY c_id,score desc
如果需要知道课程名称，可以继续join一个course表
26、查询每门课程被选修的学生数(不重点)
SELECT c_id,count(*) from score GROUP BY c_id
27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
SELECT s_id,s_name from student where s_id in (SELECT s_id from score GROUP BY s_id having count(*) = 2)
28、查询男生、女生人数(不重点)
select s_sex,count(s_sex) from student group by s_sex;
29、查询名字中含有"风"字的学生信息（不重点）
select * from student where s_name like '%风'
30、
31、查询1990年出生的学生名单（重点year）
select * from student where s_age like '1900%';
32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
select a.s_id,a.s_name,avg(case when d.score is not null then d.score else 0 end) from student as a left join score as d on a.s_id = d.s_id group by a.s_id,a.s_name having avg(d.score) > 85；
33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
select c_id ,avg (score) from score group by c_id order by avg (score) asc,c_id desc
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
select a.s_name,d.score,d.c_id from student as a inner join score as d on a.s_id = d.s_id where c_id in (select c_id from course where c_name = '数学') and d.score < 60
35、查询所有学生的课程及分数情况（重点）
SELECT a.s_id,a.s_name, sum(case when d.c_id = '01' then d.score else null end ) as sub_01, sum(case when d.c_id = '02' then d.score else null end ) as sub_02, sum(case when d.c_id = '03' then d.score else null end ) as sub_03 from student as a left join score as d on a.s_id = d.s_id group by a.s_id,a.s_name
?
错误滴答案：
SELECT a.s_id ,a.s_name,d.score from student as a left join score as d on a.s_id = d.s_id
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
正常答案：
select a.s_name,b.c_name,d.score from student as a inner join score as d on a.s_id = d.s_id inner join course as b on d.c_id = b.c_id where d.score > 70
一个比较复杂又没必要的答案：
select a.s_name,b.c_name,d.score from student as a inner join score as d on a.s_id = d.s_id inner join course as b on d.c_id = b.c_id GROUP BY a.s_name,b.c_name,d.score having MIN(d.score) > 70 ORDER BY a.s_name;
37、查询不及格的课程并按课程号从大到小排列(不重点)
select d.c_id, b.c_name ,score from score as d inner join course as b on d.c_id = b.c_id where score < 60 order by d.c_id DESC
38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
select a.s_id,a.s_name from student as a left join score as d on a.s_id = d.s_id where c_id = '03' and score > 80;
39、求每门课程的学生人数（不重要）
select c_id , count(*) from score GROUP BY c_id
40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要limit）
解法1:
SELECT s_name,score FROM student as a inner join score as d on a.s_id = d.s_id where c_id in (select c_id from course where t_id in(select t_id from teacher where t_name = '张三')) ORDER BY score desc LIMIT 1
解法2：
SELECT s_name,score FROM student as a inner join score as d on a.s_id = d.s_id inner join course as b on d.c_id = b.c_id inner join teacher as c on b.t_id = c.t_id where t_name = '张三' ORDER BY score desc LIMIT 1
解法3: select s_name,score from (SELECT s_name,score,dENSE_RANK()OVER(ORDER BY score desc) AS ranking FROM student as a inner join score as d on a.s_id = d.s_id inner join course as b on d.c_id = b.c_id inner join teacher as c on b.t_id = c.t_id where t_name = '张三' ORDER BY score desc) as t where ranking = 1
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
网上答案，我认为这个答案也不够严谨，因为学生可能两个成绩相同也可能三个成绩相同，
select s1.s_id,s1.c_id,s1.score,s2.score,s3.score from (select s_id,c_id,score from score where c_id = '01' ) as s1 inner join (select s_id,c_id,score from score where c_id = '02' ) as s2 on s1.s_id = s2.s_id inner join (select s_id,c_id,score from score where c_id = '02' ) as s3 on s2.s_id = s3.s_id where s1.score = s2.score and s2.score = s3.score
我认为我这个是正确答案
select s_id,c_id,score from score where s_id not in (select s_id from score group by s_id having max(score)!= min(score))
42、查询每门功成绩最好的前两名（同22和25题）
SELECT c_id,s_name,score from student as a INNER join (SELECT *, DENSE_RANK()OVER(PARTITION BY c_id ORDER BY score desc) AS ranking from score) as t on a.s_id = t.s_id where ranking = 1 or ranking = 2 ORDER BY c_id,score desc
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）
select c_id,COUNT(*) from score GROUP BY c_id having COUNT(*) > 5 order by COUNT(*) desc ,c_id asc
44、检索至少选修两门课程的学生学号（不重要）
select s_id,COUNT(*) from score GROUP BY s_id having COUNT(*) >= 2
45、查询选修了全部课程的学生信息（重点）
select * from student where s_id in (select s_id from score group by s_id having count(DISTINCT c_id) = (select count(*) from course))
47、查询没学过“张三”老师讲授的任一门课程的学生姓名
select s_id,s_name from student where s_id not in (SELECT s_id from score where c_id in (select c_id from course where t_id in (select t_id from teacher where t_name = '张三')))
48、查询两门以上不及格课程的同学的学号及其平均成绩
select s_id,avg(score) from score group by s_id having COUNT(s_id) >= 2 and max(score) < 60
select s_id,avg(score) from score where score < 60 group by s_id having COUNT(s_id) >= 2
49、查询各学生的年龄（精确到月份）
备注：年份转换成月份，比如结果是1.9，ditediff 最后取1年
SELECT s_id,s_age, extract(YEAR from age(current_date,s_age)) from student
时间函数用法看这里：https://www.yiibai.com/html/postgresql/2013/080783.html
50、查询本月过生日的学生
SELECT * from student where EXTRACT(month from s_age) = EXTRACT(month from CURRENT_DATE)
50.1、查询下月过生日的学生
注意where子句中，两个select 子句部分都要各自加括号，这样括号内先计算得出一个具体的数值
SELECT * from student where (SELECT extract (month from s_age)) = (SELECT extract(month from CURRENT_DATE) +1)
50.2、查询本周过生日的学生
50.3、查询下周过生日的学生
因为postgresql中没有week函数，暂时还不知道怎么写
未完待续～因为还不太会优化sq代码，日后再标注出比较好的解答
内容总结
以上是互联网集市为您收集整理的【sql学习】经典面试50题-PostgreSQL语句练习全部内容，希望文章能够帮你解决【sql学习】经典面试50题-PostgreSQL语句练习所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。