CREATE DATABASE

CREATE TABLE `Clients` (
	`Client_ID` INT NOT NULL AUTO_INCREMENT,
	`First_Name` VARCHAR(255) NOT NULL,
	`Second_Name` VARCHAR(255) NOT NULL,
	`Phone_Number` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`Client_ID`)
);

CREATE TABLE `Technique` (
	`Technique_ID` INT NOT NULL AUTO_INCREMENT,
	`Manufacturer` VARCHAR(255) NOT NULL,
	`Model` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`Technique_ID`)
);

CREATE TABLE `Orders` (
	`Order_ID` INT NOT NULL AUTO_INCREMENT,
	`Client_ID` INT NOT NULL,
	`Work_ID` INT NOT NULL,
	`Cost` FLOAT NOT NULL,
	`Status` BOOLEAN NOT NULL,
	`Payment_ID` INT NOT NULL,
	`Date` TIME NOT NULL,
	PRIMARY KEY (`Order_ID`)
);

CREATE TABLE `Purchase_Parts` (
	`Work_ID` INT NOT NULL,
	`Detail_ID` INT NOT NULL,
	`Amount` INT NOT NULL
);

CREATE TABLE `Details` (
	`Detail_ID` INT NOT NULL AUTO_INCREMENT,
	`Name` VARCHAR(255) NOT NULL,
	`Availability` BINARY NOT NULL,
	`Cost` FLOAT NOT NULL,
	PRIMARY KEY (`Detail_ID`)
);

CREATE TABLE `Providers` (
	`Provider_ID` INT NOT NULL AUTO_INCREMENT,
	`Detail_ID` INT NOT NULL,
	PRIMARY KEY (`Provider_ID`)
);

CREATE TABLE `Works` (
	`Order_ID` INT NOT NULL,
	`Work_ID` INT NOT NULL AUTO_INCREMENT,
	`Technique_ID` INT NOT NULL,
	`Worker_ID` INT NOT NULL,
	`Details_ID` INT NOT NULL,
	`Work_Price` FLOAT NOT NULL,
	PRIMARY KEY (`Work_ID`)
);

CREATE TABLE `Payment` (
	`Payment_ID` INT NOT NULL AUTO_INCREMENT,
	`Payment_Type` VARCHAR(255) NOT NULL,
	`Payment_Fees` INT NOT NULL,
	PRIMARY KEY (`Payment_ID`)
);

CREATE TABLE `Workers` (
	`Worker_ID` INT NOT NULL,
	`Department` INT NOT NULL,
	`First_Name` VARCHAR(255) NOT NULL,
	`Second_Name` VARCHAR(255) NOT NULL,
	`Phone_Number` INT NOT NULL,
	PRIMARY KEY (`Worker_ID`)
);

CREATE TABLE `Department` (
	`Department_ID` INT NOT NULL AUTO_INCREMENT,
	`Department_Name` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`Department_ID`)
);

ALTER TABLE `Orders` ADD CONSTRAINT `Orders_fk0` FOREIGN KEY (`Client_ID`) REFERENCES `Clients`(`Client_ID`);

ALTER TABLE `Orders` ADD CONSTRAINT `Orders_fk1` FOREIGN KEY (`Payment_ID`) REFERENCES `Payment`(`Payment_ID`);

ALTER TABLE `Purchase_Parts` ADD CONSTRAINT `Purchase_Parts_fk0` FOREIGN KEY (`Work_ID`) REFERENCES `Works`(`Work_ID`);

ALTER TABLE `Purchase_Parts` ADD CONSTRAINT `Purchase_Parts_fk1` FOREIGN KEY (`Detail_ID`) REFERENCES `Details`(`Detail_ID`);

ALTER TABLE `Providers` ADD CONSTRAINT `Providers_fk0` FOREIGN KEY (`Detail_ID`) REFERENCES `Details`(`Detail_ID`);

ALTER TABLE `Works` ADD CONSTRAINT `Works_fk0` FOREIGN KEY (`Order_ID`) REFERENCES `Orders`(`Order_ID`);

ALTER TABLE `Works` ADD CONSTRAINT `Works_fk1` FOREIGN KEY (`Technique_ID`) REFERENCES `Technique`(`Technique_ID`);

ALTER TABLE `Works` ADD CONSTRAINT `Works_fk2` FOREIGN KEY (`Worker_ID`) REFERENCES `Workers`(`Worker_ID`);

ALTER TABLE `Workers` ADD CONSTRAINT `Workers_fk0` FOREIGN KEY (`Department`) REFERENCES `Department`(`Department_ID`);


INSERT INTO  Payment VALUES
(2,N'Безналичный расчет',2),
(3,N'Банковский перевод',5);

INSERT INTO department VALUES
(1,N'Ремонт бытовой техники'),
(2,N'Ремонт компьютеров'),
(3,N'Ремонт мобильной техники');

INSERT INTO workers VALUES
(1,1,'Владислав','Петров',123123),
(2,1,'Алексий','Петро',123123),
(3,2,'Евгений','Петров',123123),
(4,3,'Иван','Иванов',123123),
(5,1,'Максим','Максимов',123123),
(6,3,'Александр','Александров',123123),
(7,3,'Александр','Сидоров',123123),
(8,1,'Алексей','Алексеев',123123);

INSERT INTO details VALUES
(2,'GTX660',1,1300),
(3,'Ryzen 3600',1,4100),
(4,'GTX1660',1,1040),
(5,'GTX1660 Super',1,1800),
(6,'GTX 2060',1,10405),
(7,'9400f',1,1030),
(8,'Test16',1,1005),
(9,'ARMv7',1,1400);



 /* Декартово произв.*/
 SELECT * from Workers CROSS JOIN Payment


/* объединение по условию*/
 SELECT Workers.Worker_ID, Workers.Second_Name,Department.Department_Name
 FROM Workers
 INNER JOIN Department ON (Workers.Department=Department.Department_ID);

SELECT * from Workers
WHERE Second_Name IN('Иванов','Алексеев');


SELECT DISTINCT Payment_Type,Payment_fees,
	CASE
	WHEN Payment_fees < 3.0 THEN'Все норм'
		ELSE 'Дороговато'
	END Vyvod
FROM Payment;

SELECT * FROM Payment ORDER BY Payment_fees DESC;
SELECT * FROM Payment ORDER BY Payment_fees ASC;

SELECT MIN(Payment_fees) AS Min_fees
FROM Payment;


SELECT MAX(Detail_ID),Name from Details GROUP BY Name HAVING Name LIKE '%16%';

SELECT DATE_ADD('2018-01-27T13:00:00', interval '01T00:00:15'  DAY_SECOND);

SELECT * from Worker
WHERE Worker_ID BETWEEN 2 AND 3