---

---

项目地址

• 教程作者：
• 教程地址：

https://www.bilibili.com/video/BV1q4411G7Lw/?spm_id_from=333.788.videopod.episodes&vd_source=791e6deaa9c8a56b1f845a0bc1431b71&p=4

• 代码仓库地址：

• 所用到的框架和插件：

dbt 
airflow

1. .查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

• 方法一：子查询

select a.SId from (select * from Score where CId = '01') as a 
inner join (select * from Score where CId = '02') as b
on a.SId = b.SId
where a.score>b.score

• 方法二：CTE

with score01 as
(select * from Score where CId = '01')
, score02 as
(select * from Score where CId = '02')
select score01.SId,score01.CId from score01 inner join score02
on score01.SId = score02.SId
where score01.score> score02.score

2. 查询没学过“张三”老师课的学生和学号

with zs as
(select * from Score sc
left join Teacher t
on sc.c_id = t.t_id
where t_name = '张三'
)
select s.s_id,s.s_name from Student s
left join zs
on s.s_id = zs.s_id
where zs.t_name is null

• 结果

s_id	s_name
07	郑竹
08	王菊

3. 查询同时满足学过01和02课程的学生的学号和姓名（子查询）

• 交集

select a.s_id from
(select