Pastein: --13.1 Напишите хранимую процедуру для вывода информации о сервере, о базе данных, о текущем пользователе, о тек

Загрузка данных
--13.1 Напишите хранимую процедуру для вывода информации о сервере, о базе данных, о текущем пользователе, о текущем времени, и вызовите ее.
CREATE PROC Task1
AS
BEGIN
    SELECT 
         @@Servername AS Сервер
        ,@@Version AS [Версия СУБД]
        ,Db_Name() AS [База данных]
        ,User AS [Пользователь базы данных]
        ,System_User AS [Системный пользователь]
        ,GETDATE() AS [Текущее время];
END;
GO
EXECUTE Task1;

--13.2 Напишите хранимую процедуру, которая выводит данные всех стран.
CREATE PROC Task2
AS
BEGIN
    SELECT Название, Столица, Площадь, Население, Континент
    FROM Countries;
END;
GO
EXECUTE Task2;

--13.3 Напишите хранимую процедуру, которая выводит список стран, кроме заданной части света, и вызовите ее.
CREATE PROC Task3
    @Kont AS VARCHAR(50)
AS
BEGIN
    SELECT Название, Столица, Площадь, Население, Континент
    FROM Countries
    WHERE Континент != @Kont;
END;
GO
EXECUTE Task3 'Азия';

--13.4 Напишите хранимую процедуру, которая выводит список стран, население которых находится в заданном интервале, и вызовите ее.
CREATE PROC Task4
    @MinPop AS INT,
    @MaxPop AS INT
AS
BEGIN
    SELECT Название, Столица, Площадь, Население, Континент
    FROM Countries
    WHERE Население BETWEEN @MinPop AND @MaxPop;
END;
GO
EXECUTE Task4 10000000, 50000000;

--13.5 Напишите хранимую процедуру, которая возвращает количество стран, у которых в названии отсутствует заданная буква, и вызовите ее.
CREATE PROC Task5
    @Буква AS CHAR(1),
    @Количество AS INT OUTPUT
AS
BEGIN
    SELECT @Количество = COUNT(*)
    FROM Countries
    WHERE CHARINDEX(@Буква, Название) = 0;
END;
GO
DECLARE @K INT;
DECLARE @Б CHAR(1) = 'а';
EXECUTE Task5 @Б, @K OUTPUT;
SELECT @K AS [Количество стран без буквы];

--13.6 Напишите хранимую процедуру для вывода пяти стран с наибольшим населением в заданной части света, и вызовите ее. Если часть света не указана, выбрать Африку.
CREATE PROC Task6
    @Kont AS VARCHAR(50) = 'Африка'
AS
BEGIN
    SELECT TOP 5 Название, Столица, Площадь, Население, Континент
    FROM Countries
    WHERE Континент = @Kont
    ORDER BY Население DESC;
END;
GO
EXECUTE Task6 DEFAULT;

--13.7 Напишите хранимую процедуру, которая создает таблицу «Страны_С», и заполняет ее странами, названия которых начинаются с первой буквой вашей фамилии.
CREATE PROC Task7
AS
BEGIN
    SELECT Название, Столица, Площадь, Население, Континент
    INTO Страны_С
    FROM Countries
    WHERE LEFT(Название, 1) = 'С';
END;
GO
EXECUTE Task7;

--13.8 Напишите хранимую процедуру, которая удаляет таблицу, которую вы создали в предыдущем задании и возвращает количество удаленных строк.
CREATE PROC Task8
AS
BEGIN
    DECLARE @K INT;
    SELECT @K = COUNT(*)
    FROM Страны_С;
    
    DROP TABLE Страны_С;
    RETURN @K;
END;
GO
DECLARE @Count INT;
EXECUTE @Count = Task8;
SELECT @Count AS [Количество строк в удаленной таблице];

--13.9 Напишите хранимую процедуру, принимающую число и возвращающую количество цифр в нем через параметр OUTPUT.
CREATE PROC Task9
    @Num INT,
    @DigitCount INT OUTPUT
AS
BEGIN
    IF @Num = 0 
        SET @DigitCount = 1;
    ELSE
    BEGIN
        SET @DigitCount = 0;
        DECLARE @Temp INT = ABS(@Num);
        WHILE @Temp > 0
        BEGIN
            SET @DigitCount = @DigitCount + 1;
            SET @Temp = @Temp / 10;
        END
    END
END;
GO
DECLARE @Cnt INT;
EXECUTE Task9 12345, @Cnt OUTPUT;
SELECT @Cnt AS [Количество цифр];

--13.10 Напишите хранимую процедуру AddRightDigit, добавляющую к целому положительному числу К справа цифру D (D- входной параметр целого типа, лежащий в диапазоне [0..9], К - параметр целого типа, являющийся одновременно входным и выходным).
CREATE PROC AddRightDigit
    @D INT,
    @K INT OUTPUT
AS
BEGIN
    SET @K = @K * 10 + @D;
END;
GO
DECLARE @Val INT = 123;
EXECUTE AddRightDigit 5, @Val OUTPUT;
SELECT @Val AS [Новое число];

--13.11 Напишите хранимую процедуру InvDigit, меняющую порядок следования цифр целого положительного числа К на обратный (К параметр целого типа, являющийся одновременно входным и выходным).
CREATE PROC InvDigit
    @K INT OUTPUT
AS
BEGIN
    DECLARE @Res INT = 0;
    DECLARE @Temp INT = @K;
    WHILE @Temp > 0
    BEGIN
        SET @Res = @Res * 10 + (@Temp % 10);
        SET @Temp = @Temp / 10;
    END
    SET @K = @Res;
END;
GO
DECLARE @Val INT = 1234;
EXECUTE InvDigit @Val OUTPUT;
SELECT @Val AS [Инвертированное число];

--13.12 Напишите хранимую процедуру Swap, меняющую содержимое переменных Х и Y (Х и Y вещественные параметры, являющиеся одновременно входными и выходными).
CREATE PROC Swap
    @X FLOAT OUTPUT,
    @Y FLOAT OUTPUT
AS
BEGIN
    DECLARE @Temp FLOAT = @X;
    SET @X = @Y;
    SET @Y = @Temp;
END;
GO
DECLARE @Val1 FLOAT = 5.5, @Val2 FLOAT = 10.2;
EXECUTE Swap @Val1 OUTPUT, @Val2 OUTPUT;
SELECT @Val1 AS X, @Val2 AS Y;

--13.13 Напишите хранимую процедуру SortInc, меняющую содержимое переменных А, В, С, таким образом, чтобы их значения оказались упорядоченными по возрастанию (А, В, С вещественные параметры, являющиеся одновременно входными и выходными).
CREATE PROC SortInc
    @A FLOAT OUTPUT,
    @B FLOAT OUTPUT,
    @C FLOAT OUTPUT
AS
BEGIN
    DECLARE @Temp FLOAT;
    IF @A > @B BEGIN SET @Temp = @A; SET @A = @B; SET @B = @Temp; END
    IF @A > @C BEGIN SET @Temp = @A; SET @A = @C; SET @C = @Temp; END
    IF @B > @C BEGIN SET @Temp = @B; SET @B = @C; SET @C = @Temp; END
END;
GO
DECLARE @V1 FLOAT = 9.5, @V2 FLOAT = 2.1, @V3 FLOAT = 5.4;
EXECUTE SortInc @V1 OUTPUT, @V2 OUTPUT, @V3 OUTPUT;
SELECT @V1 AS A, @V2 AS B, @V3 AS C;

--13.14 Напишите хранимую процедуру DigitCountSum, находящую количество С цифр целого положительного числа К, а также их сумму S (К входной, С, S выходные параметры целого типа).
CREATE PROC DigitCountSum
    @K INT,
    @C INT OUTPUT,
    @S INT OUTPUT
AS
BEGIN
    SET @C = 0;
    SET @S = 0;
    DECLARE @Temp INT = ABS(@K);
    IF @Temp = 0 
    BEGIN
        SET @C = 1;
        SET @S = 0;
    END
    ELSE
    BEGIN
        WHILE @Temp > 0
        BEGIN
            SET @S = @S + (@Temp % 10);
            SET @C = @C + 1;
            SET @Temp = @Temp / 10;
        END
    END
END;
GO
DECLARE @CountDigits INT, @SumDigits INT;
EXECUTE DigitCountSum 485, @CountDigits OUTPUT, @SumDigits OUTPUT;
SELECT @CountDigits AS [Количество цифр], @SumDigits AS [Сумма цифр];

--13.15 Напишите код, который удаляет все хранимые процедуры, вами созданные.
DROP PROC IF EXISTS Task1;
DROP PROC IF EXISTS Task2;
DROP PROC IF EXISTS Task3;
DROP PROC IF EXISTS Task4;
DROP PROC IF EXISTS Task5;
DROP PROC IF EXISTS Task6;
DROP PROC IF EXISTS Task7;
DROP PROC IF EXISTS Task8;
DROP PROC IF EXISTS Task9;
DROP PROC IF EXISTS AddRightDigit;
DROP PROC IF EXISTS InvDigit;
DROP PROC IF EXISTS Swap;
DROP PROC IF EXISTS SortInc;
DROP PROC IF EXISTS DigitCountSum;
GO
--14.1 Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, в случае изменения данных, должен вывести «Запись изменена».
CREATE TRIGGER Task1 ON Ученики
AFTER UPDATE
AS
BEGIN
    PRINT 'Запись изменена';
END;
GO

--14.2 Напишите триггер на добавление и удаление записи из таблицы «Ученики». Данный триггер, в случае успешного добавления или удаления данных, должен вывести «Количество строк изменено».
CREATE TRIGGER Task2 ON Ученики
AFTER INSERT, DELETE
AS
BEGIN
    PRINT 'Количество строк изменено';
END;
GO

--14.3 Напишите триггер на добавление, изменение и удаление данных в таблице «Ученики». Данный триггер должен вывести «{Текущий пользователь изменил таблицу. Время: {текущее время}».
CREATE TRIGGER Task3 ON Ученики
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Msg VARCHAR(200);
    SET @Msg = SYSTEM_USER + ' изменил таблицу. Время: ' + CONVERT(VARCHAR, GETDATE(), 120);
    PRINT @Msg;
END;
GO

--14.4 Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, при попытке изменения данных, должен вывести «Нельзя редактировать данные».
CREATE TRIGGER Task4 ON Ученики
INSTEAD OF UPDATE
AS
BEGIN
    PRINT 'Нельзя редактировать данные';
END;
GO

--14.5 Создать таблицу «Ученики_Сергеев», которая будет содержать фамилии удаленных учеников и даты их удаления. Написать триггер, который будет фиксировать в таблице «Ученики_Сергеев» данные учеников при удалении из таблицы «Ученики», в том случае, если у них остались однофамильцы в таблице «Ученики».
CREATE TABLE Ученики_Сергеев
(
    Фамилия VARCHAR(50) NOT NULL,
    Удалено DATETIME NOT NULL
);
GO

CREATE TRIGGER Task5 ON Ученики
AFTER DELETE
AS
BEGIN
    INSERT INTO Ученики_Сергеев (Фамилия, Удалено)
    SELECT d.Фамилия, GETDATE()
    FROM DELETED d
    WHERE EXISTS (
        SELECT 1 FROM Ученики u WHERE u.Фамилия = d.Фамилия
    );
END;
GO

--14.6 Напишите команды для приостановления и запуска триггера из предыдущей задачи.
DISABLE TRIGGER Task5 ON Ученики;
ENABLE TRIGGER Task5 ON Ученики;
GO

--14.7 Напишите команды для удаления всех созданных вами триггеров.
DROP TRIGGER IF EXISTS Task1;
DROP TRIGGER IF EXISTS Task2;
DROP TRIGGER IF EXISTS Task3;
DROP TRIGGER IF EXISTS Task4;
DROP TRIGGER IF EXISTS Task5;
GO
--15.1 Создайте представление, содержащее список африканских стран, население которых больше 10 млн. чел., а площадь больше 500 тыс. кв. км, и используйте его.
CREATE VIEW Task1_View
AS
SELECT 
    Название, 
    Столица, 
    Площадь, 
    Население, 
    Континент
FROM Countries
WHERE Континент = 'Африка' 
  AND Население > 10000000 
  AND Площадь > 500000;
GO

SELECT * FROM Task1_View;
GO

--15.2 Создайте представление, содержащее список континентов, среднюю площадь стран, которые находятся на нем, среднюю плотность населения, и используйте его.
CREATE VIEW Task2_View
AS
SELECT 
    Континент, 
    AVG(Площадь) AS [Средняя площадь], 
    AVG(CAST(Население AS FLOAT) / Площадь) AS [Средняя плотность]
FROM Countries
GROUP BY Континент;
GO

SELECT * FROM Task2_View;
GO

--15.3 Создайте представление, содержащее фамилии преподавателей, их должность, звание, степень, место работы, количество их экзаменов, и используйте его.
CREATE VIEW Task3_View
AS
SELECT 
    С.Фамилия, 
    С.Должность, 
    П.Звание, 
    П.Степень, 
    К.Название AS [Место работы], 
    COUNT(Э.Код) AS [Количество экзаменов]
FROM Сотрудник С
INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер
INNER JOIN Кафедра К ON С.Шифр = К.Шифр
LEFT JOIN Экзамен Э ON С.Таб_номер = Э.Таб_номер
GROUP BY С.Фамилия, С.Должность, П.Звание, П.Степень, К.Название;
GO

SELECT * FROM Task3_View;
GO

--15.4 Создайте табличную переменную, содержащую три столбца («Номер месяца», «Название месяца», «Количество дней»), заполните ее для текущего года, и используйте ее.
DECLARE @Task4_Table TABLE
(
    [Номер месяца] INT,
    [Название месяца] VARCHAR(50),
    [Количество дней] INT
);

DECLARE @Month INT = 1;
WHILE @Month <= 12
BEGIN
    DECLARE @Date DATE = DATEFROMPARTS(YEAR(GETDATE()), @Month, 1);
    INSERT INTO @Task4_Table ([Номер месяца], [Название месяца], [Количество дней])
    VALUES (
        @Month, 
        DATENAME(MONTH, @Date), 
        DAY(EOMONTH(@Date))
    );
    SET @Month = @Month + 1;
END

SELECT * FROM @Task4_Table;
GO

--15.5 Создайте табличную переменную, содержащую список стран, площадь которых в 100 раз меньше, чем средняя площадь стран на континенте, где они находятся, и используйте ее.
DECLARE @Task5_Table TABLE
(
    Название VARCHAR(50),
    Столица VARCHAR(50),
    Площадь FLOAT,
    Население BIGINT,
    Континент VARCHAR(50)
);

INSERT INTO @Task5_Table
SELECT А.Название, А.Столица, А.Площадь, А.Население, А.Континент
FROM Countries А
WHERE А.Площадь * 100 < (
    SELECT AVG(Б.Площадь)
    FROM Countries Б
    WHERE Б.Континент = А.Континент
);

SELECT * FROM @Task5_Table;
GO

--15.6 Создайте локальную временную таблицу, имеющую три столбца («Номер недели», «Количество экзаменов», «Количество студентов»), заполните и используйте ее.
SELECT 
    DATEPART(WEEK, Дата) AS [Номер недели],
    COUNT(Код) AS [Количество экзаменов],
    COUNT(DISTINCT Рег_номер) AS [Количество студентов]
INTO #Task6_Temp
FROM Экзамен
GROUP BY DATEPART(WEEK, Дата);

SELECT * FROM #Task6_Temp;
DROP TABLE #Task6_Temp;
GO

--15.7 Создайте глобальную временную таблицу, содержащую название континентов, наибольшую и наименьшую площадь стран на них, заполните и используйте ее.
CREATE TABLE ##Task7_Global
(
    Континент VARCHAR(50),
    [Наибольшая площадь] FLOAT,
    [Наименьшая площадь] FLOAT
);

INSERT INTO ##Task7_Global (Континент, [Наибольшая площадь], [Наименьшая площадь])
SELECT 
    Континент, 
    MAX(Площадь), 
    MIN(Площадь)
FROM Countries
GROUP BY Континент;

SELECT * FROM ##Task7_Global;
DROP TABLE ##Task7_Global;
GO

--15.8 С помощью обобщенных табличных выражений напишите запрос для вывода списка сотрудников, чьи зарплаты меньше, чем средняя зарплата по факультету, их зарплаты и название факультета.
WITH FacultyAvgSalary AS (
    SELECT 
        Ф.Аббревиатура AS ФакультетШифр,
        Ф.Название AS ФакультетНазвание,
        AVG(С.Зарплата) AS [Средняя зарплата по факультету]
    FROM Сотрудник С
    INNER JOIN Кафедра К ON С.Шифр = К.Шифр
    INNER JOIN Факультет Ф ON К.Факультет = Ф.Аббревиатура
    GROUP BY Ф.Аббревиатура, Ф.Название
)
SELECT 
    С.Фамилия,
    С.Зарплата,
    F.ФакультетНазвание AS [Факультет]
FROM Сотрудник С
INNER JOIN Кафедра К ON С.Шифр = К.Шифр
INNER JOIN FacultyAvgSalary F ON К.Факультет = F.ФакультетШифр
WHERE С.Зарплата < F.[Средняя зарплата по факультету];
GO

--15.9 Напишите команды для удаления всех созданных вами представлений.
DROP VIEW IF EXISTS Task1_View;
DROP VIEW IF EXISTS Task2_View;
DROP VIEW IF EXISTS Task3_View;
GO
Больше возможностей при регистрации:
