Bn1mZj1q

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'Sergeeva'
8)
9ALTER DATABASE [Sergeeva] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'Sergeeva'
16)
17DROP DATABASE [Sergeeva]
18GO
19
20CREATE DATABASE [Sergeeva]
21GO
22
23USE [Sergeeva]
24GO
25
26IF EXISTS(
27  SELECT *
28    FROM sys.schemas
29   WHERE name = N'Sergeeva'
30) 
31 DROP SCHEMA Sergeeva
32GO
33
34CREATE SCHEMA Sergeeva 
35GO
36
37CREATE TABLE [Sergeeva].Sergeeva.books
38(
39	BookId int IDENTITY(1,1) NOT NULL, 
40	BookTitle nvarchar(60) NULL, 
41	PublishingHouse nvarchar(40) NULL,
42	PublishingPlace nvarchar(40) NULL,
43	PublishingYear date NULL,
44	PageSize int NULL,
45	BookClass nvarchar(20) NULL,
46	Price money NULL,
47	Quantity int NULL
48    CONSTRAINT PK_BookId PRIMARY KEY (BookId) 
49)
50GO
51
52 INSERT INTO [Sergeeva].Sergeeva.books
53 (BookTitle, PublishingHouse, PublishingPlace, PublishingYear, PageSize, BookClass, Price, Quantity)
54 VALUES 
55 ('Скарлетт', 'Белый Город', 'г. Москва', '1990', 680, 'Б90', 710, 3),
56 ('Война и мир. 2 том', 'РОСМЭН', 'г. Ленинград', '1989', 367, 'А45', 480, 2),
57 ('Лолита', 'Белый Город', 'г. Москва', '1995', 230, '074', 170, 1),
58 ('Прощай, оружие', 'Эксмо-Пресс', 'г. Калиниград', '2002', 230, 'С02', 196, 2),
59 ('Три товарища', 'Эксмо-Пресс', 'г. Калиниград', '1997', 302, 'К34', 279, 1)
60GO	
61
62CREATE TABLE [Sergeeva].Sergeeva.authors
63(
64	AuthorName nvarchar(40) NOT NULL, 
65	BookId int NULL
66	CONSTRAINT FK_BookIdAuthors FOREIGN KEY (BookId)
67	REFERENCES Sergeeva.books(BookId)
68	ON UPDATE CASCADE
69)
70GO
71
72 INSERT INTO [Sergeeva].Sergeeva.authors
73 (AuthorName, BookId)
74 VALUES 
75('Александра Рипли', 1),
76('Лев Толстой', 2),
77('Владимир Набоков', 3),
78('Эрнест Хемингуэй', 4),
79('Эрих Мария Ремарк', 5)
80GO	
81
82CREATE TABLE [Sergeeva].Sergeeva.users
83(
84	ReaderTicket nvarchar(40) NOT NULL, 
85	UserSurname nvarchar(60) NULL, 
86	UserName nvarchar(60) NULL, 
87	UserFatherName nvarchar(60) NULL, 
88	UserAdress nvarchar(100) NULL,
89	UserTel nvarchar(20) NULL
90    CONSTRAINT PK_ReaderTicket PRIMARY KEY (ReaderTicket) 
91)
92GO
93
94 INSERT INTO [Sergeeva].Sergeeva.users
95 (ReaderTicket, UserSurname, UserName, UserFatherName, UserAdress, UserTel)
96 VALUES 
97 ('3456790', 'Шевченко', 'Анастасия', 'Дмитриевна', 'ул. Полярная, д. 9', '+79820236789'),
98 ('3479654', 'Романенко', 'Мария', 'Александровна', 'ул. Сизова, д. 2', '+79224560883'),
99 ('5478021', 'Кудунов', 'Артем', 'Владимирович', 'ул. Комсомольская, д. 3', '+79512210131'),
100 ('1237652', 'Перепичка', 'Ирина', 'Сергеевна', 'ул. Сафонова, д. 7', '+79227850066')
101GO	
102
103CREATE TABLE [Sergeeva].Sergeeva.booking
104(
105	BookingId int IDENTITY(1,1) NOT NULL, 
106	BookId int NULL, 
107	ReaderTicket nvarchar(40) NULL,
108	BookingDate date NULL
109    CONSTRAINT PK_BookingId PRIMARY KEY (BookingId)
110	CONSTRAINT FK_BookIdBooking FOREIGN KEY (BookId)
111	REFERENCES Sergeeva.books(BookId)
112	ON UPDATE CASCADE,
113	CONSTRAINT FK_ReaderTicketBooking FOREIGN KEY (ReaderTicket)
114	REFERENCES Sergeeva.users(ReaderTicket)
115	ON UPDATE CASCADE 
116)
117GO
118
119 INSERT INTO [Sergeeva].Sergeeva.booking
120 (BookId, ReaderTicket, BookingDate)
121 VALUES 
122(2, '5478021', '2017-09-30'),
123(5, '1237652', '2017-10-02'),
124(2, '3479654', '2017-10-02')
125GO	
126
127CREATE TABLE [Sergeeva].Sergeeva.biblioSystem
128(
129	RecordId int IDENTITY(1,1) NOT NULL, 
130	BookId int NULL, 
131	ReaderTicket nvarchar(40) NULL,
132	GivenDate date NULL,
133	ReturnDate date NULL
134    CONSTRAINT PK_RecordId PRIMARY KEY (RecordId) 
135	CONSTRAINT FK_BookId FOREIGN KEY (BookId)
136	REFERENCES Sergeeva.books(BookId)
137	ON UPDATE CASCADE,
138	CONSTRAINT FK_ReaderTicket FOREIGN KEY (ReaderTicket)
139	REFERENCES Sergeeva.users(ReaderTicket)
140	ON UPDATE CASCADE
141)
142GO
143
144CREATE TRIGGER Sergeeva.oneUserForOneBook
145ON [Sergeeva].Sergeeva.biblioSystem
146AFTER INSERT
147AS
148BEGIN
149	DECLARE @idBook int
150	SET @idBook=(
151		SELECT TOP(1) BookId
152		FROM Sergeeva.biblioSystem
153		ORDER BY RecordId DESC
154	)
155	DECLARE @countBook int
156		SET @countBook=(
157		SELECT Quantity
158		FROM Sergeeva.books
159		WHERE books.BookId = @idBook
160	)
161	DECLARE @countBookInSystem int
162	SET @countBookInSystem=(
163		SELECT COUNT(*)
164		FROM Sergeeva.biblioSystem
165		WHERE BookId = @idBook
166	)
167	IF @countBook > @countBookInSystem - 1 RETURN;
168	DECLARE @dateOfReturn date
169	SET @dateOfReturn=(
170		SELECT MIN(a.ReturnDate)
171		FROM (
172			SELECT TOP(@countBook + 1) ReturnDate
173			FROM Sergeeva.biblioSystem
174			ORDER BY RecordId DESC
175		) AS a
176	)
177	DECLARE @curGivenDate date
178	SET @curGivenDate=(
179		SELECT TOP(1) GivenDate
180		FROM Sergeeva.biblioSystem
181		ORDER BY RecordId DESC
182	)
183	IF @dateOfReturn < @curGivenDate RETURN
184	RAISERROR ('Эта книга уже взята другим читателем', 10, 1)
185	ROLLBACK TRANSACTION;
186RETURN
187END
188GO
189
190
191 INSERT INTO [Sergeeva].Sergeeva.biblioSystem
192 (BookId, ReaderTicket, GivenDate, ReturnDate)
193 VALUES 
194(1, '3456790', '2017-09-15', '2017-09-25'),
195(5, '3479654', '2017-09-26', '2017-10-06'),
196(4, '1237652', '2017-09-19', '2017-10-29'),
197(3, '3456790', '2017-09-23', '2017-10-03'), 
198(5, '3456790', '2017-10-07', '2017-10-17')
199GO
200
201-- Взять "Лолита", которая уже взята другим читателем (она всего одна)
202 INSERT INTO [Sergeeva].Sergeeva.biblioSystem
203 (BookId, ReaderTicket, GivenDate, ReturnDate)
204 VALUES 
205(3, '3456790', '2017-09-25', '2017-10-05')
206GO
207
208-- Взять "Прощай, оружие", которых всего 2, и обе у читателей
209 INSERT INTO [Sergeeva].Sergeeva.biblioSystem
210 (BookId, ReaderTicket, GivenDate, ReturnDate)
211 VALUES 
212(4, '3456790', '2017-09-26', '2017-10-06'),
213(4, '1237652', '2017-09-28', '2017-10-08')
214GO
215
216-- Вывод сведений о книгах, взятых определенным читателем
217DECLARE @curUser nvarchar(40)
218SET @curUser = '3456790'
219SELECT books.BookTitle AS 'Название книги', 
220		authors.AuthorName AS 'Автор книги',
221		books.PublishingHouse AS 'Издательство', 
222		books.PublishingPlace AS 'Место издания', 
223		FORMAT(books.PublishingYear, N'yyyy') AS 'Год издания', 
224		books.BookClass AS 'Библиотечный шифр',
225		books.PageSize AS 'Количество страниц',
226		books.Price AS 'Цена'
227FROM Sergeeva.authors INNER JOIN
228    Sergeeva.books ON Sergeeva.authors.BookId = Sergeeva.books.BookId INNER JOIN
229    Sergeeva.biblioSystem ON Sergeeva.books.BookId = Sergeeva.biblioSystem.BookId
230WHERE biblioSystem.ReaderTicket = @curUser
231
232-- Сведения о читателях, у которых находится определенная книга
233DECLARE @curBook int
234SET @curBook = 5
235SELECT users.ReaderTicket AS 'Номер читательского билета', 
236		users.UserSurname AS 'Фамилия', 
237		users.UserName AS 'Имя',
238		users.UserFatherName AS 'Отчество',
239		users.UserAdress AS 'Адрес',
240		users.UserTel AS 'Телефон'
241FROM Sergeeva.users INNER JOIN 
242Sergeeva.biblioSystem ON Sergeeva.biblioSystem.ReaderTicket = Sergeeva.users.ReaderTicket INNER JOIN
243Sergeeva.books ON Sergeeva.biblioSystem.BookId = Sergeeva.books.BookId
244WHERE biblioSystem.BookId = @curBook
245
246-- Сведения о читателе, прочитавшем за определенный интервал времени максимальное количество книг
247DECLARE @startRead date
248SET @startRead = '2017-09-15'
249DECLARE @endRead date
250SET @endRead = '2017-10-30'
251SELECT TOP(1) a.ReaderTicket AS 'Номер читательского билета', 
252		users.UserSurname AS 'Фамилия', 
253		users.UserName AS 'Имя',
254		users.UserFatherName AS 'Отчество',
255		users.UserAdress AS 'Адрес',
256		users.UserTel AS 'Телефон'
257FROM (
258	SELECT ReaderTicket, COUNT(*) as countBooks
259	FROM Sergeeva.biblioSystem
260	WHERE GivenDate > @startRead AND ReturnDate < @endRead
261	GROUP BY ReaderTicket
262) AS a INNER JOIN Sergeeva.users ON a.ReaderTicket = Sergeeva.users.ReaderTicket
263ORDER BY a.countBooks DESC
264
265-- Сведения о книге, количество экземпляров которой больше всего
266SELECT TOP(1) books.BookTitle AS 'Название книги', 
267	authors.AuthorName AS 'Автор книги',
268	books.PublishingHouse AS 'Издательство', 
269	books.PublishingPlace AS 'Место издания', 
270	FORMAT(books.PublishingYear, N'yyyy') AS 'Год издания', 
271	books.BookClass AS 'Библиотечный шифр',
272	books.PageSize AS 'Количество страниц',
273	books.Price AS 'Цена'
274FROM Sergeeva.authors INNER JOIN
275    Sergeeva.books ON Sergeeva.authors.BookId = Sergeeva.books.BookId 
276ORDER BY Quantity DESC
277
278-- Сведения о книге, на которую заказов больше всего
279SELECT TOP(1) books.BookTitle AS 'Название книги', 
280	authors.AuthorName AS 'Автор книги',
281	books.PublishingHouse AS 'Издательство', 
282	books.PublishingPlace AS 'Место издания', 
283	FORMAT(books.PublishingYear, N'yyyy') AS 'Год издания', 
284	books.BookClass AS 'Библиотечный шифр',
285	books.PageSize AS 'Количество страниц',
286	books.Price AS 'Цена'
287FROM (
288	SELECT BookId, COUNT(*) AS countBookings
289	FROM Sergeeva.booking
290	GROUP BY BookId
291) AS b INNER JOIN Sergeeva.books ON b.BookId = Sergeeva.books.BookId INNER JOIN
292Sergeeva.authors ON Sergeeva.authors.BookId = Sergeeva.books.BookId
293ORDER BY b.countBookings DESC
294
295-- Сведения о книге, которую читают за определенный интервал времени максимальное количество читателей
296DECLARE @startReadForBook date
297SET @startReadForBook = '2017-09-15'
298DECLARE @endReadForBook date
299SET @endReadForBook = '2017-10-30'
300SELECT TOP(1) books.BookTitle AS 'Название книги', 
301	authors.AuthorName AS 'Автор книги',
302	books.PublishingHouse AS 'Издательство', 
303	books.PublishingPlace AS 'Место издания', 
304	FORMAT(books.PublishingYear, N'yyyy') AS 'Год издания', 
305	books.BookClass AS 'Библиотечный шифр',
306	books.PageSize AS 'Количество страниц',
307	books.Price AS 'Цена'
308FROM (
309	SELECT BookId, COUNT(*) as countReaders
310	FROM Sergeeva.biblioSystem
311	WHERE GivenDate > @startReadForBook AND ReturnDate < @endReadForBook
312	GROUP BY BookId
313) AS a INNER JOIN Sergeeva.books ON a.BookId = Sergeeva.books.BookId INNER JOIN
314Sergeeva.authors ON Sergeeva.authors.BookId = Sergeeva.books.BookId
315ORDER BY a.countReaders DESC