view/modify_view

--------------------------------------------------------------------------
-----------------------------Изгледи = Views------------------------------
--------------------------------------------------------------------------

--  Създаване на изгледи

--1. Да се създаде изглед, който съдържа номерата на поръчките,
-- датата им, името на клиента, направил поръчката.
CREATE VIEW CustomerOrders
AS
SELECT c.FNAME +  ' ' + c.LNAME as Customer,
       o.ORDER_ID,
       o.ORDER_DATE
  FROM ORDERS o, CUSTOMERS c
 WHERE o.CUSTOMER_ID = c.CUSTOMER_ID

SELECT * FROM CustomerOrders

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

ALTER VIEW CustomerOrders
AS
SELECT c.FNAME +  ' ' + c.LNAME as Customer,
       o.ORDER_ID,
       o.ORDER_DATE,
       e.FNAME + ' ' + e.LNAME as Employee
  FROM ORDERS o, CUSTOMERS c, EMPLOYEES e
 WHERE e.EMPLOYEE_ID = o.EMPLOYEE_ID
   AND o.CUSTOMER_ID = c.CUSTOMER_ID

--3. Да се модифицира горния изглед така, че да съдържа само поръчките,
--обработени от служител с идентификатор = 167.

ALTER VIEW CustomerOrders
AS
SELECT C.FNAME + ' ' + C.LNAME AS Customers,
       O.ORDER_ID,
       O.ORDER_DATE,
       E.FNAME + ' ' + E.LNAME AS Employees
  FROM EMPLOYEES E, ORDERS O, CUSTOMERS C
 WHERE E.EMPLOYEE_ID = O.EMPLOYEE_ID
   AND O.CUSTOMER_ID = C.CUSTOMER_ID
   AND E.EMPLOYEE_ID = 167

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

 CREATE VIEW EmployeeOrders
 AS
  SELECT E.FNAME,
         E.LNAME,
         MAX(ORD.TOTAL) MaxSum,
         SUM(ORD.TOTAL) TotalPrice
    FROM EMPLOYEES E,
         (SELECT O.EMPLOYEE_ID,
                 SUM(OI.UNIT_PRICE*OI.QUANTITY) AS Total
            FROM ORDERS O, ORDER_ITEMS OI
           WHERE O.ORDER_ID = OI.ORDER_ID
        GROUP BY O.ORDER_ID, O.EMPLOYEE_ID) ORD
   WHERE E.EMPLOYEE_ID = ORD.EMPLOYEE_ID
GROUP BY E.EMPLOYEE_ID, E.FNAME, E.LNAME

SELECT * FROM EmployeeOrders
ORDER BY TotalPrice DESC

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

-- 1.Да се създаде изглед, който да съдържа колони с идентификатор,
-- държава и име на клиент от таблицата CUSTOMERS.
CREATE VIEW UpdateCustomerView
AS
SELECT CUSTOMER_ID,
       COUNTRY_ID,
       FNAME,
       LNAME
  FROM CUSTOMERS

INSERT INTO UpdateCustomerView(CUSTOMER_ID, COUNTRY_ID,  FNAME, LNAME)
     VALUES (2, 'BG', 'Plamen', 'Georgiev')

--INSERT INTO UpdateCustomerView(CUSTOMER_ID, COUNTRY_ID,  FNAME, LNAME)
--VALUES (2, 'BG', 'Plamen', 'Georgiev')

SELECT * FROM UpdateCustomerView

---------------------------------------------------------------------------
-- Променяне на данни през изглед

--1. Да се промени името на клиент с идентификатор ‘2’.

UPDATE UpdateCustomerView
   SET LNAME = 'Stoychev'
 WHERE CUSTOMER_ID = 2

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

-- Изтриване на данни през изглед
--1. Да се изтрие клиент с идентификатор ‘2’.

DELETE FROM UpdateCustomerView
WHERE CUSTOMER_ID = 2