Sn4U4SMt

1.mode column
2.head on
3
4pragma foreign_keys = on;
5
6drop table if exists l;
7drop table if exists t;
8drop table if exists lt;
9
10create table l (
11    lnr		varchar(2) not null primary key
12,   lname	varchar(6) not null
13,	rabatt	decimal(2) not null
14,	stadt	varchar(6) not null	
15);
16
17create table t(
18	tnr		varchar(2) not null primary key
19,	tname	varchar(8) not null
20,	farbe	varchar(6) not null
21,	preis	decimal(3) not null
22,	stadt	varchar(6) not null
23);
24
25create table lt(
26	lnr		varchar(2) not null references l
27,	tnr		varchar(2) not null	references t
28, 	menge	decimal(3) not null check(menge > 0)
29, 	primary key(lnr, tnr)
30);
31
32insert into l 
33	(lnr,	lname,	rabatt,	stadt)
34values 
35	('L1', 'Schmid',	20,	'London')
36,	('L2', 	'Jonas',	10,	'Paris'	)
37,	('L3', 'Berger',	20,	'Paris'	)
38,	('L4', 	'Klein',	20,	'London')
39,	('L5',	 'Adam',	20,	'Athen'	)
40;
41
42insert into t
43	(tnr,	tname,		  farbe,	preis,	stadt)
44values
45	('T1',	  'Mutter',	 'rot',	   12,	'London')
46,	('T2',	  'Bolzen',	'gelb',	   17,	'Paris'	)
47, 	('T3',	'Schraube',	'blau',	   17,	'Rom'	)
48, 	('T4',	'Schraube',	 'rot',	   14,	'London')
49, 	('T5',     'Welle',	'blau',	   12,	'Paris'	)
50,	('T6',	 'Zahnrad',	 'rot',	   19,	'London')
51;
52
53insert into lt
54	(lnr,	tnr,	menge)
55values
56	('L1',	'T1',	  300)
57,	('L1',	'T2',	  200)
58,	('L1',	'T3',	  400)
59,	('L1',	'T4',	  200)
60,	('L1',	'T5',	  100)
61,	('L1',	'T6',	  100)
62,	('L2',	'T1',	  300)
63,	('L2',	'T2',	  400)
64,	('L3',	'T2',	  200)
65,	('L4',	'T2',	  200)
66,	('L4',	'T4',	  300)
67,	('L4',	'T5',	  400)
68;
69
70select *
71	from l
72;
73
74.print
75
76select *
77	from t
78;
79
80.print
81
82select * 
83	from lt
84;
85
86.print
87
88select t.tnr, tname,
89	   preis * menge "umsatz"
90	from lt
91	join t on lt.tnr = t.tnr
92   where lnr = 'L4'
93	 and preis between 14 and 17
94	 and stadt like 'p%'
95;
96
97.print