postgresql测试题_【sql学习】经典⾯试50题-PostgreSQL语
句练习
以下语句由PostgreSQL数据库语法编写，都全部调试过，欢迎探讨～部分语句结尾未添加分号，时间关系⼤⼩写不统⼀，格式不是特别整洁，强迫症求放过～
先创建四张表格
create tablestudent(
s_idvarchar(10),
s_namevarchar(20),
s_age date,
s_sexvarchar(10)
);create tablecourse(
c_idvarchar(10),
c_namevarchar(20),
t_idvarchar(10)
);create tableteacher (
t_idvarchar(10),
t_namevarchar(20)
);create tablescore (
s_idvarchar(10),
c_idvarchar(10),
score integer ,);
插⼊数据
insert intostudent (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 intocourse (c_id, c_name, t_id)values ('01' , '语⽂' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');insert intoteacher (t_id, t_name)values ('01' , '张三'),
('02' , '李四'),
('03' , '王五');insert intoscore (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，
第⼆步：以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') ainner join(select s_id, score as score2 from score where c_id='02') bon a.s_id=b.s_idwhere score1>score2;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select * from(select s_id, score as score1 from score where c_id='01') ainner join(select s_id, score as score2 from score where c_id='02') bon 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') aleft join(select s_id, score as score2 from score where c_id='02') bon a.s_id=b.s_id
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from(select s_id, score as score1 from score where c_id='01') aright join(select s_id, score as score2 from score where c_id='02') bon a.s_id=b.s_idwhere score1 is null
2、查询平均成绩⼤于60分的学⽣的学号和平均成绩(重点)
SELECT s_id,AVG(score)FROMscoreGROUP BYs_idhaving avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩
每天学点sql经典句子简洁的写法
SELECT t1.s_id,t1.s_name,count(t1.s_id),sum(t2.score)FROM student ASt1LEFT JOINscoreast2ON t1.s_id =t2.s_idGROUP 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 ASt1LEFT JOINscoreast2ON t1.s_id =t2.s_idGROUP BY t1.s_id,t1.s_name
注意：这和代码有bug。王菊同学的选课数⽬应该为0
⼀个冗长的答案：
SELECTt1.s_id,t1.s_name,al_scoreFROM(SELECTs_id,s_nameFROMstudent
)ASt1LEFT JOIN(SELECT score.s_id,COUNT(*) AS cnt,SUM(score) AStotal_scoreFROMscoreGROUP BY score.s_id) ASt2ON t1.s_id = t2.s_id
解题思路：先将student表和score表进⾏联结，这⾥选左联结，确保到所有学⽣的信息，然后对新表进⾏汇总和计算，group by中只能出现select中出现的字段哟～
第四题：查询姓“李”的⽼师的个数；
SELECT count(*)fromteacherwhere t_name like '李%'
第五题：查询没学过“张三”⽼师课的学⽣的学号、姓名(重点)
思路：
SELECT DISTINCTs_id,s_nameFROMstudentWHERE s_id NOT IN(SELECTs_idFROM score asdLEFT JOIN course AS b ON d.c_id =b.c_idLEFT JOIN teacher AS c ON b.t_id =c.t_idWHERE c.t_name = '张三')
常见的错误⽰范：，相当于把张三⽼师的记录去掉了，实际上会选出错误的结果
select distincta.s_id,a.s_namefrom student asaLEFT JOIN score asdon a.s_id =d.s_idleft join course asbon d.c_id =b.c_idleft join teacher ascon b.t_id =c.t_idWHERE c.t_name <> '张三'
第六题：查询学过“张三”⽼师所教的所有课的同学的学号、姓名(重点)
select distincta.s_id,a.s_namefrom student asaLEFT JOIN score asdon a.s_id =d.s_idleft join course asbon d.c_id =b.c_idleft join teacher ascon b.t_id =c.t_idWHERE c.t_name = '张三'
SELECT DISTINCTs_id,s_nameFROMstudentWHERE s_id IN(SELECTs_idFROM score asdLEFT JOIN course AS b ON d.c_id =b.c_idLEFT JOIN teacher AS c ON b.t_id =c.t_idWHERE c.t_name = '张三')
SELECTs_id,s_namefromstudentwhere s_id in(SELECTs_idfromscorewhere c_id in(SELECTc_idfromcoursewhere t_id in (SELECTt_idfrom teacher where t_name = '张三')))
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号、姓名(重点)
SELECT DISTINCTs_id,s_nameFROMstudentWHERE s_id IN(SELECT
DISTINCTt1.s_idFROM(SELECTs_id,scoreFROMscoreWHERE c_id = '01') ast1JOIN(SELECTs_id,scoreFROMscoreWHERE
c_id = '02') ast2ON t1.s_id =t2.s_id
)
解题思路：在这⾥⾸先是利⽤score表字查询的⾃联结将原来表中的列变成了⾏
8、查询课程编号为“02”的总成绩(不重点)
SELECT sum(score)fromscorewhere c_id = '02'
9、查询所有课程成绩⼩于60分的学⽣的学号、姓名(同第2题，要注意存在成绩为null的情况，因此得采⽤not in)
思路解读：因为这样⼦查询中的结果不会包含null的id，整体-不符合要求 = 符合要求的+null
selects_id,s_namefromstudentwhere s_id not in (select s_id from score GROUP BY s_id having max(score)>= 60);
错误写法：从score中直接选id的话，这种未包含全部学⽣的id，成绩为null的情况selects_id,s_namefromstudentwhere s_id in (select s_id from score GROUP BY s_id having max(score) < 60);
10、查询没有学全所有课的学⽣的学号、姓名(重点)
selecta.s_id,a.s_namefrom student asaleft join score asdon a.s_id =d.s_idleft join course asbon b.c_id =d.c_idGROUP
BYa.s_id,a.s_namehaving count (a.s_id)
注意：此处不⽤distinct也可
错误的写法：从score中选会漏掉选课结果为null的王菊，但是因为course表没有跟student表直接管理，因此最好的办法是join
selects_id，s_namefromstudentwhere s_id in(SELECT s_id,count(s_id)fromscoreGROUP BYs_idhaving count(s_id) < SELECT count(*) from course))
11、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名(重点)
inner join的效率⽐in⾼⼀些
⼀种复杂的写法：
SELECTt1.s_id,t1.s_namefrom student ast1right join(SELECT DISTINCT s_id fromscorewhere c_id in (SELECT c_id from score where s_id = '01' )and s_id != '01') ast2on t1.s_id = t2.s_id
注意；通过⼀对多⽅式来选择时会出现很多重复数据，这⾥指，⼀个s_id 对应多个c_id，⼀对⼀联结是⽐较好的，根据c_id来选s_id的时候，必然会发⽣重复，去重⽤distinct
SELECTs_id,s_namefromstudentwhere s_id in(SELECT DISTINCT s_id fromscorewhere c_id in (SELECT c_id from score where s_id = '01') and s_id != '01')
12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
复杂写法
selects_idfromscorewhere c_id = (SELECTc_idfromscorewhere s_id = '01') and s_id != '01'
GROUP BYs_idhaving COUNT(s_id) = (SELECT COUNT(*) from score where s_id = '01')
简洁写法：
SELECTs_idfromscoregroup bys_idhaving (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
SELECTs_idfromscorewhere s_id not in(selects_idfromscorewhere c_id not in(SELECTc_idfromscorewhere s_id = '01') )and s_id != '01'GROUP BYs_idhaving COUNT(s_id) = (SELECT COUNT(*) from score where s_id = '01')
13.查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名(重点)
SELECT DISTINCTs_id,s_namefromstudentwhere 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 asaINNER JOIN (SELECT s_id,AVG(score)as avg_score from score GROUP BY s_id having COUNT(*) >=2 and max(score)< 60) aston 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
SELECTs_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)fromscoreGROUP BYs_idORDER BY AVG(score) desc
错误⽰范：这种形式不是最优解
SELECT a.s_id,d.score,AVG(d.score)FROM student asaLEFT JOIN score asdon a.s_id =d.s_idGROUP BYa.s_id,d.scoreORDER 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) asmiddle,AVG(case when score >= 80 and score < 90 then 1 else 0 end)
asgood,AVG(case when score >= 90 then 1 else 0 end) asgreatfromscoreGROUP BY c_id;
19、按各科成绩进⾏排序，并显⽰排名(重点dens_rank()over(order by 列)
SELECTs_id,c_id,score,
DENSE_RANK()OVER(PARTITION BY c_id ORDER BY score desc) ASrankingfrom score
窗⼝函数的⽤法看这⾥：看这⾥
20、查询学⽣的总成绩并进⾏排名(不重点)
SELECT s_id,sum(score)fromscoregroup bys_idorder by sum(score) desc
21、查询不同⽼师所教不同课程平均分从⾼到低显⽰(不重点)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论