# [SQL]中级SQL(2)

## 数据集

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

## 中级SQL

1. 比较听课并考试的同学的成绩和不听课只考试的同学成绩：
with no_lec as (
select avg(note) as avg_note
from pruefen p
where not exists (
select *
from hoeren h
where h.matrnr = p.matrnr
)),
with_lec as (
select avg(note) as avg_note
from pruefen p
where exists (
select *
from hoeren h
where h.matrnr = p.matrnr
))

select *
from no_lec, with_lec;
1. 计算权重的成绩(sws就是我们的权重)：

with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403 , 5049 , 2126 , 1),
(26120 , 5001 , 2137 , 1),
(26120 , 5043 , 2126 , 3),
(26120 , 5052 , 2126 , 4),
(26120 , 4630 , 2137 , 1)
)
with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403, 5049, 2126, 1),
(26120, 5001, 2137, 1),
(26120, 5043, 2126, 3),
(26120, 5052, 2126, 4),
(26120, 4630, 2137, 1)
select s.name, s.matrnr, v.titel, p.note, v.sws as factor, (p.note * v.sws) as finalGrade
from studenten s, pruefenxl p, vorlesungen v
where p.vorlnr = v.vorlnr and s.matrnr = p.matrnr
)

select matrnr, name, sum(finalGrade) / sum(factor) as finalAVG
group by matrnr, name
order by matrnr

with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403 , 5049 , 2126 , 1),
(26120 , 5001 , 2137 , 1),
(26120 , 5043 , 2126 , 3),
(26120 , 5052 , 2126 , 4),
(26120 , 4630 , 2137 , 1)
)

select s.matrnr, s.name, sum(p.note * v.sws) / sum(v.sws) as avg
from studenten s, pruefenxl p, vorlesungen v
where s.matrnr = p.matrnr and p.vorlnr = v.vorlnr
group by s.matrnr , s.name
order by s.matrnr

1. 搜索学生通过上课能认识的其他学生名字：
select s1.name, s2.name
from studenten s1, hoeren h1, hoeren h2, studenten s2
where h1.vorlnr = h2.vorlnr and h1.matrnr = s1.matrnr  and h2.matrnr = s2.matrnr and s1.matrnr != s2.matrnr
1. 对每一个同学认识的人进行计数：
with bekannte as (
select s1.matrnr as student, s2.matrnr as sein_bekannte
from studenten s1,
hoeren h1,
hoeren h2,
studenten s2
where h1.vorlnr = h2.vorlnr
and h1.matrnr = s1.matrnr
and h2.matrnr = s2.matrnr
and s1.matrnr != s2.matrnr
)

select s.matrnr, s.name, count(b.sein_bekannte) as num_friends
from studenten s, bekannte b
where s.matrnr = b.student
group by s.matrnr, s.name
order by num_friends desc
1. 在2.的基础上再考虑：不上课(也就不认识同学)的人
with bekannte as (
select s1.matrnr as student, s2.matrnr as sein_bekannte
from studenten s1,
hoeren h1,
hoeren h2,
studenten s2
where h1.vorlnr = h2.vorlnr
and h1.matrnr = s1.matrnr
and h2.matrnr = s2.matrnr
and s1.matrnr != s2.matrnr
)

select s.matrnr, s.name, count(b.sein_bekannte) as num_friends
from studenten s left outer join bekannte b
on s.matrnr = b.student
group by s.matrnr, s.name
order by num_friends desc

1. 求每个学生的选课数量的平均数，需要考虑不上课的学生：
select count(h.vorlnr), count(distinct s.matrnr) -- 听课和不听课的学生都在
from studenten s left outer join hoeren h on s.matrnr = h.matrnr
select hcount / (scount * 1.00)
from (select count(*) as hcount from hoeren) h,
(select count(*) as scount from studenten) s -- 听课和不听课的学生都在
1. 搜索选课超过学生选课sws平均数的学生，需要考虑不上课的学生：
with num_stu as (
select count(*) as count_stu
from studenten),
num_sws as (
select sum(vor.sws) as count_sws
from hoeren h, vorlesungen vor
where h.vorlnr = vor.vorlnr)

select s.*
from studenten s
where s.matrnr in (
select h.matrnr
from hoeren h, vorlesungen v
where h.vorlnr = v.vorlnr
group by h.matrnr
having sum(sws) > (select cast(num_sws.count_sws as decimal (5, 2)) / num_stu.count_stu from num_sws, num_stu)
)

with num_stu as (
select count(*) as count_stu
from studenten),
num_sws as (
select sum(vor.sws) as count_sws
from hoeren h, vorlesungen vor
where h.vorlnr = vor.vorlnr),
avg_sws as (
select cast(num_sws.count_sws as decimal(5, 2)) / num_stu.count_stu as sws
from num_stu, num_sws),
stu_sws as (
select s.matrnr, s.name, s.semester, sum(v.sws) as sum_sws
from studenten s, hoeren h, vorlesungen v
where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
group by s.matrnr, s.name, s.semester)

select s.*
from stu_sws s, avg_sws
where s.sum_sws > avg_sws.sws

with swsProStudent as (
select s.matrnr, s.name,
cast((case when sum(v.sws) is null then 0
else sum(v.sws) end) as real) as anzSWS
from studenten s
left outer join hoeren h on s.matrnr = h.matrnr
left outer join vorlesungen v on h.vorlnr = v.vorlnr
group by s.matrnr, s.name
)

select s.*
from studenten s
where s.matrnr in (
select sws.matrnr
from swsProStudent sws
where sws.anzSWS > (
select avg(anzSWS)
from swsProStudent
)
)

1. 确定一门的semester属性：这个根据听这门课中人数最多的一个学生semester组决定。如果有很多组人数相同，取这些组们中semester最小的数值。比如一门课被100第一学期的同学和100个第三学期的同学上，我们当这门课是属于第一学期的课(那100第三学期的被我们当做前一年挂科重修的人)。
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
)

select v.vorlnr, min(v.semester) as semester
from vorl_semester_anz v
where v.num = (
select max(vhelp.num)
from vorl_semester_anz vhelp
where v.vorlnr = vhelp.vorlnr
)
group by v.vorlnr

with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester_maxanz as (
select v.vorlnr, max(num) as max -- 每一门课最多的人数是一个确定的数字，用这个数字可以找回semester
from vorl_semester_anz v
group by v.vorlnr
)

select v1.vorlnr, min(v1.semester) as semester
from vorl_semester_anz v1, vorl_semester_maxanz v2
where v1.vorlnr = v2.vorlnr and v1.num = v2.max
group by v1.vorlnr
1. 搜索提前上课的同学(同学的semester比课程的semester要小)：
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester as (
select v.vorlnr, min(v.semester) as semester
from vorl_semester_anz v
where v.num = (
select max(vhelp.num)
from vorl_semester_anz vhelp
where v.vorlnr = vhelp.vorlnr
)
group by v.vorlnr)

select v.vorlnr, v.titel, count(s.matrnr) as num_advanced_stu
from vorlesungen v left outer join vorl_semester vs on v.vorlnr = vs.vorlnr
left outer join hoeren h on v.vorlnr = h.vorlnr
left outer join studenten s on h.matrnr = s.matrnr and s.semester < vs.semester
group by v.vorlnr, v.titel

with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester_maxanz as (
select v.vorlnr, max(num) as max -- 每一门课最多的人数是一个确定的数字，用这个数字可以找回semester
from vorl_semester_anz v
group by v.vorlnr
), vorl_semester as (
select v1.vorlnr, min(v1.semester) as semester
from vorl_semester_anz v1, vorl_semester_maxanz v2
where v1.vorlnr = v2.vorlnr and v1.num = v2.max
group by v1.vorlnr
)

select v.vorlnr, v.titel, count(s.matrnr) as num_advanced_stu
from vorlesungen v left outer join vorl_semester vs on v.vorlnr = vs.vorlnr
left outer join hoeren h on v.vorlnr = h.vorlnr
left outer join studenten s on h.matrnr = s.matrnr and s.semester < vs.semester
group by v.vorlnr, v.titel