q6BqTJKd

1USE master
2GO
3
4IF NOT EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = N'Skole')
5BEGIN
6	PRINT 'Databasen :Skole: findes ikke! - Oprettes..'
7	CREATE DATABASE Skole
8END
9ELSE
10	PRINT 'Databasen :Skole: findes allerede!'
11
12GO
13USE Skole
14
15
16IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N'Elev')
17BEGIN
18CREATE TABLE Elev (
19ElevID int primary key identity NOT NULL,
20Fornavn nvarchar(30),
21Efternavn nvarchar(30),
22Adresse nvarchar(50),
23PostNr int,
24Klasse int
25)
26PRINT 'Opretter :Elev: tabellen..'
27END
28ELSE 
29PRINT 'Tabellen :Elev: findes allerede!'
30
31IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N'Laerer')
32BEGIN
33CREATE TABLE Laerer (
34LaererID int primary key identity NOT NULL,
35Fornavn nvarchar(30),
36Efternavn nvarchar(30),
37Adresse nvarchar(50),
38PostNr int
39)
40PRINT 'Opretter :Laerer: tabellen..'
41END
42ELSE 
43PRINT 'Tabellen :Laerer: findes allerede!'
44
45
46IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N'Klasse')
47BEGIN
48CREATE TABLE Klasse (
49KlasseID int primary key not null,
50KlasseNavn nvarchar(25),
51)
52PRINT 'Opretter :Klasse: tabellen..'
53END
54ELSE 
55PRINT 'Tabellen :Klasse: findes allerede!'
56
57
58IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N'PostNr')
59BEGIN
60CREATE TABLE PostNR (
61PostNr int primary key NOT NULL,
62Bynavn nvarchar(30),
63)
64PRINT 'Opretter :PostNr: tabellen..'
65END
66ELSE 
67PRINT 'Tabellen :PostNr: findes allerede!'
68
69
70IF NOT EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = N'LaererKlasse')
71BEGIN
72CREATE TABLE LaererKlasse (
73lkID int primary key identity not null,
74LaererID int,
75KlasseID int
76)
77PRINT 'Opretter :LaererKlasse: tabellen..'
78END
79ELSE 
80PRINT 'Tabellen :LaererKlasse: findes allerede!'
81
82
83--Insert into command
84--INSERT INTO Student VALUES 
85--('Test', 'Testensen')
86
87--Removes all data
88--TRUNCATE TABLE Student
89
90--Update command
91--update Student set Student_Adress = 'Testvej 12' WHERE Student_ID = 2
92
93--Delete command
94--delete from Student WHERE Student_ID = 2
95
96--Select From command
97--SELECT * FROM Teacher
98--SELECT * FROM Grade
99--SELECT * FROM PostalCode
100--SELECT * FROM TeacherGrade
101
102
103--Select TOP command
104--SELECT TOP (25) [Student_ID] as ElevID
105--      ,[Student_FirstName] as Fornavn
106--      ,[Student_LastName] as Efternavn
107--      ,[Student_Address] as Adresse
108--      ,[Student_ZIP] as Postnummer
109--      ,[Student_Grade] as Klasse 
110--  FROM [School].[dbo].[Student]
111
112  INSERT INTO Elev VALUES
113  ('Søren', 'Jensen', 'Ved Vejen 6', '4000', '235'),
114  ('Edvard', 'Olsen', 'GÃ¥sevejen 13', '2750', '235'),
115  ('Søren', 'Olsen', 'Borgvej 145', '2800', '235'),
116  ('Søren', 'Jensen', 'Vejvejen 543', '2000', '235'),
117  ('Eddy', 'Jørgensen', 'Gnustræde 50', '2750', '235'),
118  ('Arne', 'Poulsen', 'Abegade 15', '2000', '235')
119
120  INSERT INTO Laerer VALUES
121  ('Gert', 'Tanatar', 'Danmarksvej 3', 3630),
122  ('Berk', 'Taersen', 'Tyervej 6', 3600),
123  ('John', 'Johnsen', 'Lærervej 10', 2750)
124
125  INSERT INTO Klasse VALUES
126  (235, 'Special Klassen'),
127  (236, 'A Klassen'),
128  (237, 'Q Klassen')
129
130  INSERT INTO PostNR VALUES
131  (4000, 'Roskilde'),
132  (2750, 'Ballerup'),
133  (2800, 'Lyngby'),
134  (2000, 'Frederiksberg'),
135  (3600, 'Frederikssund'),
136  (3630, 'Jægerspris')
137  
138 ALTER TABLE Laerer ADD CONSTRAINT pk_fk_postnrLaerer
139FOREIGN KEY (PostNr) REFERENCES Postnr (postnr)
140
141ALTER TABLE Elev ADD CONSTRAINT pk_fk_postnrElev
142FOREIGN KEY (postNr) REFERENCES Postnr (postNr)
143
144ALTER TABLE Elev ADD CONSTRAINT pk_fk_klasse
145FOREIGN KEY (klasse) REFERENCES klasse (klasseid)
146  --SELECT Student_ID as 'Elev ID', Student_FirstName + ' ' + Student_LastName AS Navn, Student_Address + ', ' + Student_ZIP as Adresse, Student_Grade as 'Klasse Nummer'
147  --FROM Student;
148  --Select * FROM Student
149  --SELECT * FROM Grade
150  --SELECT * FROM Teacher
151  --SELECT * From PostalCode
152
153  --ALTER TABLE Laerer