[SQL]中级SQL(1)

正文

这里我们会遇到subquery，它可以出现在select子句中或者where子句或者from子句中。它会产生一个对应的结果表格，我们可以给这个表示命名。

数据集

我们这一篇文章采用PostgreSQL的SQL语法。重点我们关注select...from...where这种读操作，分析query　(analytical query)。
数据集在　https://hyper-db.de/interface...　可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。

架构 Schema:

下载:
https://db.in.tum.de/teaching...

Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。

课件：

• https://db.in.tum.de/teaching...
• https://db.in.tum.de/teaching...

书：　https://db.in.tum.de/teaching...

中级SQL

• 在pruefen中搜索note小于平局值的：

select *
from pruefen
where note < (
    select avg(note)
    from pruefen
    )

• 对每一个professoren，对应的vorlesungen的sws求和：

-- correlated sub-query
select p.persnr, p.name, (
    select sum(v.sws) as lehrbelastung
    from vorlesungen v
    where v.gelesenvon = p.persnr
    )
from professoren p

-- no sub-query
select p.persnr, p.name, sum(sws)
from professoren p left outer join vorlesungen v on p.persnr = v.gelesenvon
group by p.name, p.persnr

• 搜索上课数大于２的学生：

select tmp.matrnr, tmp.name, tmp.vorlanzahl
from (select s.matrnr, s.name, count(*) as vorlanzahl
    from studenten s, hoeren h
    where s.matrnr = h.matrnr
    group by s.matrnr, s.name) tmp
where tmp.vorlanzahl > 2

这时候我们对这个subquery的结果表格进行命名tmp。当然我们可以用with子句来做同样的事情。我主观上更喜欢用with,它很清晰地把暂时需要的表格写在最上方，而且对debug也更加友好。当然两者是结果等价，运行时间也等价的。

with tmp as (select s.matrnr, s.name, count(*) as vorlanzahl
    from studenten s, hoeren h
    where s.matrnr = h.matrnr
    group by s.matrnr, s.name) 

select tmp.matrnr, tmp.name, tmp.vorlanzahl
from tmp
where tmp.vorlanzahl > 2

• 计算每一个vorlesungen的人数占比：

select h.vorlnr, h.anzProVorl, g.gesamtAnz, cast(h.anzProVorl as decimal(6, 1)) / g.gesamtAnz as MarkAnteil
from (select vorlnr, count(*) as anzProVorl
    from hoeren
    group by vorlnr) as h,
     (select count(*) as gesamtAnz
    from studenten) g

-- with子句版本
with h as (select vorlnr, count(*) as anzProVorl
    from hoeren
    group by vorlnr),
     g as (select count(*) as gesamtAnz
    from studenten)

select h.vorlnr, h.anzProVorl, g.gesamtAnz, cast(h.anzProVorl as decimal(6, 1)) / g.gesamtAnz as MarkAnteil
from h, g

• 计算每一个professoren通过上课认识的studenten个数以及比例：

with kenntSich as (
    select distinct v.gelesenvon as profpersnr, h.matrnr as studmatrnr
    from hoeren h join vorlesungen v on h.vorlnr =v.vorlnr
    ),
     kenntAnzahl as (
    select profpersnr, count(*) as anzstudenten
    from kenntSich
    group by profpersnr),
     wieviel as (
    select count(*) as gesamtanz
    from studenten)

select k.profpersnr, p.name, k.anzstudenten, w.gesamtanz, 1.00 * k.anzstudenten / w.gesamtanz as bekanntheitsgard
from kenntAnzahl k, wieviel w, professoren p
where k.profpersnr = p.persnr
order by bekanntheitsgard desc

---

• 搜索听了所有sws=4 vorlesungen的学生：

SELECT s.*
FROM studenten s
where not exists(
    select *
    from vorlesungen v
    where v.sws = 4 and not exists(
        select *
        from hoeren h
        where h.vorlnr = v.vorlnr and h.matrnr = s.matrnr
        )
    )

SQL92中没有定义for all Quantifier(全称量词)。所以我们只能改写关系代数：

$$ \{s|s\in studenten \wedge \forall v \in vorlesungen (v.sws = 4 \Rightarrow \\ \exists h \in hoeren (h.vorlnr = v.vorlnr \wedge h.matrnr = s.matrnr)) \} $$

我们先把$\forall t \in R (P(t))$改写成$\neg (\exists t \in R(\neg P(t)))$:

$$ \{s|s\in studenten \wedge \neg (\exists v \in vorlesungen \; \neg (v.sws = 4 \Rightarrow \\ \exists h \in hoeren (h.vorlnr = v.vorlnr \wedge h.matrnr = s.matrnr))) \} $$

再把$R \Rightarrow T$改写成$\neg R \vee T$:

$$ \{s|s\in studenten \wedge \neg (\exists v \in vorlesungen \; \neg (\neg (v.sws = 4) \vee \\ \exists h \in hoeren (h.vorlnr = v.vorlnr \wedge h.matrnr = s.matrnr))) \} $$

再用DeMorgan律简化一下：

$$ \{s|s\in studenten \wedge \neg (\exists v \in vorlesungen (v.sws = 4) \wedge \\ \neg (\exists h \in hoeren (h.vorlnr = v.vorlnr \wedge h.matrnr = s.matrnr))) \} $$

用中文说：不存在一门sws=4的课，没有被这个学生听。这样我们可以对应关系代数到上面的SQL。

另外一种trick解法，使用count:

-- 先把hoeren变成sws=4hoeren: hoerenStudentenWith4SWS
with hoerenStudentenWith4SWS (matrnr, vorlnr) as (
    select h.matrnr, v.vorlnr
    from hoeren h, vorlesungen v
    where h.vorlnr = v.vorlnr and v.sws = 4
    )

-- 再看学生是不是听完了所有hoerenStudentenWith4SWS
select h.matrnr
from hoerenStudentenWith4SWS h
group by h.matrnr
having count(*) = (select count(*) from vorlesungen v where v.sws = 4)

• (对上面的类似练习) 搜索学生所有考过的试对应的科目，都是这个同学所听过：

select s.*
from studenten s
where not exists(
    select *
    from pruefen p
    where p.matrnr = s.matrnr and not exists(
        select *
        from hoeren h
        where h.vorlnr = p.vorlnr and h.matrnr = s.matrnr
        )
    )

用中文说：没有一门被考过的科目，没有出现在对应学生hoeren表格中。

另外因为这个要求是独立得应用在每一个学生上，每一个学生因为考试不同，所有要求听的科目也不同。因此上面那题的trick不再适用。trick应用条件是对所有学生需要普遍性，而排除独立性 -- 一视同仁。

• (对上面的类似练习) 搜索学生所有听过的科目，都考试并通过(note<=4)：

select * 
from Studenten s
where not exists (
    select *
    from hoeren h
    where h.MatrNr = s.MatrNr and not exists (
        select * 
        from pruefen p
        where p.MatrNr = s.MatrNr and p.VorlNr = h.VorlNr and p.Note <= 4
        )
    )

用中文说：没有一门上过课的科目，没有出现在对应学生pruefen表格中并没有通过。

这个依旧很难用trick。

---

• 求至少听Sokrates一门课的学生们的平均学期数：

with vl_von_sokrates as (
    select *
    from vorlesungen v, professoren p
    where v.gelesenvon = p.persnr and p.name = 'Sokrates'
), studenten_von_sokrates as (
    select distinct s.name, s.matrnr, s.semester
    from studenten s, hoeren h, vl_von_sokrates v
    where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
)

select avg(semester)
from studenten_von_sokrates;

这题一定要注意,可能一个学生听了Sokrates的很多课，但是这种同学不能被重复计数。我们可以用distinct。

但是我们也有一种解法不需要distinct，它不用join,而是带exists的correlated subquery:

with vl_von_sokrates as (
    select *
    from vorlesungen v, professoren p
    where v.gelesenvon = p.persnr and p.name = 'Sokrates'
), studenten_von_sokrates as (
    select *
    from studenten s
    where exists(
        select *
        from hoeren h, vl_von_sokrates vl
        where h.matrnr = s.matrnr and h.vorlnr = vl.vorlnr
    )
)

select avg(semester)
from studenten_von_sokrates;

• 求每个学生听几节课，需要考虑不听任何课的学生：

    select count(*) as hcount
    from hoeren
    ),
     s as (
    select count(*) as scount
    from studenten
)

select hcount / (scount * 1.00) as avg_vl
from h, s

或者

with h as (
    select count(*) as hcount
    from hoeren
    ),
     s as (
    select count(*) as scount
    from studenten
)

select hcount / (cast(scount as decimal(10, 4))) as avg_vl
from h, s