CREATE DATABASE Shop;

USE Shop;



CREATE TABLE ProductCategory(
	Id int  PRIMARY KEY,
	Name varchar(50) NOT NULL);
	
	
Insert INTO ProductCategory VALUES
(1, 'Computer'),
(2, 'Accessory'),
(3, 'Drive'),
(4, 'Printer');



CREATE TABLE Product(
	Id int PRIMARY KEY ,
	Name varchar(50) NOT NULL,
	Price float(18, 0) NOT NULL,
	CategoryId int NOT NULL);

Insert into Product 
 VALUES 
  (1, 'Laptop', 2000, 1),
  (2, 'Mouse', 50, 2),
  (3, 'Keyboard', 300, 2),
  (4, 'Flash drive', 100, 3),
  (5, 'Hard drive', 400, 3);



CREATE TABLE Orders(
	Id int PRIMARY KEY ,
	OrderNumber varchar(50) NOT NULL,
	OrderDate datetime NOT NULL,
	OrderTotal float(18, 0) NOT NULL,
	EmployeeId int NOT NULL);
	

INSERT INTO Orders 
 VALUES 
(1, 'Z/13/2014', '2014-01-14', 55, 3),
 (2, 'Z/25/2015', '2015-02-21', 668, 5),
 (3, 'Z/30/2015', '2015-06-29', 2890, 1),
 (5, 'Z/45/2015', '2015-07-24', 800, 3),
 (6, 'Z/50/2015', '2015-08-02', 2700, 4);



CREATE TABLE OrderDetails(
	Id int  PRIMARY KEY,
	OrderId int NOT NULL,
	ProductId int NOT NULL,
	Quantity int NOT NULL,
	UnitCost numeric(18, 0) NOT NULL)
;
INSERT INTO OrderDetails 
 VALUES (2, 1, 2, 1, 55),
(3, 2, 2, 1, 55),
 (4, 2, 4, 2, 99),
 (5, 2, 5, 1, 415),
(6, 3, 1, 2, 1900),
 (7, 3, 2, 2, 45),
 (8, 5, 2, 20, 40);



CREATE TABLE FormerEmployee(
	Id int PRIMARY KEY,
	FirstName varchar(50) NOT NULL,
	SecondName varchar(50) NULL,
	LastName varchar(50) NULL,
	Gender char(1) NULL,
	BirthDate date NULL,
	Salary decimal(6, 2) NULL,
	DepartmentId int NULL,
	HireDate date NULL,
	RelieveDate date NULL);

INSERT INTO FormerEmployee (Id, FirstName, SecondName, LastName, Gender, BirthDate, Salary, DepartmentId, HireDate, RelieveDate) 
VALUES (6, 'John', NULL, 'Miller', 'W', '1993-10-20', 2000.00, 4, '2013-03-27', '2014-10-06');
INSERT INTO FormerEmployee (Id, FirstName, SecondName, LastName, Gender, BirthDate, Salary, DepartmentId, HireDate, RelieveDate) VALUES (11, 'Ann', NULL,'Pritchett', 'M', '1980-05-21', 2400.00, 6, '2014-01-05', '2015-02-01');


CREATE TABLE Employee(
	Id int AUTO_INCREMENT PRIMARY KEY,
	FirstName varchar(50) NOT NULL,
	SecondName varchar(50) NULL,
	LastName varchar(50) NULL,
	Gender char(1) NULL,
	BirthDate date NULL,
	Salary decimal(6, 2) NULL,
	DepartmentId int NULL,
	HireDate date NULL,
	RelieveDate date NULL,
	Active bit NULL);

INSERT INTO Employee VALUES 
(1, 'Mary', NULL, 'Smith', 'W', '1985-12-18', 2000.00, 1, '2011-05-06', NULL, 1),
(2, 'Paul', 'George', 'Johnson', 'M', '1990-01-13', 1500.00, 3, '2011-10-29', NULL, 1),
 (3, 'Patricia', NULL, 'Wilson', 'W', '1995-05-28', 1000.00, 4, '2012-01-24', '2013-11-25', 0),
 (4, 'Linda', 'Lisa', 'Brown', 'W', '1983-07-11', 2700.00, 2, '2012-06-15', NULL, 1),
 (5, 'Johnata', NULL, 'Davis', 'M', '1978-01-24', 1200.00, 1, '2013-01-15', NULL, 1),
 (6, 'John', NULL, 'Miller', NULL, '1993-10-20', 2000.00, 4, '2013-03-27', '2014-10-06', 0),
(7,'Elizabeth','Dorothy', 'Taylor','W','1992-09-09', 2110.00,1,'2013-05-01',NULL,1), 
(8, 'James', NULL, 'Wilson', 'M', '1978-03-19', 1000.00, 3, '2013-10-09', '2014-03-01', 1),
 (9, 'Robert', NULL, 'Anderson', 'M', '1989-12-13', 2900.00, 2, '2013-12-20', '2014-05-05', 0),
 (10, 'John', 'Mark', 'Smith', 'W', '1990-05-24', 2500.00, 1, '2014-01-24', NULL, 1);



CREATE TABLE Department(
	Id int AUTO_INCREMENT PRIMARY KEY NOT NULL,
	Name varchar(50) NOT NULL,
	ManagerId int NULL,
	CityId int NULL);

INSERT INTO Department 
VALUES (1, 'IT', 5, 2),
(2, 'Accounting', 4, NULL),
 (3, 'HR', 8, 2),
(4, 'Marketing', 3, 1),
(5, 'Controlling', 11, 3),
(6, 'Data Warehouse', 5, 4);


CREATE TABLE Customer(
	Id int AUTO_INCREMENT  PRIMARY KEY NOT NULL,
	Name varchar(50) NOT NULL,
	PhoneNumber varchar(50) NOT NULL);



CREATE TABLE Company(
	Id int AUTO_INCREMENT PRIMARY KEY NOT NULL,
	Name varchar(50) NOT NULL,
	Street varchar(50) NULL,
	Number varchar(50) NULL,
	PostCode varchar(50) NULL,
	CityId int NULL,
	StartDate date NULL,
	EmployeeCount int NULL,
	AnnualIncome int NULL,
	Active bit NOT NULL) DEFAULT CHARSET=utf8 ;
 
INSERT INTO Company (Id, Name, Street, Number, PostCode, CityId, StartDate, EmployeeCount, AnnualIncome, Active)
 VALUES (1, 'Casio', 'Akacjowa', '10', '54-621', 5, '1993-03-18', 1000, 145000, 1),
 (2, 'Kawasaki', 'Brzozowa', '12/5', '69-586', 3, '2001-05-25', 570, 596000, 1),
 (3, 'Prada', 'Holenderska', '18B', '59-100', 4, '1982-03-17', 1350, 120000, 1),
 (4, 'Nokia', 'Trójkątna', '16/9', '68-487', 5, '2005-08-19', 800, 65000, 1),
 (5, 'Tesco', 'Lipowa', '56', '36-895', 2, '2008-12-12', 450, 89000, 1),
 (6, 'Shell', 'Niedźwiedzia', '12', '47-895', 3, '1999-10-30', 1100, 19000, 1),
 (7, 'Pepsi', 'Hłaski', '84', '56-156', NULL, '2011-05-13', 790, 300000, 1),
 (8, 'Lenovo Group', 'Grecka', '129', '69-895', 4, '2008-01-19', 900, 78000, 0),
 (9, 'Kyocera', NULL, '15/85', '54-785', 2, '1999-01-24', 1500, 12000, 1),
 (10, 'Motorola', 'Lotnicza', '12B/5', '78-695', 5, NULL, 2000, 36000, 1),
 (11, 'Xerox', 'Topolowa', '13', '56-469', 1, '2004-09-09', 1700, 69000, 1),
 (12, 'Phillips', 'Słowiańska', '54', '65-916', NULL, '2000-09-28', 850, 80000, 1),
 (13, 'Red Hat', 'Brzozowa', '1', '54-608', 1, '1998-08-31', 290, 29000, 1),
 (14, 'Skanska', 'Lotnicza', '17A/8', '63-896', 4, '2010-02-14', 670, 47000, 0),
 (15, 'Vodafone', 'Rodziewiczówny', '26', '759-62', 3, NULL, 800, 19000, 1),
 (16, 'Yamaha', 'Kolista', '53', '75-647', 3, '1980-07-14', 1350, 299000, 1),
 (17, 'Twitter', 'Sosnowa', '18/6', '68-954', 5, '2013-01-15', 1900, 48000, 1);


CREATE TABLE City(
	Id int AUTO_INCREMENT PRIMARY KEY NOT NULL,
	Name varchar(50) NOT NULL);


INSERT INTO City 
VALUES (1, 'New York'),
(2, 'Warsaw'),
 (3, 'London'),
(4, 'Paris'),
(5, 'Venice');



CREATE TABLE ArchivedProduct(
	Id int NOT NULL PRIMARY KEY,
	Name varchar(50) NOT NULL,
	Price float(18, 0) NOT NULL,
	CategoryId int NOT NULL);


INSERT INTO ArchivedProduct (Id, Name, Price, CategoryId) VALUES (3, 'Keyboard', 300, 2);
INSERT INTO ArchivedProduct (Id, Name, Price, CategoryId) VALUES (6, 'Monitor', 900, 2);