7Zq8FW9D

1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
2/*!40101 SET NAMES utf8 */;
3/*!50503 SET NAMES utf8mb4 */;
4/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
5/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
6/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
7
8CREATE TABLE IF NOT EXISTS `department` (
9  `Department_id` int NOT NULL AUTO_INCREMENT,
10  `Worker_id` int NOT NULL,
11  `net_income_id` int NOT NULL,
12  `Expenses_id` int NOT NULL,
13  PRIMARY KEY (`Department_id`),
14  KEY `FK_department_workers` (`Worker_id`),
15  KEY `FK_department_net_income` (`net_income_id`),
16  KEY `FK_department_expenses` (`Expenses_id`),
17  CONSTRAINT `FK_department_expenses` FOREIGN KEY (`Expenses_id`) REFERENCES `expenses` (`Expenses_id`) ON DELETE CASCADE,
18  CONSTRAINT `FK_department_net_income` FOREIGN KEY (`net_income_id`) REFERENCES `net_income` (`net_income_id`) ON DELETE CASCADE,
19  CONSTRAINT `FK_department_workers` FOREIGN KEY (`Worker_id`) REFERENCES `workers` (`Worker_id`) ON DELETE CASCADE ON UPDATE RESTRICT
20) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='зберігає інформацію про відділи';
21
22CREATE TABLE IF NOT EXISTS `departure` (
23  `Departure_id` int NOT NULL AUTO_INCREMENT,
24  `Department_id` int NOT NULL,
25  `Worker_id` int NOT NULL,
26  `Departure_time` date NOT NULL COMMENT 'Час відправки',
27  `time_to_rec` date NOT NULL COMMENT 'Час отримання',
28  `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Тип відправлення (send, receive)',
29  `name` varchar(50) NOT NULL,
30  `price` float NOT NULL,
31  `amounth` int NOT NULL,
32  `address` varchar(255) NOT NULL,
33  `city` varchar(50) NOT NULL,
34  `time_id` int NOT NULL,
35  PRIMARY KEY (`Departure_id`),
36  KEY `time_id` (`time_id`),
37  KEY `Worker_id` (`Worker_id`),
38  KEY `Department_id` (`Department_id`),
39  CONSTRAINT `FK_departure_department` FOREIGN KEY (`Department_id`) REFERENCES `department` (`Department_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
40  CONSTRAINT `FK_departure_time` FOREIGN KEY (`time_id`) REFERENCES `time` (`Time_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
41  CONSTRAINT `FK_departure_workers` FOREIGN KEY (`Worker_id`) REFERENCES `workers` (`Worker_id`) ON DELETE CASCADE ON UPDATE RESTRICT
42) ENGINE=InnoDB DEFAULT CHARSET=utf8;
43
44CREATE TABLE IF NOT EXISTS `expenses` (
45  `Expenses_id` int NOT NULL AUTO_INCREMENT,
46  `Department_cost` float NOT NULL COMMENT 'вистрати на роботу відділу',
47  `salaries` float NOT NULL COMMENT 'зп',
48  `logistic_services` float NOT NULL COMMENT 'витрати на логістику',
49  `execution_of_items` float NOT NULL COMMENT 'витрати на оформлення речей',
50  PRIMARY KEY (`Expenses_id`)
51) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='зберігає інформацію про витрати';
52
53CREATE TABLE IF NOT EXISTS `net_income` (
54  `net_income_id` int NOT NULL AUTO_INCREMENT,
55  `Department_id` int NOT NULL,
56  `Expenses_id` int NOT NULL,
57  PRIMARY KEY (`net_income_id`),
58  KEY `FK_net_income_department` (`Department_id`),
59  KEY `FK_net_income_expenses` (`Expenses_id`),
60  CONSTRAINT `FK_net_income_department` FOREIGN KEY (`Department_id`) REFERENCES `department` (`Department_id`) ON DELETE CASCADE,
61  CONSTRAINT `FK_net_income_expenses` FOREIGN KEY (`Expenses_id`) REFERENCES `expenses` (`Expenses_id`) ON DELETE CASCADE
62) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='зберігає інформацію про чистий дохід';
63
64CREATE TABLE IF NOT EXISTS `time` (
65  `Time_id` int NOT NULL AUTO_INCREMENT,
66  `date` date NOT NULL,
67  `time` time NOT NULL,
68  PRIMARY KEY (`Time_id`)
69) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='зберігає інформацію про час відправлення';
70
71CREATE TABLE IF NOT EXISTS `workers` (
72  `Worker_id` int NOT NULL AUTO_INCREMENT,
73  `name` varchar(50) NOT NULL,
74  `sname` varchar(50) NOT NULL,
75  `bday` date NOT NULL,
76  `phone` varchar(50) NOT NULL,
77  `address` varchar(255) NOT NULL,
78  PRIMARY KEY (`Worker_id`)
79) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='зберігає інформацію про працівників ';
80
81/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
82/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
83/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
84/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
85
86
87#==============================================================
88
89#1 Завдання
90SELECT d.* FROM departure AS d
91LEFT JOIN time AS t ON d.time_id = t.Time_id
92LEFT JOIN workers AS w ON d.Worker_id = w.Worker_id
93WHERE d.type = "send";
94
95#2 Завдання
96SELECT d.* FROM departure AS d
97LEFT JOIN time AS t ON d.time_id = t.Time_id
98LEFT JOIN workers AS w ON d.Worker_id = w.Worker_id
99WHERE d.type = "send";
100
101#3 Завдання
102SELECT d.Department_id, e.Department_cost FROM department AS d
103LEFT JOIN expenses AS e ON d.Expenses_id = e.Expenses_id
104WHERE d.Department_id = 11;
105
106#4 Завдання
107SELECT d.Department_id, e.salaries FROM department AS d
108LEFT JOIN expenses AS e ON d.Expenses_id = e.Expenses_id
109WHERE d.Department_id = 11;
110
111#5 Завдання
112SELECT d.Department_id, e.execution_of_items FROM department AS d
113LEFT JOIN expenses AS e ON d.Expenses_id = e.Expenses_id
114WHERE d.Department_id = 11;
115
116#6 Завдання
117SELECT d.Department_id, e.logistic_services FROM department AS d
118LEFT JOIN expenses AS e ON d.Expenses_id = e.Expenses_id
119WHERE d.Department_id = 11;
120
121#7 Завдання
122SELECT d.* FROM departure AS d
123LEFT JOIN time AS t ON d.time_id = t.Time_id
124LEFT JOIN workers AS w ON d.Worker_id = w.Worker_id
125WHERE d.time_to_rec BETWEEN "2021-03-21" AND "2021-05-21" ;
126
127#8 Завдання
128
129