[SQL]中级SQL(2)

数据集

我们这一篇文章采用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

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)
), factorGrade as (
    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
from factorGrade
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

这里用了一个left outer join。右边的表格bekannte b只含有上课的同学(即出现在hoeren表格中的同学)，但是左边的表格studenten s含有所有学生。

---

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

这个结构其实很难用join完成，但是也是可行的。另外一种间接的方式：

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

这里必须要提到一下:

• count(s.matrnr) as num_advanced_stu不能写成count(*)
• 原因是：left outer join会生成很多null，因为很多课没有人听，因此这些课也没有semester。

但是count(*)会将这些课计数，而count(s.matrnr)计的数字是真正上这个课学生人数，而不是null。