tDn9zg3d

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
101--prislisteID, navn
102insert into prisliste values
103(1, 'fredagsbar'),
104(2, 'butik') 
105
106--prisfralisteID, pris, fremmedenøgle(vareID), fremmedenøgle(prislisteID)
107insert into prisfraliste values
108(1, 50, 1, 1),
109(2, 36, 1, 2),
110(3, 50, 2, 1),
111(4, 36, 2, 2),
112(5, 50, 3, 1),
113(6, 36, 3, 2),
114(7, 50, 4, 1)
115
116--(8, 36, 4, 2)
117
118-- bestillingID, købsdato, betalingstype, fremmedenøgle(kundeID)
119insert into bestilling values
120(1, '2017-06-06', 'Dankort',	1),
121(2, '2017-06-06', 'Mobilpay',	2),
122(3, '2017-06-06', 'Kontant',	3)
123
124-- ordrelinjeID, antal, fremmedenøgle(bestillingID), fremmedenøgle(udlejningsproduktID), fremmedenøgle(prisfralisteID)
125insert into ordrelinje values
126(1, 1, 1, null, 1), -- Dankort,		50 kr.
127(2, 1, 2, null, 2), -- Mobilepay,	36 kr.
128(3, 1, 3, null, 3), -- kontant		50 kr.
129(4, 1, 3, null, 5)  -- kontant		50 kr.
130
131drop proc vare_navn
132
133-- Opgave 2A
134create proc vare_navn
135@vareID int,
136@vareType varchar(30),
137@udløbsdato date,
138@volume int,
139@navn varchar(30),
140@pris int,
141@prislistenavn varchar(30)
142as
143Declare @prisfralisteID int
144Declare @prislisteID int
145Declare @tempID4PrisListe int -- TEMP BRUGES KUN VED NY PRISLISTE
146Select @prislisteID = prisliste.prislisteID
147from prisliste
148if exists (select (prisliste.navn) from prisliste where prisliste.navn = @prislistenavn)
149begin
150set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
151insert into vare values
152(@vareID, @vareType, @udløbsdato, @volume, @navn)
153insert into prisfraliste values
154(@prisfralisteID, @pris, @vareID, @prislisteID)
155END
156else
157begin 
158set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
159set @tempID4PrisListe = (select MAX(prisliste.prislisteID)+1 from prisliste)
160insert into prisliste values
161(@tempID4PrisListe, @prislistenavn)
162insert into vare values
163(@vareID, @vareType, @udløbsdato, @volume, @navn)
164insert into prisfraliste values
165(@prisfralisteID, @pris, @vareID, @prislisteID)
166END
167
168
169exec vare_navn @vareID = 14, @vareType = 'flasker', @udløbsdato = '2017-06-06', @volume = 10, 
170@navn = 'blondie', @pris = 100, @prislistenavn = 'butik'
171
172-- start på test til Opgave 2A
173Select prisfraliste.prislisteID
174from prisfraliste, prisliste
175where prisfraliste.prislisteID = prisliste.prislisteID
176
177Select *
178from vare
179
180select prisfraliste.vareID
181from prisfraliste, vare
182where prisfraliste.vareID = vare.vareID
183
184Select *
185from prisliste
186-- Slut på test til opgave 2A
187
188
189-- opgave 2B del 1
190create procedure salgpaadagen
191@dato date
192as
193select prisfraliste.pris
194from prisfraliste
195full join ordrelinje
196on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
197full join bestilling
198on ordrelinje.bestillingID = bestilling.bestillingID
199where bestilling.købsdato = @dato
200
201exec salgpaadagen @dato = '2017-06-06'
202
203
204-- opgave 2B del 2
205Create proc salgprgruppe
206@dato date,
207@type varchar(30)
208as
209select prisfraliste.pris
210from prisfraliste
211full join vare
212on vare.vareID = prisfraliste.vareID
213full join ordrelinje
214on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
215full join bestilling
216on ordrelinje.bestillingID = bestilling.bestillingID
217where bestilling.købsdato = @dato and vare.varetype = @type
218
219drop proc salgprgruppe
220
221exec salgprgruppe @dato = '2017-06-06', @type = 'flasker'
222
223
224--opgave 3A Løsning 1
225create proc visVarePrisliste
226@vare as varchar(30),
227@type as varchar(30)
228as
229select vare.navn, prisfraliste.pris, prisliste.navn 
230from prisfraliste
231full join vare
232on vare.vareID = prisfraliste.vareID
233full join prisliste
234on prisliste.prislisteID = prisfraliste.prislisteID
235where vare.navn = @vare and vare.varetype = @type
236
237exec visVarePrisliste @vare = 'Klosterbryg', @type = 'flasker'
238
239drop proc visVarePrisliste
240
241
242--opgave 3A Løsning 2
243create proc visVarePrisliste2
244@vareID as varchar(30)
245as
246select vare.navn, prisfraliste.pris, prisliste.navn 
247from prisfraliste
248full join vare
249on vare.vareID = prisfraliste.vareID
250full join prisliste
251on prisliste.prislisteID = prisfraliste.prislisteID
252where prisfraliste.vareID = @vareID
253
254exec visVarePrisliste2 @vareID = 3
255
256drop proc visVarePrisliste2
257
258
259--opgave 3A Løsning 3
260create proc visVarePrisliste3
261@vare as varchar(30),
262@type as varchar(30)
263as
264Declare @vareID int
265	set @vareID =(Select vare.vareID from vare where vare.navn = @vare and vare.varetype = @type)
266select vare.navn, prisfraliste.pris, prisliste.navn 
267from prisfraliste
268full join vare
269on @vareID = prisfraliste.vareID
270full join prisliste
271on prisliste.prislisteID = prisfraliste.prislisteID
272where vare.navn = @vare and vare.varetype = @type
273
274exec visVarePrisliste3 @vare = 'Klosterbryg', @type = 'flasker'
275
276drop proc visVarePrisliste3
277
278
279--opgave 3B
280create proc totalpris
281@id int
282as
283select SUM(prisfraliste.pris) as totalPris
284from prisfraliste
285full join ordrelinje
286on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
287full join bestilling
288on ordrelinje.bestillingID = bestilling.bestillingID
289where bestilling.bestillingID = @id
290
291exec totalpris @id = 3
292
293drop proc totalpris
294
295--opgave 3C
296create proc getRabat
297@bestillingId int
298as
299select rabat.pris
300from rabat
301full join kunde
302on rabat.rabatID = kunde.rabatID
303full join bestilling
304on kunde.kundeID = bestilling.bestillingID
305where bestilling.bestillingID = @bestillingId
306
307exec getRabat @bestillingId = 3