LGs8CmZs

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name
6FROM sys.databases
7WHERE name = N'Stroeva'
8)
9ALTER DATABASE [Stroeva] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name
14FROM sys.databases
15WHERE name = N'Stroeva'
16)
17DROP DATABASE [Stroeva]
18GO
19
20CREATE DATABASE [Stroeva]
21GO
22
23USE [Stroeva]
24GO
25
26IF EXISTS(
27  SELECT *
28FROM sys.schemas
29WHERE name = N'lab5'
30) 
31 DROP SCHEMA lab5
32GO
33
34CREATE SCHEMA lab5 
35GO
36
37IF OBJECT_ID('[Stroeva].lab5.Deputies', 'U') IS NOT NULL
38  DROP TABLE  [Stroeva].lab5.Deputies
39GO
40
41CREATE TABLE lab5.Deputies
42(
43    DeputyId INT IDENTITY(1,1) NOT NULL,
44    Surname nvarchar(50) NOT NULL,
45    DeputyName nvarchar(50) not null,
46    FatherName nvarchar(50) not null,
47    Address nvarchar(100) not null,
48    HomePhone nvarchar(15) not null,
49    WorkPhone nvarchar(15) not null,
50    CONSTRAINT PK_DeputyId PRIMARY KEY(DeputyId)
51)
52
53IF OBJECT_ID('[Stroeva].lab5.Comissions', 'U') IS NOT NULL
54  DROP TABLE  [Stroeva].lab5.Comissions
55GO
56
57CREATE TABLE lab5.Comissions
58(
59    ComissionId int identity(1,1) not null,
60    Subject nvarchar(50) not null,
61    CONSTRAINT PK_ComissionId PRIMARY KEY(ComissionId)
62)
63
64IF OBJECT_ID('[Stroeva].lab5.ComissionMembers', 'U') IS NOT NULL
65  DROP TABLE  [Stroeva].lab5.ComissionMembers
66GO
67
68CREATE TABLE lab5.ComissionMembers
69(
70    ComissionId int not null,
71    MemberId int not null,
72    InDate date not null,
73    OutDate date,
74    IsPresent bit not null,
75    IsChairman bit not null,
76    CONSTRAINT FK_ComissionId FOREIGN KEY (ComissionId)
77        REFERENCES lab5.Comissions(ComissionId)
78        ON UPDATE CASCADE,
79    CONSTRAINT FK_MemberId FOREIGN KEY (MemberId)
80        REFERENCES lab5.Deputies(DeputyId)
81        ON UPDATE CASCADE
82)
83
84IF OBJECT_ID('[Stroeva].lab5.Conferences', 'U') IS NOT NULL
85  DROP TABLE  [Stroeva].lab5.Conferences
86GO
87
88CREATE TABLE lab5.Conferences
89(
90    ConfId INT IDENTITY(1,1) NOT NULL,
91    ComissionId int not null,
92    Place nvarchar(100) NOT NULL,
93    ConfTime datetime not null,
94    CONSTRAINT PK_ConfId PRIMARY KEY(ConfId),
95    CONSTRAINT FK_ConfComisionId FOREIGN KEY(ComissionId)
96        REFERENCES lab5.Comissions(ComissionId)
97        ON UPDATE CASCADE
98)
99
100IF OBJECT_ID('[Stroeva].lab5.ConferenceAttendance', 'U') IS NOT NULL
101  DROP TABLE  [Stroeva].lab5.ConferenceAttendance
102GO
103
104CREATE TABLE lab5.ConferenceAttendance
105(
106    ConferenceId int not null,
107    MemberId int not null,
108    CONSTRAINT FK_ConferenceId FOREIGN KEY(ConferenceId)
109        REFERENCES lab5.Conferences(ConfId)
110        ON UPDATE CASCADE,
111    CONSTRAINT FK_ConfMemberId FOREIGN KEY(MemberId)
112        REFERENCES lab5.Deputies
113        ON UPDATE CASCADE
114)
115
116INSERT INTO lab5.Deputies
117VALUES
118    ('Иванов', 'Иван', 'Иванович', 'ул. Комсомольская, 35, 21', '314-23-54', '221-96-15'),
119    ('Сидоров', 'Андрей', 'Константинович', 'ул. Ленина, 54, 15', '345-12-12', '221-96-10'),
120    ('Петров', 'Александр', 'Сергеевич', 'ул. Пушкина, 3, 7', '345-75-43', '221-96-34'),
121    ('Попов', 'Павел', 'Игоревич', 'ул. Советская, 62, 132', '314-45-93', '221-67-23'),
122    ('Стрелков', 'Михаил', 'Юрьевич', 'ул. Первомайская, 43, 98', '314-19-37', '221-67-52'),
123    ('Мамонова', 'Екатерина', 'Олеговна', 'ул. Пехотинцев, 97, 84', '289-97-98', '221-10-64'),
124    ('Коверко', 'Юлия', 'Сергеевна', 'пр. Космонавтов, 145, 273', '146-11-27', '221-78-51'),
125    ('Щепина', 'Екатерина', 'Геннадьевна', 'ул. Стачек, 55, 280', '264-77-15', '221-95-56'),
126    ('Палешев', 'Артём', 'Андреевич', 'ул. Уральская, 87, 13', '314-87-51', '221-57-14'),
127    ('Буньков', 'Алексей', 'Дмитриевич', 'ул. Крылова, 21, 15', '168-98-78', '221-24-57'),
128    ('Бахтеев', 'Владислав', 'Викторович', 'ул. Восточная, 45, 97', '314-24-56', '221-62-15'),
129    ('Кудунов', 'Артём', 'Владимирович', 'ул. Ильича, 45, 75', '314-45-27', '221-78-43'),
130    ('Рязанова', 'Ксения', 'Дмитриевна', 'ул. Московская, 241, 451', '356-54-89', '221-16-87')
131GO
132
133SELECT *
134FROM lab5.Deputies
135
136INSERT INTO lab5.Comissions
137VALUES
138    ('Образование'),
139    ('ЖКХ'),
140    ('Общественный транспорт'),
141    ('Медицина'),
142    ('Культура'),
143    ('Управление'),
144    ('Дороги'),
145    ('Благоустройство'),
146    ('Строительство'),
147    ('Сельское хозяйство')
148GO
149
150INSERT INTO lab5.ComissionMembers
151VALUES
152    (1, 1, '2017-04-15', null, 1, 1),
153    (1, 2, '2017-04-15', null, 1, 0),
154    (1, 3, '2017-04-18', null, 1, 0),
155    (1, 7, '2017-04-21', null, 1, 0),
156    (1, 9, '2017-05-4', '2017-10-09', 0, 0),
157    (1, 11, '2016-12-11', '2017-04-14', 0, 1),
158    (2, 5, '2017-06-22', null, 1, 1),
159    (2, 8, '2017-08-24', '2017-11-20', 0, 0),
160    (2, 13, '2017-09-10', null, 1, 0),
161    (2, 7, '2017-07-18', null, 1, 0),
162    (3, 12, '2016-06-15', null, 1, 1),
163    (3, 6, '2016-07-11', null, 1, 0),
164    (3, 4, '2016-12-23', '2017-10-12', 0, 0)
165GO
166
167INSERT INTO lab5.Conferences
168VALUES
169    (1, 'каб. 150', '2017-10-11'),
170    (1, 'каб. 340', '2016-12-15'),
171    (1, 'каб. 451', '2017-04-20'),
172    (1, 'каб. 300', '2017-10-10'),
173    (2, 'каб. 340', '2017-10-11'),
174    (2, 'каб. 300', '2017-08-10'),
175    (2, 'каб. 500', '2017-08-30'),
176    (3, 'каб. 100', '2016-11-15'),
177    (3, 'каб. 245', '2017-10-10')
178GO
179
180CREATE TRIGGER lab5.AttendanceValid
181ON lab5.ConferenceAttendance
182AFTER INSERT
183AS
184BEGIN
185    IF EXISTS(
186        SELECT *
187    FROM inserted as first INNER JOIN
188        lab5.Conferences first_conf ON first.ConferenceId=first_conf.ConfId,
189        inserted as second INNER JOIN
190        lab5.Conferences second_conf ON second.ConferenceId=second_conf.ConfId
191    WHERE first.MemberId=second.MemberId AND
192        first.ConferenceId!=second.ConferenceId AND
193        first_conf.ConfTime=second_conf.ConfTime
194    )
195    BEGIN
196        ROLLBACK TRANSACTION;
197        THROW 51000, 'Депутат находится на двух комиссиях одновременно', 1;
198        RETURN;
199    END
200END
201GO
202
203INSERT INTO lab5.ConferenceAttendance
204VALUES
205    (1, 1),
206    (1, 2),
207    (1, 3),
208    (1, 7),
209    (2, 11),
210    (3, 1),
211    (3, 2),
212    (4, 1),
213    (4, 3),
214    (4, 7),
215    (5, 5),
216    (5, 8),
217    (5, 13),
218    -- (5, 7),
219    -- (5,1),
220    (6, 13),
221    (6, 7),
222    (7, 7),
223    (7, 13),
224    (7, 8),
225    (8, 6),
226    (8, 12),
227    (9, 4),
228    (9, 6),
229    (9, 12)
230
231--Показать список комиссий, для каждой – ее состав и председателя.
232SELECT coms1.Subject as 'Комиссия',
233    dep1.Surname+' '+dep1.DeputyName+' '+dep1.FatherName as 'Председатель',
234    dep2.Surname+' '+dep2.DeputyName+' '+dep2.FatherName as 'Депутат'
235FROM lab5.Comissions as coms1 INNER JOIN
236    lab5.ComissionMembers as members1 ON coms1.ComissionId=members1.ComissionId AND members1.IsChairman=1 INNER JOIN
237    lab5.Deputies dep1 ON members1.MemberId=dep1.DeputyId,
238    lab5.Comissions as coms2 INNER JOIN
239    lab5.ComissionMembers as members2 ON coms2.ComissionId=members2.ComissionId AND members2.IsChairman=0 INNER JOIN
240    lab5.Deputies dep2 ON members2.MemberId=dep2.DeputyId
241WHERE members1.IsPresent=1 AND members2.IsPresent=1
242
243--показать в хронологическом порядке всех председателей комиссии
244DECLARE @startDate date='2016-10-10'
245DECLARE @endDate date='2018-01-01'
246DECLARE @comission nvarchar(50)='Образование'
247SELECT Comissions.Subject as 'Комиссия',
248    deps.Surname+' '+deps.DeputyName+' '+deps.FatherName as 'Председатель'
249FROM lab5.Comissions INNER JOIN
250    lab5.ComissionMembers as members ON members.ComissionId=Comissions.ComissionId INNER JOIN
251    lab5.Deputies as deps on members.MemberId=deps.DeputyId
252WHERE Comissions.Subject=@comission AND
253    members.IsChairman=1 AND
254    members.InDate>@startDate AND
255    (members.OutDate<@endDate OR
256    members.OutDate IS NULL)
257ORDER BY members.InDate
258
259--Показать список членов Думы, для каждого из них – список комиссий, в которых он участвовал и/или был председателем
260SELECT
261    dep.Surname+' '+dep.DeputyName+' '+dep.FatherName as 'Депутат',
262    comms.Subject as 'Комиссия'
263FROM lab5.Deputies as dep INNER JOIN
264    lab5.ComissionMembers as members ON dep.DeputyId=members.MemberId INNER JOIN
265    lab5.Comissions as comms ON members.ComissionId=comms.ComissionId
266GROUP BY dep.Surname+' '+dep.DeputyName+' '+dep.FatherName, comms.Subject
267
268--Для указанного интервала дат и комиссии выдать список членов с указанием количества пропущенных заседаний.
269DECLARE @startMissDate date='2016-10-10';
270DECLARE @endMissDate date='2018-01-01';
271DECLARE @comissionMiss nvarchar(50)='Образование';
272WITH AttendanceCount(memberId,confCount) AS (
273SELECT members.MemberId as memberId,
274COUNT(*) as confCount
275FROM lab5.Conferences as conf INNER JOIN
276lab5.Comissions as coms ON coms.ComissionId=conf.ComissionId INNER JOIN
277lab5.ComissionMembers as members ON coms.ComissionId=members.ComissionId INNER JOIN
278lab5.ConferenceAttendance as attend ON attend.MemberId=members.MemberId AND attend.ConferenceId=conf.ConfId
279WHERE coms.Subject=@comissionMiss AND
280    conf.ConfTime>@startMissDate AND
281    conf.ConfTime<@endMissDate
282GROUP BY members.MemberId)
283SELECT dep.Surname+' '+dep.DeputyName+' '+dep.FatherName as 'Депутат',
284COUNT(*)-AttendanceCount.confCount as 'Пропущенные заседания'
285FROM lab5.Conferences as conf INNER JOIN
286lab5.Comissions as coms ON coms.ComissionId=conf.ComissionId,
287AttendanceCount INNER JOIN
288lab5.Deputies as dep ON dep.DeputyId=AttendanceCount.memberId
289WHERE coms.Subject=@comissionMiss
290GROUP BY dep.Surname+' '+dep.DeputyName+' '+dep.FatherName, AttendanceCount.confCount
291
292--Вывести список заседаний в указанный интервал дат в хронологическом порядке, для каждого заседания – список присутствующих
293DECLARE @confStartDate date='2016-10-10'
294DECLARE @confEndDate date='2018-01-01'
295SELECT comms.Subject as 'Комиссия',
296    FORMAT(conf.ConfTime, 'D', 'ru-RU') as 'Дата',
297    dep.Surname+' '+dep.DeputyName+' '+dep.FatherName as 'Депутат'
298FROM
299    lab5.Conferences as conf INNER JOIN
300    lab5.Comissions as comms ON conf.ComissionId=comms.ComissionId INNER JOIN
301    lab5.ConferenceAttendance as attend ON attend.ConferenceId=conf.ConfId INNER JOIN
302    lab5.Deputies as dep ON dep.DeputyId=attend.MemberId
303WHERE conf.ConfTime>@confStartDate AND
304    conf.ConfTime<@confEndDate
305ORDER BY conf.ConfTime
306
307--По каждой комиссии показать количество проведенных заседаний в указанный период времени
308DECLARE @confListStartDate date='2016-10-10'
309DECLARE @confListEndDate date='2018-01-01'
310SELECT coms.Subject as 'Комиссия',
311COUNT(*) as 'Количество заседаний'
312FROM lab5.Conferences as conf INNER JOIN
313lab5.Comissions as coms ON conf.ComissionId=coms.ComissionId
314WHERE conf.ConfTime>@confListStartDate AND
315    conf.ConfTime<@confListEndDate
316GROUP BY coms.Subject