20.02 MASTERS CONDITIONS

         -- ПРИМПОМНЯНЕ НА INNER JOIN

        ----table1 , table2
        ----where ..........

        ----table1 join table2
        ----on.........

-------------------------------------------------------------------------------------
--------------------------------5. Изгледи / Views-----------------------------------
-------------------------------------------------------------------------------------
--  Създаване на изгледи

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

CREATE VIEW CUSTOMERORDERS
AS
   SELECT C.FNAME + ' ' + C.LNAME AS NAME ,   --ALL COLUMNS HAVE NAME IN VIEWS
          O.ORDER_ID, O.ORDER_DATE
   FROM CUSTOMERS C, ORDERS O
   WHERE O.CUSTOMER_ID=C.CUSTOMER_ID

-- Да се изведат данните от изгледа.
SELECT * FROM CUSTOMERORDERS

-- Пример 5-2.
-- Да се модифицира горният изглед така, че да съдържа и колона с името на
-- съответния служител, обработил поръчката.
ALTER VIEW CUSTOMERORDERS
AS
   SELECT C.FNAME + ' ' + C.LNAME AS NAME_CUST ,   --ALL COLUMNS HAVE NAME IN VIEWS
          O.ORDER_ID,
          O.ORDER_DATE,
          E.FNAME + ' ' + E.LNAME AS NAME_EMPLO
   FROM CUSTOMERS C, ORDERS O, EMPLOYEES E
   WHERE O.CUSTOMER_ID=C.CUSTOMER_ID AND E.EMPLOYEE_ID=O.EMPLOYEE_ID

-- Да се изведат данните от изгледа.
SELECT * FROM CUSTOMERORDERS

-- Пример 5-3.
-- Да се модифицира горния изглед така, че да съдържа само поръчките,
-- обработени от служител с идентификатор = 167.
ALTER VIEW CUSTOMERORDERS
AS
   SELECT C.FNAME + ' ' + C.LNAME AS NAME_CUST ,   --ALL COLUMNS HAVE NAME IN VIEWS
          O.ORDER_ID,
          O.ORDER_DATE, e.EMPLOYEE_ID,
          E.FNAME + ' ' + E.LNAME AS NAME_EMPLO
   FROM CUSTOMERS C, ORDERS O, EMPLOYEES E
   WHERE O.CUSTOMER_ID=C.CUSTOMER_ID
   AND E.EMPLOYEE_ID=O.EMPLOYEE_ID
   AND E.EMPLOYEE_ID = 167

 -- Да се изведат данните от изгледа.
SELECT * FROM CUSTOMERORDERS

-- Пример 5-4.
-- Да се създаде изглед, съдържащ име и фамилия на служител и общата сума
-- на поръчките, които той е обработил.
CREATE VIEW EMPLOYEESORDERS
AS
    SELECT  E.FNAME + ' ' + E.LNAME AS NAME,
            SUM(OI.QUANTITY * OI.UNIT_PRICE) AS PRICE
    FROM EMPLOYEES E, ORDER_ITEMS  OI, ORDERS O
    WHERE E.EMPLOYEE_ID = O.EMPLOYEE_ID AND OI.ORDER_ID = O.ORDER_ID
    GROUP BY E.FNAME, E.LNAME


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

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

SELECT * FROM EMPLOYEES_TOP_5_SALARIES


------------------------5.4.Манипулиране на данни чрез изглед -----------------------
-------------------------------------------------------------------------------------
--Следващият изглед CUSTOMERS_COUNTRIES, базиран на JOIN между COUNTRIES и CUSTOMERS,
--ще демонстрира манипулирането на данни.
 CREATE VIEW CUSTOMER_COUNTRIES
 AS
 SELECT C.COUNTRY_ID, C.NAME, C.REGION_ID, M.CUSTOMER_ID, M.FNAME, M.LNAME, M.GENDER, M.COUNTRY_ID AS CUST_COUNTRY_ID, M.EMAIL
 FROM CUSTOMERS M JOIN COUNTRIES C
 ON C.COUNTRY_ID=M.COUNTRY_ID

--Пример 5-6.
--Да се добави нов запис в таблицата CUSTOMERS през изгледа CUSTOMERS_COUNTRIES.
 INSERT INTO CUSTOMER_COUNTRIES(CUSTOMER_ID, FNAME, LNAME, CUST_COUNTRY_ID)
 VALUES(10, 'IVAN', 'ILIEV', 'BG')

 SELECT * FROM CUSTOMER_COUNTRIES
 SELECT * FROM CUSTOMERS

--5.4.2. Променяне на данни през изглед
---Пример 5-7.
--Да се промени фамилията на клиент с идентификатор 10.
UPDATE CUSTOMER_COUNTRIES
SET LNAME = 'KOLEV'
WHERE CUSTOMER_ID = 10

 SELECT * FROM CUSTOMER_COUNTRIES
 SELECT * FROM CUSTOMERS

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

 DELETE FROM CUSTOMER_COUNTRIES
 WHERE CUSTOMER_ID = 10

--5.5.Задачи
--Задача 5-1.
--Да се създаде изглед, който съдържа имената на продуктите и общо поръчано
--количество от продукт.
 CREATE VIEW PRODUCTS_QUANTITY_VIEW
 AS
 SELECT P.NAME, SUM(OI.QUANTITY) QUANTITY
 FROM PRODUCTS P JOIN ORDER_ITEMS OI
 ON P.PRODUCT_ID=OI.PRODUCT_ID
 GROUP BY P.NAME, P.PRODUCT_ID

 SELECT * FROM PRODUCTS_QUANTITY_VIEW

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

CREATE VIEW BIGGEST_QUANTITY_ORDERS_VIEW
AS
SELECT TOP 10 WITH TIES C.CUSTOMER_ID, C.FNAME, C.LNAME, COUNT(O.CUSTOMER_ID) COUNT_ORDERS
                   FROM ORDERS O JOIN CUSTOMERS C
                     ON O.CUSTOMER_ID=C.CUSTOMER_ID
               GROUP BY C.CUSTOMER_ID, C.FNAME, C.LNAME
               ORDER BY 4 DESC

SELECT * FROM BIGGEST_QUANTITY_ORDERS_VIEW

---------------------------------------------------------------------------------------------------
----------------------------------------------ТРАНЗАКЦИИ-------------------------------------------
---------------------------------------------------------------------------------------------------

SELECT GETDATE()

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

BEGIN TRANSACTION

    --ОПЕРАЦИЯ 1: добавя нов клиент
    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
    VALUES(1010, 'BG', 'GEORGI', 'PETROV', 'PLOVDIV BUL.BULGARIA', 'gp@abv.bg', 'M')

    --ОПЕРАЦИЯ 2: създава поръчка за него
    INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS, ORDER_DATE)
    VALUES(10, 1010, 107, 'PLOVDIV BUL.BULGARIA', GETDATE())

    --включваща два продукта:
    --ОПЕРАЦИЯ 3 продукT 1726
    INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
    VALUES(10, 1726, 99, 1)

    --ОПЕРАЦИЯ 4 продукT 1782
    INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
    VALUES(10, 1782, 99, 1)

 COMMIT TRANSACTION


 SELECT * FROM ORDER_ITEMS
 WHERE ORDER_ID = 10


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

BEGIN TRAN
    PRINT 'ФАМИЛИЯ ПРЕДИ ТРАНЗАКЦИЯ'
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1010

    --променя фамилията на клиент с идентификатор = 1010
    UPDATE CUSTOMERS
    SET LNAME = 'MARINOV'
    WHERE CUSTOMER_ID = 1010

    PRINT 'ФАМИЛИЯ СЛЕД ПРОМЯНА ОТ ТРАНЗАКЦИЯТА'
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1010

ROLLBACK TRAN

    PRINT 'ФАМИЛИЯ СЛЕД ОТХВЪРЛЯНЕ (ROLLBACK) НА ТРАНЗАКЦИЯ'
    SELECT LNAME
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = 1010

/*Пример 6-3.
транзакция, която въвежда нов клиент, поставя точка на запис,
въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
отхвърля се само поръчката.*/
BEGIN TRAN
    --OPERATION 1  която въвежда нов клиент,
    INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, GENDER)
    VALUES(1012, 'BG', 'MIRA', 'ILIEVA', 'F')
-- поставя точка на запис,
SAVE TRAN POINT1
    --OPERATION 2 въвежда поръчка,
    INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
    VALUES(12, 1012, 110, GETDATE())
ROLLBACK TRAN POINT1
COMMIT TRAN

SELECT * FROM CUSTOMERS
WHERE CUSTOMER_ID = 1012

SELECT * FROM ORDERS
WHERE CUSTOMER_ID = 1012

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

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