From aacd6b2bf7317d858a355e6bf0dfcf385c708a0e Mon Sep 17 00:00:00 2001
From: Gregor Kleen <gkleen@yggdrasil.li>
Date: Fri, 30 Oct 2015 17:01:23 +0100
Subject: Datenbanken - Blatt 03

---
 ws2015/datenbanksysteme/blaetter/03/abgabe.md | 54 +++++++++++++++++++++++++++
 1 file changed, 54 insertions(+)
 create mode 100644 ws2015/datenbanksysteme/blaetter/03/abgabe.md

(limited to 'ws2015')

diff --git a/ws2015/datenbanksysteme/blaetter/03/abgabe.md b/ws2015/datenbanksysteme/blaetter/03/abgabe.md
new file mode 100644
index 0000000..df6a9b2
--- /dev/null
+++ b/ws2015/datenbanksysteme/blaetter/03/abgabe.md
@@ -0,0 +1,54 @@
+# SQL-DDL
+
+a)
+    ~~~ {.sql}
+    CREATE TABLE L
+      ( lnr VARCHAR(10) PRIMARY KEY
+      , lname VARCHAR(40) NOT NULL
+      , sitz VARCHAR(40)
+      );
+
+    CREATE TABLE T
+      ( tnr VARCHAR(10) PRIMARY KEY
+      , tname VARCHAR(40) NOT NULL
+      , farbe VARCHAR(40)
+      , gewicht INTEGER
+      , preis VARCHAR(40)
+      );
+
+    CREATE TABLE P
+      ( pnr VARCHAR(10) PRIMARY KEY
+      , pname VARCHAR(40) NOT NULL
+      , ort VARCHAR(40)
+      );
+
+    CREATE TABLE LTP
+      ( lnr VARCHAR(10)
+      , tnr VARCHAR(10)
+      , pnr VARCHAR(10)
+      , menge INTEGER
+      , FOREIGN KEY (lnr) REFERENCES L(lnr)
+      , FOREIGN KEY (tnr) REFERENCES T(tnr)
+      , FOREIGN KEY (pnr) REFERENCES P(pnr)
+      , PRIMARY KEY (lnr, tnr, pnr)
+      );
+    ~~~
+b)
+    ~~~ {.sql}
+    ALTER TABLE L ADD status INTEGER;
+    ~~~
+c)
+    ~~~ {.sql}
+    ALTER TABLE T MODIFY preis float(2);
+    ~~~
+d)
+    ~~~ {.sql}
+    ALTER TABLE T DROP COLUMN preis;
+    ~~~
+e)
+    ~~~ {.sql}
+    DROP TABLE LTP;
+    DROP TABLE L;
+    DROP TABLE T;
+    DROP TABLE P;
+    ~~~
-- 
cgit v1.2.3