LgLpP9Es

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 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 ;
19drop function delete_strain();
20
21
22drop view if exists docent cascade ;
23drop view if exists rich_teacher cascade ;
24drop view if exists Dep_Design cascade ;
25-------------------------------------------------------------------Таблицы----------------------------------------------
26
27--количество часов
28create sequence clock_ids;
29create table if not exists clock(
30  id int primary key default nextval('clock_ids'),
31  number_of_hours int not null);
32
33
34
35--Группа
36create sequence group_of_student_ids;
37create table if not exists group_of_student (
38id int primary key default nextval('group_of_student_ids'),
39number_group varchar(64)not null ,
40specialty varchar(64) not null);
41
42
43
44
45--Кафедра
46create sequence pulpit_ids;
47create table if not exists pulpit(id int primary key default nextval('pulpit_ids'),
48name_pulpit varchar(64) not null);
49
50
51--Должность
52create sequence post_ids;
53create table if not exists post(
54id int primary key default nextval('post_ids'),
55post_name varchar(50) not null);
56
57
58--Научная степень
59create sequence academic_title_ids;
60create table if not exists academic_title(
61id int primary key default nextval('academic_title_ids'),
62name_degree varchar (64) not null  );
63
64
65--Преподаватель
66create sequence teacher_ids ;
67create table if not exists teacher(
68id int primary key default nextval('teacher_ids'),
69name varchar(64) not null ,
70gender varchar(64) not null ,
71adress varchar(64) not null ,
72family_camp varchar(64 ) not null,
73salary int not null ,
74pulpit_id int not null ,
75post_id int not null ,
76academic_title_id int ,
77foreign key (pulpit_id) references pulpit (id),
78foreign key (post_id) references post (id),
79foreign key (academic_title_id) references academic_title(id));
80
81
82--Предмет
83create sequence thing_ids;
84create table if not exists thing(
85id int primary key default nextval('thing_ids'),
86name_thing varchar(64) not null,
87clock_id int not null,
88group_of_student_id int not null,
89foreign key (group_of_student_id) references group_of_student(id),
90foreign key (clock_id) references clock(id));
91
92
93
94--Нагрузка
95create sequence strain_ids;
96create table if not exists strain(
97id int primary key default nextval('strain_ids'),
98type_strain varchar (64) not null,
99teacher_id int not null ,
100thing_id int not null ,
101foreign key (teacher_id) references teacher(id),
102foreign key (thing_id) references thing (id));
103
104
105
106--Группа
107insert into group_of_student
108values(nextval('group_of_student_ids'),'AI-173','IT-developer');
109
110insert into group_of_student
111values (nextval('group_of_student_ids'),'AI-174','IT-developer');
112
113insert into group_of_student
114values (nextval('group_of_student_ids'),'AE-165','Web-desiqner');
115
116
117
118
119--Кафеда
120insert into pulpit
121values (nextval('pulpit_ids'),'Department of Computer Science');
122
123insert into pulpit
124values (nextval('pulpit_ids'),'Department of Design');
125
126
127
128--Должность
129insert into post
130values(nextval('post_ids'),'docent');
131
132insert into post
133values (nextval('post_ids'),'teacher');
134
135insert into post
136values (nextval('post_ids'),'assistant');
137
138
139
140
141
142--Ученное звание
143insert  into academic_title
144values (nextval('academic_title_ids'),'PhD');--кандидат наук
145
146insert  into academic_title
147values (nextval('academic_title_ids'),'Ph.D');--доктор наук
148
149
150
151
152
153--Учителя
154insert into teacher
155values (nextval('teacher_ids'),'Petrovich Aleksandr Aleksandrovich','male','Zabolotnogo 54','married',10500,1,1,1);
156
157insert into teacher
158values (nextval('teacher_ids'),'Dmitrenko Sergey Olkseevich','male','Deribasovskaya 14 ','not married',7500,2,2,2 );
159
160insert  into teacher
161values (nextval('teacher_ids'),'Vorotnyak Elena Viktorovna','female','Kanatnaya 25','not married',20500,2,2,null );
162
163--Количество часов
164insert into clock
165values (nextval('clock_ids'),5);
166
167insert into clock
168values (nextval('clock_ids'),10);
169
170insert into clock
171values (nextval('clock_ids'),15);
172
173
174
175--Предмет
176insert into thing
177values (nextval('thing_ids'),'Computer science',1,1);
178
179insert into thing
180values (nextval('thing_ids'),'Web-development',2,3);
181
182insert into thing
183values (nextval('thing_ids'),'OOP',3,2);
184
185
186--Нагрузка
187insert into strain
188values (nextval('strain_ids'),'lecture',1,1);
189
190
191insert into strain
192values (nextval('strain_ids'),'course work',2,2);
193
194insert into strain
195values (nextval('strain_ids'),'exam',3,3);
196
197
198
199
200
201
202--Вывод таблиц--
203select * from group_of_student;
204select * from  pulpit;
205select * from post;
206select * from academic_title;
207select * from teacher;
208select * from clock;
209select * from thing;
210select * from strain;
211
212
213
214--Запросы/подзапросы
215select name_thing from thing where clock_id =1;--вывод предмета,которыц имеет кол-во часов =5
216select * from pulpit where name_pulpit ='Department of Computer Science';
217select * ,salary from teacher order by (salary) desc ;
218select * from group_of_student where number_group like 'AI%';
219select * from teacher order by name  ;
220
221select AVG(salary) as salaryAVG from teacher;--средняя зарплата преподователей
222select max(salary) from teacher;
223select min(salary) from teacher;
224
225select  * from group_of_student where number_group like'AI%' and  specialty like 'IT-developer%';
226select * from  teacher where pulpit_id in (1,2);
227select * from thing where clock_id between 2 and 3;
228select  count(*) from teacher;
229select sum(salary) from teacher ;
230
231
232--Подзапросы
233select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='OOP'));
234select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Computer science'));
235select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Web-development'));
236
237select * from teacher where academic_title_id =(select id from academic_title  where id='1');--кандидат наук
238select * from teacher where academic_title_id =(select id from academic_title  where id='2');--доктор наук
239
240select * from teacher where id in(select id from strain where  type_strain='course work');
241select * from teacher where id in(select id from strain where  type_strain='lecture');
242select * from teacher where id in(select id from strain where  type_strain='exam');
243
244select * from teacher where exists(select id from pulpit where id = 3);--так как поле пустое ,то подзапрос ничего не выведет
245
246
247
248
249--Триггеры/Функции
250create or replace function delete_strain()
251returns trigger as
252  $$
253  begin delete from strain where (teacher_id=old.id);
254  end ;
255
256
257
258  $$language'plpgsql'
259
260create trigger del_strain
261  before delete
262  on teacher
263  for each row
264  execute procedure delete_strain();
265
266
267
268
269--Представление
270create view docent as select * from teacher where id in(select id from post where post_name ='docent');
271select * from docent;
272
273
274create view rich_teacher as select * from teacher where salary > 10500;
275select * from rich_teacher;
276
277
278
279
280create 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'));
281select * from Dep_Design;
282
283--Пользователи
284create user administrator;
285create user client;
286
287
288
289
290grant all on all tables in schema public to admimistrator;
291grant select on all tables in schema  to public;