1SAjnhz3

1DELIMITER $$
2
3CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_MellanDagsRea`()
4BEGIN
5	UPDATE kladesplagg SET prisMedMoms = ROUND(prisMedMoms * 0.8);
6END$$
7
8DELIMITER ;
9
10CREATE TABLE IF NOT EXISTS `kategori` (
11  `namn` varchar(50) NOT NULL,
12  PRIMARY KEY (`namn`)
13) ENGINE=InnoDB DEFAULT CHARSET=latin1;
14
15INSERT INTO `kategori` (`namn`) VALUES
16('Byxor'),
17('Jackor'),
18('Klänningar'),
19('Tröjor'),
20('Underkläder'),
21('Väskor');
22
23CREATE TABLE IF NOT EXISTS `kladesplagg` (
24  `artikelnummer` varchar(100) NOT NULL,
25  `kategori` varchar(50) DEFAULT NULL,
26  `typ` varchar(50) DEFAULT NULL,
27  `namn` varchar(50) NOT NULL,
28  `tillverkare` varchar(50) NOT NULL,
29  `tillverkningsland` varchar(50) NOT NULL,
30  `kon` varchar(20) NOT NULL,
31  `farg` varchar(20) NOT NULL,
32  `storlek` varchar(15) NOT NULL,
33  `vikt` double NOT NULL,
34  `prisUtanMoms` double NOT NULL,
35  `prisMedMoms` double NOT NULL,
36  `antal` int(11) NOT NULL,
37  PRIMARY KEY (`artikelnummer`),
38  KEY `typ` (`typ`),
39  KEY `kategori` (`kategori`)
40) ENGINE=InnoDB DEFAULT CHARSET=latin1;
41
42INSERT INTO `kladesplagg` (`artikelnummer`, `kategori`, `typ`, `namn`, `tillverkare`, `tillverkningsland`, `kon`, `farg`, `storlek`, `vikt`, `prisUtanMoms`, `prisMedMoms`, `antal`) VALUES
43('10lsvart', 'Underkläder', 'Boxershort', 'Boxershorts', 'Dressmann', 'Taiwan', 'Man', 'svart', 'large', 346, 120, 1291, 3),
44('11lsvart', 'Väskor', 'Skinnväskor', 'Skinnväska', 'Dressmann', 'Taiwan', 'Man', 'svart', 'large', 346, 120, 1496, 4),
45('12lsvart', 'Väskor', 'Ryggsäckar', 'Ryggsäck', 'Dressmann', 'Taiwan', 'Unisex', 'svart', 'large', 346, 120, 1714, 13),
46('1lsvart', 'Byxor', 'Jeans', 'Coola jeans', 'Dressmann', 'Taiwan', 'Kvinna', 'svart', 'large', 346, 120, 590, 9),
47('2lsvart', 'Byxor', 'Shorts', 'Coola shorts', 'Dressmann', 'Taiwan', 'Man', 'svart', 'large', 346, 120, 712, 4),
48('3lsvart', 'Jackor', 'Skinnjackor', 'Skinnjacka', 'Dressmann', 'Taiwan', 'Kvinna', 'svart', 'large', 346, 120, 1493, 6),
49('4lsvart', 'Jackor', 'Vinterjackor', 'Vinterjacka', 'Dressmann', 'Taiwan', 'Unisex', 'svart', 'large', 346, 120, 1837, 4),
50('5lsvart', 'Klänningar', 'Långklänningar', 'Långklänning', 'Dressmann', 'Taiwan', 'Man', 'svart', 'large', 346, 120, 1216, 3),
51('6lsvart', 'Klänningar', 'Korta klänningar', 'Kort klänning', 'Dressmann', 'Taiwan', 'Kvinna', 'svart', 'large', 346, 120, 1872, 15),
52('7lsvart', 'Tröjor', 'Sweatshirt', 'Cool sweatshirt', 'Dressmann', 'Taiwan', 'Unisex', 'svart', 'large', 346, 120, 620, 4),
53('8lsvart', 'Tröjor', 'T-shirt', 'Cool t-shirt', 'Dressmann', 'Taiwan', 'Kvinna', 'svart', 'large', 346, 120, 386, 8),
54('9lsvart', 'Underkläder', 'BH', 'Stor BH', 'Dressmann', 'Taiwan', 'Man', 'svart', 'large', 346, 120, 1376, 12);
55
56CREATE TABLE IF NOT EXISTS `konto` (
57  `personnummer` varchar(13) NOT NULL,
58  `fornamn` varchar(50) NOT NULL,
59  `efternamn` varchar(50) NOT NULL,
60  `kon` varchar(50) NOT NULL,
61  `adress` varchar(100) NOT NULL,
62  `postadress` varchar(100) NOT NULL,
63  `telefon` varchar(20) NOT NULL,
64  `epost` varchar(100) NOT NULL,
65  `kontotyp` int(11) NOT NULL DEFAULT '1',
66  PRIMARY KEY (`personnummer`)
67) ENGINE=InnoDB DEFAULT CHARSET=latin1;
68
69INSERT INTO `konto` (`personnummer`, `fornamn`, `efternamn`, `kon`, `adress`, `postadress`, `telefon`, `epost`, `kontotyp`) VALUES
70('960901-1037', 'Elias', 'Tångring', 'Man', 'Vägen 3', 'Vägen 4', '654321', 'epost@webbplats.se', 2),
71('961002-1317', 'Aram', 'Al Yousifi', 'Kvinna', 'Stigen 5', 'Stigen 6', '213465', 'brev@hemsida.org', 3),
72('961126-2370', 'Jonathan', 'Sundqvist', 'Man', 'Gatan 1', 'Gatan 2', '123456', 'mail@sida.nu', 1);
73
74CREATE TABLE IF NOT EXISTS `kupong` (
75  `namn` varchar(50) NOT NULL,
76  `giltigFran` datetime NOT NULL,
77  `giltigTill` datetime NOT NULL,
78  `rabatt` double NOT NULL,
79  PRIMARY KEY (`namn`)
80) ENGINE=InnoDB DEFAULT CHARSET=latin1;
81
82INSERT INTO `kupong` (`namn`, `giltigFran`, `giltigTill`, `rabatt`) VALUES
83('PÃ…SK', '2016-03-25 00:00:00', '2016-03-28 00:00:00', 5.5);
84
85CREATE TABLE IF NOT EXISTS `order` (
86  `id` int(11) NOT NULL AUTO_INCREMENT,
87  `kundnr` varchar(13) DEFAULT NULL,
88  `kupong` varchar(50) DEFAULT NULL,
89  `vikt` double NOT NULL,
90  `adress` varchar(100) NOT NULL,
91  `koptillfälle` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
92  `summa` double NOT NULL,
93  PRIMARY KEY (`id`),
94  KEY `kundnr` (`kundnr`),
95  KEY `kupong` (`kupong`)
96) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
97
98INSERT INTO `order` (`id`, `kundnr`, `kupong`, `vikt`, `adress`, `koptillfälle`, `summa`) VALUES
99(1, '961126-2370', NULL, 1038, 'Gatan 2', '2016-10-04 02:23:19', 897),
100(2, '961126-2370', NULL, 1730, 'Gatan 2', '2016-10-04 02:23:19', 1495),
101(3, '961126-2370', NULL, 692, 'Gatan 2', '2016-10-04 02:23:19', 598),
102(4, '961126-2370', NULL, 4844, 'Gatan 2', '2016-10-04 02:23:19', 4186);
103
104-- --------------------------------------------------------
105
106--
107-- Stand-in structure for view `people_buy_most_clothes`
108--
109CREATE TABLE IF NOT EXISTS `people_buy_most_clothes` (
110`Kön` varchar(20)
111,`Antal` bigint(21)
112);
113
114CREATE TABLE IF NOT EXISTS `produkter_order` (
115  `Id` int(11) NOT NULL AUTO_INCREMENT,
116  `OrderId` int(11) NOT NULL,
117  `Artikelnummer` varchar(100) DEFAULT NULL,
118  PRIMARY KEY (`Id`),
119  KEY `OrderId` (`OrderId`),
120  KEY `Artikelnummer` (`Artikelnummer`)
121) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;
122
123INSERT INTO `produkter_order` (`Id`, `OrderId`, `Artikelnummer`) VALUES
124(1, 1, '1lsvart'),
125(2, 1, '2lsvart'),
126(3, 1, '3lsvart'),
127(4, 2, '1lsvart'),
128(5, 2, '2lsvart'),
129(6, 2, '3lsvart'),
130(7, 2, '4lsvart'),
131(8, 2, '5lsvart'),
132(9, 3, '1lsvart'),
133(10, 3, '2lsvart'),
134(11, 4, '1lsvart'),
135(12, 4, '2lsvart'),
136(13, 4, '3lsvart'),
137(14, 4, '4lsvart'),
138(15, 4, '5lsvart'),
139(16, 4, '6lsvart'),
140(17, 4, '7lsvart'),
141(18, 4, '8lsvart'),
142(19, 4, '7lsvart'),
143(20, 4, '6lsvart'),
144(21, 4, '5lsvart'),
145(22, 4, '4lsvart'),
146(23, 4, '3lsvart'),
147(24, 4, '2lsvart');
148
149-- --------------------------------------------------------
150
151--
152-- Stand-in structure for view `profit_margin`
153--
154CREATE TABLE IF NOT EXISTS `profit_margin` (
155`namn` varchar(50)
156,`Vinstmarginal i %` double(17,0)
157);
158-- --------------------------------------------------------
159
160--
161-- Stand-in structure for view `sorted_list_under_500`
162--
163CREATE TABLE IF NOT EXISTS `sorted_list_under_500` (
164`Namn` varchar(50)
165,`Pris` double
166);
167-- --------------------------------------------------------
168
169--
170-- Stand-in structure for view `top_ten_bestsellers`
171--
172CREATE TABLE IF NOT EXISTS `top_ten_bestsellers` (
173`Namn` varchar(50)
174,`Antal` bigint(21)
175);
176-- --------------------------------------------------------
177
178--
179-- Stand-in structure for view `top_ten_bestseller_men`
180--
181CREATE TABLE IF NOT EXISTS `top_ten_bestseller_men` (
182`Namn` varchar(50)
183,`Antal` bigint(21)
184);
185-- --------------------------------------------------------
186
187--
188-- Stand-in structure for view `top_ten_bestseller_unisex`
189--
190CREATE TABLE IF NOT EXISTS `top_ten_bestseller_unisex` (
191`Namn` varchar(50)
192,`Antal` bigint(21)
193);
194-- --------------------------------------------------------
195
196--
197-- Stand-in structure for view `top_ten_bestseller_women`
198--
199CREATE TABLE IF NOT EXISTS `top_ten_bestseller_women` (
200`Namn` varchar(50)
201,`Antal` bigint(21)
202);
203
204CREATE TABLE IF NOT EXISTS `typ` (
205  `namn` varchar(50) NOT NULL,
206  PRIMARY KEY (`namn`)
207) ENGINE=InnoDB DEFAULT CHARSET=latin1;
208
209INSERT INTO `typ` (`namn`) VALUES
210('BH'),
211('Boxershort'),
212('Cardigans'),
213('Jeans'),
214('Korta klänningar'),
215('Kostymer'),
216('Linnen'),
217('Långklänningar'),
218('Nattlinnen'),
219('Ryggsäckar'),
220('Shorts'),
221('Skinnjackor'),
222('Skinnväskor'),
223('Sweatshirt'),
224('T-Shirt'),
225('Trosor'),
226('Träningsbyxor'),
227('Vinterjackor'),
228('Y-front'),
229('Övriga jackor');
230
231-- --------------------------------------------------------
232
233--
234-- Stand-in structure for view `view_products_lower_5`
235--
236CREATE TABLE IF NOT EXISTS `view_products_lower_5` (
237`Namn` varchar(50)
238,`Antal` int(11)
239);
240-- --------------------------------------------------------
241
242--
243-- Structure for view `people_buy_most_clothes`
244--
245DROP TABLE IF EXISTS `people_buy_most_clothes`;
246
247CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `people_buy_most_clothes` AS select `kladesplagg`.`kon` AS `Kön`,count(`kladesplagg`.`kon`) AS `Antal` from (`produkter_order` join `kladesplagg` on((`kladesplagg`.`artikelnummer` = `produkter_order`.`Artikelnummer`))) group by `kladesplagg`.`kon` order by count(`kladesplagg`.`kon`) desc;
248
249-- --------------------------------------------------------
250
251--
252-- Structure for view `profit_margin`
253--
254DROP TABLE IF EXISTS `profit_margin`;
255
256CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `profit_margin` AS select `kladesplagg`.`namn` AS `namn`,round((((`kladesplagg`.`prisMedMoms` - `kladesplagg`.`prisUtanMoms`) / `kladesplagg`.`prisUtanMoms`) * 100),0) AS `Vinstmarginal i %` from `kladesplagg` group by `kladesplagg`.`namn` order by round((((`kladesplagg`.`prisMedMoms` - `kladesplagg`.`prisUtanMoms`) / `kladesplagg`.`prisUtanMoms`) * 100),0) desc;
257
258-- --------------------------------------------------------
259
260--
261-- Structure for view `sorted_list_under_500`
262--
263DROP TABLE IF EXISTS `sorted_list_under_500`;
264
265CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sorted_list_under_500` AS select `kladesplagg`.`namn` AS `Namn`,`kladesplagg`.`prisMedMoms` AS `Pris` from `kladesplagg` where (`kladesplagg`.`prisMedMoms` < 500) group by `kladesplagg`.`namn` order by `kladesplagg`.`namn` desc;
266
267-- --------------------------------------------------------
268
269--
270-- Structure for view `top_ten_bestsellers`
271--
272DROP TABLE IF EXISTS `top_ten_bestsellers`;
273
274CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `top_ten_bestsellers` AS select `kladesplagg`.`namn` AS `Namn`,count(0) AS `Antal` from (`produkter_order` join `kladesplagg` on((`kladesplagg`.`artikelnummer` = `produkter_order`.`Artikelnummer`))) group by `kladesplagg`.`namn` order by count(0) desc limit 10;
275
276-- --------------------------------------------------------
277
278--
279-- Structure for view `top_ten_bestseller_men`
280--
281DROP TABLE IF EXISTS `top_ten_bestseller_men`;
282
283CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `top_ten_bestseller_men` AS select `kladesplagg`.`namn` AS `Namn`,count(0) AS `Antal` from (`produkter_order` join `kladesplagg` on((`kladesplagg`.`artikelnummer` = `produkter_order`.`Artikelnummer`))) where (`kladesplagg`.`kon` = 'Man') group by `kladesplagg`.`namn` order by count(0) desc limit 10;
284
285-- --------------------------------------------------------
286
287--
288-- Structure for view `top_ten_bestseller_unisex`
289--
290DROP TABLE IF EXISTS `top_ten_bestseller_unisex`;
291
292CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `top_ten_bestseller_unisex` AS select `kladesplagg`.`namn` AS `Namn`,count(0) AS `Antal` from (`produkter_order` join `kladesplagg` on((`kladesplagg`.`artikelnummer` = `produkter_order`.`Artikelnummer`))) where (`kladesplagg`.`kon` = 'Unisex') group by `kladesplagg`.`namn` order by count(0) desc limit 10;
293
294-- --------------------------------------------------------
295
296--
297-- Structure for view `top_ten_bestseller_women`
298--
299DROP TABLE IF EXISTS `top_ten_bestseller_women`;
300
301CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `top_ten_bestseller_women` AS select `kladesplagg`.`namn` AS `Namn`,count(0) AS `Antal` from (`produkter_order` join `kladesplagg` on((`kladesplagg`.`artikelnummer` = `produkter_order`.`Artikelnummer`))) where (`kladesplagg`.`kon` = 'Kvinna') group by `kladesplagg`.`namn` order by count(0) desc limit 10;
302
303-- --------------------------------------------------------
304
305--
306-- Structure for view `view_products_lower_5`
307--
308DROP TABLE IF EXISTS `view_products_lower_5`;
309
310CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_products_lower_5` AS select `kladesplagg`.`namn` AS `Namn`,`kladesplagg`.`antal` AS `Antal` from `kladesplagg` where (`kladesplagg`.`antal` < 5) order by `kladesplagg`.`antal` desc;
311
312--
313-- Constraints for table `kladesplagg`
314--
315ALTER TABLE `kladesplagg`
316  ADD CONSTRAINT `kategori` FOREIGN KEY (`kategori`) REFERENCES `kategori` (`namn`) ON DELETE SET NULL ON UPDATE CASCADE,
317  ADD CONSTRAINT `typ` FOREIGN KEY (`typ`) REFERENCES `typ` (`namn`) ON DELETE SET NULL ON UPDATE CASCADE;
318
319--
320-- Constraints for table `order`
321--
322ALTER TABLE `order`
323  ADD CONSTRAINT `kundnr` FOREIGN KEY (`kundnr`) REFERENCES `konto` (`personnummer`) ON DELETE SET NULL ON UPDATE CASCADE,
324  ADD CONSTRAINT `kupong` FOREIGN KEY (`kupong`) REFERENCES `kupong` (`namn`) ON DELETE SET NULL ON UPDATE CASCADE;
325
326--
327-- Constraints for table `produkter_order`
328--
329ALTER TABLE `produkter_order`
330  ADD CONSTRAINT `artikelnummer` FOREIGN KEY (`Artikelnummer`) REFERENCES `kladesplagg` (`artikelnummer`) ON DELETE SET NULL ON UPDATE CASCADE,
331  ADD CONSTRAINT `orderid` FOREIGN KEY (`OrderId`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;