V1jGR9FB

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