dbs - 07

1 files changed, 44 insertions, 0 deletions

diff --git a/ws2015/dbs/blaetter/07/abgabe.md b/ws2015/dbs/blaetter/07/abgabe.md
new file mode 100644
index 0000000..1b5297a
--- /dev/null
+++ b/ws2015/dbs/blaetter/07/abgabe.md
@@ -0,0 +1,44 @@
+---
+header-includes:
+  - \lstset{frame=single, breaklines=true, postbreak=\raisebox{0ex}[0ex][0ex]{\ensuremath{\color{red}\hookrightarrow\space}}}
+---
+
+# Anfragen in SQL
+
+a)
+
+~~~ {.sql}
+SELECT pname FROM P WHERE ort IN (SELECT sitz FROM L WHERE lname = "Müller" OR lname = "Schmidt" )
+~~~
+
+b)
+
+~~~ {.sql}
+SELECT pname FROM P WHERE pnr IN (SELECT LTP.pnr FROM LTP, T WHERE T.tnr = LTP.tnr AND T.gewicht > 10)
+~~~
+
+c)
+
+~~~ {.sql}
+SELECT lname, status, sitz FROM L ORDER BY status, sitz DESC
+~~~
+
+d)
+
+~~~ {.sql}
+SELECT tname, farbe, gewicht FROM T WHERE gewicht >= (SELECT MAX(gewicht) FROM T)
+~~~
+
+e)
+
+~~~ {.sql}
+SELECT P.pname FROM P, LTP LEFT JOIN ( SELECT lnr, COUNT(*) ls FROM L WHERE status <= 30 ) L ON LTP.lnr = L.lnr WHERE LTP.pnr = P.pnr AND L.ls <= 2
+~~~
+
+# Anfragen in SQL
+
+a)
+
+~~~ {.sql}
+SELECT L.lname FROM L WHERE (SELECT COUNT(*) FROM LTP, P WHERE p.ort = "Berlin" AND LTP.lnr = L.lnr GROUP BY P.pnr) >= (SELECT COUNT(*) FROM P WHERE P.ort = "Berlin")
+~~~