From d414ce3baea8202cd832773fbfd48725cef33dd8 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Wed, 9 Dec 2015 12:41:45 +0100 Subject: DBS - 08 --- ws2015/dbs/blaetter/08/abgabe.md | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100644 ws2015/dbs/blaetter/08/abgabe.md (limited to 'ws2015') diff --git a/ws2015/dbs/blaetter/08/abgabe.md b/ws2015/dbs/blaetter/08/abgabe.md new file mode 100644 index 0000000..634a07e --- /dev/null +++ b/ws2015/dbs/blaetter/08/abgabe.md @@ -0,0 +1,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 L.lnr, L.lname, L.lsitz, T.tname, LTP.menge * L.gewicht / LTPSS.aggr * 100 FROM L, T, LTP JOIN (SELECT 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 LTP.tnr WHERE L.lnr = LTP.lnr AND T.tnr = LTP.tnr GROUP BY LTP.tnr +~~~ -- cgit v1.2.3