23.02.22

    -------------------------------------------------------------------------------------
    -------------------------------   HOMEWORK   ----------------------------------------
    -------------------------------------------------------------------------------------
    --Задача 5-2.
    --Да се създаде изглед, който съдържа десетимата клиенти с най-голям брой
    --поръчки. Ако последният клиент има равен брой поръчки с други клиенти, те също да участват в изгледа.

    CREATE VIEW CUST_ORDER_NUM_TOP_WITH_TIES
    AS
    SELECT TOP 10 WITH TIES C.FNAME + ' ' + C.LNAME AS CUSTOMER,
                            COUNT(O.ORDER_ID)       AS ORDER_NUMBER
    FROM CUSTOMERS C JOIN ORDERS O
    ON C.CUSTOMER_ID=O.CUSTOMER_ID
    GROUP BY  C.FNAME + ' ' + C.LNAME , C.CUSTOMER_ID
    ORDER BY ORDER_NUMBER DESC

    SELECT * FROM CUST_ORDER_NUM_TOP_WITH_TIES

    --Задача 0.4*
    --Да се създаде изглед с имената на държавите с повече от 5 клиента от тях.
    SELECT COUNT(CU.COUNTRY_ID) , CO.NAME
    FROM COUNTRIES CO JOIN CUSTOMERS CU
    ON CO.COUNTRY_ID = CU.COUNTRY_ID
    GROUP BY CO.NAME
    HAVING COUNT(CU.COUNTRY_ID)> 5

    SELECT * FROM CUSTOMERS
    WHERE COUNTRY_ID = 'BG'

    --------------------------------------------------------------------------------
    -------------------------------- ТРАНЗАКЦИИ ------------------------------------
    --------------------------------------------------------------------------------
    --6.4.Примери
    --Пример 6-1.
    --Да се създаде транзакция

    --1--@@ERROR
    BEGIN TRAN
        --1., която добавя нов клиент
        INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
        VALUES(1001, 'BG', 'Маргарита', 'Розова', 'бул. Марица 10, гр. Пловдив', '[email protected]', 'F')
        IF @@ERROR <>0 ROLLBACK

        --2.и създава поръчка за него,
        INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
        VALUES(1, GETDATE(), 1001, 101, 'бул. В. Априлов 18, гр. Пловдив')
        IF @@ERROR <>0 ROLLBACK

        --3. , 4. включваща два продукта.
        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
        VALUES(1, 1745, 1, 340)
        IF @@ERROR <>0 ROLLBACK

        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
        VALUES(1, 2252, 2, 80)
        IF @@ERROR <>0 ROLLBACK
    COMMIT TRAN

    --2 --XACT_ABORT
    BEGIN TRAN
    SET XACT_ABORT ON
        --1., която добавя нов клиент
        INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
        VALUES(1002, 'BG', 'Маргарита', 'Розова', 'бул. Марица 10, гр. Пловдив', '[email protected]', 'F')

        --2.и създава поръчка за него,
        INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
        VALUES(1, GETDATE(), 1001, 101, 'бул. В. Априлов 18, гр. Пловдив')

        --3. , 4. включваща два продукта.
        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
        VALUES(1, 1745, 1, 340)

        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE)
        VALUES(1, 2252, 2, 80)
    COMMIT TRAN


    /*Пример 6-2.
    Транзакция, която променя фамилията на клиент с идентификатор = 1001,
    след което отхвърля направените промени.*/

    BEGIN TRAN

        --OPER1
        SELECT LNAME FROM CUSTOMERS
        WHERE CUSTOMER_ID = 1001

        --OPER2
        UPDATE CUSTOMERS
        SET LNAME = 'Лалева'
        WHERE CUSTOMER_ID = 1001

        --OPER3
        SELECT LNAME FROM CUSTOMERS
        WHERE CUSTOMER_ID = 1001

    ROLLBACK TRAN

        --OPER4
        SELECT LNAME FROM CUSTOMERS
        WHERE CUSTOMER_ID = 1001

    /*Пример 6-3.
    Транзакция, която въвежда нов клиент, поставя точка на запис,
    въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
    отхвърля се само поръчката.*/

    BEGIN TRAN

        --OPER1
        INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
        VALUES(1003, 'BG', 'Илия', 'Георгиев', 'бул. Марица 10, гр. Пловдив', '[email protected]', 'M')

    SAVE TRAN POINT1

        --OPER2
        INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
        VALUES(2, GETDATE(), 1003, 101, 'бул. В. Априлов 18, гр. Пловдив')

    --отхвърля промените до точката на запис
    ROLLBACK TRAN POINT1

    COMMIT TRAN

    /*Задача 6-1.
    Транзакция, която има за цел да изтрие отдел „Мениджмънт“,
    като преди това прехвърли всички служители от него в отдел „Администрация“.*/

    SELECT * FROM DEPARTMENTS
    WHERE DEPARTMENT_ID = 90

    BEGIN TRAN

        ----Мениджмънт    90 --> Администрация 10  ,
        UPDATE EMPLOYEES
        SET DEPARTMENT_ID = 10
        WHERE DEPARTMENT_ID = 90

        ----DELETE Мениджмънт    90
        DELETE FROM DEPARTMENTS
        WHERE DEPARTMENT_ID = 90

    COMMIT TRAN

    -------------------------------------------------------------------------------------
    ---------------------------------- ПРОЦЕДУРИ ----------------------------------------
    -------------------------------------------------------------------------------------
    --Пример 7-2.
    --Да се създаде процедура, която за подадена като входен параметър идентификатор на
    --поръчка извежда имена на служител, който я е обработил, както и общата й стойност.
    CREATE PROC EMPL_ORDERS_PROC @ORDER INT
    AS
    SELECT FNAME, LNAME, O.ORDER_ID, SUM(OI.QUANTITY*OI.UNIT_PRICE) AS TOTAL
    FROM EMPLOYEES E JOIN ORDERS O ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
                     JOIN ORDER_ITEMS OI ON OI.ORDER_ID = O.ORDER_ID
    WHERE @ORDER = O.ORDER_ID
    GROUP BY FNAME, LNAME, O.ORDER_ID

    EXEC EMPL_ORDERS_PROC @ORDER = 2454

    -------------------------------------------------------------------------------------
    ---------------------------------- ФУНКЦИИ ------------------------------------------
    -----1.--Скаларни -------------------------------------------------------------------
    --Пример 7-4.
    --Да се създаде функция, връщаща като скаларна стойност текст, съдържащ името на
    --отдел (подаден като параметър) и обща стойност на заплатите в него.
    CREATE FUNCTION DEPT_SALARY_FUNCTION (@DEPT_ID INT ) RETURNS VARCHAR(200)
    AS
    BEGIN
    DECLARE @SUM_SALARY NUMERIC(10,2), @NAME VARCHAR(50)

    SELECT @NAME = D.NAME, @SUM_SALARY = SUM(E.SALARY)
    FROM DEPARTMENTS D JOIN EMPLOYEES E
    ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
    WHERE D.DEPARTMENT_ID = @DEPT_ID
    GROUP BY D.DEPARTMENT_ID, D.NAME

    RETURN 'Сумата на заплатите в ' + @NAME +
           ' (ID=' + CAST(@DEPT_ID AS VARCHAR) +
           ') е ' + CAST(@SUM_SALARY AS VARCHAR) + '.'
    END

    SELECT DBO.DEPT_SALARY_FUNCTION (DEPARTMENT_ID) FROM DEPARTMENTS

    -----2.-- Функции, връщащи резултатен набор------------------------------------------
    --Пример 7-5.
    --Да се създаде функция, връщаща като резултат служителите с техните длъжности.
    CREATE FUNCTION EMP_JOBS_FUNCTION () RETURNS TABLE
    AS
    RETURN
        SELECT FNAME, LNAME, JOB_TITLE
        FROM EMPLOYEES E JOIN JOBS J
        ON E.JOB_ID = J.JOB_ID

    SELECT * FROM DBO.EMP_JOBS_FUNCTION()
    ORDER BY JOB_TITLE

    -------------------------------------------------------------------------------------
    ----------------------------------- КУРСОРИ -----------------------------------------
    -------------------------------------------------------------------------------------
    /*Пример 8-1.
    Да се създаде курсор, който демонстрира прочитане на данни ред по ред от
    курсор. Резултатният набор за целта ще съдържа всички клиенти от Германия.
    Стъпките от жизнения цикъл са обозначени в коментари.*/

    DECLARE @CUST_ID VARCHAR(10), @F_NAME VARCHAR(20), @L_NAME VARCHAR(20)

    -- 1 Деклариране на курсора чрез конструкцията DECLARE
    DECLARE CUSTOMERS_CURSOR CURSOR FOR
    SELECT CUSTOMER_ID, FNAME, LNAME
    FROM CUSTOMERS
    WHERE COUNTRY_ID = 'DE'

    -- 2. Отваряне на курсора.
    OPEN CUSTOMERS_CURSOR

    /* 3. Извличане на ред от курсора чрез конструкцията FETCH.
    Това обикновено става в цикъл, в който на всяка итерация се проверява
    стойността на системната променлива @@FETCH_STATUS след всяко извличане.
    Ако стойността й е:
              0 – извличането е било успешно;
             -1 – няма повече редове в курсора;
             -2 – редът вече не съществува в курсора, т.е.
                той е бил изтрит след отваряне на
                курсора или променен така, че вече не отговаря на условията,
                на които отговарят редовете, участващи в резултатния набор на курсора.
    */
    FETCH NEXT FROM CUSTOMERS_CURSOR INTO @CUST_ID, @F_NAME, @L_NAME
    PRINT 'Клиенти от Германия: '
    PRINT '-----------------------------'
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- 4. Прочитане, актуализиране или изтриване на реда, на който е позициониран курсорът.
     PRINT @F_NAME + ' ' + @L_NAME + ' (ID = ' + @CUST_ID + ')'

     FETCH NEXT FROM CUSTOMERS_CURSOR INTO @CUST_ID, @F_NAME, @L_NAME
    END

    -- 5. Затваряне на курсора. Това прекратява активното действие на курсора. Той все още
    --може да бъде отворен без да се налага да се декларира отново.
    CLOSE CUSTOMERS_CURSOR

    -- 6. Освобождаване на курсора, за да бъдат освободени структурите от данни, които
    --съставят курсора.
    DEALLOCATE CUSTOMERS_CURSOR

    -------------------------------------------------------------------------------------
    ----------------------------- Тригери -----------------------------------------------
    -------------------------------------------------------------------------------------
    /*Задача 9-1.
    Да се създаде тригер, който при всяка промяна на фамилия на клиент
    записва ред в нова таблица CUSTOMERS_HISTORY с атрибути:
    • идентификатор на клиент;
    • стара фамилия;
    • нова фамилия.*/

    CREATE TABLE CUSTOMER_HISTORY
    (
        CUSTOMER_ID INT,
        OLD_LNAME VARCHAR(50),
        NEW_LNAME VARCHAR(50)
    )

    CREATE TRIGGER TGR_CUST_HIST
    ON CUSTOMERS
    FOR UPDATE
    AS
        IF UPDATE(LNAME)
        BEGIN
        INSERT INTO CUSTOMER_HISTORY(CUSTOMER_ID, OLD_LNAME, NEW_LNAME)
        SELECT I.CUSTOMER_ID, D.LNAME, I.LNAME
        FROM inserted I, deleted D
        WHERE I.CUSTOMER_ID = D.CUSTOMER_ID
        END

    UPDATE CUSTOMERS
    SET LNAME = LNAME + '-' + 'Колева'
    WHERE CUSTOMER_ID IN (111, 114, 118)

    SELECT * FROM CUSTOMERS

    SELECT * FROM CUSTOMER_HISTORY
    WHERE CUSTOMER_ID IN (111, 114, 118)


    -------------------------------------------------------------------------------------
    -------------------------------   HOMEWORK   ----------------------------------------
    -------------------------------------------------------------------------------------
    --Задача 0.5*
    --Да се създаде транзакция, която променя:
    --фамилията на служител с идентификатор 103 на 'Гочев',
    --променя фамилията на служител с идентификатор 114 на 'Петров',
    --както и фамилията на служител с идентификатор 118 на 'Маринов'.
    --Нека след това извлече в резултат име и фамилия само за горепосочените служители,
    --като промените от транзакцията останат постоянни.

    /* Задача 6-2.
    Транзакция, която изтрива продукт 1726 -първо го изтрива от всички поръчки
    после от таблицата с продукти, и накрая отхвърля направените промени.*/