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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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;
~~~
|
