SHARE
TWEET

Individual task final

Arzybek Dec 11th, 2019 (edited) 266 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*** ВАРИАНТ 9 ***/
  2. USE KN_301_Kazikulov
  3. go
  4.  
  5. DROP TABLE KN_301_Kazikulov.Kazikulov.Biblio;
  6. go
  7. DROP TABLE  KN_301_Kazikulov.Kazikulov.Records
  8. GO
  9. DROP TABLE KN_301_Kazikulov.Kazikulov.Books
  10. GO
  11. DROP TABLE KN_301_Kazikulov.Kazikulov.Abonents
  12. GO
  13.  
  14. DROP SYNONYM Currencies
  15. go
  16.  
  17. DROP PROCEDURE ShowCourses;
  18. GO
  19.  
  20. DROP TABLE KN_301_Kazikulov.Kazikulov.Abonents
  21. CREATE TABLE KN_301_Kazikulov.Kazikulov.Abonents
  22. (
  23.     Id Int NOT NULL PRIMARY KEY,
  24.     Name NVARCHAR(100) NOT NULL,
  25.     Surname NVARCHAR(100) NOT NULL,
  26.     Patronymic NVARCHAR(100) NULL,
  27.     Address NVARCHAR(200) NOT NULL,
  28.     PhoneNumber NVARCHAR(10) NOT NULL CHECK(try_convert(bigint,PhoneNumber) is not null),
  29.     CONSTRAINT UK_Abonents UNIQUE (Name,Surname,Patronymic)
  30. )
  31. GO
  32.  
  33. CREATE SYNONYM Abonents
  34. FOR [KN_301_Kazikulov].[Kazikulov].[Abonents]
  35. GO
  36.  
  37. Delete from Abonents;
  38. INSERT INTO Abonents
  39. VALUES
  40.     (1,'Арзыбек', 'Казикулов', NULL, 'г. Екатеринбург', '3221337777'),
  41.     (2,'Джокер', 'Доминикович', NULL, 'г. Готэм', '1234567890'),
  42.     (3, 'Вася', 'Пупкин', NULL, 'г. Москва', '1234567890')
  43. GO
  44. SELECT * FROM Abonents
  45.  
  46. DROP TABLE KN_301_Kazikulov.Kazikulov.Books
  47. CREATE TABLE KN_301_Kazikulov.Kazikulov.Books
  48. (
  49.     ID Int not null primary key,
  50.     Name NVARCHAR(100) NOT NULL,
  51.     Authors NVARCHAR(200) NOT NULL,
  52.     Izdatelstvo NVARCHAR(100) NOT NULL,
  53.     Place NVARCHAR(100) Not null,
  54.     Year SMALLINT CHECK(Year>0 AND YEAR<3000) NOT NULL,
  55.     PagesCount SMALLINT CHECK(PagesCount>0) NOT NULL,
  56.     CONSTRAINT UK_Books UNIQUE (Name,Authors,Year)
  57. )
  58. GO
  59.  
  60. CREATE SYNONYM Books
  61. FOR [KN_301_Kazikulov].[Kazikulov].[Books]
  62. GO
  63.  
  64. DELETE FROM Books
  65. GO
  66. INSERT INTO Books
  67. VALUES
  68.     (1,'Основы математического анализа','Фихтенгольц Г.М.', 'Наука', 'Москва', 1968, 441),
  69.     (2,'Гарри Поттер и Философский камень','Джоан К. Роулинг', 'Росмэн', 'Москва', 2003, 361),
  70.     (3, 'Операционные системы', 'Таненбаум', 'МГУ', 'Москва', 2006, 500),
  71.     (4, 'Единственная книга', 'Я', 'Я', 'Земля', 2019, 1)
  72. GO
  73. SELECT * FROM BOOKS
  74.  
  75. DROP TABLE KN_301_Kazikulov.Kazikulov.Biblio;
  76. CREATE TABLE KN_301_Kazikulov.Kazikulov.Biblio
  77. (
  78.     IDBook Int NOT NULL,
  79.     Cipher INT NOT NULL CHECK(Cipher>0),
  80.     Price INT NOT NULL CHECK(Price>0),
  81.     CountBook INT Not Null Check(CountBook>=0),
  82.     CONSTRAINT PkBook PRIMARY KEY (IDBook)
  83. )
  84. GO
  85.  
  86. ALTER TABLE [KN_301_Kazikulov].Kazikulov.Biblio ADD
  87.     CONSTRAINT FK_Books FOREIGN KEY (IDbook)
  88.     REFERENCES [KN_301_Kazikulov].Kazikulov.Books(Id)
  89. GO 
  90. CREATE SYNONYM Biblio
  91. FOR [KN_301_Kazikulov].[Kazikulov].Biblio
  92. GO
  93.  
  94. INSERT INTO Biblio
  95. VALUES
  96.     (1, 1, 100, 500),
  97.     (2, 11, 99, 100),
  98.     (3, 111, 199, 50),
  99.     (4, 1111, 99, 1)
  100. GO
  101. SELECT * FROM Biblio
  102.  
  103. DROP TABLE  KN_301_Kazikulov.Kazikulov.Records
  104. CREATE TABLE KN_301_Kazikulov.Kazikulov.Records
  105. (
  106.     IDBook INT Not null,
  107.     DateGiven DATE NOT NULL,
  108.     DateReturned DATE NULL,
  109.     IDAbonent INT Not null
  110.     constraint PK_Records PRIMARY KEY (IDbook, IDAbonent, DateGiven)
  111. )
  112. GO
  113.  
  114. CREATE SYNONYM Records
  115. FOR [KN_301_Kazikulov].[Kazikulov].Records
  116. GO
  117. ALTER TABLE [KN_301_Kazikulov].Kazikulov.Records ADD
  118.     CONSTRAINT FK_Book FOREIGN KEY (IDbook)
  119.     REFERENCES [KN_301_Kazikulov].Kazikulov.Books(Id)
  120. GO 
  121. ALTER TABLE [KN_301_Kazikulov].Kazikulov.Records ADD
  122.     CONSTRAINT FK_Abonent FOREIGN KEY (IdAbonent)
  123.     REFERENCES [KN_301_Kazikulov].Kazikulov.Abonents(Id)
  124. GO 
  125.  
  126. DROP TRIGGER Kazikulov.Records_Insert;
  127. go
  128. CREATE TRIGGER Records_Insert
  129. ON [KN_301_Kazikulov].[Kazikulov].[Records]
  130. instead of INSERT
  131. AS
  132. BEGIN
  133.     declare @returned date
  134.     declare @given date
  135.     declare @idBook smallint
  136.     declare @iduser smallint
  137.     declare @count smallint
  138.  
  139.     declare @curreturned cursor
  140.     declare @curidbook cursor
  141.     declare @curgiven cursor
  142.     declare @curiduser cursor
  143.    
  144.     set @CurReturned = CURSOR FOR  
  145.         SELECT DateReturned
  146.         FROM inserted
  147.     set @curgiven = CURSOR FOR  
  148.         SELECT DateGiven
  149.         FROM inserted
  150.     set @CurIDBook = CURSOR FOR  
  151.         SELECT IDBook
  152.         FROM inserted
  153.     set @CurIDUser = CURSOR FOR  
  154.         SELECT IDAbonent
  155.         FROM inserted
  156.  
  157.     open @CurIDBook
  158.     open @CurReturned
  159.     open @CurGiven
  160.     open @CurIdUser
  161.  
  162.     FETCH NEXT FROM @CurIDbook
  163.     INTO @idbook
  164.     FETCH NEXT FROM @CurReturned  
  165.     INTO @returned
  166.     FETCH NEXT FROM @CurGiven
  167.     INTO @given
  168.     FETCH NEXT FROM @CurIdUser  
  169.     INTO @iduser
  170.  
  171.  
  172.     WHILE @@FETCH_STATUS = 0  
  173.         BEGIN  
  174.         if(@returned is Null)
  175.         BEGIN
  176.             set @count = (select CountBook from Biblio where Biblio.IDBook = @idBook)
  177.  
  178.             if(@count<=0)
  179.                 raiserror('недостаточно книг', 16,1)
  180.  
  181.             UPDATE Biblio
  182.             SET CountBook -=1
  183.             WHERE Biblio.IDBook = @idBook
  184.                
  185.  
  186.             Insert into Records VALUES (@idBook, @given, @returned, @iduser)
  187.  
  188.             FETCH NEXT FROM @CurIDbook
  189.             INTO @idbook
  190.             FETCH NEXT FROM @CurReturned  
  191.             INTO @returned
  192.             FETCH NEXT FROM @CurGiven
  193.             INTO @given
  194.             FETCH NEXT FROM @CurIdUser  
  195.             INTO @iduser
  196.         END
  197.     end
  198.     close @curreturned
  199.     close @curidbook
  200. END
  201. GO
  202.  
  203. INSERT INTO Records
  204. VALUES
  205.     (1, '2019-12-10', NULL, 1),
  206.     (1, '2019-11-10', NULL, 2),
  207.     (2, '2019-10-09', NULL, 3)
  208. GO
  209.  
  210. select * from biblio
  211.  
  212. INSERT INTO Records
  213. VALUES
  214.     (4, '2019-12-10', NULL, 1)
  215. GO
  216.  
  217. INSERT INTO Records
  218. VALUES
  219.     (4, '2019-12-10', NULL, 1)
  220. GO
  221.  
  222. select * from biblio
  223. SELECT * FROM Records
  224.  
  225. DROP TABLE  KN_301_Kazikulov.Kazikulov.Orders
  226. CREATE TABLE KN_301_Kazikulov.Kazikulov.Orders
  227. (
  228.     IDBook INT Not null,
  229.     DateOrder DATE NOT NULL,
  230.     IDAbonent INT Not null
  231.     constraint PK_Orders PRIMARY KEY (IDbook, IDAbonent, DateOrder)
  232. )
  233. GO
  234.  
  235. CREATE SYNONYM Orders
  236. FOR [KN_301_Kazikulov].[Kazikulov].Orders
  237. GO
  238. ALTER TABLE [KN_301_Kazikulov].Kazikulov.Orders ADD
  239.     CONSTRAINT FK_OrderBook FOREIGN KEY (IDbook)
  240.     REFERENCES [KN_301_Kazikulov].Kazikulov.Books(Id)
  241. GO 
  242. ALTER TABLE [KN_301_Kazikulov].Kazikulov.Orders ADD
  243.     CONSTRAINT FK_OrderAbonent FOREIGN KEY (IdAbonent)
  244.     REFERENCES [KN_301_Kazikulov].Kazikulov.Abonents(Id)
  245. GO 
  246.  
  247. INSERT INTO Orders
  248. VALUES
  249.     (3, '2019-12-10', 2)
  250. GO
  251. SELECT * FROM Orders
  252.  
  253.  
  254. DROP FUNCtion GetAllPersonsBooks
  255. go
  256. CREATE Function GetAllPersonsBooks(@name nvarchar(100), @surname nvarchar(100), @patr nvarchar = null)
  257. RETURNs @result table (
  258.     [Имя] nvarchar(100),
  259.     [Авторы] nvarchar(200),
  260.     [Издательство] nvarchar(100),
  261.     [Город] nvarchar(100),
  262.     [Год] smallint,
  263.     [Кол-во страниц] smallint,
  264.     [Дата выдачи] date,
  265.     [Дата возврата] date,
  266.     [Ф.И.О] nvarchar(max)
  267. )
  268. AS
  269.     begin
  270.         INSERT INTO @result
  271.         (
  272.             Имя,
  273.             Авторы,
  274.             Издательство,
  275.             Город,
  276.             Год,
  277.             [Кол-во страниц],
  278.             [Дата выдачи],
  279.             [Дата возврата],
  280.             [Ф.И.О]
  281.         )
  282.         SELECT [KN_301_Kazikulov].Kazikulov.Books.Name,
  283.         Authors,
  284.         Izdatelstvo,
  285.         Place,
  286.         Year,
  287.         PagesCount,
  288.         DateGiven,
  289.         DateReturned,
  290.         CONCAT(Abonents.Name, ' ', Abonents.Surname, ' ', Abonents.Patronymic)
  291.         from records INNER JOIN [KN_301_Kazikulov].Kazikulov.Books ON IdBook = ID
  292.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Abonents ON records.IDAbonent = Abonents.Id
  293.         WHERE Abonents.Name LIKE @name AND Abonents.Surname like @surname AND (Abonents.Patronymic = @patr OR Abonents.Patronymic is null)
  294. return
  295. end
  296. go
  297.  
  298. select * from Abonents;
  299. SELECT * FROM GetAllPersonsBooks('Арзыбек', 'Казикулов', DEFAULT);
  300.  
  301. DROP FUNCtion GetAllPersonsBooks
  302. go
  303. CREATE Function GetBooksAllPersons(@name nvarchar(200))
  304. RETURNs @result table (
  305.     [Дата выдачи] date,
  306.     [Ф.И.О] nvarchar(max)
  307. )
  308. AS
  309.     begin
  310.         INSERT INTO @result
  311.         (
  312.             [Дата выдачи],
  313.             [Ф.И.О]
  314.         )
  315.         SELECT
  316.         DateGiven,
  317.         CONCAT(Abonents.Name, ' ', Abonents.Surname, ' ', Abonents.Patronymic)
  318.         from records INNER JOIN [KN_301_Kazikulov].Kazikulov.Books ON IdBook = ID
  319.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Abonents ON records.IDAbonent = Abonents.Id
  320.         WHERE Books.Name LIKE @name
  321. return
  322. end
  323. go
  324.  
  325. SELECT * FROM GetBooksAllPersons('Основы математического анализа');
  326.  
  327.  
  328. DROP Procedure ReturnBook
  329. go
  330. CREATE PROCEDURE ReturnBook @date date, @nameBook nvarchar(200), @namePerson nvarchar(100), @surname nvarchar(100), @patr nvarchar(100) = null AS
  331. BEGIN
  332.         UPDATE Biblio
  333.         SET CountBook +=1
  334.         from Biblio
  335.         inner join Books
  336.         on Biblio.IDBook = Books.ID
  337.         WHERE Books.Name = @nameBook
  338.  
  339.         UPDATE Records
  340.         SET DateReturned = @date
  341.         from Records
  342.         inner join Abonents
  343.         on Records.IDAbonent = Abonents.Id
  344.         WHERE Abonents.Name = @namePerson and Abonents.Surname = @surname and (Abonents.Patronymic = @patr or Abonents.Patronymic is Null)
  345.         Print(concat(@namePerson,' updated ', @nameBook,' on date ', @date))
  346. end
  347. go
  348.  
  349. exec ReturnBook '2019-11-13', 'Основы математического анализа', 'Арзыбек', 'Казикулов'
  350. exec ReturnBook '2019-12-30', 'Гарри Поттер и Философский камень', 'Арзыбек', 'Казикулов'
  351.  
  352. DROP Procedure GetMostReadPerson
  353. go
  354. CREATE PROCEDURE GetMostReadPerson @from date, @to date AS
  355. BEGIN
  356.         SELECT TOP 1
  357.         CONCAT(Abonents.Name, ' ', Abonents.Surname, ' ', Abonents.Patronymic) as ФИО,
  358.         count(records.DateGiven) as [Кол-во]
  359.         from records
  360.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Abonents ON records.IDAbonent = Abonents.Id
  361.         WHERE records.DateGiven >= @from and records.DateReturned is not null and Records.DateReturned <= @to
  362.         Group by Abonents.Name, Abonents.Surname, Abonents.Patronymic
  363. end
  364. go
  365.  
  366.  
  367. INSERT INTO Records
  368. VALUES
  369.     (2, '2019-12-10', NULL, 1)
  370. GO
  371. select * from biblio
  372. SELECT * FROM Records
  373.  
  374. exec GetMostReadPerson '2019-01-01','2019-12-31'
  375.  
  376. DROP Procedure GetMostBook
  377. go
  378. CREATE PROCEDURE GetMostBook
  379. as begin
  380.         SELECT top 1
  381.         Books.Name as Имя,
  382.         Books.Authors as Авторы,
  383.         Books.Year as Год,
  384.         Biblio.CountBook as Количество
  385.         from Biblio
  386.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Books ON Biblio.IDBook = Books.Id
  387.         Order by Biblio.CountBook DESC
  388. end
  389. go
  390.  
  391. exec GetMostBook
  392.  
  393.  
  394. DROP Procedure GetMostOrderedBook
  395. go
  396. CREATE PROCEDURE GetMostOrderedBook
  397. as begin
  398.         SELECT top 1
  399.         Books.Name as Имя,
  400.         Books.Authors as Авторы,
  401.         Books.Year as Год,
  402.         count(Orders.IDBook) as Количество
  403.         from Orders
  404.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Books ON Orders.IDBook = Books.Id
  405.         GROUP BY Books.Name, Books.Authors, Books.Year, Orders.IDBook
  406. end
  407. go
  408.  
  409. select * from orders
  410. exec GetMostOrderedBook
  411.  
  412. DROP Procedure GetMostReadedBook
  413. go
  414. CREATE PROCEDURE GetMostReadedBook  @from date, @to date
  415. as begin
  416.         SELECT top 1
  417.         Books.Name as Имя,
  418.         Books.Authors as Авторы,
  419.         Books.Year as Год,
  420.         count(Records.IDBook) as Количество
  421.         from Records
  422.         INNER JOIN [KN_301_Kazikulov].Kazikulov.Books ON Records.IDBook = Books.Id
  423.         WHERE records.DateGiven >= @from and (Records.DateReturned <= @to or Records.DateReturned is null)
  424.         GROUP BY Books.Name, Books.Authors, Books.Year, Records.IDBook
  425. end
  426. go
  427.  
  428. select * from records
  429. exec GetMostReadedBook '2019-01-01','2019-12-31'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top