BmKCDcXj

1DROP TABLE IF EXISTS client CASCADE;
2DROP TABLE IF EXISTS dealer CASCADE;
3DROP TABLE IF EXISTS contract CASCADE;
4DROP TABLE IF EXISTS repair CASCADE;
5DROP TABLE IF EXISTS master CASCADE;
6DROP TABLE IF EXISTS STO CASCADE;
7DROP TABLE IF EXISTS fix CASCADE;
8DROP SEQUENCE IF EXISTS s_client CASCADE;
9DROP SEQUENCE IF EXISTS s_dealer CASCADE;
10DROP SEQUENCE IF EXISTS s_contract CASCADE;
11DROP SEQUENCE IF EXISTS s_repair CASCADE;
12DROP SEQUENCE IF EXISTS s_master CASCADE;
13DROP SEQUENCE IF EXISTS s_STO CASCADE;
14DROP SEQUENCE IF EXISTS s_fix CASCADE;
15DROP FUNCTION IF EXISTS sumSale(name text) CASCADE ;
16DROP FUNCTION IF EXISTS updatePriceSale()  CASCADE ;
17DROP FUNCTION IF EXISTS kolv(N INT) CASCADE ;
18DROP FUNCTION IF EXISTS kolvD(N INT) CASCADE ;
19DROP TABLE IF EXISTS total_priceSale_and_commission CASCADE ;
20DROP FUNCTION IF EXISTS checkId() CASCADE ;
21DROP FUNCTION IF EXISTS add_dealer() CASCADE ;
22
23CREATE SEQUENCE s_client;
24CREATE SEQUENCE s_dealer;
25CREATE SEQUENCE s_contract;
26CREATE SEQUENCE s_repair;
27CREATE SEQUENCE s_master;
28CREATE SEQUENCE s_STO;
29CREATE SEQUENCE s_fix;
30
31CREATE TABLE IF NOT EXISTS STO (
32id INT PRIMARY KEY DEFAULT nextval('s_STO'),
33name text NOT NULL,
34address text NOT NULL,
35phone text NOT NULL,
36email text DEFAULT null,
37dateFounding text NOT NULL,
38assessment float DEFAULT NULL
39);
40
41CREATE TABLE IF NOT EXISTS client (
42id INT PRIMARY KEY DEFAULT nextval('s_client'),
43fullName text NOT NULL,
44cityName text NOT NULL,
45phone text NOT NULL,
46address text NOT NULL
47);
48
49CREATE TABLE IF NOT EXISTS dealer (
50id INT PRIMARY KEY DEFAULT nextval('s_dealer'),
51fullName text NOT NULL,
52carView text NOT NULL,
53photoNumber int NOT NULL,
54phone text NOT NULL,
55address text NOT NULL
56);
57
58CREATE TABLE IF NOT EXISTS fix (
59id INT PRIMARY KEY DEFAULT nextval('s_fix'),
60nameFix text NOT NULL,
61specialty text NOT NULL
62);
63
64CREATE TABLE IF NOT EXISTS contract (
65id INT PRIMARY KEY DEFAULT nextval('s_contract'),
66idClient int NOT NULL,
67idDealer int NOT NULL,
68dateContract DATE NOT NULL,
69carBrand text NOT NULL,
70photoCarNumber int NOT NULL,
71dateIssue DATE NOT NULL,
72mileage int NOT NULL,
73dateSale DATE NOT NULL,
74priceSale decimal(50,2) CHECK (priceSale>0) NOT NULL,
75commission decimal(50,2) CHECK (commission<contract.priceSale) NOT NULL,
76notes text DEFAULT NULL,
77FOREIGN KEY (idClient) REFERENCES client(id),
78FOREIGN KEY (idDealer) REFERENCES dealer(id)
79);
80
81CREATE TABLE IF NOT EXISTS master (
82id INT PRIMARY KEY DEFAULT nextval('s_master'),
83name text NOT NULL,
84idSTO int NOT NULL,
85specialty text NOT NULL,
86experience int CHECK (experience>0) NOT NULL,
87salary decimal(50,2) CHECK (salary>0) NOT NULL,
88phone text NOT NULL,
89address text NOT NULL,
90FOREIGN KEY (idSTO) REFERENCES STO(id)
91);
92
93CREATE TABLE IF NOT EXISTS repair (
94id INT PRIMARY KEY DEFAULT nextval('s_repair'),
95idFix int NOT NULL,
96idContract int NOT NULL,
97idMaster int NOT NULL,
98dateStart DATE NOT NULL,
99repairPeriodDay int CHECK (repairPeriodDay>0) NOT NULL,
100price decimal(50,2) CHECK (price>0) NOT NULL,
101FOREIGN KEY (idFix) REFERENCES fix (id),
102FOREIGN KEY (idContract) REFERENCES contract(id),
103FOREIGN KEY (idMaster) REFERENCES master(id)
104);
105
106/*INSERT INTO fix
107VALUES (DEFAULT, 'Motor', 'Dvij');
108INSERT INTO client
109VALUES (DEFAULT, 'Petr Petrov Petrovich', 'Kharkov', 'Kojemako 5a', 38082928);
110INSERT INTO dealer
111VALUES (DEFAULT, 'John Bar Fergust', 1, 'Fora 3', 390827232);
112INSERT INTO contract
113VALUES (DEFAULT, 1, 1, '2018-01-18', 'BMW', 1, '2012-05-02', 50000, '2012-06-20',
11420000, 2000, 'TEXT: SHOTO SLUCHILOS');
115INSERT INTO master
116VALUES (DEFAULT, 'Ivan Pedro Kurion', 1, 'All', 15, 30000, 9023842, 'pr. Lemur
11745v');
118INSERT INTO repair
119VALUES (DEFAULT, 1, 1, 1, '2018-01-20', 90, 4000);
120SELECT * FROM contract;*/
121
122
123INSERT INTO STO
124VALUES (DEFAULT, 'Галиан', 'город Одесса, улица Разумовская, 14','0964553288, 0633731348, 0509285554, 0487093236', 'Lutskanandrew@gmail.com', '2010-02-01');
125INSERT INTO STO
126VALUES (DEFAULT, 'Limard', 'город Одесса, улица Приморская 18', '482342233', 'gallian@paco.net', '2012-10-20',5.0);
127INSERT INTO STO
128VALUES (DEFAULT, 'AutoHelp', 'город Одесса, улица Гаражный кооператив Ветеран 4', '380634790095, 380973328049', 'autohelp@gmail.com', '2014-09-25', 2.5);
129INSERT INTO STO
130VALUES (DEFAULT, 'Мастер Кар', 'город Одесса, улица Михаила Грушевского, 17А', '0487707788', 'g37od@mail.ru,  info@mastercar-odessa', '2011-07-15',3.5);
131INSERT INTO STO
132VALUES (DEFAULT, 'Автотрейдинг–Одесса', 'город Одесса, улица 6-й км Овидиопольской дороги, 10/2, 39/4', '0487343658', '', '2015-08-09', 4.8);
133
134/*Работники СТО Галиан*/
135INSERT INTO master
136VALUES (DEFAULT, 'Федотов Алексей Александрович', 1, 'Моторист', 3, 18000, '380673489234', 'город Одесса, улица Пантелеймоновская 76');
137INSERT INTO master
138VALUES (DEFAULT, 'Демчук Леонид Викторович', 1, 'Механик-диагност', 5, 20000, '380661398475', 'город Одесса, улица Сегедская 21');
139INSERT INTO master
140VALUES (DEFAULT, 'Арнович Борис Сергеевич', 1, 'Автоэлектрик', 4, 17000, '380965348712', 'город Одесса, улица Канатная 65');
141INSERT INTO master
142VALUES (DEFAULT , 'Радужный Дмитрий Михайлович', 1, 'Маляр', 4, 14500, '380934328412', 'город Одесса, улица Академика Королёва 70');
143INSERT INTO master
144VALUES (DEFAULT , 'Береговой Фёдр Александрович', 1, 'Автожестянщик', 2, 15000, '380934565345', 'город Одесса, улица Академика Филатова 90');
145INSERT INTO master
146VALUES (DEFAULT, 'Саратов Георгий Викторович', 1, 'Моторист', 6, 23000, '380672398475', 'город Одесса, улица Прохоровская 30');
147INSERT INTO master
148VALUES (DEFAULT , 'Бравый Александр Александрович', 1, 'Маляр', 5, 18000, '380953745890', 'город Одесса, улица Итальянский бульвар 3');
149
150/*Работники СТО Limard*/
151INSERT INTO master
152VALUES (DEFAULT , 'Краснянский Артур Левович', 2, 'Моторист', 7, 25000,'380650449122', 'город Одесса, улица Кантная 23');
153INSERT INTO master
154VALUES (DEFAULT , 'Ягодич Максимильян Игнатиевич', 2, 'Моторист', 4, 18000,'380112876627', 'город Одесса, улица Сегедская 15');
155INSERT INTO master
156VALUES (DEFAULT , 'Мартюшев Валерьян Иванович', 2, 'Автожестянщик', 4, 20000,'380941701860', 'город Одесса, улица Ришельевская 5');
157INSERT INTO master
158VALUES (DEFAULT , 'Абушаев Адриан Севастьянович', 2, 'Автоэлектрик', 6, 24000,'380846516326', 'город Одесса, улица Шевченка, 8/3');
159INSERT INTO master
160VALUES (DEFAULT , 'Кожевин Аристарх Евлампиевич', 2, 'Автоэлектрик', 7, 27000,'380225467720', 'город Одесса, улица Екатерининська, 12');
161INSERT INTO master
162VALUES (DEFAULT , 'Абоймов Харитон Евстафиевич', 2, 'Механик-диагност', 3, 14000,'380226771478', 'город Одесса, улица Шевченка, 4Е');
163INSERT INTO master
164VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Механик-диагност', 4, 16000,'380780823150', 'город Одесса, улица Прохоровськая, 8');
165INSERT INTO master
166VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Механик-диагност', 3, 12000,'380879303623', 'город Одесса, улица Канатна, 132');
167INSERT INTO master
168VALUES (DEFAULT , 'Попков Юрий Ульянович', 2, 'Маляр', 8, 19000,'380979177143', 'город Одесса, улица Маршала Говорова, 10/1');
169INSERT INTO master
170VALUES (DEFAULT , 'Занина Лидия Василиевна', 2, 'Маляр', 4, 12000,'380696116615', 'город Одесса, улица Шевченка, 16');
171INSERT INTO master
172VALUES (DEFAULT , 'Чунца Василиса Федотовна', 2, 'Вулканизаторщик', 2, 10000,'380743976899', 'город Одесса, улица Фонтанська дорога, 61');
173
174/*Работники СТО AutoHelp*/
175INSERT INTO master
176VALUES (DEFAULT , 'Юнге Аскольд Натанович', 3, 'Моторист', 2, 10000, '380132229055', 'город Одесса, улица Академика Королёва 12 кв. 23');
177INSERT INTO master
178VALUES (DEFAULT , 'Григорьев Никифор Игнатиевич', 3, 'Механик-диагност', 1, 7000, '380549214204', 'город Одесса, Небесной Сотни проспект 30в');
179INSERT INTO master
180VALUES (DEFAULT , 'Зёмин Андриян Вадимович', 3, 'Механик-диагност', 3, 13000, '380267453395', 'город Одесса, улица Генерала Пертрова 2 кв. 3');
181INSERT INTO master
182VALUES (DEFAULT , 'Иванков Матвей Измаилович', 3, 'Автоэлектрик', 2, 11000, '380688897133', 'город Одесса, улица Прохоровская 12');
183INSERT INTO master
184VALUES (DEFAULT , 'Бойдало Эдуард Леонович', 3, 'Маляр', 4, 10000, '380357144390', 'город Одесса, Люстдорфская дорога 14');
185
186/*Работники СТО Мастер Кар*/
187INSERT INTO master
188VALUES (DEFAULT , 'Шерешевский Осип Прокофиевич', 4, 'Моторист', 4, 16000, '380635385405', 'город Одесса, улица Академика Глушко 3а кв.45');
189INSERT INTO master
190VALUES (DEFAULT , 'Куделин Максимильян Зиновиевич', 4, 'Моторист', 3, 15000, '380136882710', 'город Одесса, улица Марсельская 32А');
191INSERT INTO master
192VALUES (DEFAULT , 'Балахнов Степан Тимурович', 4, 'Автожестянщик', 7, 19000, '380719660773', 'город Одесса, улица Асташкина 19 кв.32');
193INSERT INTO master
194VALUES (DEFAULT , 'Федосов Валерий Ипатиевич', 4, 'Механик-диагност', 8, 25000, '380627228748', 'город Одесса, проспект Гагрина 49');
195INSERT INTO master
196VALUES (DEFAULT , 'Труш Лука Зиновиевич', 4, 'Механик-диагност', 2, 12000, '380343960401', 'город Одесса, улица Болгарская 49');
197INSERT INTO master
198VALUES (DEFAULT , 'Мамонов Максимильян Георгиевич', 4, 'Автоэлектрик', 4, 18000, '380242522147', 'город Одесса, улица Одария 12');
199INSERT INTO master
200VALUES (DEFAULT , 'Гольца Анисья Борисовна', 4, 'Автоэлектрик', 5, 20000, '380202284424', 'город Одесса, улица Высоцкого 12/3');
201INSERT INTO master
202VALUES (DEFAULT , 'Румянцева Оксана Анатолиевна', 4, 'Механик-диагност', 6, 28000, '380403646596', 'город Одесса, улица Садовая 3 кв. 39');
203INSERT INTO master
204VALUES (DEFAULT , 'Аспидов Владилен Иосифович', 4, 'Маляр', 2, 10000, '380554435759', 'город Одесса, улица Канатная 2 кв 23');
205INSERT INTO master
206VALUES (DEFAULT , 'Ольховский Парфен Фомевич', 4, 'Маляр', 7, 20000, '380736491251', 'город Одесса, улица Сегедская 15а');
207
208/*Работники СТО Авторейдинг Одесса*/
209INSERT INTO master
210VALUES (DEFAULT , 'Чукреев Аркадий Владимирович', 5, 'Моторист', 10,18000 , '380838777704', 'город Одесса, проспект Добровольского 5');
211INSERT INTO master
212VALUES (DEFAULT , 'Филенков Дементий Модестович', 5, 'Механик-диагност', 9, 2000, '380950498104', 'город Одесса, улица Пантелеймоновская 99');
213INSERT INTO master
214VALUES (DEFAULT , 'Устинов Михей Мечиславович', 5, 'Механик-диагност', 15, 30000, '380925035772', 'город Одесса, улица Марсельская 65');
215INSERT INTO master
216VALUES (DEFAULT , 'Панюшкин Елисей Яковович', 5, 'Автоэлектрик', 9, 23000, '380631701028', 'город Одесса, улица Асташкина 37');
217INSERT INTO master
218VALUES (DEFAULT , 'Замятин Михей Никонович', 5, 'Моторист', 20, 40000, '380903923780', 'город Одесса, проспект Гагарина 34 квю 73');
219INSERT INTO master
220VALUES (DEFAULT , 'Махмудов Федот Сергеевич', 5, 'Автожестянщик', 10, 27000, '380852239808', 'город Одесса, улица Одария 2');
221INSERT INTO master
222VALUES (DEFAULT , 'Гроссман Юлиан Вячеславович', 5, 'Автожестянщик', 7, 17000, '380434230331', 'город Одесса, улица Садовая 89');
223INSERT INTO master
224VALUES (DEFAULT , 'Луньков Михей Тихонович', 5, 'Вулканизаторщик', 4, 20000, '380754129626', 'город Одесса, улица Канатная 32 кв. 54');
225INSERT INTO master
226VALUES (DEFAULT , 'Яндарбиев Потап Макарович', 5, 'Маляр', 5, 24000, '380208468539', 'город Одесса, улица Сегедская 17 кв. 42');
227
228INSERT INTO dealer
229VALUES (DEFAULT , 'Интер-Авто', 'MAZDA', 1635, '565(051)747-19-50', 'город Одесса, улица Канатная 36');
230INSERT INTO dealer
231VALUES (DEFAULT , 'Palisade', 'HYUNDAI', 1157, '41(38)420-14-15', 'город Одесса, проспект Шевченка 63');
232INSERT INTO dealer
233VALUES (DEFAULT , 'Эмералд Моторс', 'BMW', 8810, '849(77)291-33-39', 'город Одесса, улица Академика Королёва 2/5');
234INSERT INTO dealer
235VALUES (DEFAULT , 'Автомоильный дом', 'WOLKSWAGEN', 4677, '8(646)649-01-19', 'город Одесса, улица Сегедская 30/4');
236INSERT INTO dealer
237VALUES (DEFAULT , 'Авто Груп', 'RENAULT', 7864, '9(65)997-53-74', 'город Одесса, улица Академика Глушко 112');
238INSERT INTO dealer
239VALUES (DEFAULT , 'Одесса-Авто', 'KIA', 6026, '1(929)814-17-23', 'город Одесса, улица Ришельевская 74');
240INSERT INTO dealer
241VALUES (DEFAULT , 'Fiat-Авто', 'FIAT', 1276, '15(25)805-80-23', 'город Одесса, улица Фонтаская 3');
242INSERT INTO dealer
243VALUES (DEFAULT , 'АВТОМИР', 'JEEP', 9440, '88(123)192-23-40', 'город Одесса, улица Левитана 41');
244INSERT INTO dealer
245VALUES (DEFAULT , 'Mersedes Company', 'MERSEDES', 4462, '81(549)948-30-63', 'город Одесса, улица Артиллерийская 14');
246
247INSERT INTO client
248VALUES (DEFAULT , 'Цейдлица Зоя Данииловна', 'Одесса', '380314106225', 'город Одесса, улица Филатова 23');
249INSERT INTO client
250VALUES (DEFAULT , 'Кривоухова Милена Елизаровна', 'Одесса', '380154915929', 'город Одесса, улица Канатная 12 кв. 54');
251INSERT INTO client
252VALUES (DEFAULT , 'Десяткова Анфиса Василиевна', 'Одесса', '380513635207', 'город Одесса, улица Академика Глушко 27 кв. 56');
253INSERT INTO client
254VALUES (DEFAULT , 'Пыжалов Прокл Сигизмундович', 'Одесса', '380607363669', 'город Одесса, улица Канатная 56 кв. 19');
255INSERT INTO client
256VALUES (DEFAULT , 'Перевалова Вероника Ефимовна', 'Одесса', '380285183625', 'город Одесса, улица Сегедская 64');
257INSERT INTO client
258VALUES (DEFAULT , 'Пересторонин Варфоломей Тихонович', 'Одесса', '380963300996', 'город Одесса, улица Ришельевская 94');
259INSERT INTO client
260VALUES (DEFAULT , 'Горемыкин Эрнест Мирославович', 'Одесса', '380789579999', 'город Одесса, улица Левитана 64');
261INSERT INTO client
262VALUES (DEFAULT , 'Царегородцев Никита Всеволодович', 'Одесса', '380756445342', 'город Одесса, улица Одария 74');
263INSERT INTO client
264VALUES (DEFAULT , 'Амелина Рената Иосифовна', 'Одесса', '380531935419', 'город Одесса, улица Асташникская 32 кв.93');
265INSERT INTO client
266VALUES (DEFAULT , 'Смольникова Агата Алексеевна', 'Одесса', '380418821399', 'город Одесса, улица Марсельская 31');
267INSERT INTO client
268VALUES (DEFAULT , 'Караева Инга Георгиевна', 'Одесса', '380836010947', 'город Одесса, улица большая Арнаутская 7 кв. 24');
269INSERT INTO client
270VALUES (DEFAULT , 'Смышляев Вацлав Аполлинариевич', 'Одесса', '380439630844', 'город Одесса, улица малая Арнаутская 74');
271INSERT INTO client
272VALUES (DEFAULT , 'Якуткин Тарас Матвеевич', 'Одесса', '380518399393', 'город Одесса, улица Болгарская 71 кв. 62');
273INSERT INTO client
274VALUES (DEFAULT , 'Розенбах Григорий Прокофиевич', 'Одесса', '380661762364', 'город Одесса, улица Деребасовская 3 кв. 95');
275INSERT INTO client
276VALUES (DEFAULT , 'Труш Платон Евграфович', 'Одесса', '380123909948', 'город Одесса, улица Пантелеймоновская 18/6');
277INSERT INTO client
278VALUES (DEFAULT , 'Бурцева Агния Ростиславовна', 'Одесса', '380858060690', 'город Одесса, улица Прохоровская 29');
279INSERT INTO client
280VALUES (DEFAULT , 'Истомин Моисей Назарович', 'Одесса', '380385397683', 'город Одесса, улица Мясоедовская 15б');
281INSERT INTO client
282VALUES (DEFAULT , 'Ябурова Лариса Семеновна', 'Одесса', '380952418081', 'город Одесса, улица Троицкая 6 ка.38');
283INSERT INTO client
284VALUES (DEFAULT , 'Севостьянова Арина Захаровна', 'Одесса', '380459900252', 'город Одесса, улица Жуковского 106');
285INSERT INTO client
286VALUES (DEFAULT , 'Севостьянова Арина Захаровна', 'Одесса', '380971252202', 'город Одесса, улица Пастера 72');
287INSERT INTO client
288VALUES (DEFAULT , 'Полторак Андриян Касьянович', 'Одесса', '380720993638', 'город Одесса, улица Торговая 22 кв. 63');
289INSERT INTO client
290VALUES (DEFAULT , 'Сухомлин Даниил Архипович', 'Одесса', '380253961659', 'город Одесса, улица Приморская 82');
291INSERT INTO client
292VALUES (DEFAULT , 'Лясин Герман Иннокентиевич', 'Одесса', '380835674748', 'город Одесса, улица Успенская 9. кв. 10');
293INSERT INTO client
294VALUES (DEFAULT , 'Кайпанов Максимильян Леонидович', 'Одесса', '380515771847', 'город Одесса, улица Базарная 73');
295INSERT INTO client
296VALUES (DEFAULT , 'Ямбушева Ираида Давидовна', 'Одесса', '380201302027', 'город Одесса, улица Затишная 30 кв.30');
297INSERT INTO client
298VALUES (DEFAULT , 'Хрущёв Даниил Семенович', 'Одесса', '380250118061 ', 'город Одесса, улица Приговорская 15');
299INSERT INTO client
300VALUES (DEFAULT , 'Антипин Владислав Вадимович', 'Одесса', '380451219486', 'город Одесса, улица Семинарская 83');
301INSERT INTO client
302VALUES (DEFAULT , 'Балаев Артем Севастьянович', 'Одесса', '380592973301', 'город Одесса, улица Армейская 15/4 кв 42');
303INSERT INTO client
304VALUES (DEFAULT , 'Греф Поликарп Кондратиевич', 'Одесса', '380962720696', 'город Одесса, улица Зоопарковая 55');
305INSERT INTO client
306VALUES (DEFAULT , 'Ямковой Юлия Афанасиевна', 'Одесса', '380991937210', 'город Одесса, улица Академическая 18');
307INSERT INTO client
308VALUES (DEFAULT , 'Головченко Владлена Станиславовна', 'Одесса', '380400355539', 'город Одесса, улица Генуезькая 40 кв.72');
309INSERT INTO client
310VALUES (DEFAULT , 'Лосевский Феликс Андреевич', 'Одесса', '380907829435', 'город Одесса, улица Тернистая 90/1а');
311INSERT INTO client
312VALUES (DEFAULT , 'Шаповалов Михей Сократович', 'Одесса', '380409996826', 'город Одесса, улица Гвардейская 32');
313INSERT INTO client
314VALUES (DEFAULT , 'Поличева Стела Вячеславовна', 'Одесса', '380701488055', 'город Одесса, улица Литературная 82');
315INSERT INTO client
316VALUES (DEFAULT , 'Ярыгина Агафья Фомевна', 'Одесса', '380377421232', 'город Одесса, улица Репина 30');
317INSERT INTO client
318VALUES (DEFAULT , 'Лешев Матвей Алексеевич', 'Одесса', '380639266435', 'город Одесса, улица Куприна 23 кв.43');
319
320/*Контракты 1 дилера*/
321INSERT INTO contract
322VALUES (DEFAULT , 1, 1, '2018-01-01', 'MAZDA', 526, '2017-12-02', 100, '2018-01-02', 150000, 6000, '');
323INSERT INTO contract
324VALUES (DEFAULT , 2, 1, '2018-02-04', 'MAZDA', 508, '2016-11-22', 345, '2018-02-04', 200000, 15000, '');
325INSERT INTO contract
326VALUES (DEFAULT , 3, 1, '2017-10-01', 'MAZDA', 672, '2015-07-14', 100, '2017-10-05', 250000, 20000, '');
327INSERT INTO contract
328VALUES (DEFAULT , 4, 1, '2017-12-20', 'MAZDA', 891, '2017-10-10', 110, '2017-12-21', 175000, 15000, '');
329/*Контракты 2 дилера*/
330INSERT INTO contract
331VALUES (DEFAULT , 5, 2, '2018-11-10', 'HYUNDAI', 132, '2018-11-01', 493, '2018-11-14', 700000, 80000, '');
332INSERT INTO contract
333VALUES (DEFAULT , 6, 2, '2010-05-11', 'HYUNDAI', 782, '2009-01-23', 500, '2010-05-14', 300000, 20000, '');
334INSERT INTO contract
335VALUES (DEFAULT , 7, 2, '2017-12-20', 'HYUNDAI', 912, '2016-12-02', 536, '2017-12-23', 450000, 45000, '');
336INSERT INTO contract
337VALUES (DEFAULT , 8, 2, '2017-04-18', 'HYUNDAI', 392, '2017-03-16', 254, '2016-11-02', 600000, 50000, '');
338/*Контракты 3 дилера*/
339INSERT INTO contract
340VALUES (DEFAULT , 9, 3, '2008-05-02', 'BMW', 113, '2007-01-01', 600, '2008-05-12', 450000, 30000, '');
341INSERT INTO contract
342VALUES (DEFAULT , 10, 3, '2018-03-03', 'BMW', 345, '2018-01-01', 543, '2018-03-11', 1100000, 100000, '');
343INSERT INTO contract
344VALUES (DEFAULT , 11, 3, '2017-04-20', 'BMW', 654, '2016-11-01', 932, '2017-04-24', 490000, 34000, '');
345INSERT INTO contract
346VALUES (DEFAULT , 12, 3, '2017-11-12', 'BMW', 962, '2015-04-01', 1100, '2017-11-17', 780000, 70000, '');
347/*Контракты 4 дилера*/
348INSERT INTO contract
349VALUES (DEFAULT , 13, 4, '2018-07-12', 'WOLKSWAGEN', 912, '2018-07-13', 1100, '2018-06-01', 210000, 15000, '');
350INSERT INTO contract
351VALUES (DEFAULT , 14, 4, '2018-05-12', 'WOLKSWAGEN', 254, '2015-05-13', 1200, '2017-06-29', 300000, 19000, '');
352INSERT INTO contract
353VALUES (DEFAULT , 15, 4, '2018-10-12', 'WOLKSWAGEN', 823, '2018-10-14', 943, '2018-01-12', 780000, 43000, '');
354INSERT INTO contract
355VALUES (DEFAULT , 16, 4, '2018-03-12', 'WOLKSWAGEN', 190, '2018-03-14', 892, '2018-02-20', 780000, 50000, '');
356/*Контракты 5 дилера*/
357INSERT INTO contract
358VALUES (DEFAULT , 17, 5, '2016-02-11', 'RENAULT', 422, '2016-01-01', 365, '2016-02-13', 190000, 30000, '');
359INSERT INTO contract
360VALUES (DEFAULT , 18, 5, '2016-11-20', 'RENAULT', 672, '2015-12-01', 542, '2016-11-24', 290000, 25000, '');
361INSERT INTO contract
362VALUES (DEFAULT , 19, 5, '2015-05-01', 'RENAULT', 825, '2015-01-10', 102, '2015-05-08', 550000, 50000, '');
363INSERT INTO contract
364VALUES (DEFAULT , 20, 5, '2017-07-15', 'RENAULT', 501, '2017-01-29', 321, '2017-07-24', 430000, 41000, '');
365/*Контракты 6 дилера*/
366INSERT INTO contract
367VALUES (DEFAULT , 21, 6, '2015-07-15', 'KIA', 501, '2015-06-22', 382, '2015-07-22', 354000, 24000, '');
368INSERT INTO contract
369VALUES (DEFAULT , 22, 6, '2018-06-05', 'KIA', 221, '2018-03-29', 454, '2018-06-12', 410000, 41000, '');
370INSERT INTO contract
371VALUES (DEFAULT , 23, 6, '2017-10-25', 'KIA', 532, '2016-03-10', 333, '2017-11-01', 640000, 64000, '');
372INSERT INTO contract
373VALUES (DEFAULT , 24, 6, '2016-03-18', 'KIA', 452, '2016-02-01', 205, '2017-03-24', 550000, 55000, '');
374/*Контракты 7 дилера*/
375INSERT INTO contract
376VALUES (DEFAULT , 25, 7, '2016-05-10', 'FIAT', 111, '2016-02-01', 205, '2016-05-23', 320000, 25000, '');
377INSERT INTO contract
378VALUES (DEFAULT , 26, 7, '2017-06-02', 'FIAT', 145, '2016-05-01', 205, '2017-06-05', 478000, 45000, '');
379INSERT INTO contract
380VALUES (DEFAULT , 27, 7, '2018-11-20', 'FIAT', 284, '2017-09-01', 205, '2018-11-23', 578000, 54000, '');
381INSERT INTO contract
382VALUES (DEFAULT , 28, 7, '2018-12-01', 'FIAT', 402, '2018-02-01', 205, '2018-12-01', 400000, 39000, '');
383/*Контракты 8 дилера*/
384INSERT INTO contract
385VALUES (DEFAULT , 29, 8, '2017-03-11', 'JEEP', 325, '2016-03-15', 545, '2018-12-01', 450000, 60000, '');
386INSERT INTO contract
387VALUES (DEFAULT , 30, 8, '2018-04-03', 'JEEP', 345, '2018-04-05', 265, '2018-12-01', 3600000, 400000, '');
388INSERT INTO contract
389VALUES (DEFAULT , 31, 8, '2018-05-29', 'JEEP', 432, '2018-05-030', 465, '2018-12-01', 2500000, 230000, '');
390INSERT INTO contract
391VALUES (DEFAULT , 32, 8, '2015-06-21', 'JEEP', 352, '2015-06-23', 687, '2018-12-01', 1100000, 130000, '');
392/*Контракты 9 дилера*/
393INSERT INTO contract
394VALUES (DEFAULT , 33, 9, '2018-04-14', 'MERSEDES', 124, '2018-04-10', 137, '2018-04-14', 1550000, 200000, '');
395INSERT INTO contract
396VALUES (DEFAULT , 34, 9, '2018-04-23', 'MERSEDES', 234, '2018-03-01', 143, '2018-04-23', 700000, 100000, '');
397INSERT INTO contract
398VALUES (DEFAULT , 35, 9, '2017-09-09', 'MERSEDES', 324, '2017-05-20', 192, '2018-09-10', 2300000, 300000, '');
399INSERT INTO contract
400VALUES (DEFAULT , 36, 9, '2018-01-15', 'MERSEDES', 941, '2018-01-10', 182, '2018-01-16', 550000, 70000, '');
401INSERT INTO contract
402VALUES (DEFAULT , 36, 7, '2018-01-15', 'MERSEDES', 941, '2018-01-10', 182, '2018-01-16', 550000, 70000, '');
403
404/*Работы Моториста*/
405INSERT INTO fix
406VALUES (DEFAULT , 'Ремонт ходовой', 'Моторист');
407INSERT INTO fix
408VALUES (DEFAULT , 'Ремонт двигателя', 'Моторист');
409INSERT INTO fix
410VALUES (DEFAULT , 'Замена двигателя', 'Моторист');
411INSERT INTO fix
412VALUES (DEFAULT , 'Установка новых запчастей', 'Моторист');
413/*Работы Автоэлектрика*/
414INSERT INTO fix
415VALUES (DEFAULT , 'Устранения неполадок электрики', 'Автоэлектрик');
416INSERT INTO fix
417VALUES (DEFAULT , 'Частичная замена электрики', 'Автоэлектрик');
418INSERT INTO fix
419VALUES (DEFAULT , 'Полная замена электрики', 'Автоэлектрик');
420INSERT INTO fix
421VALUES (DEFAULT , 'Подключения к автомобилю новых девайсов', 'Автоэлектрик');
422/*Работы Маляра*/
423INSERT INTO fix
424VALUES (DEFAULT , 'Устранения царапин', 'Маляр');
425INSERT INTO fix
426VALUES (DEFAULT , 'Шпаклевка', 'Маляр');
427INSERT INTO fix
428VALUES (DEFAULT , 'Устранения корозии', 'Маляр');
429INSERT INTO fix
430VALUES (DEFAULT , 'Покраска', 'Маляр');
431INSERT INTO fix
432VALUES (DEFAULT , 'Полная обработка кузова', 'Маляр');
433/*Работы Вулканизаторщика*/
434INSERT INTO fix
435VALUES (DEFAULT , 'Установка камер', 'Вулканизаторщик');
436INSERT INTO fix
437VALUES (DEFAULT , 'Настройка камер', 'Вулканизаторщик');
438INSERT INTO fix
439VALUES (DEFAULT , 'Починка камер', 'Вулканизаторщик');
440/*Работы Автожестянщика*/
441INSERT INTO fix
442VALUES (DEFAULT , 'Рихтовка кузова', 'Автожестянщик');
443INSERT INTO fix
444VALUES (DEFAULT , 'Исправления вмятин', 'Автожестянщик');
445INSERT INTO fix
446VALUES (DEFAULT , 'Полная обработка кузова', 'Автожестянщик');
447/*Работы Механика-диагноста*/
448INSERT INTO fix
449VALUES (DEFAULT , 'Диагностика автомобиля', 'Механик-диагност');
450INSERT INTO fix
451VALUES (DEFAULT , 'Диагностика двигателя', 'Механик-диагност');
452INSERT INTO fix
453VALUES (DEFAULT , 'Диагностика ходовой', 'Механик-диагност');
454INSERT INTO fix
455VALUES (DEFAULT , 'Диагностика электрики', 'Механик-диагност');
456
457
458
459INSERT INTO repair
460VALUES (DEFAULT , 1, 14, 24,'2018-11-20', 2, 38230);
461INSERT INTO repair
462VALUES (DEFAULT , 12, 26, 32,'2018-11-20', 20, 18655);
463INSERT INTO repair
464VALUES (DEFAULT , 14, 2, 18,'2017-11-20', 20, 11350);
465INSERT INTO repair
466VALUES (DEFAULT , 20, 31, 27,'2018-11-20', 11, 37710);
467INSERT INTO repair
468VALUES (DEFAULT , 6, 16, 3,'2018-11-20', 11, 25288);
469INSERT INTO repair
470VALUES (DEFAULT , 21, 30, 15,'2018-11-20', 20, 22208);
471INSERT INTO repair
472VALUES (DEFAULT , 2, 15, 1,'2017-11-20', 9, 15892);
473INSERT INTO repair
474VALUES (DEFAULT , 4, 11, 25,'2018-11-20', 8, 32722);
475INSERT INTO repair
476VALUES (DEFAULT , 11, 28, 16,'2018-11-20', 10, 37909);
477INSERT INTO repair
478VALUES (DEFAULT , 9, 10, 23,'2018-11-20', 18, 19248);
479INSERT INTO repair
480VALUES (DEFAULT , 18, 23, 10,'2018-11-20', 3, 18856);
481INSERT INTO repair
482VALUES (DEFAULT , 17, 23, 40,'2018-11-20', 9, 10018);
483INSERT INTO repair
484VALUES (DEFAULT , 3, 17, 34,'2017-11-20', 8, 25377);
485INSERT INTO repair
486VALUES (DEFAULT , 23, 35, 13,'2018-12-01', 11, 14097);
487INSERT INTO repair
488VALUES (DEFAULT , 14, 24, 41,'2018-11-20', 24, 11924);
489INSERT INTO repair
490VALUES (DEFAULT , 4, 11, 9,'2018-11-20', 1, 20863);
491INSERT INTO repair
492VALUES (DEFAULT , 13, 28, 33,'2018-11-20', 50, 32775);
493INSERT INTO repair
494VALUES (DEFAULT , 8, 24, 12,'2018-11-20', 20, 18768);
495INSERT INTO repair
496VALUES (DEFAULT , 16, 36, 41,'2017-11-20', 2, 23196);
497INSERT INTO repair
498VALUES (DEFAULT , 7, 19, 11,'2018-11-20', 38, 25654);
499INSERT INTO repair
500VALUES (DEFAULT , 19, 15, 39,'2018-11-20', 45, 21934);
501
502/*Полная информация о дилерах*/
503CREATE VIEW FullContractInformation AS
504SELECT contract.id ContractId, dealer.fullName Dealer, contract.dateContract, carBrand CarBrand, client.fullName NameClient,
505  client.cityName City,  client.address, client.phone, contract.dateSale, contract.priceSale, contract.notes
506FROM contract
507RIGHT OUTER JOIN dealer ON contract.idDealer = dealer.id
508RIGHT OUTER JOIN client ON contract.idClient = client.id;
509/*SELECT * FROM FullContractInformation;*/
510
511
512
513/*Сортировка клиентов*/
514CREATE VIEW sorting AS
515SELECT * FROM client
516  ORDER BY client.fullName;
517/*SELECT *FROM sorting;*/
518
519/*Вывод суммы всех продаж и коммисий*/
520CREATE VIEW total_priceSale_and_commission AS
521SELECT SUM(priceSale) TotalPriceSale, SUM(commission) TotalCommission From contract;
522/*SELECT * FROM total_priceSale_and_commission;*/
523
524
525/*Зароботок фирмы за все контракты*/
526CREATE FUNCTION sumSale(name text) RETURNS float AS
527$$
528DECLARE
529    sum float;
530
531    BEGIN
532    sum := (SELECT SUM(priceSale) AS q FROM contract WHERE name=(SELECT fullName FROM dealer WHERE dealer.id= contract.idDealer));
533
534    return sum;
535  end;
536$$ LANGUAGE plpgsql;
537/*SELECT * FROM sumSale('Palisade');*/
538
539/*Вывод контрактов которые младше определенной даты*/
540/*SELECT id, idDealer, idClient, notes FROM contract WHERE dateSale<'2017-08-01';*/
541
542/*Вывод клиентов которые находятся на ремонте*/
543/*SELECT * FROM client WHERE id IN (SELECT idClient FROM contract WHERE id IN (SELECT idContract FROM repair));*/
544
545
546/*сколько всего заказов в дилера
547SELECT COUNT(*) FROM contract WHERE idDealer = 1;*/
548
549/*Вывод ифнормации о деятельности дилере*/
550/*SELECT * FROM contract WHERE idDealer = 4;*/
551
552/*Вывод суммы всех зарплат мастеров и их кол-во*/
553/*SELECT SUM(salary) AS Sum_Salary, COUNT(*) AS Quantity FROM master;*/
554
555/*Вывод средней зарплаты мастеров и иx количество*/
556/*SELECT AVG(salary) AS average_salary, COUNT(*) AS Quantity FROM master;*/
557
558/*Выводит 5 самых больших заказов
559SELECT contract.idClient, client.fullName, contract.idDealer, dealer.fullName, contract.dateContract, contract.carBrand, contract.priceSale
560FROM contract, client, dealer
561WHERE contract.idDealer = dealer.id AND contract.idClient=client.id
562ORDER BY contract.priceSale DESC
563LIMIT 5;*/
564
565/*Выводит мастеров у которых зарплата в диапазоне до 10 000 до 15 000*/
566/*SELECT * FROM master WHERE salary BETWEEN 10000 AND 15000;*/
567
568/*Вывод мастеров у которых зарплата больше средней*/
569SELECT * FROM master WHERE salary > (SELECT AVG(salary) FROM master);
570
571/*Вывод имен клиентов у которых больше 1 заказа*/
572SELECT fullName FROM client WHERE 1<(SELECT COUNT(idClient) FROM contract WHERE idClient = client.id);
573
574/*Подсчет количества заключенных контрактов всех клиентов*/
575CREATE FUNCTION kolv(N INT) RETURNS int AS
576$$
577DECLARE
578q INT;
579BEGIN
580  q:=(SELECT COUNT(*) FROM contract WHERE idClient=N);
581  RETURN q;
582END;
583$$LANGUAGE plpgsql;
584SELECT idClient, kolv(idClient) FROM contract  GROUP BY idClient HAVING COUNT(*) >=1 ORDER BY idClient ;
585
586/*Подсчет количества заключенных контрактов диллера*/
587CREATE FUNCTION kolvD(N INT) RETURNS int AS
588$$
589DECLARE
590q INT;
591BEGIN
592  q:=(SELECT COUNT(*) FROM contract WHERE idDealer=N);
593  RETURN q;
594END;
595$$LANGUAGE plpgsql;
596/*SELECT idDealer, kolvD(idDealer) FROM contract GROUP BY idDealer HAVING COUNT(*) >=1 ORDER BY idDealer ;*/
597
598/*Обеспечения целотности данных*/
599CREATE FUNCTION checkId() RETURNS trigger AS
600$$
601DECLARE
602rec record;
603BEGIN
604  SELECT into rec*
605  FROM dealer WHERE id=new.idDealer;
606  IF NULL
607    THEN
608    ELSE
609    RETURN new;
610  END IF;
611END;
612$$LANGUAGE plpgsql;
613
614CREATE TRIGGER control
615BEFORE INSERT
616ON contract
617FOR EACH ROW
618EXECUTE PROCEDURE checkId();
619
620
621/*Тригер контроля добавления*/
622CREATE OR REPLACE FUNCTION add_dealer()
623RETURNS TRIGGER
624AS $$ BEGIN
625  PERFORM id FROM dealer
626  WHERE id=new.id;
627  IF FOUND THEN
628    RAISE EXCEPTION
629    'Error!';
630END IF;
631RETURN new;
632END;$$
633LANGUAGE 'plpgsql';
634
635CREATE TRIGGER add_dealer
636BEFORE INSERT OR UPDATE ON dealer
637FOR EACH ROW
638EXECUTE PROCEDURE add_dealer();
639
640/*Проверка уникальности нового контракта*/
641CREATE OR REPLACE FUNCTION add_contract()
642RETURNS TRIGGER
643AS $$ BEGIN
644  PERFORM id FROM contract
645  WHERE id=new.id;
646  IF FOUND THEN
647    RAISE EXCEPTION
648    'Error new id exist!';
649  END IF;
650  RETURN new;
651END;$$
652LANGUAGE 'plpgsql';
653
654CREATE TRIGGER add_contract
655BEFORE INSERT OR UPDATE ON contract
656FOR EACH ROW
657EXECUTE PROCEDURE add_contract();
658
659/*Проверка уникальности нового дилера*/
660CREATE OR REPLACE FUNCTION add_dealer()
661RETURNS TRIGGER
662AS $$ BEGIN
663  PERFORM id FROM dealer
664  WHERE id=new.id;
665  IF FOUND THEN
666    RAISE EXCEPTION
667    'Error new id exist!';
668  END IF;
669  RETURN new;
670END;$$
671LANGUAGE 'plpgsql';
672
673CREATE TRIGGER add_dealer
674BEFORE INSERT OR UPDATE ON dealer
675FOR EACH ROW
676EXECUTE PROCEDURE add_dealer();
677
678/*Проверка уникальности нового клиента*/
679CREATE OR REPLACE FUNCTION add_client()
680RETURNS TRIGGER
681AS $$ BEGIN
682  PERFORM id FROM client
683  WHERE id=new.id;
684  IF FOUND THEN
685    RAISE EXCEPTION
686    'Error new id exist!';
687  END IF;
688  RETURN new;
689END;$$
690LANGUAGE 'plpgsql';
691
692CREATE TRIGGER add_client
693BEFORE INSERT OR UPDATE ON client
694FOR EACH ROW
695EXECUTE PROCEDURE add_client();
696
697
698/*Владелец*/
699CREATE ROLE ownserver WITH PASSWORD 'AFr$gX9@Bf43*7#V12=LoR-S';
700GRANT ALL ON ALL TABLES IN SCHEMA public TO ownserver WITH GRANT OPTION;
701
702/*Администратор*/
703CREATE ROLE administrator WITH PASSWORD 'KLA(SD898%^J@h)s7KL-S,M';
704GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO administrator;
705
706/*Работник*/
707CREATE ROLE employee WITH PASSWORD 'JlKIl193$*&@0QmwD)';
708GRANT SELECT, INSERT, UPDATE ON contract, dealer, client TO employe;
709
710/*Клиент*/
711CREATE ROLE client WITH PASSWORD 'IUA)S$D5b56DNA2';
712GRANT SELECT ON dealer TO client;