NG2mHKKx

1
2drop table klippekort
3drop table ordrelinje
4drop table prisfraliste
5drop table prisliste
6drop table vare
7drop table bestilling
8drop table udlejningsprodukt
9drop table kunde
10drop table rabat
11
12create table rabat(
13rabatID int primary key NOT NULL,
14pris int
15)
16
17Create table kunde(
18kundeID int primary key NOT NULL,
19navn varchar(30),
20adresse varchar(30),
21telefonnr int,
22email varchar(30),
23rabatID int foreign key references rabat(rabatID)
24)
25
26create table klippekort(
27klippekortID int primary key NOT NULL,
28antalklip int,
29pris int,
30kundeID int foreign key references kunde(KundeID)
31)
32
33create table bestilling(
34bestillingID int primary key NOT NULL,
35købsdato date default getdate(),
36betalingstype varchar(30),
37kundeID int foreign key references kunde(KundeID)
38)
39
40create table udlejningsprodukt(
41udlejningsproduktID int primary key NOT NULL,
42udltype varchar(30),
43navn varchar(30),
44pris int,
45udlejet date default getdate(),
46tilbagelevering date,
47ledig bit default 1 -- NÃ¥r den er 1 = ledig
48)
49
50create table vare(
51vareID int primary key NOT NULL,
52varetype varchar(30),
53udløbsdato date CHECK (udløbsdato >= getDATE() ),
54volume int,
55navn varchar(30)
56)
57
58create table prisliste(
59prislisteID int primary key NOT NULL,
60navn varchar(30)
61)
62
63create table prisfraliste(
64prisfralisteID int primary key NOT NULL,
65pris int,
66vareID int foreign key references vare(vareID) NOT NULL,
67prislisteID int foreign key references prisliste(prislisteID) NOT NULL
68)
69
70create table ordrelinje(
71ordrelinjeID int primary key NOT NULL,
72antal int,
73bestillingID int foreign key references bestilling(bestillingID),
74udlejningsproduktID int foreign key references udlejningsprodukt(udlejningsproduktID),
75prisfralisteID int foreign key references prisfraliste(prisfralisteID),
76)
77
78--RabatID, pris
79insert into rabat values
80(1, 50),
81(2, 100),
82(3,	150),
83(4,	200),
84(5, 250),
85(6, 300)
86
87--KundeID, navn , adresse, telefonnr, email, rabatID
88insert into kunde values
89(1, 'Daniel', '8900 Randersvej 100', 20202020, 'daniel@gmail.com', 1),
90(2, 'Simon', '8210 Gellerupvej 200', 20204020, 'simon@gmail.com', 2),
91(3, 'Dennis', '8230 Silkeborgvej 300', 20205020, 'dennis@gmail.com', 3)
92
93-- vareID, varetype, udløbsdato, volume, navn
94insert into vare values
95(1, 'flasker', '2017-06-06', 75, 'Klosterbryg'), 
96(2, 'fustage', '2017-06-06', 75, 'Sweet Georgia Brown'),
97(3, 'flasker', '2017-06-06', 75, 'Extra Pilsner'), 
98(4, 'flasker', '2017-06-06', 75, 'Celebration') 
99
100--prislisteID, navn
101insert into prisliste values
102(1, 'fredagsbar'),
103(2, 'butik') 
104
105--prisfralisteID, pris, fremmedenøgle(vareID), fremmedenøgle(prislisteID)
106insert into prisfraliste values
107(1, 50, 1, 1),
108(2, 36, 1, 2),
109(3, 50, 2, 1),
110(5, 50, 3, 1)
111
112-- bestillingID, købsdato, betalingstype, fremmedenøgle(kundeID)
113insert into bestilling values
114(1, '2017-06-06', 'Dankort',	1),
115(2, '2017-06-06', 'Mobilpay',	2),
116(3, '2017-06-06', 'Kontant',	3)
117
118-- ordrelinjeID, antal, fremmedenøgle(bestillingID), fremmedenøgle(udlejningsproduktID), fremmedenøgle(prisfralisteID)
119insert into ordrelinje values
120(1, 1, 1, null, 1), -- Dankort,		50 kr.
121(2, 1, 2, null, 2), -- Mobilepay,	36 kr.
122(3, 1, 3, null, 3), -- kontant		50 kr.
123(4, 1, 3, null, 5)  -- kontant		50 kr.
124
125drop proc vare_navn
126
127-- Opgave 2A
128create proc vare_navn
129@vareID int,
130@vareType varchar(30),
131@udløbsdato date,
132@volume int,
133@navn varchar(30),
134@pris int,
135@prislistenavn varchar(30)
136as
137Declare @prisfralisteID int
138Declare @prislisteID int
139Declare @tempID4PrisListe int -- TEMP BRUGES KUN VED NY PRISLISTE
140Select @prislisteID = prisliste.prislisteID
141from prisliste
142if exists (select (prisliste.navn) from prisliste where prisliste.navn = @prislistenavn)
143begin
144set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
145insert into vare values
146(@vareID, @vareType, @udløbsdato, @volume, @navn)
147insert into prisfraliste values
148(@prisfralisteID, @pris, @vareID, @prislisteID)
149END
150else
151begin 
152set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
153set @tempID4PrisListe = (select MAX(prisliste.prislisteID)+1 from prisliste)
154insert into prisliste values
155(@tempID4PrisListe, @prislistenavn)
156insert into vare values
157(@vareID, @vareType, @udløbsdato, @volume, @navn)
158insert into prisfraliste values
159(@prisfralisteID, @pris, @vareID, @prislisteID)
160END
161
162
163exec vare_navn @vareID = 14, @vareType = 'flasker', @udløbsdato = '2017-06-06', @volume = 10, 
164@navn = 'blondie', @pris = 100, @prislistenavn = 'butik'
165
166-- start på test til Opgave 2A
167Select prisfraliste.prislisteID
168from prisfraliste, prisliste
169where prisfraliste.prislisteID = prisliste.prislisteID
170
171Select *
172from vare
173
174select prisfraliste.vareID
175from prisfraliste, vare
176where prisfraliste.vareID = vare.vareID
177
178Select *
179from prisliste
180-- Slut på test til opgave 2A
181
182
183-- opgave 2B del 1
184create procedure salgpaadagen
185@dato date
186as
187select prisfraliste.pris
188from prisfraliste
189full join ordrelinje
190on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
191full join bestilling
192on ordrelinje.bestillingID = bestilling.bestillingID
193where bestilling.købsdato = @dato
194
195exec salgpaadagen @dato = '2017-06-06'
196
197
198-- opgave 2B del 2
199Create proc salgprgruppe
200@dato date,
201@type varchar(30)
202as
203select prisfraliste.pris
204from prisfraliste
205full join vare
206on vare.vareID = prisfraliste.vareID
207full join ordrelinje
208on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
209full join bestilling
210on ordrelinje.bestillingID = bestilling.bestillingID
211where bestilling.købsdato = @dato and vare.varetype = @type
212
213drop proc salgprgruppe
214
215exec salgprgruppe @dato = '2017-06-06', @type = 'flasker'
216
217
218--opgave 3A Løsning 1
219create proc visVarePrisliste
220@vare as varchar(30),
221@type as varchar(30)
222as
223select vare.navn, prisfraliste.pris, prisliste.navn 
224from prisfraliste
225full join vare
226on vare.vareID = prisfraliste.vareID
227full join prisliste
228on prisliste.prislisteID = prisfraliste.prislisteID
229where vare.navn = @vare and vare.varetype = @type
230
231exec visVarePrisliste @vare = 'Klosterbryg', @type = 'flasker'
232
233drop proc visVarePrisliste
234
235
236--opgave 3A Løsning 2
237create proc visVarePrisliste2
238@vareID as varchar(30)
239as
240select vare.navn, prisfraliste.pris, prisliste.navn 
241from prisfraliste
242full join vare
243on vare.vareID = prisfraliste.vareID
244full join prisliste
245on prisliste.prislisteID = prisfraliste.prislisteID
246where prisfraliste.vareID = @vareID
247
248exec visVarePrisliste2 @vareID = 3
249
250drop proc visVarePrisliste2
251
252
253--opgave 3A Løsning 3
254create proc visVarePrisliste3
255@vare as varchar(30),
256@type as varchar(30)
257as
258Declare @vareID int
259	set @vareID =(Select vare.vareID from vare where vare.navn = @vare and vare.varetype = @type)
260select vare.navn, prisfraliste.pris, prisliste.navn 
261from prisfraliste
262full join vare
263on @vareID = prisfraliste.vareID
264full join prisliste
265on prisliste.prislisteID = prisfraliste.prislisteID
266where vare.navn = @vare and vare.varetype = @type
267
268exec visVarePrisliste3 @vare = 'Klosterbryg', @type = 'flasker'
269
270drop proc visVarePrisliste3
271
272
273--opgave 3B
274create proc pris
275@id int
276as
277select SUM(prisfraliste.pris) as Pris
278from prisfraliste
279full join ordrelinje
280on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
281full join bestilling
282on ordrelinje.bestillingID = bestilling.bestillingID
283where bestilling.bestillingID = @id
284
285exec pris @id = 3
286
287drop proc pris
288
289--opgave 3C Løsning 1 - Hent rabatten for en bestemt bestilling
290create proc getRabat
291@bestillingId int
292as
293select rabat.pris
294from rabat
295full join kunde
296on rabat.rabatID = kunde.rabatID
297full join bestilling
298on kunde.kundeID = bestilling.bestillingID
299where bestilling.bestillingID = @bestillingId
300
301exec getRabat @bestillingId = 2
302
303drop proc getRabat
304
305--opgave 3C Løsning 2 - pris for bestillingen minus(-) rabatten
306create proc totalpris
307@id int
308as
309select sum(prisfraliste.pris) - rabat.pris as Pris
310from prisfraliste
311full join ordrelinje
312on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
313full join bestilling
314on ordrelinje.bestillingID = bestilling.bestillingID
315full join kunde
316on bestilling.kundeID = kunde.kundeID
317full join rabat
318on kunde.rabatID = rabat.rabatID
319where bestilling.bestillingID = @id
320group by prisfraliste.pris, rabat.pris
321
322exec totalpris @id = 2
323
324drop proc totalpris
325
326--Opgave3D
327create proc opg3D
328@prisliste varchar(30),
329@vareNavn varchar(30)
330as
331Select prisliste.navn, prisfraliste.pris
332from prisfraliste
333full join prisliste
334on prisfraliste.prislisteID = prisliste.prislisteID
335full join vare
336on prisfraliste.vareID = vare.vareID
337where prisliste.navn = @prisliste and vare.navn = @vareNavn
338
339exec opg3D @prisliste = 'fredagsbar', @vareNavn = 'Celebration'
340
341drop proc opg3D
342
343--opgave 4 // Opdatere varene
344create proc opdatereVare
345@vareID int,
346@varetype varchar(30),
347@udløbsdato date,
348@volume int,
349@navn varchar(30)
350as
351update vare
352set
353vare.varetype = @varetype,
354vare.udløbsdato = @udløbsdato,
355vare.volume = @volume,
356vare.navn = @navn
357where vare.vareID = @vareID
358
359exec opdatereVare @vareid = 1, @varetype = 'flasker', @udløbsdato = '2017-06-10', @volume = 5000, @navn = 'DanielBryg'
360
361drop proc opdatereVare
362
363select *
364from vare
365
366
367
368
369--set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
370-- opgave 4 // Slet vare
371create proc sletVare
372@vareid int
373as
374delete ordrelinje
375from prisfraliste
376where ordrelinje.prisfralisteID = prisfraliste.prisfralisteID and prisfraliste.vareID = @vareid
377delete prisfraliste
378where prisfraliste.vareID = (@vareid)  
379delete vare
380where vare.vareID = @vareid
381
382exec sletVare @vareid = 1
383exec sletVare @vareid = 2
384exec sletVare @vareid = 3
385exec sletVare @vareid = 14
386
387drop proc sletVare
388
389select *
390from vare
391
392select *
393from bestilling
394
395select *
396from ordrelinje
397
398select *
399from prisliste
400
401
402
403
404--opgave 5
405create trigger tjekpåsletvare on vare for delete
406as
407begin
408delete prisliste
409from prisliste
410where prisliste.prislisteID NOT IN (Select prisfraliste.prislisteID from prisfraliste)
411end
412go
413
414
415drop trigger tjekpåsletvare