9.6.R

-------------------------------------------------------------------------------------
----------------------5.4.Създаване на изглед ---------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--Дoмашна работа:--------------------------------------------------------------------
--Пример 5-4.
--Да се създаде изглед, съдържащ име и фамилия на служител и общата сума на
--поръчките, които той е обработил.
SELECT * FROM ORDER_ITEMS
SELECT * FROM ORDERS
SELECT * FROM EMPLOYEES


---INNER JOIN:
---SELECT * FROM ..... JOIN ....... ON    ............
---SELECT * FROM .....,     ....... WHERE ............

CREATE VIEW EMPLOYEEORDERS
AS
SELECT E.FNAME, E.LNAME, SUM(OI.UNIT_PRICE*OI.QUANTITY) AS ORDERS_TOTAL
FROM EMPLOYEES E , ORDERS O, ORDER_ITEMS OI
WHERE E.EMPLOYEE_ID=O.EMPLOYEE_ID
  AND O.ORDER_ID=OI.ORDER_ID
GROUP BY E.FNAME, E.LNAME

SELECT * FROM EMPLOYEEORDERS
ORDER BY ORDERS_TOTAL DESC

-------------------------------------------------------------------------------------

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

SELECT * FROM EMPLOYEES
SELECT * FROM DEPARTMENTS

CREATE VIEW EMP_TOP_SALARIES
AS
SELECT TOP 5 E.FNAME,
             E.LNAME,
             D.NAME AS DEPT,
             E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
ORDER BY SALARY DESC

SELECT * FROM EMP_TOP_SALARIES

-------------------------------------------------------------------------------------
----------------------5.4.Манипулиране на данни чрез изглед -------------------------
-------------------------------------------------------------------------------------

--Следващият изглед, базиран на JOIN между таблиците COUNTRIES и CUSTOMERS, ще
--демонстрира манипулирането на данни:

CREATE VIEW CUSTOMERS_COUNTRIES
AS
 SELECT C.COUNTRY_ID,
        C.NAME,
        C.REGION_ID,
        M.CUSTOMER_ID,
        M.FNAME,
        M.GENDER,
        M.LNAME,
        M.COUNTRY_ID AS CUST_COUNTRY_ID,
        M.EMAIL
FROM COUNTRIES C JOIN CUSTOMERS M
ON C.COUNTRY_ID=M.COUNTRY_ID


--Пример 5-6.
--Да се добави нов запис в таблицата CUSTOMERS през изгледа CUSTOMERS_COUNTRIES.

INSERT INTO CUSTOMERS_COUNTRIES(CUSTOMER_ID, FNAME, LNAME, CUST_COUNTRY_ID)
VALUES(10, 'Иван', 'Петров', 'BG')

SELECT * FROM CUSTOMERS_COUNTRIES
SELECT * FROM CUSTOMERS

--5.4.2. Променяне на данни през изглед
---Пример 5-7.
-- Да се промени фамилията на клиент с идентификатор 10.

UPDATE CUSTOMERS_COUNTRIES
SET LNAME='Колев'
WHERE CUSTOMER_ID=10


SELECT * FROM CUSTOMERS_COUNTRIES
SELECT * FROM CUSTOMERS

--5.4.3. Изтриване на данни през изглед
--Пример 5-8.
--Да се изтрие клиент с идентификатор 10.

DELETE FROM CUSTOMERS_COUNTRIES
WHERE CUSTOMER_ID=10

-------------------------------------------------------------------------------------
--5.5.Задачи

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

CREATE VIEW PRODUCTS_QUANTITY
AS
SELECT P.NAME, SUM(OI.QUANTITY) AS OBSHTO_KOL
FROM PRODUCTS P JOIN ORDER_ITEMS OI
ON P.PRODUCT_ID=OI.PRODUCT_ID
GROUP BY P.NAME

--Задача 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

------------------------------------------------------------------------------------------
-- 1. ЗАДАЧА:
-- създай изглед с всичко от таблицата за служителите,
-- но само тези, които работят в отдел 100

CREATE VIEW EMPLOYEES_EXAMPLE_1
AS
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100


SELECT * FROM EMPLOYEES_EXAMPLE_1

------------------------------------------------------------------------------------------
-- 2. ЗАДАЧА:
-- промени изгледа - конкатенирай в една колона име и фамилия на служителите
-- , а от останалите колони покажи само идентификатор на длъжност,
-- година на наемане, заплата и идентификатор на мениджър.


ALTER VIEW EMPLOYEES_EXAMPLE_1
AS
SELECT  FNAME + ' ' + LNAME AS [FIRST, LAST NAME]  , JOB_ID, HIRE_DATE, SALARY, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100

SELECT * FROM EMPLOYEES_EXAMPLE_1

-------------------------------------------------------------------------------------------
-- 3. ЗАДАЧА:
-- създай изглед върху изгледа от 2. ЗАДАЧА, като в резултатния набор
-- има само следните колони: имена и идентификатор на мениджър

CREATE VIEW VIEW_from_VIEW
AS
SELECT [FIRST, LAST NAME], MANAGER_ID
FROM EMPLOYEES_EXAMPLE_1

SELECT * FROM VIEW_from_VIEW

-------------------------------------------------------------------------------------------
-- 4. ЗАДАЧА:
-- създай изглед с имената и броя на държавите, които са в регион "Азия"

CREATE VIEW NAME_COUNT_COUNTRIES_AND_REGIONS_ASIA
AS
SELECT  COUNT(C.COUNTRY_ID) AS [BROI DARJAVI], C.NAME
FROM COUNTRIES C, REGIONS R
WHERE C.REGION_ID = R.REGION_ID
      AND R.NAME='Азия'
GROUP BY C.NAME

SELECT * FROM NAME_COUNT_COUNTRIES_AND_REGIONS_ASIA



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

--Пример 6-1.
--транзакция, която добавя нов клиент и създава поръчка за него, включваща два продукта.

BEGIN TRAN

---OPERATION 1 -- добавя нов клиент ---INSERT INTO CUSTOMERS
    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
    VALUES (1001, 'BG', 'Иван', 'Николов', 'БУЛ.БЪЛГАРИЯ 236, ПЛОВДИВ', 'ivan@abv.bg', 'M')

---OPERATION 2 -- създава поръчка за него ----INSERT INTO ORDERS
    INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS, ORDER_DATE)
    VALUES(1, 1001, 107, 'БУЛ.МАКЕДОНИЯ 12, ПЛОВДИВ', GETDATE())

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

---OPERATION 4 -- включваща два продукта.  ---INSERT INTO ORDER_ITEMS...
    INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
    VALUES(1, 1782, 600, 1)
COMMIT TRAN

SELECT * FROM CUSTOMERS
WHERE CUSTOMER_ID =1001

SELECT * FROM ORDER_ITEMS
WHERE ORDER_ID=1


/*
Пример 6-2.
транзакция, която променя фамилията на клиент с идентификатор = 1001,
след което отхвърля направените промени.
*/
BEGIN TRAN
    --OPERATION 1 SELECT  --фамилията на клиент ПРЕДИ ПРОМЯНА
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID=1001

    --OPERATION 2 UPDATE --ПРОМЯНА
    UPDATE CUSTOMERS
    SET LNAME= 'ИВАНОВ'
    WHERE CUSTOMER_ID=1001

    --OPERATION 3 SELECT  ----фамилията на клиент СЛЕД ПРОМЯНА
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID=1001

--отхвърля направените промени
ROLLBACK TRAN

    --OPERATION 4 SELECT ----фамилията на клиент СЛЕД ROLLBACK TRAN
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID=1001


/*
Пример 6-3.
транзакция, която въвежда нов клиент, поставя точка на запис,
въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
отхвърля се само поръчката.
*/
BEGIN TRAN
    --въвежда нов клиент
    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, GENDER)
    VALUES (1002, 'BG', 'ПЕТЪР','ВАСИЛЕВ', 'M')

SAVE TRAN POINT1
    --въвежда поръчка
    INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
    VALUES(2, 1002, 110, GETDATE())

ROLLBACK TRAN  POINT1
COMMIT TRAN

SELECT * FROM CUSTOMERS
WHERE CUSTOMER_ID=1002

SELECT * FROM ORDERS
WHERE ORDER_ID=2


---6.5.Задачи

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

SELECT * FROM DEPARTMENTS
-- 10 -Администрация
-- 90 -Мениджмънт

SELECT *
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID=90


BEGIN TRAN
    -- служители от отдел „Мениджмънт“ в отдел „Администрация“
    UPDATE EMPLOYEES
    SET DEPARTMENT_ID=10
    WHERE DEPARTMENT_ID=90

    --да изтрие отдел „Мениджмънт“
    DELETE DEPARTMENTS
    WHERE DEPARTMENT_ID =90
COMMIT TRAN

SELECT *
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID=10


SELECT * FROM DEPARTMENTS

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

SELECT * FROM ORDER_ITEMS
WHERE PRODUCT_ID=1726

SELECT * FROM PRODUCTS
WHERE PRODUCT_ID=1726


SELECT *
FROM ORDER_ITEMS OI FULL JOIN PRODUCTS P
ON P.PRODUCT_ID=OI.PRODUCT_ID
WHERE P.PRODUCT_ID=1726

BEGIN TRAN
    --1726 -първо го изтрива от всички поръчки
    DELETE FROM ORDER_ITEMS
    WHERE PRODUCT_ID=1726

    --1726 -после от таблицата с продукти
    DELETE FROM PRODUCTS
    WHERE PRODUCT_ID=1726
ROLLBACK TRAN


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

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


EXECUTE EMPLOYEEES_ORDERS 2354


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

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

CREATE FUNCTION EMPLOYEE_jobs () 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.EMPLOYEE_jobs ()
    ORDER BY FNAME, LNAME

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

CREATE TABLE CUSTOMERS_HISTORY
(
    CUSTOMER_ID INT,
    OLD_NAME VARCHAR(20),
    NEW_NAME VARCHAR(20)
)

CREATE TRIGGER TGR_CUSTOMERS_HIST
ON CUSTOMERS
FOR UPDATE
AS
    IF UPDATE(LNAME)
    BEGIN
    INSERT INTO CUSTOMERS_HISTORY(CUSTOMER_ID, OLD_NAME, NEW_NAME)

    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 (103, 111, 114)

SELECT * FROM CUSTOMERS
SELECT * FROM CUSTOMERS_HISTORY