[SQL] intermediate SQL (3)

Time:2020-2-23

data set

This article adopts PostgreSQL’s SQL syntax. Focus we focus onselect...from...whereThis read operation analyzes query (analytical query).
Datasets can be used directly at https://hyper-db.de/interface. In addition, write operations are not allowed on this page:insert, update, deleteTransactional query like this. Of coursecreate tableanddrop tableAnd not allowed.

Schema:
[SQL] intermediate SQL (3)

[SQL] intermediate SQL (3)

Download:
https://db.in.tum.de/teaching…

Schma and most SQL statements come from pro. Alfons Kemper, Ph.D.’s courseware and books.

Courseware:

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

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

Intermediate SQL

[SQL] intermediate SQL (3)

Suppose our schema becomes the figure above (SQL can’t run the dataset directly, which doesn’t correspond to the figure above). But we can join the temporaryviewGet into:

with professorenF as (
    select *,
           (case when p.persnr in (2125, 2126, 2133, 2137) then 'Philosophie'
                 when p.persnr in (2127, 2136) then 'Physik'
               else 'Theologie'
           end) as fakname
    from professoren p
)

select *
from professorenF
with studentenGF as (
    select *,
           (case when s.matrnr in (24002, 26830, 27550, 29120) then 'M'
                 else 'W'
           end) as geschlecht,
           (case when s.matrnr in (24002, 26120, 26830, 27550) then 'Philosophie'
                 when s.matrnr in (28106, 29120) then 'Physik'
               else 'Theologie'
           end) as fakname
    from studenten s
)

select *
from studentenGF

We need to add the above two to each query belowview, i.e. the following modes:

with professorenF as (
    select *,
           (case when p.persnr in (2125, 2126, 2133, 2137) then 'Philosophie'
                 when p.persnr in (2127, 2136) then 'Physik'
               else 'Theologie'
           end) as fakname
    from professoren p
), studentenGF as (
    select *,
           (case when s.matrnr in (24002, 26830, 27550, 29120) then 'M'
                 else 'W'
           end) as geschlecht,
           (case when s.matrnr in (24002, 26120, 26830, 27550) then 'Philosophie'
                 when s.matrnr in (28106, 29120) then 'Physik'
               else 'Theologie'
           end) as fakname
    from studenten s
)

--Our query
select ...
from ...
where ...
  • Find the proportion of women corresponding to each fakname:
with anz(Fakname,AnzStudenten) as (
    select s.FakName, count(*)
    from StudentenGF s
    group by s.FakNAme),
     anzw(Fakname,AnzWeiblich) as (
    select sw.FakName,count(*) as AnzWeiblich
    from StudentenGF sw
    where sw.Geschlecht ='W'
    group by sw.FakName)

select anz.FakName, anz.AnzStudenten, anzw.AnzWeiblich, (cast(anzw.AnzWeiblich as decimal(5,2))/anz.AnzStudenten * 100) as ProzentWeiblich
from anz, anzw
where anz.FakName = anzw.FakName
  • Find the proportion of men corresponding to each fakname:
with anz(Fakname, AnzStudenten) as (
    select s.FakName, count(*)
    from StudentenGF s
    group by s.FakNAme),
     anzm(Fakname, AnzMaenner) as (
         select sw.FakName, count(*) as AnzWeiblich
         from StudentenGF sw
         where sw.Geschlecht = 'M'
         group by sw.FakName)

select anz.FakName,
       anz.AnzStudenten,
       anzm.AnzMaenner,
       (case when anzm.AnzMaenner is null then 0 else anzm.AnzMaenner end) / anz.AnzStudenten * 100.00 as ProzentMaenner
from anz left outer join anzm
on anz.FakName = anzm.FakName

It’s not the women’s version that changes directly to men. One key point is:There is no male in the Department of existence
caseIt can also be replaced with:COALESCE(anzm.AnzMaenner, 0) / anz.AnzStudenten * 100.00 as ProzentMaenner

Or another way:

select fakname,
       (sum(case when geschlecht = 'M' then 1.00 else 0.00 end)) / count(*)
from studentenGF
group by fakname
  • Search all the students have listened to all the courses offered by their professors:
select s.*
from studentenGF s
where not exists(
    select *
    from vorlesungen v, professorenF p
    where v.gelesenvon = p.persnr and p.fakname = s.fakname and not exists(
        select *
        from hoeren h
        where h.vorlnr = v.vorlnr and h.matrnr = s.matrnr
        )
    )

For this student, there is no class taught in his department, and this student has never heard of it.

perhaps

select s.*
from studentenGF s
where (
    select count(*)
    from vorlesungen v, professorenF p
    where v.gelesenvon = p.persnr and p.fakname = s.fakname
          )
=
      (
    select count(*)
    from hoeren h, vorlesungen v, professorenF p
    where h.matrnr = s.matrnr and h.vorlnr = v.vorlnr and p.persnr = v.gelesenvon and p.fakname= s.fakname
          )