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 sco