Удивить преподавателя в SQL скрипте

-----------------------------------------------------------------------------------------------------------
--Удаление базы
-----------------------------------------------------------------------------------------------------------
USE [master]
GO
 
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MassMediaDB')
ALTER DATABASE MassMediaDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MassMediaDB')
  DROP DATABASE MassMediaDB
GO
 
-----------------------------------------------------------------------------------------------------------
-- Создание базы
-----------------------------------------------------------------------------------------------------------
CREATE DATABASE MassMediaDB
COLLATE Cyrillic_General_CI_AS
GO
 
-----------------------------------------------------------------------------------------------------------
-- Для создания диаграммы БД
-----------------------------------------------------------------------------------------------------------
ALTER AUTHORIZATION ON DATABASE::MassMediaDB TO [sa];
 
GO
 
USE MassMediaDB;
 
-----------------------------------------------------------------------------------------------------------
-- Создание ролей и пользователей
-----------------------------------------------------------------------------------------------------------
IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'Key_One')
BEGIN
    CREATE LOGIN Key_One 
    WITH PASSWORD = N'12345'
END
 
IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'KP_journalist')
BEGIN
    CREATE LOGIN KP_journalist 
    WITH PASSWORD = '1234';
END
GO
 
CREATE USER Key_One FOR LOGIN Key_One;
 
GO
 
CREATE USER KP_journalist FOR LOGIN KP_journalist;
 
GO
 
CREATE ROLE administrators AUTHORIZATION Key_One;
CREATE ROLE journalists AUTHORIZATION KP_journalist;
 
GO
 
EXEC sp_addrolemember 'administrators', 'Key_One'
EXEC sp_addrolemember 'journalists', 'KP_journalist'
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Создание таблиц
-----------------------------------------------------------------------------------------------------------
CREATE TABLE Journalists
(
    JournalistID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255),
    ZIP VARCHAR(255),
    Country VARCHAR(255),
    Education VARCHAR(255),
    Salary INT,
    CurrentPublishHouse INT
); 
 
CREATE TABLE Events
(
    EventID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    GPSLat VARCHAR(255),
    GPSLon VARCHAR(255),
    Address VARCHAR(255),
    ZIP INT,
    City VARCHAR(255),
    Country VARCHAR(255),
    ImportanceGrade INT,
    ResponsibleJournalist INT,
    Description VARCHAR(255)
); 
 
CREATE TABLE Publishings
(
    PublishingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Title VARCHAR(255),
    PType NVARCHAR(255),
    Priority INT NOT NULL,
    JournalistNumber INT NOT NULL,
    AboutEvent INT NOT NULL,
    PublishHouseIssued INT NOT NULL,
    IssueDate DATE NOT NULL,
    MiddleNote FLOAT
); 
 
CREATE TABLE PublishHouse
(
    PublishingHouseID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PHName VARCHAR(255) NOT NULL,
    Address VARCHAR(255),
    ZIP VARCHAR(255),
    City VARCHAR(255),
    Country VARCHAR(255),
    EstimIncome INT,
    Last_Modification_Stamp DATETIME,
    HBUserCode NVARCHAR(MAX)
); 
 
GO
 
ALTER TABLE Events WITH CHECK ADD
CONSTRAINT FK_Resp_Journalist FOREIGN KEY (ResponsibleJournalist) REFERENCES Journalists (JournalistID)
ON UPDATE CASCADE ON DELETE CASCADE; 
 
ALTER TABLE Journalists WITH CHECK ADD
CONSTRAINT FK_Journalist_Working FOREIGN KEY (CurrentPublishHouse) REFERENCES PublishHouse (PublishingHouseID)
ON UPDATE CASCADE ON DELETE CASCADE; 
 
ALTER TABLE Publishings WITH CHECK ADD
CONSTRAINT FK_About_Evt FOREIGN KEY (AboutEvent) REFERENCES Events (EventID)
ON UPDATE CASCADE ON DELETE CASCADE; 
 
ALTER TABLE Publishings WITH CHECK ADD
CONSTRAINT FK_Journalists FOREIGN KEY (JournalistNumber) REFERENCES Journalists (JournalistID)
ON UPDATE NO ACTION ON DELETE NO ACTION; 
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Триггер с вложенными курсорами
-----------------------------------------------------------------------------------------------------------
 
CREATE TRIGGER TR_AddStamp ON PublishHouse
AFTER INSERT, DELETE, UPDATE
AS
DECLARE @x INT, @x_old INT, @y INT, @y_old INT
-- курсор с новыми значениями
DECLARE CUR1 CURSOR FOR
    SELECT PublishingHouseID, EstimIncome, Country
    FROM inserted
-- курсор со старыми значениями
DECLARE CUR2 CURSOR FOR
    SELECT PublishingHouseID, EstimIncome, Country
    FROM deleted
OPEN CUR1
OPEN CUR2
-- перемещаемся параллельно по обоим курсорам
    FETCH NEXT FROM CUR1 INTO @x, @y ,@c
    FETCH NEXT FROM CUR2 INTO @x_old, @y_old, @c_old
    WHILE @@FETCH_STATUS=0
      BEGIN
      UPDATE PublishHouse
      SET EstimIncome=EstimIncome+@y_old
      WHERE PublishingHouseID=@x_old
      IF NOT EXISTS (SELECT * FROM PublishHouse
         WHERE PublishingHouseID=@x)
      INSERT INTO PublishHouse(PublishingHouseID,EstimIncome)
        VALUES (@x,@y)
      ELSE
        UPDATE PublishHouse
        SET EstimIncome=EstimIncome-@y
        WHERE PublishingHouseID=@x
      FETCH NEXT FROM CUR1 INTO @x, @y
    FETCH NEXT FROM CUR2 INTO @x_old, @y_old
END
CLOSE CUR1
CLOSE CUR2
DEALLOCATE CUR1
DEALLOCATE CUR2
 
UPDATE PublishHouse  
SET Last_Modification_Stamp = CURRENT_TIMESTAMP, HBUserCode = @x
WHERE PublishingHouseID IN (SELECT DISTINCT PublishingHouseID FROM Inserted);
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Заполнение таблиц данными
-----------------------------------------------------------------------------------------------------------
 
INSERT INTO PublishHouse (
                         PHName,
                         Address,
                         City,
                         ZIP,
                         Country,
                         EstimIncome
                         )
VALUES  ('Solario','Space blvd. 3','Latisana','2234','Italy','15000'),
        ('Charlie Hebdo','Videocard street 57','Besanson-Derricole','56987','France','18000'),
        ('Komsomolskaya pravda','Red square 47','Sevastopol','012321','Russia','19000'),
        ('NY Times','Strauss-Kahn highway 14','New York','40090','USA','109000'),
        ('ANNA-News','Voentorg blvd 18','Donetsk','45669','DPR','1008009'),
        ('Tagesanzeiger','Oensingerstrasse 25','Hamburg','31222','Germany','15000'); 
 
INSERT INTO Journalists (
                        FName,
                        Address,
                        City,
                        ZIP,
                        Country,
                        Education,
                        Salary,
                        CurrentPublishHouse
                        )
VALUES  ('Sparkle Twilight','Intel street 9','Qualcommville','456789','USA','Nvidia University','7200','4'),
        ('Applejack','AMD street 13','Intelstadt','456789','Germany','Google University','55500','3'),
        ('Dash Rainbow','Nvidia street 16','TSMC City','456789','Italy','Qt University','2255','2'),
        ('Apple Bloom','Main street 1','Canterlot','023456','Equestria','Canterlot Royal High School','72575','1'),
        ('Pie Pinkie','Cup Cake street 12','Ponyville','049789','Equestria','Bake College','8200','4'); 
 
INSERT INTO Events (
                    GPSLat,
                    GPSLon,
                    Address,
                    ZIP,
                    City,
                    Country,
                    ImportanceGrade,
                    ResponsibleJournalist,
                    Description
                   )
VALUES  ('60.05780065615907','53.67906250000003','Green blvd. 21','4006','ATIville','Norway','1','1','SomethingHappened1'),
        ('61.05780065615907','52.67906250000003','Greenfieldstr. 22','117262','Nvidia city','Russia','0','2','SomethingHappd2'),
        ('62.05780065615907','51.67906250000003','Green passage 23','92345','Qualcommville','Germany','3','3','SomethingHappen3'),
        ('63.05780065615907','50.67906250000003','Green way 24','46265','Intel county','Poland','2','2','SomethingHappened4'),
        ('64.05780065615907','49.67906250000003','Green blvd. 25','42006','GTX City','Argentine','1','1','SomethingHappened5'),
        ('65.05780065615907','48.67906250000003','Green blvd. 26','46006','AMD Town','Turkey','0','3','SomethingHappened6'); 
 
INSERT INTO Publishings (
                        Title,
                        PType,
                        Priority,
                        AboutEvent,
                        JournalistNumber,
                        PublishHouseIssued,
                        IssueDate,
                        MiddleNote
                        )
VALUES  ('Research on processors','Intel GMA','1','4','2','3','19951003','2.2'),
        ('Modern HDD','i7-5820K','2','3','5','1','20000503','3.1'),
        ('SDD Drives','AMDRadeon295x2','3','4','2','1','20051103','4.0'),
        ('XML vs CHM','GTX960','1','3','5','2','20031203','4.3'),
        ('Understanding XML','Dota2','2','3','4','5','20130303','5.0'),
        ('Intel GMA','Skyrim','5','2','3','4','20120903','1.7'); 
GO
 
-- Creating View
CREATE VIEW CustomView
AS SELECT EventID, ZIP, PublishingID, PType, Priority 
FROM Events, Publishings
WHERE ZIP < 50000 AND Priority<3;
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Некластерные индексы
-----------------------------------------------------------------------------------------------------------
 
CREATE NONCLUSTERED INDEX IX_Events
    ON Events (ResponsibleJournalist);
 
CREATE NONCLUSTERED INDEX IX_Journalists
    ON Journalists (CurrentPublishHouse);
 
CREATE NONCLUSTERED INDEX IX_Publishings
    ON Publishings (AboutEvent);
 
CREATE NONCLUSTERED INDEX IX_PublishHouse
    ON PublishHouse (PHName);
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Хранимые процедуры для изменения информации в таблице
-----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE InsertEvent  
    @GPSLat  varchar(255),
    @GPSLon varchar(255), 
    @Address varchar(255), 
    @ZIP int, 
    @City varchar(255),
    @Country varchar(255),
    @ImportanceGrade int,
    @ResponsibleJournalist int
AS 
    INSERT INTO Events(GPSLat,GPSLon,Address,ZIP,City,Country,ImportanceGrade,ResponsibleJournalist) 
    VALUES(
            @GPSLat,
            @GPSLon, 
            @Address, 
            @ZIP, 
            @City,
            @Country,
            @ImportanceGrade,
            @ResponsibleJournalist
    ); 
GO
 
CREATE PROCEDURE DeleteEvent  
    @EventID  int
AS 
    DELETE FROM Events WHERE Events.EventID=@EventID;
GO
 
CREATE PROCEDURE UpdateEvent
    @EventID  int,  
    @GPSLat  varchar(255),
    @GPSLon varchar(255), 
    @Address varchar(255), 
    @ZIP int, 
    @City varchar(255),
    @Country varchar(255),
    @ImportanceGrade int,
    @ResponsibleJournalist int
AS 
    UPDATE Events SET 
            GPSLat=@GPSLat,
            GPSLon=@GPSLon, 
            Address=@Address, 
            ZIP=@ZIP, 
            City=@City,
            Country=@Country,
            ImportanceGrade=@ImportanceGrade,
            ResponsibleJournalist=@ResponsibleJournalist
    WHERE EventID=@EventID; 
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Хранимая процедура с транзакциями
-----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE ChangePublishingPriority 
    @ID int,
    @NewPriority int
AS 
    BEGIN TRANSACTION
 
BEGIN 
 
TRY
 
   UPDATE Publishings SET Priority=@NewPriority WHERE PublishingID=@ID;
 
   COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
 
  ROLLBACK TRANSACTION
 
END CATCH
 
    BEGIN TRANSACTION
 
BEGIN 
 
TRY
   EXEC ('SELECT * FROM ufn_MyVecFunc(1);')
 
   COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
 
  ROLLBACK TRANSACTION
 
END CATCH
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Extension helper to convert to http format 
-----------------------------------------------------------------------------------------------------------
CREATE FUNCTION ufnMyScalarFunc(@PublishingID int, @ArticleID int)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
        DECLARE @HTML NVARCHAR(MAX)
        SET @HTML = '';
        DECLARE @Title NVARCHAR(MAX)
        SELECT @Title = Title FROM Publishings P WHERE P.PublishingID=@ArticleID
        SELECT @HTML = @HTML + '<div class="JourDesc"><div class="text">'+ISNULL(P.Title,'')+'</div></div>'
        FROM Journalists c INNER JOIN Publishings P ON P.JournalistNumber=c.JournalistID
        RETURN @HTML;
END;
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Получение статей от журналиста с самой высокой зарплатой
-----------------------------------------------------------------------------------------------------------
 
CREATE FUNCTION ufn_MyVecFunc (@ID int)
RETURNS @JournalistEfficiency TABLE
(JournalistID    INT         NOT NULL,
FName      VARCHAR(255)         NOT NULL,
Salary       INT    NOT NULL,
Title        VARCHAR(255)         NOT NULL)
AS
BEGIN
    DECLARE @MaxLoan INT
 
    SELECT @MaxLoan = MAX(Salary)
    FROM Journalists
 
    INSERT @JournalistEfficiency
    SELECT a.JournalistID, a.FName, a.Salary, b.Title
    FROM Journalists a INNER JOIN Publishings b
        ON a.JournalistID = b.JournalistNumber
    WHERE a.Salary = @MaxLoan
    RETURN
END
 
GO
 
-----------------------------------------------------------------------------------------------------------
-- Функциональный блок для тестирования элементов БД
-----------------------------------------------------------------------------------------------------------
 
SELECT dbo.ufnMyScalarFunc(3,2) AS ScalarFunc;
 
UPDATE PublishHouse 
SET ZIP = 11111
WHERE City = 'Hamburg';
 
EXEC DeleteEvent 2;
 
EXEC InsertEvent
    @GPSLat = 50.0594899494,
    @GPSLon = 26.900842058, 
    @Address = 'Xingjuan 20', 
    @ZIP = 2869948, 
    @City = 'Tianghe',
    @Country = 'China',
    @ImportanceGrade = 2,
    @ResponsibleJournalist = 3;
 
EXEC UpdateEvent
    @EventID = 1,  
    @GPSLat = 50.009494,
    @GPSLon = 26.900842058, 
    @Address = 'TimesSquare 20', 
    @ZIP = 28948, 
    @City = 'New York',
    @Country = 'USA',
    @ImportanceGrade = 3,
    @ResponsibleJournalist = 1;
 
EXEC ChangePublishingPriority
    @ID = 3,
    @NewPriority = 2;
 
GO

CREATE TRIGGER TR_Stamp ON PublishHouse INSTEAD OF INSERT, DELETE, UPDATE AS RETURN;