Q8xmFAbN

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