· 6 years ago · May 22, 2019, 09:22 AM
1DROP TABLE IF EXISTS Kladesplagg;
2CREATE TABLE Kladesplagg (
3 ArtikelNr INT NOT NULL AUTO_INCREMENT,
4 Typ VARCHAR(100) DEFAULT NULL,
5 Modell VARCHAR(100) DEFAULT NULL,
6 Storlek VARCHAR(5) DEFAULT NULL,
7 Kon VARCHAR(10) DEFAULT NULL,
8 Farg VARCHAR(50) DEFAULT NULL,
9 Vikt INT DEFAULT NULL,
10 Inkopspris INT DEFAULT NULL,
11 Forsaljningspris INT DEFAULT NULL,
12 Lager INT DEFAULT NULL,
13 Tillverkare VARCHAR(100) DEFAULT NULL,
14 Tillverkningsland VARCHAR(50) DEFAULT NULL,
15 PRIMARY KEY (ArtikelNr)
16);
17
18#Tabell Kund
19DROP TABLE IF EXISTS Kund;
20CREATE TABLE Kund (
21 KundNr INT NOT NULL AUTO_INCREMENT,
22 Fname VARCHAR(50) NOT NULL,
23 Enamn VARCHAR(50) NOT NULL,
24 PersonNr VARCHAR(20) DEFAULT NULL,
25 MobilNr VARCHAR(15) DEFAULT NULL,
26 Epost VARCHAR(200) DEFAULT NULL,
27 Postadress VARCHAR(200) DEFAULT NULL,
28 Leveransadress VARCHAR(200) DEFAULT NULL,
29 PRIMARY KEY (KundNr)
30);
31
32#Tabell Kupong
33DROP TABLE IF EXISTS Kupong;
34CREATE TABLE Kupong(
35 KupongNr INT NOT NULL AUTO_INCREMENT,
36 KupongNamn VARCHAR(100) DEFAULT NULL,
37 RabattProcent varchar(10) DEFAULT NULL,
38 StartDatum DATE DEFAULT NULL,
39 SlutDatum DATE DEFAULT NULL,
40 PRIMARY KEY (KupongNr)
41);
42
43#Tabell KundOrder
44DROP TABLE IF EXISTS KundOrder;
45CREATE TABLE KundOrder(
46 OrdNr INT NOT NULL AUTO_INCREMENT,
47 KundNr INT NOT NULL,
48 KupongNr INT DEFAULT NULL,
49 Datum DATETIME DEFAULT NULL,
50 TotalVikt INT DEFAULT NULL,
51 Summa INT DEFAULT NULL,
52 Frakt VARCHAR(50) DEFAULT NULL,
53 TotalSumma INT DEFAULT NULL,
54 PRIMARY KEY (OrdNr),
55 CONSTRAINT FK_KundNr
56 FOREIGN KEY (KundNr)
57 REFERENCES Kund(KundNr),
58 CONSTRAINT FK_KupongNr
59 FOREIGN KEY (KupongNr)
60 REFERENCES Kupong(KupongNr)
61);
62
63#Tabell BeställdaKläder
64DROP TABLE IF EXISTS Bestalldaklader;
65CREATE TABLE Bestalldaklader(
66 ArtikelNr INT NOT NULL,
67 OrdNr INT NOT NULL,
68 CONSTRAINT FK_ArtikelNr
69 FOREIGN KEY (ArtikelNr)
70 REFERENCES Kladesplagg(ArtikelNr),
71 CONSTRAINT FK_OrdNr
72 FOREIGN KEY (OrdNr)
73 REFERENCES KundOrder(OrdNr)
74);
75
76#Tabell Omsättning
77CREATE TABLE Omsattning(
78 PlaggNr INT NOT NULL,
79 Antal INT DEFAULT NULL,
80 CONSTRAINT FK_PlaggNr
81 FOREIGN KEY (PlaggNr)
82 REFERENCES Kladesplagg(ArtikelNr)
83);
84
85DROP TABLE Bestalldaklader;
86DROP TABLE KundOrder;
87DROP TABLE Kund;
88DROP TABLE Kupong;
89DROP TABLE Omsattning;
90DROP TABLE Kladesplagg;
91
92INSERT INTO Kund(Fname, Enamn, PersonNr, MobilNr, Epost, Postadress, Leveransadress) VALUES
93('Sara', 'Karlsson', '19950318-4578', '0706578020', 'Sara.karlsson@gmail.com', 'Minervavägen 20 37141 Karlskrona', 'Minervavägen 20 37141 Karlskrona'),
94('Robert', 'Nilsson', '19900627-3256', '0738778521', 'Robert.nilsson@hotmail.com', 'Snapphanevägen 10B 37140 Karlskrona', 'Snapphanevägen 10B 37140 Karlskrona'),
95('Annika', 'Lindqvist', '200001102-1278', '0708440311', 'Annika.lindqvist@gmail.com', 'Landsvägsgatan 1A 37139 Karlskrona', 'Sveavägen 32A 10465 Stockholm');
96
97SELECT * FROM Kund;
98
99INSERT INTO Kladesplagg(typ, modell, storlek, kon, farg, vikt, inkopspris, forsaljningspris, Lager, tillverkare, tillverkningsland) VALUES
100('Tröja', 'T-Shirt', 'M', 'Kvinna', 'Vit', 150, 59, 199, 11, 'Zara', 'Tyskland'),
101('Byxor', 'Shorts', 'XL', 'Man', 'Blå', 400, 259, 499, 3, 'Vila', 'Kina'),
102('Underkläder', 'BH', '75B', 'Kvinna', 'Röd', 100, 159, 249, 2, 'Cubus', 'Kina'),
103('Klänningar', 'Långklänningar', 'S', 'Kvinna', 'Rosa', 200, 299, 459, 4, 'NLY Eve', 'Indien'),
104('Jackor', 'Skinnjackor', 'L', 'Unisex', 'Svart', 350, 1199, 2499, 15, 'Rock Denim', 'Danmark');
105
106INSERT INTO Kladesplagg(typ, modell, storlek, kon, farg, vikt, inkopspris, forsaljningspris, Lager, tillverkare, tillverkningsland) VALUES
107('Tröja', 'Sweatshirt', 'S', 'Man', 'Blå', 500, 499, 999, 32, 'Zara', 'Tyskland');
108
109SELECT * FROM Kladesplagg;
110
111INSERT INTO Kupong(kupongnamn, rabattprocent, startdatum, slutdatum) VALUES
112('PÃ…SK', '0.1', '2019-03-25', '2019-03-28');
113
114SELECT * FROM Kupong;
115
116INSERT INTO KundOrder(KundNr, KupongNr, Datum, Totalvikt, Summa, Frakt, TotalSumma) VALUES
117(1, 1, '2019-03-26 12:24:01', 1250, 5125.5 , 55, 5180.5);
118
119# Ingen kupong
120INSERT INTO KundOrder(KundNr, Datum, Totalvikt, Summa, Frakt, TotalSumma) VALUES
121(2, '2019-05-18 15:18:21', 799, 4998, 50, 5048),
122(3, '2019-03-26 09:45:10', 1450, 5945, 59, 6004);
123
124SELECT * FROM KundOrder;
125
126INSERT INTO Bestalldaklader(ArtikelNr, OrdNr) VALUES
127(1, 1),
128(3, 1),
129(4, 2),
130(1, 3),
131(2, 3),
132(3, 3),
133(4, 3),
134(4, 1),
135(3, 1),
136(4, 2),
137(4, 1),
138(4, 3);
139
140SELECT * FROM Bestalldaklader;
141
142INSERT INTO Omsattning(PlaggNr, Antal) VALUES
143(1, 2),
144(2, 1),
145(3, 3),
146(4, 6),
147(5, 0);
148
149SELECT * FROM Omsattning;
150
151
152# Webbplatsen
153
154# Kund view
155DROP VIEW IF EXISTS KundVy;
156CREATE VIEW KundVy AS
157SELECT Modell, Storlek, Kon, Farg, Forsaljningspris, Lager, Tillverkare, Tillverkningsland FROM Kladesplagg;
158
159# Sorterad lista
160DROP VIEW IF EXISTS SortedLista;
161CREATE VIEW SortedLista AS
162SELECT Typ, Forsaljningspris FROM Kladesplagg
163WHERE Forsaljningspris < 500;
164
165# Dam view
166DROP VIEW IF EXISTS ToppListaDam;
167CREATE VIEW ToppListaDam AS
168SELECT K.ArtikelNr,COUNT(K.ArtikelNr) AS Sum FROM Bestalldaklader
169INNER JOIN Kladesplagg K on Bestalldaklader.ArtikelNr = K.ArtikelNr
170WHERE Kon = 'Kvinna' GROUP BY K.ArtikelNr DESC;
171
172# Herr view
173DROP VIEW IF EXISTS ToppListaHerr;
174CREATE VIEW ToppListaHerr AS
175SELECT K.ArtikelNr,COUNT(K.ArtikelNr) AS Sum FROM Bestalldaklader
176INNER JOIN Kladesplagg K on Bestalldaklader.ArtikelNr = K.ArtikelNr
177WHERE Kon = 'Man' GROUP BY K.ArtikelNr DESC;
178
179# Inköpare
180
181# LÃ¥g lagerstatus view
182DROP VIEW IF EXISTS LagLagerstatus;
183CREATE VIEW LagLagerstatus AS
184SELECT * FROM Kladesplagg
185WHERE Lager < 5;
186
187
188DELIMITER //
189CREATE PROCEDURE sp_MellanDagsRea()
190BEGIN
191 UPDATE Kladesplagg SET Forsaljningspris = (Forsaljningspris*0.8);
192end //
193DELIMITER ;
194
195CALL sp_MellanDagsRea;
196
197DROP PROCEDURE IF EXISTS sp_MellanDagsRea;
198
199# Företagsledning
200
201# Vilka kläder har högst vinstmarginal
202DROP VIEW IF EXISTS VinstMarginal;
203CREATE VIEW VinstMarginal AS
204SELECT ArtikelNr ,(Forsaljningspris - Inkopspris)*Antal AS Sum FROM Kladesplagg
205INNER JOIN Omsattning O on Kladesplagg.ArtikelNr = O.PlaggNr
206ORDER BY Sum DESC;
207
208# Vilket klädesplagg har blivit sålt mest
209DROP VIEW IF EXISTS PlaggStat;
210CREATE VIEW PlaggStat AS
211SELECT K.ArtikelNr,COUNT(K.ArtikelNr) AS Sum FROM Bestalldaklader
212INNER JOIN Kladesplagg K on Bestalldaklader.ArtikelNr = K.ArtikelNr
213GROUP BY K.ArtikelNr DESC
214ORDER BY Sum DESC;
215
216# Vilen grupp köper flest kläder
217DROP VIEW IF EXISTS GruppStat;
218CREATE VIEW GruppStat AS
219SELECT K.Kon ,COUNT(K.ArtikelNr) AS Sum FROM Bestalldaklader
220INNER JOIN Kladesplagg K on Bestalldaklader.ArtikelNr = K.ArtikelNr
221GROUP BY K.Kon
222ORDER BY Sum DESC;
223
224# Topplista, ingen specifik grupp
225DROP VIEW IF EXISTS Topplista;
226CREATE VIEW Topplista AS
227SELECT K.ArtikelNr,COUNT(K.ArtikelNr) AS Sum FROM Bestalldaklader
228INNER JOIN Kladesplagg K on Bestalldaklader.ArtikelNr = K.ArtikelNr
229GROUP BY K.ArtikelNr DESC;