· 9 years ago · Jun 12, 2017, 10:38 AM
1DROP TABLE banertilbooking
2
3DROP TABLE booking
4
5DROP TABLE bane
6
7DROP TABLE banepris
8
9DROP TABLE kunde
10
11DROP TABLE firma
12
13CREATE TABLE firma
14 (
15 firmaid INT IDENTITY PRIMARY KEY,
16 navn VARCHAR (50),
17 opsparettimer INT
18 )
19
20CREATE TABLE kunde
21 (
22 kundeid INT IDENTITY PRIMARY KEY NONCLUSTERED,
23 navn VARCHAR (50),
24 email VARCHAR (50),
25 telefonnummer CHAR(8),-- non clustered
26 firmaid INT FOREIGN KEY REFERENCES firma(firmaid),
27 )
28
29CREATE NONCLUSTERED INDEX bigindex
30 ON kunde(telefonnummer)
31 include (navn, email)
32
33CREATE TABLE banepris
34 (
35 baneprisid INT IDENTITY PRIMARY KEY,
36 tid TIME,
37 pris INT
38 )
39
40CREATE TABLE bane
41 (
42 baneid INT IDENTITY PRIMARY KEY,
43 beskrivelse VARCHAR(50),
44 )
45
46CREATE TABLE booking
47 (
48 bookingid INT IDENTITY PRIMARY KEY NONCLUSTERED,
49 dato DATETIME,-- clustered index
50 antalpersoner INT,
51 gratis BIT,
52 kundeid INT FOREIGN KEY REFERENCES kunde(kundeid) NOT NULL,
53 baneprisid INT FOREIGN KEY REFERENCES banepris(baneprisid) NOT NULL,
54 )
55
56CREATE CLUSTERED INDEX bigindex
57 ON booking(dato)
58
59CREATE NONCLUSTERED INDEX nonindex
60 ON booking(kundeid)
61
62CREATE TABLE banertilbooking
63 (
64 banertilbookingid INT IDENTITY PRIMARY KEY NONCLUSTERED,
65 bookingid INT FOREIGN KEY REFERENCES booking(bookingid) NOT NULL,
66 baneid INT FOREIGN KEY REFERENCES bane(baneid) NOT NULL
67 -- clustered index
68 )
69
70CREATE CLUSTERED INDEX baneindex
71 ON banertilbooking(baneid)
72
73-- Insert
74INSERT INTO firma
75VALUES ('Lego A/S',
76 0),
77 ('EAAA',
78 0)
79
80INSERT INTO kunde
81VALUES ('Jesper Petersen',
82 'jesper_petersen@mail.com',
83 '12345678',
84 1),
85 ('Sandy Vu',
86 'sandy_vu@mail.com',
87 '22345678',
88 1),
89 ('Lærke Sejr',
90 'lærke_sejr@mail.com',
91 '32345678',
92 1)
93
94INSERT INTO banepris
95VALUES ('11:00',
96 50),
97 ('12:00',
98 50),
99 ('13:00',
100 50),
101 ('14:00',
102 50),
103 ('15:00',
104 50),
105 ('16:00',
106 70),
107 ('17:00',
108 70),
109 ('18:00',
110 100),
111 ('19:00',
112 100),
113 ('20:00',
114 100),
115 ('21:00',
116 100)
117
118DECLARE @i INT
119
120SELECT @i = 1
121
122WHILE @i < 33
123 BEGIN
124 IF ( @i < 7
125 OR @i > 24 )
126 BEGIN
127 INSERT INTO bane
128 VALUES ('Lille hal')
129 END
130 ELSE
131 BEGIN
132 INSERT INTO bane
133 VALUES ('Stor hal')
134 END
135
136 SELECT @i = @i + 1
137 END
138
139INSERT INTO booking
140VALUES ('2017-06-10 12:00:00',
141 5,
142 1,
143 1,
144 2),
145 ('2017-06-10 12:00:00',
146 4,
147 1,
148 1,
149 2),
150 ('2017-06-10 12:00:00',
151 3,
152 1,
153 2,
154 2),
155 ('2017-06-10 18:00:00',
156 3,
157 1,
158 3,
159 8),
160 ('2017-06-10 18:00:00',
161 3,
162 1,
163 3,
164 8)
165
166INSERT INTO banertilbooking
167VALUES (1,
168 7),
169 (1,
170 11),
171 (2,
172 8),
173 (3,
174 9),
175 (4,
176 10),
177 (5,
178 7)
179
180-- Opgave 3
181DROP TRIGGER inserttrig
182
183go
184
185CREATE TRIGGER inserttrig
186ON booking
187FOR INSERT
188AS
189 IF EXISTS (SELECT dato
190 FROM inserted
191 WHERE dato < Getdate())
192 OR EXISTS (SELECT dato
193 FROM inserted
194 WHERE dato > Dateadd(mm, 1, Getdate()))
195 BEGIN
196 PRINT 'Ikke gyldig booking'
197
198 ROLLBACK TRAN
199 END
200
201go
202
203-- Opgave 4
204go
205
206DROP VIEW statistik
207
208go
209
210CREATE VIEW statistik
211AS
212 SELECT Datename(dw, t1.dato) AS UgeDag,
213 Cast(t1.dato AS TIME) AS StartTid,
214 Sum(baner) AS UdlejedeBaner,
215 Sum(indt�gt + skopris) AS Indtægt
216 FROM (SELECT b.bookingid,
217 Sum(bp.pris) * b.gratis AS indtægt,
218 Count(bb.bookingid) AS baner,
219 b.dato
220 FROM booking b,
221 banepris bp,
222 banertilbooking bb
223 WHERE bp.tid = Cast (b.dato AS TIME)
224 AND b.bookingid = bb.bookingid
225 GROUP BY Datename(dw, b.dato),
226 Cast(b.dato AS TIME),
227 b.bookingid,
228 b.gratis,
229 b.dato) AS t1
230 JOIN (SELECT b.bookingid,
231 Sum(b.antalpersoner) * 15 AS skopris
232 FROM booking b
233 GROUP BY b.bookingid) AS t2
234 ON t1.bookingid = t2.bookingid
235 GROUP BY Datename(dw, t1.dato),
236 Cast(t1.dato AS TIME)
237
238go
239
240SELECT *
241FROM statistik
242
243-- Opgave 5
244DROP PROC tildeltimer
245
246go
247
248CREATE PROC Tildeltimer
249AS
250 SELECT *
251 INTO #temp
252 FROM (SELECT f.firmaid AS id,
253 Sum(bp.pris) AS omsætning
254 FROM kunde k,
255 firma f,
256 booking b,
257 banepris bp
258 WHERE f.firmaid = k.firmaid
259 AND k.kundeid = b.kundeid
260 AND b.baneprisid = bp.baneprisid
261 AND Datepart(yyyy, b.dato) = Datepart(yyyy, Getdate()) - 1
262 GROUP BY f.firmaid
263 HAVING Count(b.bookingid) > 2) AS x
264
265 SELECT *
266 FROM #temp
267
268 DECLARE @Id INT
269
270 WHILE (SELECT Count(*)
271 FROM #temp) > 0
272 BEGIN
273 SELECT TOP 1 @Id = id
274 FROM #temp
275
276 UPDATE firma
277 SET opsparettimer = (SELECT TOP 1 oms�tning
278 FROM #temp) / 2500
279 WHERE firmaid = @Id
280
281 DELETE #temp
282 WHERE id = @Id
283 END
284
285 DROP TABLE #temp
286
287go
288
289EXEC Tildeltimer
290
291-- Opgave 8
292-- Backup
293RESTORE DATABASE eksamensdb FROM dbdev WITH FILE = 3
294
295-- Differential backup
296RESTORE DATABASE eksamensdb FROM dbdev WITH FILE = 5
297
298-- Logs
299RESTORE log eksamensdb FROM logdev WITH FILE = 1
300
301RESTORE log eksamensdb FROM logdev WITH FILE = 2