· 9 years ago · Jun 09, 2017, 10:22 AM
1-- Drop all foreign keys
2DECLARE @sql nvarchar(MAX)
3SET @sql = ''
4
5SELECT @sql = @sql + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA)
6 + '.' + QUOTENAME(KCU1.TABLE_NAME)
7 + ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; '
8FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
9
10INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
11 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
12 AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
13 AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
14
15
16-- PRINT @sql
17EXECUTE(@sql)
18
19drop table if exists Klant;
20drop table if exists Bestelling;
21drop table if exists Leverancier;
22drop table if exists SeizoenKorting;
23drop table if exists CouponKorting;
24drop table if exists Artikel;
25drop table if exists LeveranciersArtikel;
26drop table if exists Beoordeling;
27drop table if exists BesteldArtikel;
28drop table if exists Categorie;
29
30drop procedure if exists SlechtVerkopendeArtikelen;
31drop procedure if exists PlaatsBestelling;
32drop procedure if exists VoegCouponToe;
33drop procedure if exists VoegSeizoenToe;
34
35drop trigger if exists tr_UpdatePrijs;
36drop trigger if exists tr_UpdateVoorraad;
37
38drop type if exists BestelLijst;
39DROP TYPE if exists StDevTable;
40
41go
42
43CREATE TABLE Klant (
44KlantID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
45Naam nvarchar(50) NOT NULL,
46Adres nvarchar(50) NOT NULL,
47Huisnummer int NOT NULL,
48Postcode nvarchar(6) NULL,
49Woonplaats nvarchar(50) NOT NULL,
50Email nvarchar(50) UNIQUE NOT NULL,
51Wachtwoord nvarchar(50) not null,
52RegistratieDatum date NOT NULL
53);
54
55CREATE TABLE Bestelling (
56BestellingID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
57KlantID int NOT NULL,
58Datum date NOT NULL,
59Prijs numeric(9,2) NULL,
60CONSTRAINT FK_KlantBestelling FOREIGN KEY (KlantID) REFERENCES Klant(KlantID)
61);
62
63CREATE TABLE Leverancier (
64LeverancierID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
65Naam nvarchar(50) NOT NULL,
66Adres nvarchar(50) NOT NULL,
67Huisnummer int NOT NULL,
68Postcode nvarchar(6) NULL,
69Plaats nvarchar(50) NOT NULL,
70Email nvarchar(50) UNIQUE NOT NULL,
71);
72
73CREATE TABLE SeizoenKorting(
74SeizoenID int IDENTITY(0,1) NOT NULL PRIMARY KEY,
75BeginDatum date NOT NULL,
76EindDatum date NOT NULL,
77PercentageKorting int NOT NULL
78);
79
80CREATE TABLE CouponKorting(
81CouponID int IDENTITY(0,1) NOT NULL PRIMARY KEY,
82Korting numeric(5,2) NOT NULL
83);
84
85CREATE TABLE Artikel(
86ArtikelID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
87Naam nvarchar(50) NOT NULL UNIQUE,
88Prijs numeric(9,2) NOT NULL,
89Voorraad int NOT NULL,
90CategorieID int NOT NULL,
91CONSTRAINT CHECK_Prijs CHECK (Prijs > 0)
92);
93
94CREATE TABLE LeveranciersArtikel(
95ArtikelID int NOT NULL,
96LeverancierID int NOT NULL,
97FOREIGN KEY (ArtikelID) REFERENCES Artikel(ArtikelID),
98FOREIGN KEY (LeverancierID) REFERENCES Leverancier(LeverancierID),
99PRIMARY KEY( ArtikelID, LeverancierID)
100);
101
102CREATE TABLE Beoordeling(
103BeoordelingID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
104Sterren int NOT NULL,
105Datum date NOT NULL,
106KlantID int NOT NULL,
107ArtikelID int NOT NULL,
108FOREIGN KEY (KlantID) REFERENCES Klant(KlantID),
109FOREIGN KEY (ArtikelID) REFERENCES Artikel(ArtikelID)
110);
111
112CREATE TABLE BesteldArtikel(
113BestellingID int NOT NULL,
114ArtikelID int NOT NULL,
115Aantal int NOT NULL DEFAULT 1,
116SeizoenID int NULL,
117CouponID int NULL,
118FOREIGN KEY (BestellingID) REFERENCES Bestelling(BestellingID),
119FOREIGN KEY (ArtikelID) REFERENCES Artikel(ArtikelID),
120FOREIGN KEY (SeizoenID) REFERENCES SeizoenKorting(SeizoenID),
121FOREIGN KEY (CouponID) REFERENCES CouponKorting(CouponID),
122PRIMARY KEY (BestellingID, ArtikelID)
123);
124
125CREATE TABLE Categorie(
126CategorieID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
127Naam nvarchar(50) NOT NULL,
128SubCategorie int NULL,
129FOREIGN KEY (SubCategorie) REFERENCES Categorie(CategorieID)
130);
131
132ALTER TABLE Artikel
133ADD FOREIGN KEY (CategorieID) REFERENCES Categorie(CategorieID);
134
135insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Zacherie Joynson', 'Autumn Leaf', '933', 'Saint-Félicien', 'klant@klant.nl', '11/30/2016', '1234');
136insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Mandy Hainey`', 'Trailsway', '12', 'Baranovichi', 'admin@admin.nl', '12/21/2015', '1234');
137insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Cissiee Berndsen', 'Holmberg', '91', 'Kilinochchi', 'cberndsen2@vk.com', '8/3/2016', '1234');
138insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Padraic Bolderson', 'Amoth', '3873', 'Bergen', 'pbolderson3@bbb.org', '5/4/2015', '1234');
139insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Berta Peasee', 'Carberry', '7355', 'Chadian', 'bpeasee4@creativecommons.org', '7/27/2016', '1234');
140insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Carree Szreter', 'Northland', '08325', 'San Jerónimo', 'cszreter5@mediafire.com', '4/30/2016', '1234');
141insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Kameko Allender', 'Dwight', '4084', 'Severoural’sk', 'kallender6@mapy.cz', '8/9/2016', '1234');
142insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Casi Lush', 'Fremont', '66', 'Madīnat Lab‘ūs', 'clush7@newsvine.com', '12/28/2015', '1234');
143insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Barnabe Purves', 'Debra', '776', 'Kurungannyawa', 'bpurves8@ucsd.edu', '11/14/2015', '1234');
144insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Saunders Langton', 'Gale', '8', 'Granada', 'slangton9@google.ca', '3/3/2017', '1234');
145insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Viviana O''Growgane', 'Scott', '3553', 'Pingshan', 'vogrowganea@paypal.com', '7/29/2016', '1234');
146insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Radcliffe Langman', 'Springs', '33', 'Göteborg', 'rlangmanb@marketwatch.com', '3/24/2016', '1234');
147insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Skyler Bake', 'Luster', '99', 'Jianggang', 'sbakec@aol.com', '2/21/2016', '1234');
148insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Lyndy Attenbrow', 'Susan', '635', 'Tapilon', 'lattenbrowd@google.nl', '1/17/2016', '1234');
149insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Kayley Caudray', 'Rusk', '1513', 'Jianyangping', 'kcaudraye@vk.com', '1/15/2017', '1234');
150insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Danit O''Noland', 'Weeping Birch', '3612', 'Hallstahammar', 'donolandf@dyndns.org', '3/14/2016', '1234');
151insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Kevin Gullis', 'Monica', '7', 'Yershov', 'kgullisg@github.com', '1/16/2016', '1234');
152insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Carey Harrap', '4th', '940', 'Municipio de Copacabana', 'charraph@deviantart.com', '12/25/2015', '1234');
153insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Obadiah Wakeham', 'Valley Edge', '5', 'Uppsala', 'owakehami@cnet.com', '11/5/2015', '1234');
154insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Mercedes McConnell', 'Cottonwood', '169', 'Jatipamor', 'mmcconnellj@hostgator.com', '7/15/2016', '1234');
155insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Rois Witchard', 'Acker', '3', 'Enyerhyetykaw', 'rwitchardk@gmpg.org', '6/5/2015', '1234');
156insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Leila Maddrah', 'Alpine', '71', 'Likhobory', 'lmaddrahl@google.it', '5/16/2015', '1234');
157insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Adriena Dacey', 'Oneill', '57605', 'Selebi-Phikwe', 'adaceym@usgs.gov', '12/5/2016', '1234');
158insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Leeland Darridon', 'Vernon', '9', 'Mariscala', 'ldarridonn@webmd.com', '6/15/2016', '1234');
159insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Tabbatha Napoleon', 'Del Mar', '23', 'Benito Juarez', 'tnapoleono@usnews.com', '4/28/2016', '1234');
160insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Jarret Van T''Hoog', 'Delaware', '199', 'Almafuerte', 'jvanp@msu.edu', '9/19/2015', '1234');
161insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Ted Fallowes', 'Elmside', '989', 'Viangxai', 'tfallowesq@ebay.co.uk', '3/1/2016', '1234');
162insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Salome Calderwood', 'Hintze', '0', 'Kamenica', 'scalderwoodr@gravatar.com', '4/15/2017', '1234');
163insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Daffi Iban', 'Northfield', '4', 'GÄ…socin', 'dibans@tumblr.com', '2/9/2017', '1234');
164insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Ondrea Mesant', 'Sutherland', '774', 'Banyo', 'omesantt@parallels.com', '1/10/2016', '1234');
165insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Geoffrey De Ruel', 'Loomis', '44', 'Gafanha da Encarnação', 'gdeu@wisc.edu', '2/13/2017', '1234');
166insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Lucille Bischoff', 'Old Gate', '9', 'Säffle', 'lbischoffv@ucla.edu', '4/25/2017', '1234');
167insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Obadiah Millsom', 'Clemons', '31548', 'Ban Mo', 'omillsomw@reference.com', '10/14/2015', '1234');
168insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Kippie Guillerman', 'Rowland', '3825', 'Arroio Grande', 'kguillermanx@reddit.com', '6/13/2016', '1234');
169insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Ulla Dingsdale', 'Mayer', '1', 'Rio Grande', 'udingsdaley@sina.com.cn', '11/9/2015', '1234');
170insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Lilllie Gillebride', 'Atwood', '76', 'Dasha', 'lgillebridez@google.ru', '3/6/2017', '1234');
171insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Monique Jannequin', 'Thierer', '25', 'Palencia', 'mjannequin10@examiner.com', '1/20/2017', '1234');
172insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Gregorius Matelaitis', 'Corben', '73', 'Paso de Indios', 'gmatelaitis11@typepad.com', '8/26/2015', '1234');
173insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Anna-diana Clarson', 'Fairfield', '1652', 'Larangan', 'aclarson12@bing.com', '5/1/2015', '1234');
174insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Cosimo Lilly', 'Nancy', '77948', 'Encañada', 'clilly13@ocn.ne.jp', '3/24/2016', '1234');
175insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('My Morgen', 'Surrey', '413', 'Chironkamba', 'mmorgen14@nydailynews.com', '8/31/2015', '1234');
176insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Orelie Vedenyakin', 'Green Ridge', '06369', 'Shuangxi', 'ovedenyakin15@amazonaws.com', '11/3/2015', '1234');
177insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Andras Jevon', 'Eagle Crest', '5915', 'Campoalegre', 'ajevon16@edublogs.org', '9/8/2015', '1234');
178insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Lou Sherrin', 'Namekagon', '13246', 'Libuganon', 'lsherrin17@dot.gov', '2/9/2016', '1234');
179insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Pamelina Bertomeu', 'Redwing', '276', 'Tieli', 'pbertomeu18@phoca.cz', '5/31/2016', '1234');
180insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Tomaso McCully', 'Schlimgen', '0027', 'Jiuhe', 'tmccully19@baidu.com', '5/3/2016', '1234');
181insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Violetta Sandall', 'Sachs', '979', 'Lianjiangkou', 'vsandall1a@live.com', '9/18/2015', '1234');
182insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Rowland Carlet', 'Little Fleur', '386', 'Bohdalov', 'rcarlet1b@hhs.gov', '3/10/2017', '1234');
183insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Munroe Veasey', 'Karstens', '6648', 'Renhe', 'mveasey1c@hatena.ne.jp', '2/8/2016', '1234');
184insert into Klant (Naam, Adres, Huisnummer, Woonplaats, Email, Registratiedatum, Wachtwoord) values ('Cristian Fellgett', 'Monica', '38', 'AgÃa Varvára', 'cfellgett1d@businessinsider.com', '1/7/2017', '1234');
185
186insert into Bestelling (KlantID, Datum, prijs) values (35, '6/20/2016', 1050);
187insert into Bestelling (KlantID, Datum, prijs) values (48, '2/14/2017', 338);
188insert into Bestelling (KlantID, Datum, prijs) values (15, '5/27/2016', 1918);
189insert into Bestelling (KlantID, Datum, prijs) values (14, '2/3/2017', 793);
190insert into Bestelling (KlantID, Datum, prijs) values (23, '2/11/2017', 1545);
191insert into Bestelling (KlantID, Datum, prijs) values (5, '6/26/2016', 726);
192insert into Bestelling (KlantID, Datum, prijs) values (13, '3/12/2017', 991);
193insert into Bestelling (KlantID, Datum, prijs) values (1, '3/20/2017', 542);
194insert into Bestelling (KlantID, Datum, prijs) values (27, '12/5/2016', 1498);
195insert into Bestelling (KlantID, Datum, prijs) values (30, '4/27/2017', 121);
196insert into Bestelling (KlantID, Datum, prijs) values (39, '3/20/2017', 804);
197insert into Bestelling (KlantID, Datum, prijs) values (13, '7/14/2016', 798);
198insert into Bestelling (KlantID, Datum, prijs) values (20, '8/14/2016', 880);
199insert into Bestelling (KlantID, Datum, prijs) values (35, '9/18/2016', 1764);
200insert into Bestelling (KlantID, Datum, prijs) values (32, '4/10/2017', 1103);
201insert into Bestelling (KlantID, Datum, prijs) values (5, '9/13/2016', 954);
202insert into Bestelling (KlantID, Datum, prijs) values (1, '7/12/2016', 1090);
203insert into Bestelling (KlantID, Datum, prijs) values (46, '4/15/2017', 1040);
204insert into Bestelling (KlantID, Datum, prijs) values (32, '7/30/2016', 1203);
205insert into Bestelling (KlantID, Datum, prijs) values (3, '8/3/2016', 1432);
206
207insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Kimbra Giacopazzi', 'Valley Edge', '98', 'Ar Rumaythīyah', 'kgiacopazzi0@free.fr');
208insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Englebert Sloss', 'Quincy', '1', 'Mosjøen', 'esloss1@earthlink.net');
209insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Tristan Menel', 'Pepper Wood', '03818', 'AyÃa Triás', 'tmenel2@macromedia.com');
210insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Virginie Dreamer', 'Logan', '04', 'Przelewice', 'vdreamer3@google.fr');
211insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Tiphani MacIver', 'Katie', '41173', 'Lekaj', 'tmaciver4@phpbb.com');
212insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Burlie Jamieson', 'Autumn Leaf', '9', 'Pryyutivka', 'bjamieson5@opera.com');
213insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Robin Keilloh', 'Jay', '3', 'Kryva Hora', 'rkeilloh6@mayoclinic.com');
214insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Annabella Corkhill', 'Merchant', '608', 'Lederaba', 'acorkhill7@toplist.cz');
215insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Fran Taffs', 'Hintze', '3', 'Tanglag', 'ftaffs8@cbc.ca');
216insert into Leverancier (Naam, Adres, Huisnummer, Plaats, Email) values ('Ruby Tompsett', 'Longview', '9319', 'Brive-la-Gaillarde', 'rtompsett9@java.com');
217
218INSERT INTO SeizoenKorting(BeginDatum, EindDatum, PercentageKorting) VALUES('1/1/2017', '1/31/2017', '10');
219INSERT INTO SeizoenKorting(BeginDatum, EindDatum, PercentageKorting) VALUES('1/2/2017', '2/28/2017', '20');
220INSERT INTO SeizoenKorting(BeginDatum, EindDatum, PercentageKorting) VALUES('1/4/2017', '4/30/2017', '30');
221
222INSERT INTO CouponKorting(Korting) VALUES('10');
223INSERT INTO CouponKorting(Korting) VALUES('20');
224INSERT INTO CouponKorting(Korting) VALUES('30');
225
226INSERT INTO Categorie(Naam) VALUES('TV');
227INSERT INTO Categorie(Naam) VALUES('Laptop');
228INSERT INTO Categorie(Naam) VALUES('Witgoed');
229
230INSERT INTO Categorie(Naam, subCategorie) VALUES('Philips', '1');
231INSERT INTO Categorie(Naam, subCategorie) VALUES('LG', '1');
232INSERT INTO Categorie(Naam, subCategorie) VALUES('Acer', '2');
233INSERT INTO Categorie(Naam, subCategorie) VALUES('Dell', '2');
234INSERT INTO Categorie(Naam, subCategorie) VALUES('Wasmachine', '3');
235insert into Categorie(naam, subCategorie) values('Waterkoker','3');
236
237insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('PHILIPS 32PFK4101', 249, 68, 4);
238insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('PHILIPS 43PFS5301', 389, 83, 4);
239insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('PHILIPS 32PHS5301', 297, 48, 4);
240insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('LG 28MT48DF', 179, 41, 5);
241insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('LG 43UH610V', 429, 66, 5);
242insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('LG 49UH610V', 515, 57, 5);
243insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('ACER ASPIRE F5-573-52P5', 669, 4, 6);
244insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('ACER CHROMEBOOK 14 CB3-431-C7WJ', 299, 93, 6);
245insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('ACER ASPIRE E5-774-39HZ', 599, 61, 6);
246insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('DELL VOSTRO 15 3558 3CD6X', 429, 51, 7);
247insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('DELL XPS 15 9560 703MC', 2519, 51, 7);
248insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('DELL LATITUDE E7240', 700, 51, 7);
249insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('HAIER HW60-14829', 259, 51, 8);
250insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('SAMSUNG WW70J5426FW/EN', 399, 51, 8);
251insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('SAMSUNG WF70F5E0Q4W ECO BUBBLE', 379, 51, 8);
252insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('RUSSELL HOBBS OXFORD WATERKOKER', 30, 51, 9);
253insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('PHILIPS HD4649 WATERKOKER', 35, 51, 9);
254insert into Artikel (Naam, Prijs, voorraad, categorieID) values ('EMERIO WK-110874 WATERKOKER', 20, 51, 9);
255
256
257INSERT INTO LeveranciersArtikel(ArtikelID, LeverancierID) VALUES ('1', '1');
258INSERT INTO LeveranciersArtikel(ArtikelID, LeverancierID) VALUES ('1', '2');
259INSERT INTO LeveranciersArtikel(ArtikelID, LeverancierID) VALUES ('2', '1');
260INSERT INTO LeveranciersArtikel(ArtikelID, LeverancierID) VALUES ('3', '1');
261
262insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (1, '1/29/2017', 26, 1);
263insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (5, '5/31/2016', 11, 3);
264insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '7/8/2016', 35, 1);
265insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (5, '7/30/2016', 49, 4);
266insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '10/16/2016', 41, 2);
267insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (5, '2/24/2017', 48, 10);
268insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '9/14/2016', 47, 3);
269insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '5/23/2016', 44, 9);
270insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (4, '11/4/2016', 3, 4);
271insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (2, '8/7/2016', 3, 4);
272insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '6/17/2016', 4, 2);
273insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (1, '12/3/2016', 23, 10);
274insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (1, '8/3/2016', 28, 3);
275insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (5, '5/2/2016', 6, 5);
276insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (2, '6/27/2016', 16, 2);
277insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '3/10/2017', 50, 4);
278insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '12/24/2016', 21, 6);
279insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '12/13/2016', 9, 10);
280insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (2, '7/23/2016', 37, 9);
281insert into Beoordeling (Sterren, Datum, klantID, ArtikelID) values (3, '12/25/2016', 15, 7);
282
283insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (10, 10, 1, 1, 1);
284insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (11, 3, 1, 2, 2);
285insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (13, 7, 7, null, null);
286insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (1, 7, 1, 2, 1);
287insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (7, 2, 1, 1, null);
288insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (6, 3, 1, null, 2);
289insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (12, 6, 1, 2, 1);
290insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (17, 1, 1 , null, 2);
291insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (19, 2, 1, 1,2);
292insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (4, 2, 1, null, 2);
293insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (2, 6, 7, 1, null);
294insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (3, 2, 1 , 2, 2);
295insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (10, 4, 1, null, 1);
296insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (17, 2, 1, 1, null);
297insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (1, 3, 2, 1, 2);
298insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (2, 10, 1, null, null);
299insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (5, 3, 8 , 2, 1);
300insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (17, 10, 1 , 1, 1);
301insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (19, 3, 10 ,2 , 2);
302insert into BesteldArtikel (bestellingid, artikelid, aantal, seizoenid, couponid) values (20, 4, 4, null, null);
303
304CREATE TYPE dbo.BestelLijst
305AS TABLE
306(
307 ArtikelID INT,
308 Aantal INT,
309 SeizoenID INT,
310 CouponID INT
311);
312GO
313
314CREATE TYPE dbo.StDEVTable
315AS TABLE
316(
317 Aantal INT,
318 artikelid int
319);
320GO
321
322Create procedure dbo.SlechtVerkopendeArtikelen
323as
324Begin
325DROP TABLE if exists SlechtVerkopendeProducten;
326
327CREATE TABLE SlechtVerkopendeProducten(
328ArtikelID int primary key)
329
330
331DECLARE @ArtikelId as INT;
332DECLARE @Voorraad as INT;
333DECLARE @Aantal as INT;
334DECLARE @Stdev as DECIMAL;
335DECLARE @Gem as DECIMAL;
336DECLARE @AantalProducten as INT;
337DECLARE @AantalVerkopen as INT;
338DECLARE @StDevList as dbo.StDevTable;
339
340INSERT into @StDevList SELECT sum(Aantal), ArtikelID from BesteldArtikel
341group by ArtikelID
342
343SET @AantalProducten = (select count(distinct artikelid) from BesteldArtikel)
344SET @AantalVerkopen = (select sum(aantal) from BesteldArtikel)
345SET @Gem = (@AantalVerkopen /@AantalProducten)
346SET @Stdev = (Select STDEVP(Aantal)
347 from @StDevList)
348
349DECLARE @ArtikelCursor as CURSOR;
350
351SET @ArtikelCursor = CURSOR FOR
352select a.artikelid, a.voorraad, sum(ba.aantal) as aantalb from Artikel a
353inner join BesteldArtikel ba on ba.ArtikelID=a.ArtikelID
354inner join Bestelling b on b.BestellingID=ba.BestellingID
355where a.Voorraad>(select avg(voorraad) from Artikel)
356group by a.ArtikelID, a.Voorraad
357having sum(ba.Aantal) < (@Gem-@Stdev)
358
359OPEN @ArtikelCursor;
360FETCH NEXT FROM @ArtikelCursor INTO @ArtikelId, @Voorraad, @Aantal;
361
362WHILE @@FETCH_STATUS = 0
363BEGIN
364
365INSERT INTO SlechtVerkopendeProducten(ArtikelID) values(@ArtikelId)
366
367--PRINT cast(@ArtikelId as VARCHAR (50)) + ' ' + cast(@Voorraad as VARCHAR (50)) + ' ' + cast(@Aantal as VARCHAR (50)) + ' ' + cast(@Stdev as VARCHAR (50)) + ' ' + cast(@Gem as VARCHAR (50));
368FETCH NEXT FROM @ArtikelCursor INTO @ArtikelId, @Voorraad, @Aantal;
369END
370
371CLOSE @ArtikelCursor;
372DEALLOCATE @ArtikelCursor;
373
374end
375go
376
377USE KillerApp2
378GO
379
380CREATE PROCEDURE dbo.PlaatsBestelling
381 @KlantID int,
382 @List AS dbo.BestelLijst READONLY,
383 @Datum date
384AS
385BEGIN
386 SET NOCOUNT ON;
387
388 INSERT INTO Bestelling(klantID, Datum) VALUES(@KlantID, @Datum)
389 DECLARE @Bestelid int
390 select @Bestelid = IDENT_CURRENT('Bestelling')
391 INSERT BesteldArtikel(BestellingID,ArtikelID, Aantal,SeizoenID,CouponID)
392 Select @Bestelid, ArtikelID, Aantal, SeizoenID, CouponID
393 From @List
394END
395GO
396
397use KillerApp2
398go
399
400Create Procedure dbo.VoegSeizoenToe
401@Percentage int,
402@Van date,
403@Tot date
404as
405begin
406set nocount on;
407INSERT INTO SeizoenKorting(BeginDatum, EindDatum, PercentageKorting) VALUES(@Van, @Tot, @Percentage)
408end
409go
410
411Create Procedure dbo.VoegCouponToe
412@Prijs int
413as
414begin
415set nocount on;
416INSERT INTO CouponKorting(Korting) VALUES(@Prijs)
417end
418go
419
420CREATE TRIGGER dbo.tr_UpdatePrijs
421 ON dbo.Besteldartikel
422 AFTER INSERT
423AS
424 Declare @Bestellingid int
425 set @Bestellingid = (SELECT MAX(b.BestellingID) from Bestelling b)
426BEGIN
427 SET NOCOUNT ON;
428 update Bestelling
429 set Prijs = (SELECT SUM((a.prijs *((100-isnull(sk.PercentageKorting,0))/100)-isnull(ck.Korting,0)) * ba.Aantal) from BesteldArtikel ba
430 inner join Artikel a on ba.ArtikelID=a.ArtikelID
431 inner join CouponKorting ck on ck.CouponID=ba.CouponID
432 inner join SeizoenKorting sk on sk.SeizoenID=ba.SeizoenID
433 where ba.BestellingID =@Bestellingid)
434 where BestellingID = @Bestellingid
435END
436GO
437
438CREATE TRIGGER [dbo].[tr_UpdateVoorraad]
439 ON [dbo].[BesteldArtikel]
440 AFTER INSERT
441AS
442
443DECLARE @Bestellingid int
444SET @Bestellingid = (select max(bestellingid) from Bestelling)
445
446BEGIN
447 DECLARE @Aantal int
448 DECLARE @Artikelid int
449
450 DECLARE @BestellingCursor as cursor
451
452 SET @BestellingCursor= CURSOR for
453 (Select ArtikelID, Aantal from BesteldArtikel
454 where BestellingID = @Bestellingid);
455
456 OPEN @BestellingCursor;
457 FETCH NEXT FROM @BestellingCursor INTO @Artikelid, @Aantal;
458
459 WHILE @@FETCH_STATUS = 0
460 BEGIN
461 UPDATE Artikel SET Voorraad = Voorraad -@Aantal Where ArtikelID=@Artikelid;
462
463 FETCH NEXT FROM @BestellingCursor INTO @Artikelid, @Aantal;
464
465 END
466
467 CLOSE @BestellingCursor;
468 DEALLOCATE @BestellingCursor;
469END
470GO