bJpinUKg

1drop database if exists институт;
2create database институт;
3use институт;
4
5create table отделы (
6идентификатор_отдела varchar(6) not null primary key,
7название_отдела varchar(40));
8
9create table научные_направления (
10идентификатор_направления varchar(8) not null primary key,
11название_научной_специальности varchar(40));
12
13create table сотрудники(
14табельный_номер int(4) not null primary key,
15ФИО varchar(40) not null,
16должность varchar(20) not null,
17ученое_звание varchar(10),
18шифр_специальности int(6),
19научная_специальность varchar(8),
20номер_отдела varchar(6),
21foreign key (научная_специальность) references научные_направления (идентификатор_направления) on delete set null,
22foreign key (номер_отдела) references отделы (идентификатор_отдела) on delete set null);
23
24insert into отделы values
25('000001', 'кафедра точных наук'),
26('000002', 'кафедра общественных наук'),
27('000003', 'школа дизайна'),
28('000004', 'кафедра экономики и управления'),
29('000005', 'информационные технологии');
30
31insert into научные_направления values
32('AAAAAAAA', 'математика'),
33('AAAAAAAB', 'программирование'),
34('AAAAAAAC', 'история'),
35('AAAAAAAD', 'право'),
36('AAAAAAAE', 'философия'),
37('AAAAAAAF', 'дизайн'),
38('AAAAAAAG', 'менеджмент'),
39('AAAAAAAH', 'международные отношения'),
40('AAAAAAAI', 'физика');
41
42insert into сотрудники values
43(1111, 'Павлов Сергей Юрьевич', 'Лектор', 'Профессор', 749763, 'AAAAAAAA', '000001'),
44(1112, 'Ширинкина Вера Андреевна', 'Семинарист', 'Доцент', 749763, 'AAAAAAAA', '000001'),
45(1113, 'Кожевников Александр Георгиевич', 'Лектор', 'Профессор', 749763, 'AAAAAAAA', '000001'),
46(1114, 'Губина Мария Анатольевна', 'Семинарист', null, 627474, 'AAAAAAAA', '000001'),
47(1115, 'Курских Павел Алексеевич', 'Семинарист', 'Профессор', 445444, 'AAAAAAAA', '000001'),
48(1116, 'Шатохин Артем Владиславович', 'Лектор', null, 445444, 'AAAAAAAI', '000001'),
49(1117, 'Орзоева Мари Конатовна', 'Семинарист', 'Профессор', 445444, 'AAAAAAAI', '000001'),
50(1118, 'Мусина Надежда Александровна', 'Лектор', null, 445444, 'AAAAAAAB', '000005'),
51(1119, 'Зайцев Юрий Алексеевич', 'Семинарист', 'Профессор', 839677, 'AAAAAAAB', '000005'),
52(1120, 'Городских Иван Иванович', 'Семинарист', 'Доцент', 839677, 'AAAAAAAB', '000005'),
53(1121, 'Помазан Роберт Мануйлович', 'Лектор', 'Профессор', 839677, 'AAAAAAAB', '000005'),
54(1122, 'Митина Ольга Сергеевна', 'Семинарист', 'Профессор', 839677, 'AAAAAAAC', '000002'),
55(1123, 'Карцев Андрей Александрович', 'Лектор', null, 839677, 'AAAAAAAC', '000002'),
56(1144, 'Прогаммер Линус Тодальтович', 'Семинарист', 'Доцент', 839677, 'AAAAAAAD', '000002'),
57(1124, 'Портман Зорий Ильич', 'Лектор', 'Профессор', 936836, 'AAAAAAAD', '000002'),
58(1125, 'Волова Лариса Ивановна', 'Семинарист', 'Профессор', 846948, 'AAAAAAAE', '000002'),
59(1126, 'Васильева Татьяна Андреевна', 'Лектор', null, 846948, 'AAAAAAAE', '000002'),
60(1127, 'Новиков Петр Иванович', 'Семинарист', 'Доцент', 840300, 'AAAAAAAE', '000002'),
61(1129, 'Путилова Евгения Дмитриевна', 'Семинарист', 'Профессор', 994875, 'AAAAAAAF', '000003'),
62(1130, 'Сумин Аркадий Эльманович', 'Лектор', 'Профессор', 842994, 'AAAAAAAF', '000003'),
63(1131, 'Демина Наталья Петровна', 'Семинарист', 'Доцент', 759467, 'AAAAAAAG', '000004'),
64(1132, 'Демина Наталья Петровна', 'Лектор', null, 556843, 'AAAAAAAG', '000004'),
65(1133, 'Правдин Олег Григорьевич', 'Лектор', 'Профессор', 847058, 'AAAAAAAH', '000004'),
66(1134, 'Гордеев Станислав Иванович', 'Семинарист', 'Доцент', 847058, 'AAAAAAAH', '000004');
67
68select a.ФИО, a.ученое_звание, b.название_научной_специальности
69from сотрудники a
70left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
71where a.научная_специальность is not null
72order by a.научная_специальность;
73
74select a.название_научной_специальности
75from научные_направления a
76left join сотрудники b on (a.идентификатор_направления=b.научная_специальность)
77where b.научная_специальность is null;
78
79with t1 as(select count(*) count, b.название_научной_специальности
80from сотрудники a
81left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
82where a.научная_специальность is not null
83group by b.название_научной_специальности)
84select *
85from t1
86where count = (select max(count) from t1);
87
88create view сотрудники_отдела_информационные_технологии
89as
90select a.*
91from сотрудники a
92left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
93where b.название_отдела='информационные технологии';
94select * from сотрудники_отдела_информационные_технологии;
95
96create view Качественный_состав_научных_сотрудников_отделов
97as
98select doc.название_отдела, doc.количество_доцентов, prof.количество_профессоров from(	
99				select b.название_отдела, count(*) количество_доцентов
100				from сотрудники a 
101				left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
102				where a.ученое_звание = 'доцент'
103				group by b.название_отдела) doc
104		left join (select b.название_отдела, count(*) количество_профессоров
105				from сотрудники a 
106				left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
107				where a.ученое_звание = 'профессор'
108				group by b.название_отдела) prof using(название_отдела);
109select * from Качественный_состав_научных_сотрудников_отделов;
110
111create view Научные_кадры
112as
113select b.название_отдела, c.название_научной_специальности, count(*) количесвто_сотрудников
114from сотрудники a
115left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
116left join научные_направления c on (a.научная_специальность=c.идентификатор_направления)
117group by b.название_отдела, c.название_научной_специальности;
118select * from Научные_кадры;
119
120
121drop procedure if exists сотрудники_отдела;
122delimiter //
123create procedure сотрудники_отдела(отдел varchar(40))
124begin
125select a.ФИО, b.название_отдела
126from сотрудники a
127left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
128where b.название_отдела=отдел;
129end //
130call сотрудники_отдела('кафедра точных наук');
131
132
133drop procedure if exists количество_сотрудников_специальности;
134delimiter //
135create procedure количество_сотрудников_специальности(отдел varchar(40))
136begin
137select count(*) count, b.название_научной_специальности
138from сотрудники a
139left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
140where b.название_научной_специальности=отдел;
141end //
142call количество_сотрудников_специальности('программирование');
143
144
145drop procedure if exists отдел_специальность_сотрудника;
146delimiter //
147create procedure отдел_специальность_сотрудника(сотрудник varchar(40))
148begin
149select a.ФИО, c.название_отдела, b.название_научной_специальности
150from сотрудники a
151left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
152left join отделы c on (a.номер_отдела=c.идентификатор_отдела)
153where a.ФИО=сотрудник;
154end //
155call отдел_специальность_сотрудника('Помазан Роберт Мануйлович');
156
157
158drop procedure if exists направление_должность_количество;
159delimiter //
160create procedure направление_должность_количество(направление varchar(40))
161begin
162select b.название_научной_специальности, a.должность, count(*)
163from сотрудники a
164left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
165where b.название_научной_специальности=направление
166group by b.название_научной_специальности, a.должность;
167end //
168call направление_должность_количество('программирование');
169
170
171drop procedure if exists отделы_с_огр_кол_сотрудников;
172delimiter //
173create procedure отделы_с_огр_кол_сотрудников(low int, high int)
174begin
175select b.название_отдела, count(*) count
176from сотрудники a
177left join отделы b on (a.номер_отдела=b.идентификатор_отдела)
178group by b.название_отдела
179having count >= low and count <= high;
180end //
181call отделы_с_огр_кол_сотрудников(4, 15);
182
183
184drop view if exists специальность_макс_колва_доц_проф;
185create view специальность_макс_колва_доц_проф
186as
187with t1 as(select count(*) count, b.название_научной_специальности
188from сотрудники a
189left join научные_направления b on (a.научная_специальность=b.идентификатор_направления)
190where a.ученое_звание in ('доцент', 'профессор')
191group by b.название_научной_специальности)
192select *
193from t1
194where count = (select max(count) from t1);
195
196select distinct d.название_отдела
197from специальность_макс_колва_доц_проф a 
198join научные_направления b on (b.название_научной_специальности=a.название_научной_специальности)
199join сотрудники c on (b1.идентификатор_направления=c.научная_специальность)
200join отделы d on (c.номер_отдела=d.идентификатор_отдела);
201
202
203