CREATE TABLE Customers
(CustomerID int AUTO_INCREMENT,
CompanyName varchar(100),
ContactName varchar(100),
ContactTitle varchar(100),
Adress varchar(400),
City varchar(30),
Region varchar(30),
PostalCode smallint,
Country varchar(30),
Phone smallint,
Fax smallint,
CONSTRAINT c_Customers_PK PRIMARY KEY(CustomerID));

INSERT INTO Customers
VALUES('1', 'Coca Cola', 'Ivan Atanasov', 'Manager', 'Sofia - Centur', 'Sofia', 'Sofia-grad', '1000', 'Bulgaria', '0885491456',
'2223233');

INSERT INTO Customers
VALUES('2', 'Fanta', 'Georgi Mladenov', 'SeniorManager', 'Mladost 1', 'Sofia', 'Sofia-grad', '1000', 'Bulgaria', '0885291456',
'2223234');

INSERT INTO Customers
VALUES('3', 'Sprite', 'Mladen Georgiev', 'Junior Manager', 'Mladost 2', 'Sofia', 'Sofia-grad', '1000', 'Bulgaria', '0885292456',
'2523234');

INSERT INTO Customers
VALUES('4', 'Pepsi', 'Kamen Damianov', 'Intern', 'Mladost 3', 'Sofia', 'Sofia-grad', '1000', 'Bulgaria', '0885291556',
'2623234');

INSERT INTO Customers
VALUES('5', 'Mirinda', 'Kalina Atanasova', 'Big Boss', 'Mladost 4', 'Sofia', 'Sofia-grad', '1000', 'Bulgaria', '0885291156',
'2223134');


CREATE TABLE Orders
(OrderID int AUTO_INCREMENT,
CustomerID int,
EmployeeID int,
OrderDate date,
RequiredDate date,
ShipDate date,
ShipName varchar(50),
ShipAdress varchar(100),
ShipCity varchar(39),
ShipRegion varchar(39),
ShipCountry varchar(20));
alter table Orders add CONSTRAINT c_Orders_PK PRIMARY KEY(OrderID),
alter table Orders add CONSTRAINT c_Orders_FK FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID);
alter table Orders add CONSTRAINT c_Orders_FK_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES employees(EmployeeID);

INSERT INTO Orders
VALUES('1', '1', '1', '2015-01-12', '2015-01-17', '2015-01-15', 'Titanik', 'Burgas, Morska gradina', 'Burgas',
'Burgas - grad', 'Bulgaria');

INSERT INTO Orders
VALUES('2', '2', '3', '2015-01-13', '2015-01-18', '2015-01-16', 'Britanik', 'Burgas, Morska gradina', 'Burgas',
'Burgas - grad', 'Bulgaria');

INSERT INTO Orders
VALUES('3', '4', '5', '2013-01-13', '2013-01-18', '2013-01-16', 'Britanik', 'Burgas, Morska gradina', 'Burgas',
'Burgas - grad', 'Bulgaria');

CREATE TABLE Products
(ProductID int AUTO_INCREMENT,
ProductName varchar(50),
SupplierID int,
CategoryID int,
UnitPrice double,
CONSTRAINT c_Products_PK PRIMARY KEY(ProductID));

alter table Products add CONSTRAINT c_Products_CategoryID FOREIGN KEY(CategoryID) REFERENCES Categories(CategoryID);
alter table Products add CONSTRAINT c_Products_SupplierID FOREIGN KEY(SupplierID) REFERENCES Suppliers(SupplierID);

insert into Products
Values ('1', 'Yellow coca', '1', '2', '300');

insert into Products
Values ('2', 'Brown coca', '3', '3', '300');

insert into Products
Values ('3', 'White coca', '4', '1', '300');

insert into Products
Values ('4', 'Orange coca', '2', '2', '300');

insert into Products
Values ('5', 'Sprite coca', '2', '3', '300');

create table Order_Details
(OrderID int,
ProductID int,
UnitPrice float,
Quantity smallint,
Discount decimal);
alter table Order_Details add CONSTRAINT c_Order_Product_FK FOREIGN KEY(ProductID) REFERENCES Products(ProductID);
alter table Order_Details add CONSTRAINT c_Order_Order_FK FOREIGN KEY(OrderID) REFERENCES Orders(OrderID);

create table Categories
(CategoryID int AUTO_INCREMENT,
CategoryName varchar(50),
Description varchar(250),
CONSTRAINT c_Categories_PK PRIMARY KEY(CategoryID));

INSERT INTO categories
VALUES ('1', 'Drinks', 'Non-alcoholic drinks for every day');

INSERT INTO categories
VALUES ('2', 'Foods', 'Junk food for fat people');

INSERT INTO categories
VALUES ('3', 'Shirts', 'Male and female shirts');

create table Employees
(EmployeeID int AUTO_INCREMENT,
LastName varchar(30),
FirstName varchar(30),
Title varchar(30),
TitleOfCourtsey varchar(30),
BirthDate date,
HireDate date,
Adress text,
City varchar(41),
Region varchar(41),
PostalCode smallint,
Country varchar(30),
HomePhone smallint,
ReportsTo int REFERENCES Employees(EmployeeID),
Salary decimal,
CONSTRAINT c_Employees_PK PRIMARY KEY (EmployeeID));
alter table Employees add CONSTRAINT c_Rddasd FOREIGN KEY(ReportsTo) REFERENCES Employees(EmployeeID);

ALTER TABLE employees  ADD Email varchar(200);
alter table employees DROP Email;

INSERT INTO employees
VALUES ('1', 'Berbatov', 'Dimitar', 'player', 'master', '1980-01-01', '2015-01-01', 'Sofia, bul. Bulgaria 3', 'Sofia',
'Sofia-grad', '1000', 'Bulgaria', '8769778', '1', '1050');

INSERT INTO employees
VALUES ('2', 'Velev', 'Kristian', 'SEO', 'master', '1988-01-01', '2016-01-01', 'Sofia, bul. Bulgaria 3', 'Sofia',
'Sofia-grad', '1000', 'Bulgaria', '8765778', '1', '10500');

INSERT INTO employees
VALUES ('3', 'Nikolaeva', 'Valentina', 'grandmother', 'master', '1936-01-01', '2016-01-01', 'Sofia, bul. Bulgaria 3', 'Sofia',
'Sofia-grad', '1000', 'Bulgaria', '8765773', '2', '10500');

INSERT INTO employees
VALUES ('4', 'Omonigo', 'Temile', 'black pirat', 'non-master', '1972-01-01', '2016-03-01', 'Sofia, bul. Bulgaria 3', 'Sofia',
'Sofia-grad', '1000', 'Bulgaria', '8725773', '1', '100');

INSERT INTO employees
VALUES ('5', 'Siderov', 'Volen', 'psyhopat', 'politic', '1974-01-01', '2013-03-01', 'Sofia, bul. Bulgaria 3', 'Sofia',
'Sofia-grad', '1000', 'Bulgaria', '8725773', '3', '930');

create table OrderDetails
(OrderID int,
ProductID int,
UnitPrice int,
Quantity int,
Discount decimal);
ALTER table OrderDetails add CONSTRAINT c_Order_FK FOREIGN KEY(OrderID) REFERENCES Orders(OrderID);
ALTER table orderdetails add CONSTRAINT c_Product_FK FOREIGN KEY(ProductID) REFERENCES Products(ProductID);

insert into orderdetails
values('1', '2', '40005', '23', '20');

insert into orderdetails
values('2', '1', '50005', '22', '22');

insert into orderdetails
values('3', '4', '20005', '12', '12');

insert into suppliers
values('1', 'Nike', 'Cristiano Ronaldo', 'Sales manager', 'Drujba 2', 'Miami', 'USA - 1', 'USA', '223', '2234',
'Nike.com');

insert into suppliers
values('2', 'Adidas', 'Lionel Messi', 'Contact manager', 'Drujba 3', 'Miami', 'USA - 1', 'USA', '224', '2233',
'Adidasbatka.com');

insert into suppliers
values('3', 'Puma', 'Novak Djokovic', 'Super manager', 'Drujba 4', 'Miami', 'USA - 1', 'USA', '225', '2234',
'Puma-Maimuna.com');

insert into suppliers
values('4', 'Reebok', 'Plamen Markov', 'Mega manager', 'Drujba 5', 'Miami', 'USA - 1', 'USA', '226', '2235',
'Reebok-bratok.com');

select Salary
from employees;

update employees
set Salary = salary + 100
where salary >= 0;

delete from employees
where salary < 1000;

delete e
from employees e
left join orders o ON e.EmployeeID = o.EmployeeID
where e.EmployeeID = null

alter table products drop FOREIGN key c_Products_SupplierID;
drop table suppliers;