CREATE DATABASE Swaen_Buitensport;
GO;
USE Swaen_Buitensport;
GO;
 
DROP TABLE [dbo.BESTELLING_ITEM];
CREATE TABLE [dbo.BESTELLING_ITEM] (
		Bestellingnummer int default NULL,
		Artikelnummer int NOT NULL,
		Aantal int NOT NULL,
		Stukprijs decimal(9,4) NOT NULL,
		Totaalprijs decimal(9,4) NOT NULL);
 
DELETE FROM [dbo.BESTELLING_ITEM];
INSERT INTO [dbo.BESTELLING_ITEM] (Bestellingnummer,Artikelnummer,Aantal,Stukprijs,Totaalprijs) VALUES
		(3000,100200,1,'300.0000','300.0000'),
		(2000,101100,4,'50.0000','200.0000'),
		(3000,101100,2,'50.0000','100.0000'),
		(2000,101200,2,'50.0000','100.0000'),
		(3000,101200,1,'50.0000','50.0000'),
		(1000,201000,1,'300.0000','300.0000'),
		(1000,202000,1,'130.0000','130.0000');
 
DROP TABLE [dbo.BESTELLING]
CREATE TABLE [dbo.BESTELLING] (
		Bestellingnummer int NOT NULL default '0',
		Winkelnummer int default NULL,
		Postcode varchar(9) default NULL,
		Maand varchar(12) default NULL,
		Jaar int default NULL,
		Bestellingtotaal decimal(9,4) default NULL);
 
DELETE FROM [dbo.BESTELLING];
INSERT INTO [dbo.BESTELLING] (Bestellingnummer,Winkelnummer,Postcode,Maand,Jaar,Bestellingtotaal) VALUES
		(2000,20,'02335','december',2008,'310.0000'),
		(3000,10,'98110','januari',2009,'480.0000'),
		(1000,10,'98110','december',2008,'445.0000');

DROP TABLE [dbo.BESTELLING] 
CREATE TABLE [dbo.ARTIKEL] (
		Artikelnummer int NOT NULL,
		Artikelomschrijving varchar(35) default NULL,
		Afdeling varchar(30) default NULL,
		Inkoper varchar(30) default NULL);
 
DELETE FROM [dbo.ARTIKEL];
INSERT INTO [dbo.ARTIKEL] (Artikelnummer,Artikelomschrijving,Afdeling,Inkoper) VALUES
		(100100,'Standaard SCUBA, geel','Watersport','Peter Jansen'),
		(100200,'Standaard SCUBA, magenta','Watersport','Peter Jansen'),
		(101100,'Duikmasker, small, helder','Watersport','Najma Barends'),
		(101200,'Duikmasker, medium, helder','Watersport','Najma Barends'),
		(201000,'Halvekoepeltent','Camping','Charlotte Laan'),
		(202000,'Grondzeil halvekoepeltent','Camping','Charlotte Laan'),
		(301000,'Lichtgewicht klimharnas','Klimmen','Johan Martens'),
		(302000,'Karabijnsluiting, ovaal','Klimmen','Johan Martens');

DROP TABLE [dbo.WAREHOUSE]
CREATE TABLE [dbo.WAREHOUSE] (
		Warehouse varchar(32) NOT NULL,
		Manager varchar(45) NOT NULL,
		SquareFeet bigint NOT NULL);

DELETE FROM [dbo.WAREHOUSE];
INSERT INTO [dbo.WAREHOUSE] (Warehouse,Manager,SquareFeet) VALUES
		('Atlanta','Jones',125000),
		('Chicago','Smith',100000),
		('New Jersey','Evans',150000),
		('Seattle','Rogers',130000);
 
DROP TABLE [dbo.INVENTORY] -- IF EXISTS INVENTORY;
CREATE TABLE [dbo.INVENTORY] (
		Artikelnummer int NOT NULL,
		Description varchar(128) NOT NULL,
		QuantityOnHand int default NULL,
		QuantityOnOrder int default NULL,
		Warehouse varchar(32) NOT NULL);

DELETE FROM [dbo.INVENTORY];
INSERT INTO [dbo.INVENTORY] (Artikelnummer,Description,QuantityOnHand,QuantityOnOrder,Warehouse) VALUES
		(100100,'Std. Scuba Tank, Yellow',250,0,'Atlanta'),
		(100100,'Std. Scuba Tank, Yellow',100,50,'Chicago'),
		(100100,'Std. Scuba Tank, Yellow',100,NULL,'New Jersey'),
		(100100,'Std. Scuba Tank, Yellow',200,NULL,'Seattle'),
		(100200,'Std. Scuba Tank, Magenta',200,30,'Atlanta'),
		(100200,'Std. Scuba Tank, Magenta',75,75,'Chicago'),
		(100200,'Std. Scuba Tank, Magenta',100,100,'New Jersey'),
		(100200,'Std. Scuba Tank, Magenta',250,NULL,'Seattle'),
		(101100,'Duikmasker, small, helder',0,500,'Atlanta'),
		(101100,'Duikmasker, small, helder',0,500,'Chicago'),
		(101100,'Duikmasker, small, helder',300,200,'New Jersey'),
		(101100,'Duikmasker, small, helder',450,0,'Seattle'),
		(101200,'Duikmasker, medium, helder',100,500,'Atlanta'),
		(101200,'Duikmasker, medium, helder',50,500,'Chicago'),
		(101200,'Duikmasker, medium, helder',475,NULL,'New Jersey'),
		(101200,'Duikmasker, medium, helder',250,250,'Seattle'),
		(201000,'Halvekoepeltent',2,100,'Atlanta'),
		(201000,'Halvekoepeltent',10,250,'Chicago'),
		(201000,'Halvekoepeltent',250,0,'New Jersey'),
		(201000,'Halvekoepeltent',0,250,'Seattle'),
		(202000,'Grondzeil halvekoepeltent',10,250,'Atlanta'),
		(202000,'Grondzeil halvekoepeltent',1,250,'Chicago'),
		(202000,'Grondzeil halvekoepeltent',100,NULL,'New Jersey'),
		(202000,'Grondzeil halvekoepeltent',0,200,'Seattle'),
		(301000,'Light Fly Climbing Harness',300,250,'Atlanta'),
		(301000,'Light Fly Climbing Harness',250,250,'Chicago'),
		(301000,'Light Fly Climbing Harness',0,250,'New Jersey'),
		(301000,'Light Fly Climbing Harness',0,250,'Seattle'),
		(302000,'Locking carabiner',1000,0,'Atlanta'),
		(302000,'Locking carabiner',1250,NULL,'Chicago'),
		(302000,'Locking carabiner',500,500,'New Jersey'),
		(302000,'Locking carabiner',0,1000,'Seattle');