【sql学习】经典面试50题-PostgreSQL语句练习

时间：2020-07-24 10:47:32 阅读：94 评论：0 收藏：0 [点我收藏+]

以下语句由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语句练习

原文：https://www.cnblogs.com/mengzisama/p/13363541.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)