LaYERNRh

1drop table if exists teacher cascade;
2drop table if exists strain cascade;
3drop table if exists thing cascade;
4drop table if exists pulpit cascade;
5drop table if exists group_of_student cascade;
6drop table if exists post cascade;
7drop table if exists academic_title cascade;
8drop table if exists clock cascade ;
9DROP VIEW IF EXISTS rich_teacher CASCADE ;
10
11drop sequence if exists teacher_ids cascade;
12drop sequence if exists strain_ids cascade;
13drop sequence if exists thing_ids cascade;
14drop sequence if exists pulpit_ids cascade;
15drop sequence if exists group_of_student_ids cascade;
16drop sequence if exists post_ids cascade;
17drop sequence if exists academic_title_ids cascade;
18drop sequence if exists clock_ids cascade ;
19
20
21
22
23drop view if exists docent cascade;
24drop view if exists rich_teacher cascade ;
25drop view if exists Dep_Design cascade ;
26
27-----------------------------------------------------------------------------------------------------------------------
28--Таблицы
29
30
31--количество часов
32create sequence clock_ids;
33create table if not exists clock(
34  id int primary key default nextval('clock_ids'),
35  number_of_hours int not null);
36
37
38--Группа
39create sequence group_of_student_ids;
40create table if not exists group_of_student (
41id int primary key default nextval('group_of_student_ids'),
42number_group varchar(64)not null ,
43specialty varchar(64) not null);
44
45
46--Кафедра
47create sequence pulpit_ids;
48create table if not exists pulpit(
49id int primary key default nextval('pulpit_ids'),
50name_pulpit varchar(64) not null);
51
52
53--Должность
54create sequence post_ids;
55create table if not exists post(
56id int primary key default nextval('post_ids'),
57post_name varchar(50) not null);
58
59
60--Научная степень
61create sequence academic_title_ids;
62create table if not exists academic_title(
63id int primary key default nextval('academic_title_ids'),
64name_degree varchar (64) not null  );
65
66
67--Преподаватель
68create sequence teacher_ids ;
69create table if not exists teacher(
70id int primary key default nextval('teacher_ids'),
71name varchar(64) not null ,
72gender varchar(64) not null ,
73adress varchar(64) not null ,
74family_camp varchar(64 ) not null,
75salary int not null ,
76pulpit_id int not null ,
77post_id int not null ,
78academic_title_id int ,
79foreign key (pulpit_id) references pulpit (id),
80foreign key (post_id) references post (id),
81foreign key (academic_title_id) references academic_title(id));
82
83
84--Предмет
85create sequence thing_ids;
86create table if not exists thing(
87id int primary key default nextval('thing_ids'),
88name_thing varchar(64) not null,
89clock_id int not null,
90group_of_student_id int not null,
91foreign key (group_of_student_id) references group_of_student(id),
92foreign key (clock_id) references clock(id));
93
94
95--Нагрузка
96create sequence strain_ids;
97create table if not exists strain(
98id int primary key default nextval('strain_ids'),
99type_strain varchar (64) not null,
100teacher_id int not null ,
101thing_id int not null ,
102foreign key (teacher_id) references teacher(id),
103foreign key (thing_id) references thing (id));
104
105-----------------------------------------------------------------------------------------------------------------------
106-- Триггеры:
107
108-- 1 Триггер проверки формата номера группы
109create or replace function validate_group() returns trigger as
110$$
111begin
112  IF (new.number_group not like 'A%-%%%') THEN
113    RAISE EXCEPTION 'Wrong group!';
114    return null;
115  end if;
116  return new;
117end;
118$$ language 'plpgsql';
119
120create trigger t_validate_group
121  before insert or update
122  on group_of_student
123  for each row
124execute procedure validate_group();
125
126-- 2 При удалении преподователя, удаляет все его нагрузки
127create or replace function del_teacher() returns trigger as
128$$
129begin
130  delete from strain where teacher_id = old.id;
131end;
132$$ language 'plpgsql';
133
134create trigger t_del_teacher
135  before delete
136  on teacher
137  for each row
138execute procedure del_teacher();
139
140-- 3 Триггер проверяет зарплаты
141  -- (у доктора наук должна быть больше
142  -- средней зарплаты кандидатов в доктора)
143create or replace function check_salary() returns trigger as
144$$
145begin
146  IF (new.academic_title_id = (select id from academic_title where name_degree = 'PhD')) THEN
147    return new;
148  end if;
149  IF (new.salary > (select avg(salary)
150                    from teacher
151                    where academic_title_id = (select id from academic_title where name_degree = 'PhD'))) THEN
152    return new;
153  else
154    RAISE exception 'Salary is too small';
155    return null;
156  end if;
157end;
158$$ language 'plpgsql';
159
160create trigger t_check_salary
161  before insert or update
162  on teacher
163  for each row
164execute procedure check_salary();
165
166-- 4 Проверить, добавлен ли предмет к академ группе
167create or replace function check_thing() returns trigger as
168$$
169begin
170  IF exists(
171      select * from thing where name_thing = new.name_thing and group_of_student_id = new.group_of_student_id) THEN
172    RAISE EXCEPTION 'Group already has this thing to learn';
173    return null;
174  end if;
175  return new;
176end;
177$$ language 'plpgsql';
178
179create trigger t_check_thing
180  before insert
181  on thing
182  for each row
183execute procedure check_thing();
184
185-- 5 Проверить нет ли такой нагрузки по такому предмету
186create or replace function check_strain() returns trigger as
187$$
188begin
189  IF exists(select * from strain where type_strain = new.type_strain and thing_id = new.thing_id) THEN
190    RAISE EXCEPTION 'Такая нагрузка по данному предмету уже была добавлена';
191    return null;
192  end if;
193  return new;
194end;
195$$ language 'plpgsql';
196
197create trigger t_check_strain
198  before insert
199  on strain
200  for each row
201execute procedure check_strain();
202
203-----------------------------------------------------------------------------------------------------------------------
204-- Функции
205
206-- 1 Рассчитать налог на сотрудника
207create or replace function count_nalog(teacher_id int) returns float as
208$$
209declare
210  salary float;
211begin
212  salary := (select teacher.salary from teacher where id = teacher_id);
213  return ((salary * 18) / 100);
214end;
215$$ language 'plpgsql';
216
217-- 2 Подсчитать общее количество часов нагрузки
218create or replace function count_total_hours(teacher_id int) returns int as
219$$
220declare
221  hours int;
222begin
223  select sum(number_of_hours)
224  from clock
225  where id in (select clock_id
226               from thing
227               where id in (select thing_id from strain where strain.teacher_id = strain.teacher_id)) into hours;
228  return hours;
229end;
230$$ language 'plpgsql';
231
232-- Средняя заплата, среди преподавателей
233  -- с +/- одинаковым количеством часов
234create or replace function count_average_salary(t_id int) returns float as
235$$
236declare
237  hours  int;
238  salary float;
239begin
240  select count_total_hours(t_id) into hours;
241  select avg(teacher.salary) from teacher where (select count_total_hours(id) between (hours - 5) and (hours + 5)) into salary;
242  return salary;
243end;
244$$ language 'plpgsql';
245
246
247-----------------------------------------------------------------------------------------------------------------------
248--Ввод данных
249
250--Группа
251insert into group_of_student
252values(nextval('group_of_student_ids'),'AI-173','IT-developer');
253
254insert into group_of_student
255values (nextval('group_of_student_ids'),'AI-174','IT-developer');
256
257insert into group_of_student
258values (nextval('group_of_student_ids'),'AE-165','Web-desiqner');
259
260
261--Кафеда
262insert into pulpit
263values (nextval('pulpit_ids'),'Department of Computer Science');
264
265insert into pulpit
266values (nextval('pulpit_ids'),'Department of Design');
267
268
269--Должность
270insert into post
271values(nextval('post_ids'),'docent');
272
273insert into post
274values (nextval('post_ids'),'teacher');
275
276insert into post
277values (nextval('post_ids'),'assistant');
278
279
280--Ученное звание
281insert  into academic_title
282values (nextval('academic_title_ids'),'PhD');--кандидат наук
283
284insert  into academic_title
285values (nextval('academic_title_ids'),'Ph.D');--доктор наук
286
287
288--Учителя
289insert into teacher
290values (nextval('teacher_ids'),'Petrovichenko  Aleksandr Aleksandrovich','male','Zabolotnogo 54','married',11500,1,1,1);
291
292insert into teacher
293values (nextval('teacher_ids'),'Dmitrenko Sergey Olkseevich','male','Deribasovskaya 14 ','not married',14500,2,2,2 );
294
295insert  into teacher
296values (nextval('teacher_ids'),'Vorotnyak Elena Viktorovna','female','Kanatnaya 25','not married',20500,2,2,null );
297
298
299--Количество часов
300insert into clock
301values (nextval('clock_ids'),5);
302
303insert into clock
304values (nextval('clock_ids'),10);
305
306insert into clock
307values (nextval('clock_ids'),15);
308
309
310--Предмет
311insert into thing
312values (nextval('thing_ids'),'Computer science',1,1);
313
314insert into thing
315values (nextval('thing_ids'),'Web-development',2,3);
316
317insert into thing
318values (nextval('thing_ids'),'OOP',3,2);
319
320
321--Нагрузка
322insert into strain
323values (nextval('strain_ids'),'lecture',1,1);
324
325
326insert into strain
327values (nextval('strain_ids'),'course work',2,2);
328
329insert into strain
330values (nextval('strain_ids'),'exam',3,3);
331
332
333-----------------------------------------------------------------------------------------------------------------------
334--Вывод таблиц--
335
336--1
337select * from group_of_student;
338--2
339select * from  pulpit;
340--3
341select * from post;
342--4
343select * from academic_title;
344--5
345select * from teacher;
346--6
347select * from clock;
348--7
349select * from thing;
350--8
351select * from strain;
352
353
354-----------------------------------------------------------------------------------------------------------------------
355--Запросы
356
357--1
358select name_thing from thing where clock_id =1;--вывод предмета,который  имеет кол-во часов =5
359--2
360select * from pulpit where name_pulpit ='Department of Computer Science';--Данный запрос выволит кафедру компьютерных наук
361--3
362select * ,salary from teacher order by (salary) desc ; --Данный запрос выводит зарплату ,Операрот order by сортирует выходные значения,параметр DESC-порядок сортирования по убыванию
363--4
364select * from group_of_student where number_group like 'AI%';--Данный запрос вывовидит номер группы по формату 'AI'
365--5
366select * from teacher order by name  ; --Данный запрос выводит преподователй ,сортируя их по имени,как было ранее сказано операторо order by сортирует выходные значения
367--6
368select AVG(salary) as salaryAVG from teacher;--Данный запрос выводит среднию зарплату преподователей,именно оператор AVG() считает среднее значение
369--7
370select max(salary) from teacher;--Данный запрос выводит самую высокую зарплату, MAX() функция возвращающая максимальное значение столбца таблицы
371--8
372select min(salary) from teacher;--Данный запрос выводит самую маленькую  зарплату,MIN() функция возвращабщая минимальное значение столбца таблицы
373--9
374select  * from group_of_student where number_group like'AI%' and  specialty like 'IT-developer%'; --Данный запрос вывовидит номер группы по формату 'AI' или специальность 'IT-developer'
375--10
376select * from  teacher where pulpit_id in (1,2);--Данный запрос выводит преподователей, которые работают на кафедрах равным по id один,два .Оператор IN позволяет определить, совпадает ли значение объекта со значением в списке
377--11
378select * from thing where clock_id between 2 and 3;--Данный запрос выводит таблицу предметы с количеством часов в диапазоне  id от двух  до трех. Оператор  BETWEEN задает диапазон, в котором будет осуществляться проверка условия.
379--12
380select  count(*) from teacher;--Данный запрос выводит количество преподователей
381--13
382select sum(salary) from teacher ;--Данный запрос выводит общую сумму зарплат всех преподователей
383
384
385--Подзапросы
386
387--1
388select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='OOP'));--Данный запрос выводит имя преподователя ,который преподает предмет 'OOP'
389select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Computer science'));--Данный запрос выводит имя преподователя ,который преподает предмет 'Computer science'
390select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Web-development'));--Данный запрос выводит имя преподователя ,который преподает предмет 'Web-development'
391--2
392select * from teacher where academic_title_id =(select id from academic_title  where id='1');--Данный запрос выводит преподователя с ученной степенью 'кандидат наук'
393select * from teacher where academic_title_id =(select id from academic_title  where id='2');--Данный запрос выводит преподователя с ученной степенью 'доктор наук'
394--3
395select * from teacher where id in(select id from strain where  type_strain='course work');--Данный запрос выводит препедователя ,который имеет академическую нагрузку 'курсоввая работа'
396select * from teacher where id in(select id from strain where  type_strain='lecture');
397select * from teacher where id in(select id from strain where  type_strain='exam');
398--4
399select * from thing where clock_id > ALL( select clock_id from thing where name_thing='Web-development');--Данный запрос выводит предметы ,у которых больше количество чаосов ,чем у предмета'OOP'
400--5
401select * from teacher where exists(select id from pulpit where id = 3);--Данный запрос должен выводить кафедру ,которая имеет номер id три,но так как поле пустое ,то подзапрос ничего не выведет Запросы для вывода ранее сделанный представлений
402--Запрос на вывод представлений
403
404
405
406--Представление
407--1
408create view docent as select * from teacher where id in(select id from post where post_name ='docent');
409select * from docent
410--2
411create view rich_teacher as select * from teacher where salary >=10500 order by salary desc;
412select * from rich_teacher
413--3
414create view Dep_Design as select * from teacher where id in(select teacher_id from strain where pulpit_id=(select id from pulpit where  name_pulpit='Department of Design'));
415select * from Dep_Design
416
417
418-----------------------------------------------------------------------------------------------------------------------
419--Пользователи
420create user administrator;
421create user client;
422
423grant all on all tables in schema public to admimistrator;
424grant select on all tables in schema  to public;