summaryrefslogtreecommitdiff
path: root/ws2015/dbs/blaetter/08/abgabe.md
blob: c5c5913b2aa1e30d441a2e83c570465b6a0ef4d9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
---
header-includes:
  - \lstset{frame=single, breaklines=true, postbreak=\raisebox{0ex}[0ex][0ex]{\ensuremath{\color{red}\hookrightarrow\space}}}
---

# Anfragen in SQL

a)

~~~ {.sql}
SELECT L.lnr, L.lname, COUNT(LTP.pnr) FROM L, LTP WHERE L.lnr = LTP.lnr GROUP BY LTP.pnr
~~~

b)

~~~ {.sql}
SELECT LTP.pnr, SUM(LTP.menge) FROM LTP GROUP BY LTP.pnr
~~~

c)

~~~ {.sql}
SELECT L.lnr, L.lname, COUNT(LTP.pnr) FROM L, LTP WHERE L.lnr = LTP.lnr, COUNT(LTP.pnr) > 2 GROUP BY LTP.pnr
~~~

d)

~~~ {.sql}
SELECT LTP.tnr as tnr, L.lnr, L.lname, L.lsitz, T.tname, LTP.menge * L.gewicht / LTPSS.aggr * 100 FROM L, T, LTP JOIN (SELECT LTPS.tnr as tnr, SUM(LTPS.menge) * TS.gewicht as aggr FROM LTP as LTPS, T as TS WHERE LTP.tnr = LTPS.tnr AND TS.tnr = LTPS.tnr GROUP BY LTPS.tnr) as LTPSS USING tnr WHERE L.lnr = LTP.lnr AND T.tnr = LTP.tnr GROUP BY LTP.tnr
~~~