QeyQpMkr

1drop table if exists transport_company cascade;
2drop table if exists route cascade;
3drop table if exists maintenance cascade;
4drop table if exists timetable cascade;
5drop table if exists bus cascade ;
6drop table if exists driver cascade;
7
8drop sequence if exists transport_company_ids cascade;
9drop sequence if exists route_ids cascade;
10drop sequence if exists maintenance_ids cascade;
11drop sequence if exists timetable_ids cascade;
12drop sequence if exists bus_ids cascade;
13drop sequence if exists driver_ids cascade;
14
15drop function if exists add_to_the_maitance();
16drop function if exists del_maintance();
17drop function if exists unavaible_route();
18drop function if exists count_stops(route_id int);
19
20drop  user if exists Adnan ;
21drop user if exists  Peresunko ;
22drop user if exists  Sarafanof ;
23
24--Таблицы
25
26create sequence transport_company_ids;
27create table if not exists transport_company (
28  id                 int primary key default nextval('transport_company_ids'),
29  company_name       char(35) not null,
30  year_of_foundation int      not null,
31  wage               int      not null check (wage > 0),
32  transport_charge   int      not null check (transport_charge > 0)
33);
34
35create sequence maintenance_ids;
36create table if not exists maintenance (
37id         int primary key default nextval('maintenance_ids'),
38model      char(20) not null,
39spare_part char(25) not null
40);
41
42create sequence route_ids;
43create table if not exists route (
44id     int primary key  default nextval('route_ids'),
45price  int DEFAULT 7,
46time   int not null,
47avaible boolean default true--Доступность маршрута
48);
49
50create sequence timetable_ids;
51create table if not exists timetable (
52id       int primary key default nextval('timetable_ids'),
53time     int      not null,
54stop     char(32) not null,
55route_id int      not null,
56foreign key (route_id) references route (id)
57);
58
59
60
61CREATE SEQUENCE bus_ids;
62create table if not exists bus (
63id       int primary key  default nextval('bus_ids'),
64model    char(25) NOT NULL,
65price    int    default 7,
66numbers  int      not null,
67route_id int      not null,
68transport_company_id int not null,
69maintance_id int ,
70avaible boolean default true, --Доступен ли? Или сломан/на обслуживании
71foreign key (transport_company_id) references transport_company (id),
72foreign key (route_id) references route (id),
73foreign key (maintance_id) references maintenance (id)
74);
75
76
77create sequence driver_ids;
78create table if not exists driver (
79id          int primary key  default nextval('driver_ids'),
80name        char(256) not null,
81driver_code int       not null,
82adress      char(100) not null,
83phone       char(15)       not null,
84bus_id      int       not null,
85transport_company_id int not null ,
86foreign key (transport_company_id) references transport_company(id),
87foreign key (bus_id) references bus (id)
88);
89
90
91-- Функции и триггеры
92
93-- Если мы добавляем автобус с заданным id на обслуживание (тип компания закупила еле живые маршрутки)
94-- срабатывает триггер, который отмечает, что автобус не доступен
95CREATE FUNCTION add_to_the_maitance() returns TRIGGER AS
96  $$
97  BEGIN
98    UPDATE bus SET avaible = false WHERE maintance_id = NEW.id;
99    RETURN NEW;
100  end;
101  $$ LANGUAGE plpgsql;
102CREATE TRIGGER t_add_bus_tp_the_maintance AFTER INSERT ON bus FOR EACH ROW EXECUTE PROCEDURE add_to_the_maitance();
103
104-- Подчищаем соотвествую запись об обслуживании, когда автобус готов
105CREATE FUNCTION del_maintance() returns TRIGGER AS
106  $$
107  BEGIN
108    IF new.maintance_id isnull and new.avaible = true THEN
109      DELETE FROM maintenance WHERE id = old.maintance_id;
110    end if;
111    RETURN new;
112  end;
113$$ LANGUAGE plpgsql;
114CREATE TRIGGER t_del_maintance AFTER UPDATE ON bus FOR EACH ROW EXECUTE PROCEDURE del_maintance();
115
116--Делает маршрут недоступным, если был удалён последний автобус с маршрута
117CREATE FUNCTION unavaible_route() returns trigger AS
118  $$
119  BEGIN
120    IF (SELECT count(*) FROM bus WHERE route_id = old.route_id) = 0 THEN
121        UPDATE route set avaible = false WHERE id = old.route_id;
122  end if;
123    return new;
124  end;
125$$ LANGUAGE plpgsql;
126CREATE TRIGGER t_unavaible_route AFTER DELETE ON bus FOR EACH ROW execute procedure unavaible_route();
127
128--Подсчёт количества остановок на маршруте
129CREATE FUNCTION count_stops(route_id int) RETURNS int AS
130  $$
131  BEGIN
132      return (SELECT count(*) FROM timetable WHERE timetable.route_id = timetable.route_id);
133    end;
134$$ LANGUAGE plpgsql;
135
136
137--Ввод данных
138
139insert into transport_company
140values (nextval('transport_company_ids'), 'Diego bus',2002,3500,1000);
141insert into transport_company
142values (nextval('transport_company_ids'),'Diego bus',2002,8000,1000);
143insert into transport_company
144values (nextval('transport_company_ids'),'Auto bus',1998,5000,500);
145
146
147
148insert into maintenance
149values (nextval('maintenance_ids'),'motor low','motor hight');
150insert into maintenance
151values (nextval('maintenance_ids'),'motor low','motor hight');
152insert into maintenance
153values  (nextval('maintenance_ids'),'pedal one ','pedal two');
154insert into maintenance
155values (nextval('maintenance_ids'),'motor','motor spare');
156
157
158insert into route
159values (nextval('route_ids'),DEFAULT ,3);
160insert into  route
161values (nextval('route_ids'),DEFAULT ,4);
162insert into  route
163values (nextval('route_ids'),DEFAULT ,5);
164
165insert into timetable
166values (nextval('timetable_ids'),4,'Проспект Шевченко',1);
167insert into timetable
168VALUES (nextval('timetable_ids'),5,'Успенская',2);
169
170
171insert into bus
172values (nextval('bus_ids'),'Toyota',default ,146,1,1, 1);
173insert into bus
174values (nextval('bus_ids'),'Mercedes',default ,121,2,2, 2);
175insert into bus
176values (default , 'Bogdan', default, 148, 3,3, default);
177
178update bus set maintance_id = null, avaible = true where id = 1;
179delete from bus WHERE id = 3;
180
181
182
183insert into driver
184values (nextval('driver_ids'),'Александр Александрович Дмитренко',1001,'Заболотного','380969825174',1,1);
185insert into driver
186values (nextval('driver_ids'),'Максим Сергеевич Костовский ',1002,'Высоцкого ','380963685147',2,2);
187
188SELECT * FROM bus;
189SELECT * FROM maintenance;
190SELECT * FROM route;
191SELECT count_stops(2);
192
193
194
195
196
197--Вывод таблиц
198 select * from transport_company;
199 select * from maintenance;
200select * from route;
201 select * from timetable;
202 select * from bus;
203 select * from driver;
204
205
206
207--Запросы
208 select company_name,wage from transport_company WHERE wage = 3500;
209 select company_name, year_of_foundation from transport_company WHERE year_of_foundation >= 2000;
210 select AVG(wage) AS WageAVG from transport_company;
211 select * from driver ORDER BY name;
212 select model,count(*) AS ModelsCount from maintenance  group by  model;
213 select  * from transport_company where company_name like '%bus%' order by company_name ;
214 select SUM(wage) from  transport_company group  by wage having SUM(wage) > 3000;
215 select time ,MIN(TIME) from timetable group by time having  min(time) < 5;
216 select model   from bus where model NOTNULL ;
217 CREATE VIEW rich_company as select  * from transport_company order by transport_charge desc ;
218 select * from rich_company;
219 select d.name ,d.adress , b.model from driver  d , bus b  where model  like '%Toyota% ';
220
221
222
223--Подзапросы
224 select * from transport_company where wage >(select  AVG(wage)from transport_company);
225 select * from transport_company where transport_charge < ALL (select transport_charge from transport_company where company_name ='Diego bus');
226 select * from transport_company where wage < ANY (select wage from transport_company where company_name ='Auto bus' );
227 select * from driver where bus_id =(select id from bus where numbers ='121');
228 select * from driver where bus_id =(select id from bus where numbers ='146');
229 select stop from timetable where id in(select id  from route where time >3);
230 select * from  driver where exists (select  id from transport_company where id = 4);
231 select * from transport_company where wage >all(select wage from transport_company where company_name ='Auto bus');
232 select company_name from transport_company  where 11500 <(select sum(wage)from transport_company where id = transport_company.id);--С помощью данного запроса получаем сумерную зарплату,которая больше 8000
233
234
235
236
237
238-- 1 задание
239create user Adnan ;
240create user Peresunko;
241create user Sarafanof;
242
243--2 задание
244GRANT UPDATE (company_name,wage) on transport_company to Adnan;--даем права на смену некоторых столбцов первому пользователю
245--3 задание
246GRANT select on bus to public; --даем права всем пользователем на осмотр таблиц
247--4 задание
248 grant insert , update on timetable to Sarafanof with grant option;-- даем права второму пользователю вставлять или мод значение таблиц с правами передавать свои права
249--5 задание
250grant all on all tables in schema public to Peresunko; -- даем третьому пользователю права администратора
251--6 задание
252revoke insert on route from Sarafanof ; --убираем права вставки в таблицу третьому пользователю
253--7 задание
254create view manager_Adnan as (select  * from transport_company where company_name='Auto bus');
255GRANT all on manager_Adnan to Adnan;-- даем права первом пользователю на доступ только для определенных строчек како-либо таблицы
256--8 задание
257grant all on driver to Peresunko;