xXrhgdyC

1USE [Aarhus Bryghus]
2GO
3
4DROP TABLE IF EXISTS salgslinje
5DROP TABLE IF EXISTS salg
6DROP TABLE IF EXISTS produktpris
7DROP TABLE IF EXISTS salgssituation
8DROP TABLE IF EXISTS produkt
9DROP TABLE IF EXISTS produkttype
10
11CREATE TABLE produkttype(
12typenavn VARCHAR(20) PRIMARY KEY NOT NULL
13)
14
15CREATE TABLE produkt(
16varenummer INT identity(1000, 1) PRIMARY KEY NOT NULL,
17produktnavn VARCHAR (22),
18typenavn VARCHAR(20) constraint typenavnForeign FOREIGN KEY REFERENCES produkttype not null 
19)
20
21CREATE TABLE salgssituation(
22situationnavn VARCHAR(20) PRIMARY KEY NOT NULL
23)
24
25CREATE TABLE produktpris(
26produktprisID VARCHAR(2) PRIMARY KEY NOT NULL,
27pris INT,
28rabat INT,
29varenummer INT FOREIGN KEY REFERENCES produkt NOT NULL,
30situationnavn VARCHAR(20) FOREIGN KEY REFERENCES salgssituation NOT NULL
31)
32
33CREATE TABLE salg(
34salgID CHAR PRIMARY KEY NOT NULL,
35dato DATE
36)
37
38CREATE TABLE salgslinje(
39salgslinjeID char PRIMARY KEY NOT NULL,
40aftaltpris INT,
41antal INT, 
42produktprisID VARCHAR(2) FOREIGN KEY REFERENCES produktpris NOT NULL,
43salgID CHAR FOREIGN KEY REFERENCES salg NOT NULL
44)
45
46
47INSERT INTO produkttype VALUES ('Flaskeøl')
48INSERT INTO produkttype VALUES ('Fadøl')
49INSERT INTO produkttype VALUES ('Vand')
50INSERT INTO produkttype VALUES ('Snacks')
51INSERT INTO produkttype VALUES ('Spiritus')
52INSERT INTO produkttype VALUES ('Fustage')
53INSERT INTO produkttype VALUES ('Kulsyre')
54INSERT INTO produkttype VALUES ('Glas')
55INSERT INTO produkttype VALUES ('Anlæg')
56INSERT INTO produkttype VALUES ('Anlæg2')
57
58INSERT INTO produkt VALUES('Klosterbryg', 'Flaskeøl')
59INSERT INTO produkt VALUES('Celebration', 'Fadøl')
60INSERT INTO produkt VALUES('Æblebrus', 'Vand')
61INSERT INTO produkt VALUES('Chips', 'Snacks')
62INSERT INTO produkt VALUES('Spirit of Aarhus', 'Spiritus')
63INSERT INTO produkt VALUES('Klosterbryg, 20 liter', 'Fustage')
64INSERT INTO produkt VALUES('Kulsyre 6 kg', 'Kulsyre')
65INSERT INTO produkt VALUES('Glas', 'Glas')
66INSERT INTO produkt VALUES('Bar med flere haner', 'Anlæg')
67
68INSERT INTO salgssituation VALUES ('Fredagsbar')
69INSERT INTO salgssituation VALUES ('Butik')
70INSERT INTO salgssituation VALUES ('PÃ¥skefrokost')
71
72
73INSERT INTO produktpris VALUES(1, 50, 10, 1000, 'Butik')
74INSERT INTO produktpris VALUES(2, 50, 0, 1000, 'Fredagsbar')
75INSERT INTO produktpris VALUES(3, 50, 25, 1000, 'PÃ¥skefrokost')
76
77INSERT INTO produktpris VALUES(4, 50, 0, 1001, 'Butik')
78INSERT INTO produktpris VALUES(5, 50, 10, 1001, 'Fredagsbar')
79INSERT INTO produktpris VALUES(6, 50, 25, 1001, 'PÃ¥skefrokost')
80
81INSERT INTO produktpris VALUES(7, 25, 0, 1002, 'Fredagsbar')
82INSERT INTO produktpris VALUES(8, 25, 0, 1002, 'PÃ¥skefrokost')
83
84INSERT INTO produktpris VALUES(9, 25, 0, 1003, 'Fredagsbar')
85
86INSERT INTO produktpris VALUES(10, 300, 50, 1004, 'Butik')
87INSERT INTO produktpris VALUES(11, 300, 0, 1004, 'Fredagsbar')
88
89INSERT INTO produktpris VALUES(12, 400, 0, 1005, 'Butik')
90INSERT INTO produktpris VALUES(13, 400, 0, 1005, 'Fredagsbar')
91
92INSERT INTO produktpris VALUES(14, 500, 0, 1006, 'Butik')
93
94INSERT INTO salg VALUES(1, '2019-4-7')
95INSERT INTO salg VALUES(2, '2019-3-7')
96INSERT INTO salg VALUES(3, '2019-2-7')
97
98INSERT INTO salgslinje VALUES(1, 200, 10, 1, 1)
99INSERT INTO salgslinje VALUES(2, null, 10, 4, 1)
100INSERT INTO salgslinje VALUES(3, null, 2, 10, 1)
101INSERT INTO salgslinje VALUES(4, null, 10, 1, 1)
102INSERT INTO salgslinje VALUES(5, null, 10, 1, 2)
103INSERT INTO salgslinje VALUES(6, null, 10, 5, 3)
104
105
106
107-- Opgave 2.
108-- a. For et givet produkt alle de priser produktet har lige nu for de forskelige salgssituationer
109SELECT produkt.produktnavn, produktpris.situationnavn, (produktpris.pris-produktpris.rabat) AS pris 
110FROM produktpris join produkt ON 
111produktpris.varenummer = produkt.varenummer
112WHERE produkt.produktnavn = 'Klosterbryg'
113
114-- b. Udregner rabatten for et givet salg. Her skal kun medtages den rabat, 
115-- der er på selve produktet i salgssituationen, dvs. der ses bort fra aftalt pris.
116SELECT salg.salgID, sum((produktpris.pris * antal) - (produktpris.pris - rabat) * antal) AS rabat 
117FROM salg 
118join salgslinje ON salg.salgID = salgslinje.salgID
119join produktpris ON salgslinje.produktprisID = produktpris.produktprisID
120WHERE salg.salgID = 1
121GROUP BY salg.salgID
122
123-- c. Udregner den totale/endelige pris for et givet salg.
124SELECT salg.salgID, SUM(isnull(aftaltpris, (produktpris.pris - produktpris.rabat) * antal)) AS 'Samledepris'
125FROM salg 
126left join salgslinje ON salg.salgID = salgslinje.salgID
127join produktpris ON salgslinje.produktprisID = produktpris.produktprisID
128WHERE salg.salgID = 1
129group by salg.salgID
130
131
132-- d. Udregner for hvert produkt det samlede antal solgte produkter i en given måned, 
133-- kun produkter, hvor der er solgt mere end 5 stk. medtages.
134SELECT produkt.produktnavn, produkt.varenummer, sum(salgslinje.antal) AS 'Antal solgte varer', salg.dato 
135FROM salgslinje 
136join salg ON salgslinje.salgID = salg.salgID
137join produktpris ON salgslinje.produktprisID = produktpris.produktprisID
138join produkt ON produktpris.varenummer = produkt.varenummer
139WHERE MONTH(salg.dato) = 04 and antal > 5
140GROUP BY produkt.produktnavn, produkt.varenummer, salg.dato
141
142
143
144-- e. Navne på produkter og det tilhørende produktgruppenavn, 
145-- hvor der ikke er en pris til salgssituationen 'fredagsbar'
146SELECT produkt.produktnavn, produkt.typenavn 
147FROM produkt
148WHERE produkt.produktnavn not in
149(SELECT produkt.produktnavn 
150FROM produkt
151join produktpris ON produkt.varenummer = produktpris.varenummer
152WHERE produktpris.situationnavn = 'Fredagsbar')
153
154-- f. Udregn hvor mange penge, der i gennesnit sælges for per salg
155SELECT AVG(Samledepris) AS 'Average' FROM
156(SELECT SUM(isnull(aftaltpris, (produktpris.pris - produktpris.rabat) * antal)) AS 'Samledepris'
157FROM salgslinje 
158left join produktpris ON salgslinje.produktprisID = produktpris.produktprisID
159GROUP BY salgslinje.salgID) AS innerquery
160
161
162-- Opgave 3.
163GO
164DROP VIEW IF exists view1
165GO
166CREATE VIEW view1 AS
167SELECT produkt.typenavn, produkt.produktnavn, count(salgslinje.produktprisID) AS 'Antal af salg' 
168FROM produktpris 
169join produkt ON produktpris.varenummer = produkt.varenummer
170left join salgslinje ON produktpris.produktprisID = salgslinje.produktprisID
171group by produkt.typenavn, produkt.produktnavn
172GO 
173select * from view1
174-- Opgave 4a.
175GO
176DROP PROC IF exists myproc1
177GO
178CREATE PROC myproc1 
179@salgssituation varchar(20)
180AS
181SELECT produkt.produktnavn, (produktpris.pris - rabat) AS 'Reele pris' 
182FROM produktpris 
183join produkt ON produktpris.varenummer = produkt.varenummer
184WHERE produktpris.situationnavn = @salgssituation
185GO
186EXEC myproc1 'Fredagsbar'
187
188-- Opgave 4b.
189DROP PROC IF exists myproc2
190GO
191CREATE PROC myproc2
192@produktgruppe varchar(20), @rabatsats int
193AS
194UPDATE produktpris
195SET produktpris.rabat = @rabatsats 
196FROM produktpris
197join produkt ON produktpris.varenummer = produkt.varenummer
198WHERE produkt.typenavn = @produktgruppe
199GO
200EXEC myproc2 'Flaskeøl', 20
201
202-- Opgave 5.
203GO
204DROP TRIGGER IF exists mytrigger1
205GO 
206CREATE TRIGGER mytrigger1
207ON produkt
208AFTER DELETE 
209AS
210BEGIN
211DELETE produkttype
212WHERE produkttype.typenavn not in
213(SELECT produkt.typenavn 
214FROM produkt)
215END
216GO