HCkdrcVA

1drop table if exists lt;
2drop table if exists l;
3drop table if exists t;
4
5create table l (
6  lnr varchar(2) primary key,
7  lname varchar(6) not null,
8  rabatt decimal(2) not null,
9  stadt varchar(6) not null
10);
11
12create table t (
13  tnr varchar(2) primary key,
14  tname varchar(8) not null,
15  farbe varchar(6) not null,
16  preis decimal(4, 2) not null,
17  stadt varchar(6) not null
18);
19
20create table lt (
21  lnr varchar(2) references l,
22  tnr varchar(2) references t,
23  menge integer not null check(menge > 0),
24  primary key(lnr, tnr)
25);
26
27-- Insert values into table l.
28insert into l values('L1', 'Schmidt', 20, 'London');
29insert into l values('L2', 'Jonas', 10, 'Paris');
30insert into l values('L3', 'Berger', 30, 'Paris');
31insert into l values('L4', 'Klein', 20, 'London');
32insert into l values('L5', 'Adam', 30, 'Athen');
33
34-- Insert values into table t.
35
36insert into t values('T1', 'Mutter', 'rot', 12, 'London');
37insert into t values('T2', 'Bolzen', 'gelb', 17, 'Paris');
38insert into t values('T3', 'Schraube', 'blau', 17, 'Rom');
39insert into t values('T4', 'Schraube', 'rot', 14, 'London');
40insert into t values('T5', 'Welle', 'blau', 12, 'Paris');
41insert into t values('T6', 'Zahnrad', 'rot', 19, 'London');
42
43-- Insert values into table lt.
44
45insert into lt values('L1', 'T1', 300);
46insert into lt values('L1', 'T2', 200);
47insert into lt values('L1', 'T3', 400);
48insert into lt values('L1', 'T4', 200);
49insert into lt values('L1', 'T5', 100);
50insert into lt values('L1', 'T6', 100);
51insert into lt values('L2', 'T1', 300);
52insert into lt values('L2', 'T2', 400);
53insert into lt values('L3', 'T2', 200);
54insert into lt values('L4', 'T2', 200);
55insert into lt values('L4', 'T4', 300);
56insert into lt values('L4', 'T5', 400);