6iRrsQV7

1if not exists (select name from master.sys.databases where name = N'TEC')
2create database TEC
3
4go
5use TEC
6
7
8if not exists (selecT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'PostNrBy')
9begin
10	
11create table PostNrBy (
12	PostNr int primary key identity, 
13	ByNavn varchar(300),
14)
15set identity_insert PostNrBy on;
16insert into PostNrBy(PostNr, ByNavn)
17values 
18	(2650, 'Hvidovre'),
19	(2300, 'København S'),
20	(2500, 'Valby'),
21	(2610, 'Rødovre'),
22	(3650, 'Ølstykke'),
23	(2830, 'Virum'),
24	(2770, 'Kastrup'),
25	(1824, 'Frederiksberg C'),
26	(2740, 'Skovlunde'),
27	(2750, 'Ballerup');
28
29SELECT * FROM PostNrBy
30set identity_insert PostNrBy off;
31
32end
33
34if not exists (selecT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Klasse')
35begin
36	
37create table Klasse (
38	KlasseID int primary key identity (1,1),
39	KlasseNavn varchar(300), 
40)
41set identity_insert Klasse on;
42insert into Klasse(KlasseID, KlasseNavn)
43values 
44	(1, 'A210'),
45	(2, 'E224'),
46	(3, 'D307');
47
48SELECT * FROM Klasse
49set identity_insert Klasse off;
50
51end
52
53if not exists (selecT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Elev')
54begin
55	
56create table Elev (
57	ElevID int primary key identity (1,1),
58	Fornavn varchar(300), 
59	Efternavn varchar(300),
60	Adresse varchar(300),
61	PostNR int,
62	foreign key (PostNR) references PostNrBy(PostNR)
63)
64set identity_insert Elev on;
65
66insert into Elev (ElevID, Fornavn, Efternavn, Adresse, PostNR)
67values 
68	(1, 'Bo', 'Andersen', 'Gammel byvej 12', 2650),
69	(2, 'Frederikke', 'Hansen', 'Amager Boulevard 5', 2300),
70	(3, 'Jens', 'Mikkelsen', 'Lily Brobergs vej 17', 2500),
71	(4, 'Phillip', 'Mortensen', 'Brunevang 90', 2610),
72	(5, 'Kasper', 'Frederiksen', 'Bryggertorvet 32', 3650),
73	(6, 'Milla', 'Jørgensen', 'Virum torv 25', 2830),
74	(7, 'Fie', 'Knudsen', 'Allen 85', 2770),
75	(8, 'Henrik', 'Madsen', 'Lily Brobergs vej 53', 2500);
76
77SELECT * FROM Elev
78set identity_insert Elev off;
79
80end
81
82if not exists (selecT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Laerer')
83begin
84	
85create table Laerer (
86	LaererID int primary key identity (1,1),
87	Fornavn varchar(300), 
88	Efternavn varchar(300),
89	Adresse varchar(300),
90	PostNR int,
91	foreign key (PostNR) references PostNrBy(PostNR),
92	foreign key (LaererID) references Klasse(KlasseID),
93)
94set identity_insert Laerer on;
95insert into Laerer (LaererID, Fornavn, Efternavn, Adresse, PostNR)
96values 
97	(1, 'Tom', 'It', 'Sankt Thomas Alle 3', 1824),
98	(2, 'Lars', 'Hansen', 'Nissedalen 76', 2740),
99	(3, 'Mia', 'Hansen', 'Østervej 16', 2750);
100
101SELECT * FROM Laerer
102set identity_insert Laerer off;
103
104end
105
106else
107	select * from Elev
108	select * from PostNrBy
109	select * from Klasse