# [SQL]中级SQL(1)

## 正文

### 数据集

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

### 中级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

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)) \}$$

$$\{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))) \}$$

$$\{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))) \}$$

$$\{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))) \}$$

-- 先把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
)
)

• (对上面的类似练习) 搜索学生所有听过的科目，都考试并通过(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
)
)

• 求至少听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;

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

1 声望
1 粉丝
0 条评论