BUUsQjbH

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--KundeID, navn , adresse, telefonnr, email, rabatID
79insert into kunde values
80(1, 'Daniel', '8900 Randersvej 100', 20202020, 'daniel@gmail.com', null),
81(2, 'Simon', '8210 Gellerupvej 200', 20204020, 'simon@gmail.com', null),
82(3, 'Dennis', '8230 Silkeborgvej 300', 20205020, 'dennis@gmail.com', null)
83
84-- vareID, varetype, udløbsdato, volume, navn
85insert into vare values
86(1, 'flasker', '2017-06-06', 75, 'Klosterbryg'), -- fredagsbar 50 kr
87(2, 'flasker', '2017-06-06', 75, 'Sweet Georgia Brown'), -- fredagsbar 50 kr.
88(3, 'flasker', '2017-06-06', 75, 'Extra Pilsner'), -- butik 36 kr.
89(4, 'flasker', '2017-06-06', 75, 'Celebration') -- butik 36 kr.
90
91--prislisteID, navn
92insert into prisliste values
93(1, 'fredagsbar'),
94(2, 'butik') 
95
96--prisfralisteID, pris, fremmedenøgle(vareID), fremmedenøgle(prislisteID)
97insert into prisfraliste values
98(1, 50, 1, 1 ),
99(2, 50, 2,1),
100(3, 36, 3,2),
101(4, 36,4,2)
102
103-- bestillingID, købsdato, betalingstype, fremmedenøgle(kundeID)
104insert into bestilling values
105(1, '2017-06-06', 'Dankort', 1),
106(2, '2017-06-06', 'Mobilpay', 2),
107(3, '2017-06-06', 'Kontant', 3)
108
109-- ordrelinjeID, antal, fremmedenøgle(bestillingID), fremmedenøgle(udlejningsproduktID), fremmedenøgle(prisfralisteID)
110insert into ordrelinje values
111(1, 1, 1, null, 1), -- (ordrelinjeID, antal, bestillingID, udlejningsproduktID, prisfralisteID)
112(2,1,2,null,2),
113(3,1,3,null,3)
114
115drop proc vare_navn
116
117-- Opgave 2A
118create proc vare_navn
119@vareID int,
120@vareType varchar(30),
121@udløbsdato date,
122@volume int,
123@navn varchar(30),
124@pris int,
125@prislistenavn varchar(30)
126as
127Declare @prisfralisteID int
128Declare @prislisteID int
129Declare @tempID4PrisListe int -- TEMP BRUGES KUN VED NY PRISLISTE
130Select @prislisteID = prisliste.prislisteID
131from prisliste
132if exists (select (prisliste.navn) from prisliste where prisliste.navn = @prislistenavn)
133begin
134set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
135insert into vare values
136(@vareID, @vareType, @udløbsdato, @volume, @navn)
137insert into prisfraliste values
138(@prisfralisteID, @pris, @vareID, @prislisteID)
139END
140else
141begin 
142set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
143set @tempID4PrisListe = (select MAX(prisliste.prislisteID)+1 from prisliste)
144insert into prisliste values
145(@tempID4PrisListe, @prislistenavn)
146insert into vare values
147(@vareID, @vareType, @udløbsdato, @volume, @navn)
148insert into prisfraliste values
149(@prisfralisteID, @pris, @vareID, @prislisteID)
150END
151
152
153exec vare_navn @vareID = 14, @vareType = 'flasker', @udløbsdato = '2017-06-06', @volume = 10, 
154@navn = 'blondie', @pris = 100, @prislistenavn = 'butik'
155
156-- start på test til Opgave 2A
157Select prisfraliste.prislisteID
158from prisfraliste, prisliste
159where prisfraliste.prislisteID = prisliste.prislisteID
160
161Select *
162from vare
163
164select prisfraliste.vareID
165from prisfraliste, vare
166where prisfraliste.vareID = vare.vareID
167
168Select *
169from prisliste
170-- Slut på test til opgave 2A
171
172
173-- opgave 2B
174create procedure opg2
175@dato date
176as
177select prisfraliste.pris
178from prisfraliste
179full join ordrelinje
180on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
181full join bestilling
182on ordrelinje.bestillingID = bestilling.bestillingID
183where bestilling.købsdato = @dato
184
185exec opg2 @dato = '2017-06-06'