16.02.22

-----------------------------------------------------------------------------------------
--------------------------------Homework-------------------------------------------------
-----------------------------------------------------------------------------------------
  /* Задача 4-13.
   Да се изведат име, фамилия и пол на клиентите, направили последните 5 поръчки.*/

   SELECT TOP 5 C.FNAME, C.LNAME, C.GENDER, O.ORDER_DATE
   FROM CUSTOMERS C JOIN ORDERS O
   ON C.CUSTOMER_ID = O.CUSTOMER_ID
   ORDER BY ORDER_DATE DESC

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

    CREATE VIEW MY_FIRST_VIEW
    AS
    SELECT   C.FNAME + ' ' + C.LNAME AS КЛИЕНТИ
           , O.ORDER_ID
           , O.ORDER_DATE
    FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID

    SELECT * FROM MY_FIRST_VIEW

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

    ALTER VIEW MY_FIRST_VIEW
    AS
    SELECT   C.FNAME + ' ' + C.LNAME AS КЛИЕНТИ
           , E.FNAME + ' ' + E.LNAME AS СЛУЖИТЕЛИ
           , O.ORDER_ID
           , O.ORDER_DATE
    FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
                     JOIN EMPLOYEES E ON E.EMPLOYEE_ID = O.EMPLOYEE_ID

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

    ALTER VIEW MY_FIRST_VIEW
    AS
    SELECT   C.FNAME + ' ' + C.LNAME AS КЛИЕНТИ
           , E.FNAME + ' ' + E.LNAME AS СЛУЖИТЕЛИ
           , O.ORDER_ID
           , O.ORDER_DATE
    FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
                     JOIN EMPLOYEES E ON E.EMPLOYEE_ID = O.EMPLOYEE_ID
    WHERE E.EMPLOYEE_ID = 167
    ORDER BY O.ORDER_ID
    OFFSET 0 ROWS

    SELECT * FROM MY_FIRST_VIEW
    ORDER BY 1

--Изведете имената на служителите и имената на техните преки началници*/
SELECT E.FNAME + ' ' + E.LNAME AS EMPLOYEE,
       M.FNAME + ' ' + M.LNAME AS MANAGER
FROM EMPLOYEES E JOIN EMPLOYEES M
ON E.MANAGER_ID = M.EMPLOYEE_ID

--Изведете имената на служителите и имената на техните преки началници,
--нека в резултата участват и служителите, които нямат преки началници
SELECT E.FNAME + ' ' + E.LNAME AS EMPLOYEE,
       M.FNAME + ' ' + M.LNAME AS MANAGER
FROM EMPLOYEES E LEFT JOIN EMPLOYEES M
ON E.MANAGER_ID = M.EMPLOYEE_ID

    --ЗАД 0.6
    --Да се създаде изглед, съдържащ информация за служителите,
    --които са и мениджъри и по колко починени имат.
    ALTER  VIEW BOSS_OF_EMPLOYEES_COUNT
    AS
    SELECT E1.EMPLOYEE_ID, E1.FNAME, E1.LNAME, COUNT(E2.MANAGER_ID) ПОДЧИНЕНИ
    FROM EMPLOYEES E1 JOIN EMPLOYEES E2
    ON E1.EMPLOYEE_ID = E2.MANAGER_ID
    GROUP BY E1.EMPLOYEE_ID, E1.FNAME, E1.LNAME
    ORDER BY 4 DESC
    OFFSET 0 ROWS

    --ЗАД 0.7
    --Да се създаде изглед, съдържащ информация за отделите, в които не работят
    --никакви служители.
    CREATE VIEW DEPT_WITHOUT_EMPLOYEES
    AS
    SELECT D.*
    FROM DEPARTMENTS D LEFT JOIN EMPLOYEES E
    ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
    WHERE  E.DEPARTMENT_ID IS NULL


    --Пример 5-4.
    --Да се създаде изглед, съдържащ име и фамилия на служител и общата сума на
    --поръчките, които той е обработил.
    CREATE VIEW EMP_TOTAL
    AS
    SELECT E.FNAME, E.LNAME, SUM(UNIT_PRICE * QUANTITY) 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
    GROUP BY E.FNAME, E.LNAME, E.EMPLOYEE_ID

    --Пример 5-5.
    --Да се създаде изглед, който съдържа имена, отдел и заплата на 5-мата
    --служители с най-висока заплата.
    SELECT TOP 5 FNAME, LNAME, DEPARTMENT_ID, SALARY
    FROM EMPLOYEES
    ORDER BY SALARY DESC

 -----------------------------------------------------------------------------------------
----------------------------------ПРЕПИТКА 3---------------------------------------------
-----------------------------------------------------------------------------------------
    --ЗАДАЧА 3.1
    --Създай изглед с име, фамилия, телефон и име на длъжност на служителите,
    --които работят в отдел 100.

    CREATE VIEW  EXAMPLEVIEW
    AS
    SELECT  e.FNAME, e.LNAME, e.PHONE, j.JOB_TITLE
    FROM EMPLOYEES e join JOBS j
    on e.JOB_ID = j.JOB_ID
    WHERE e.DEPARTMENT_ID = 100

    SELECT * FROM EXAMPLEVIEW
    ------------------------------------------------------------------------------------------

    --ЗАДАЧА 3.2
    --Модифицирай горния изглед като конкатенираш в една колона име и фамилия на служител,
    --и добавиш колони заплата на служителя и неговия пряк ръководител (мениджър).
    ALTER VIEW EXAMPLEVIEW
    AS
    SELECT  e.FNAME + ' ' + e.LNAME AS NAME,
            e.PHONE,
            j.JOB_TITLE,
            e.SALARY,
            e.MANAGER_ID
    FROM EMPLOYEES e join JOBS j
    on e.JOB_ID = j.JOB_ID
    WHERE e.DEPARTMENT_ID = 100

    SELECT * FROM EXAMPLEVIEW

    -------------------------------------------------------------------------------------------
    --ЗАДАЧА 3.3
    --Създай изглед върху изгледа от ЗАДАЧА 3.2 като в резултатния набор включиш само колоните:
    -- имена и идентификатор на мениджър.

    CREATE VIEW VIEWFROMVIEW
     AS
    SELECT NAME, MANAGER_ID
      FROM EXAMPLEVIEW

    SELECT * FROM VIEWFROMVIEW

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

    --ЗАДАЧА 3.4
    --Създай изглед с имената на регионите и броя на държавите в тях.
    CREATE VIEW COUNT_OF_COUNTRIES
    AS
    SELECT R.NAME, R.REGION_ID, COUNT(C.COUNTRY_ID) [броя на държавите]
    FROM REGIONS R JOIN COUNTRIES C
    ON R.REGION_ID = C.REGION_ID
    GROUP BY R.NAME, R.REGION_ID
    ORDER BY REGION_ID
    OFFSET 0 ROWS

    --------------------------------------------------------------------------
    ------------------5.4.Манипулиране на данни чрез изглед ------------------
    --------------------------------------------------------------------------
    --Пример 5-6.1
    --Създай изглед базиран на JOIN между таблиците COUNTRIES и CUSTOMERS
    CREATE VIEW COUNTRIES_CUSTOMERS
    AS
    SELECT CO.COUNTRY_ID AS COUNTRY_COUNTRY_ID , CO.NAME, CO.REGION_ID,
           CU.CUSTOMER_ID, CU.FNAME, CU.LNAME, CU.GENDER,
           CU.COUNTRY_ID AS CUSTOMERS_COUNTRY_ID, CU.EMAIL
    FROM CUSTOMERS CU JOIN COUNTRIES CO
    ON CU.COUNTRY_ID = CO.COUNTRY_ID

    SELECT * FROM COUNTRIES_CUSTOMERS

    -----------------5.4.1. Добавяне на данни чрез изглед---------------------

    --Пример 5-6.2
    --Да се добави нов запис в таблицата CUSTOMERS през изгледа от Пр. 5-6.1.
     INSERT INTO COUNTRIES_CUSTOMERS (CUSTOMER_ID, FNAME, LNAME, GENDER, CUSTOMERS_COUNTRY_ID, EMAIL)
     VALUES (10, 'Мира', 'Тодорова', 'F', 'BG', 'miramira@gmail.com')

     SELECT * FROM CUSTOMERS
     WHERE CUSTOMER_ID = 10

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

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

     UPDATE COUNTRIES_CUSTOMERS
     SET LNAME = 'Лукова'
     WHERE CUSTOMER_ID = 10

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


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

    SELECT * FROM PROD_SUM_QUNT

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


    --Задача 0.4*
    --Да се създаде изглед с имената на държавите с повече от 5 клиента от тях.