week8-РЕШЕНИЯ

    --------------------------------------------------------------------------------
    --------------------------------   HOMEWORK  CHECK   ---------------------------
    --------------------------------------------------------------------------------

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

    SELECT * FROM COUNTRY


    --Задача 0.5*
    --Да се създаде транзакция, която променя фамилията на служител с
    --идентификатор 101 на 'Гочева', променя фамилията на служител с
    --идентификатор 104 на 'Петрова', както и фамилията на служител с
    --идентификатор 108 на 'Маринова'.
    --Нека след това извлече в резултат име и фамилия само за горепосочените
    --променени служители. Като промените от транзакцията останат постоянни.

    SELECT * FROM EMPLOYEES

BEGIN TRAN
    SELECT FNAME, LNAME, EMPLOYEE_ID
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID IN (101, 104, 108)

    UPDATE EMPLOYEES
    SET LNAME = 'Гочева'
    WHERE EMPLOYEE_ID = 101

    UPDATE EMPLOYEES
    SET LNAME = 'Петрова'
    WHERE EMPLOYEE_ID = 104

    UPDATE EMPLOYEES
    SET LNAME = 'Маринова'
    WHERE EMPLOYEE_ID = 108

    SELECT FNAME, LNAME, EMPLOYEE_ID
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID IN (101, 104, 108)
COMMIT TRAN


-------------------------------------------------------------------------------------
-------------------------------- ТРАНЗАКЦИИ -----------------------------------------
-------------------------------------------------------------------------------------
    --6.4.Примери

    --Пример 6-1.
    --Да се създаде транзакция, която добавя нов клиент и създава поръчка за него,
    --включваща два продукта.
        SELECT * FROM  CUSTOMERS
        SELECT * FROM ORDER_ITEMS
        WHERE ORDER_ID = 1

    --НАЧИН 1
    BEGIN TRAN
        --1 добавя нов клиент INSERT
        INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
        VALUES(3, 'BG', 'Марин', 'Петров')
        IF @@ERROR <> 0 ROLLBACK

        --2 създава поръчка за него INSERT
        INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
        VALUES(1, GETDATE(), 1001, 174, 'бул. Черхи връх 39, София')
        IF @@ERROR <> 0 ROLLBACK

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

        --3 поръчка 1 включваща продукт 2 INSERT
        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
        VALUES(1, 2243, 5, 100)
        IF @@ERROR <> 0 ROLLBACK
    COMMIT TRAN

        SELECT * FROM  CUSTOMERS
        SELECT * FROM ORDER_ITEMS
        WHERE ORDER_ID = 1

    ----НАЧИН 1
    BEGIN TRAN
    SET XACT_ABORT ON
        --1 добавя нов клиент /INSERT/
        INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
        VALUES(3, 'BG', 'Марин', 'Петров')

        --2 създава поръчка за него /INSERT/
        INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
        VALUES(1, GETDATE(), 1001, 174, 'бул. Черхи връх 39, София')

        --3 поръчка 1 включваща продукт /1 INSERT/
        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
        VALUES(1, 3400, 789.00, 1)

        --3 поръчка 1 включваща продукт /2 INSERT/
        INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
        VALUES(1, 2243, 5, 100)
    COMMIT TRAN


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

BEGIN TRAN
    SELECT LNAME AS CURRENTLY
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1001

    UPDATE CUSTOMERS
    SET LNAME = 'Кастрева'
    WHERE CUSTOMER_ID = 1001

    SELECT LNAME AS  AFTER_UPDATE
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1001
ROLLBACK TRAN

    SELECT LNAME  AS  AFTER_ROLLBACK
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1001


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

BEGIN TRAN
    --OPERATION 1 /въвежда нов клиент/
    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
    VALUES(3, 'CA', 'Кейт', 'Рейнолдс')

--поставя точка на запис
SAVE TRAN POINT1

    --въвежда поръчка
    INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID)
    VALUES(2, GETDATE(), 3, 174)

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

--ЗАПАЗИ ПРОМЕНИТЕ ОТ ТРАНЗАКЦИЯТА ПРЕДИ ТОЧКАТА НА ЗАПИС
COMMIT TRAN

SELECT * FROM ORDERS

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

SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 90

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10

BEGIN TRAN
    -- прехвърли всички служители от 90 в отдел 10
    UPDATE EMPLOYEES
    SET DEPARTMENT_ID = 10
    WHERE DEPARTMENT_ID = 90

    --изтрие отдел 90
    DELETE FROM DEPARTMENTS
    WHERE DEPARTMENT_ID = 90
COMMIT TRAN

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

BEGIN TRAN
    DELETE FROM ORDER_ITEMS
    WHERE PRODUCT_ID = 1726

    DELETE FROM PRODUCTS
    WHERE PRODUCT_ID = 1726
ROLLBACK TRAN

    SELECT *
    FROM ORDER_ITEMS
    WHERE PRODUCT_ID = 1726

-------------------------------------------------------------------------------------
---------------------------------- ПРОЦЕДУРИ ----------------------------------------
-------------------------------------------------------------------------------------

--Пример 7-2.
--Да се създаде процедура, която за подадена като входен параметър идентификатор на
--поръчка извежда имена на служител, който я е обработил, както и общата й стойност.

CREATE PROC EMPLOYEES_ORDERS_PROC @ORDER INT
AS
SELECT FNAME, LNAME, O.ORDER_ID, SUM(OI.UNIT_PRICE*OI.QUANTITY) TOTAL
FROM EMPLOYEES E JOIN ORDERS O ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
                 JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
WHERE O.ORDER_ID = @ORDER
GROUP BY FNAME, LNAME, O.ORDER_ID

EXEC EMPLOYEES_ORDERS_PROC @ORDER = 1

-------------------------------------------------------------------------------------
---------------------------------- ФУНКЦИИ ------------------------------------------
-----1.--Скаларни -------------------------------------------------------------------

--Пример 7-4.
--Да се създаде функция, връщаща като скаларна стойност текст, съдържащ името на
--отдел (подаден като параметър) и обща стойност на заплатите в него.

CREATE FUNCTION DEPARTMENTS_SALARIES_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 E.DEPARTMENT_ID = D.DEPARTMENT_ID
    WHERE D.DEPARTMENT_ID = @DEPT_ID
    GROUP BY D.NAME, D.DEPARTMENT_ID

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

SELECT dbo.DEPARTMENTS_SALARIES_FUNCTION(DEPARTMENT_ID) FROM DEPARTMENTS

-----2.-- Функции, връщащи резултатен набор------------------------------------------

--Пример 7-5.
--Да се създаде функция, връщаща като резултат служителите с техните длъжности.

CREATE FUNCTION EMPLOYEES_JOBS_FUNCTION() RETURNS TABLE
AS
RETURN
    SELECT FNAME, LNAME, E.JOB_ID, JOB_TITLE
    FROM EMPLOYEES E JOIN JOBS J
    ON J.JOB_ID = E.JOB_ID

    SELECT * FROM dbo.EMPLOYEES_JOBS_FUNCTION()

-------------------------------------------------------------------------------------
----------------------------------- КУРСОРИ -----------------------------------------
-------------------------------------------------------------------------------------

/*Пример 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 CUSTOMERS_HISTORY
(
    CUSTOMER_ID INT,
    OLD_LNAME VARCHAR(50),
    NEW_LNAME VARCHAR(50)
)

 CREATE TRIGGER FOR_UPDATE_LNAME_TRIGGER
 ON CUSTOMERS
 FOR UPDATE
 AS
    IF UPDATE(LNAME)
    BEGIN
        INSERT INTO CUSTOMERS_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, 213, 171)

SELECT *
FROM CUSTOMERS
WHERE CUSTOMER_ID IN (111, 213, 171)

SELECT * FROM CUSTOMERS_HISTORY