9.2.РЕШЕНИЯ

-----------------------------------------------------------------------------------------
--------------------------------Homework-------------------------------------------------
-----------------------------------------------------------------------------------------
    -- Задача
    -- Имена, заплата и длъжност на служителите, които работят в отдел 80 и не са
    --обработвали поръчки до момента;

    --1
    SELECT E.FNAME,  E.LNAME,
           E.SALARY,
           E.JOB_ID,
           E.DEPARTMENT_ID
      FROM EMPLOYEES E LEFT JOIN ORDERS O
        ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
     WHERE E.DEPARTMENT_ID = 80 AND ORDER_ID IS NULL

    --2 С HAVING
      SELECT E.FNAME, E.LNAME,
             E.SALARY,
             E.JOB_ID,
             E.DEPARTMENT_ID,
             COUNT(O.ORDER_ID)
        FROM EMPLOYEES E LEFT JOIN ORDERS O
          ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
       WHERE E.DEPARTMENT_ID = 80
    GROUP BY E.FNAME, E.LNAME, E.SALARY, E.JOB_ID, E.DEPARTMENT_ID
      HAVING COUNT(O.ORDER_ID) = 0

    -- Задача 4-9.
    -- Да се изведат имената на всички клиенти и id на поръчките им.
    -- В резултатния набор да участват и клиентите, които все още не са правили поръчки.

    SELECT C.FNAME, LNAME, O.ORDER_ID
          -- ,ISNULL(CAST(O.ORDER_ID AS VARCHAR), '-') AS ПОРЪЧКИ
    FROM CUSTOMERS C LEFT JOIN ORDERS O
    ON O.CUSTOMER_ID=C.CUSTOMER_ID
    ORDER BY ORDER_ID

    -- Задача 4-11.
    -- Да се изведат имената на всички клиенти, които са от държави в регион „Западна Европа“
   SELECT CU.FNAME + ' ' + CU.LNAME AS CUSTOMER,
          CO.NAME AS COUNTRY,
          R.NAME AS REGION
     FROM CUSTOMERS CU, COUNTRIES CO, REGIONS R
    WHERE CU.COUNTRY_ID=CO.COUNTRY_ID
          AND R.REGION_ID=CO.REGION_ID
          AND R.NAME = 'Западна Европа'

-----------------------------------------------------------------------------------------
--------------------------------- Задачи ОТ СЕДМИЦА 6:  ---------------------------------
-----------------------------------------------------------------------------------------
    --Зад 0.4
    --Клиентите, които са поръчвали през 2000 година
    SELECT C.FNAME, C.LNAME,
           O.ORDER_ID,
           O.ORDER_DATE
           --,DATENAME(MONTH, O.ORDER_DATE) + ' ' + CAST (YEAR(O.ORDER_DATE) AS VARCHAR) AS DATE
    FROM  CUSTOMERS C JOIN  ORDERS O
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
    WHERE YEAR(O.ORDER_DATE) = 2000
    --AND MONTH(O.ORDER_dATE) = 7
    ORDER BY MONTH(O.ORDER_DATE)

    --ЗАД 0.5
    --Служителите, които са обработили повече от 5 поръчки
    SELECT COUNT(O.ORDER_ID) ORD_COUNT,
           E.FNAME, E.LNAME,
           E.EMPLOYEE_ID
    FROM EMPLOYEES E JOIN ORDERS O
    ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
    GROUP BY E.FNAME, E.LNAME, E.EMPLOYEE_ID
    HAVING COUNT(O.ORDER_ID) > 5

  -----------------------------------------------------------------------------------------
  ----------------------------------ПРЕПИТКА 2---------------------------------------------
  -----------------------------------------------------------------------------------------
        --ЗАД 1.
        --Клиентите, които са поръчали само веднъж. Сортирай по малко име във възходящ ред
         SELECT COUNT(ORDER_ID) AS ПОРЪЧКИ,
               C.CUSTOMER_ID,
               C.FNAME,C.LNAME
        FROM CUSTOMERS C JOIN ORDERS O
        ON C.CUSTOMER_ID = O.CUSTOMER_ID
        GROUP BY C.CUSTOMER_ID, C.FNAME, C.LNAME
        HAVING  COUNT(ORDER_ID) = 1
        ORDER BY FNAME

         --ЗАД 2.
        --Изведи минималната заплата на длъжностите, в които има назначени повече от 10 служителя
         SELECT COUNT(E.JOB_ID) AS СЛУЖИТЕЛИ,
               J.JOB_TITLE,
               J.MIN_SALARY
        FROM EMPLOYEES E JOIN JOBS J
        ON J.JOB_ID = E.JOB_ID
        GROUP BY J.MIN_SALARY, J.JOB_TITLE
        HAVING COUNT(E.JOB_ID) > 10

         --ЗАД 3.
        --Изведи длънжостите, на които няма назначени служители
         SELECT COUNT(E.JOB_ID) AS СЛУЖИТЕЛИ,
               J.JOB_ID,
               J.JOB_TITLE
        FROM EMPLOYEES E RIGHT JOIN JOBS J
        ON J.JOB_ID = E.JOB_ID
        GROUP BY J.MIN_SALARY, J.JOB_TITLE,  J.JOB_ID
        HAVING COUNT(E.JOB_ID) = 0

         --ЗАД 4.
        --В кой град се намира отдела, чийто служители получават най-голяма средна заплата
         SELECT TOP 1 AVG(E.SALARY),
                      E.DEPARTMENT_ID,
                      D.CITY
        FROM EMPLOYEES E JOIN DEPARTMENTS D
        ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
        GROUP BY E.DEPARTMENT_ID, D.CITY
        ORDER BY 1 DESC

-----------------------------------------------------------------------------------------
---------------------------------4.7.2. OFFSET и FETCH ----------------------------------
-----------------------------------------------------------------------------------------
    --ЗАД 4. OT ПРЕПИТКА 2 МОЖЕ ДА БЪДЕ РЕШЕНА И С OFFSET и FETCH
    --В кой град се намира отдела, чийто служители получават най-голяма средна заплата
        SELECT     AVG(E.SALARY) AS S,
                   E.DEPARTMENT_ID,  D.CITY
        FROM EMPLOYEES E  JOIN DEPARTMENTS D
        ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
        GROUP BY E.DEPARTMENT_ID, D.CITY
        ORDER BY 1 DESC
        OFFSET 0 ROW                      ---ПРЕМАХВАНЕ НА БР.РЕДОВЕ
        FETCH NEXT 1 ROW ONLY             ---ИЗВЕЖДАНЕ НА СЛЕДВАЩИТЕ БР.РЕДОВЕ


    --Пример 4-21.
    --Всички служители, подредени по дата на постъпване.
     SELECT *
     FROM EMPLOYEES
     ORDER BY HIRE_DATE

    -- Петимата служители, започвайки от 10-ти ред, подредени по дата на постъпване.
     SELECT *
     FROM EMPLOYEES
     ORDER BY HIRE_DATE
     OFFSET 9 ROWS
     FETCH NEXT 5 ROWS ONLY

    --Задача 4-12.
    --вторите 10 най-добре платени служители (подредени по заплата низходящо).  */
    SELECT *
    FROM EMPLOYEES
    ORDER BY SALARY DESC
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY

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

----------------------------------------------------------------------------------------
-----------------------------Изгледи = Views--------------------------------------------
-----------------------------Създаване на изгледи-CREATE VIEW---------------------------

    --Пример 5-1.
    --Да се създаде изглед, който
    --съдържа име и фамилия на клиентите, както и
    --номер и дата на поръчките, които те са направили.
    CREATE VIEW MY_FIRST_VIEW
    AS
    SELECT C.FNAME + ' ' + C.LNAME AS CUSTOMER, --- ВСИЧКИ КОЛОНИ ТРЯБВА ДА ИМАТ ИМЕНА
           O.ORDER_ID,
           O.ORDER_DATE
    FROM CUSTOMERS C JOIN ORDERS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID

    SELECT * FROM MY_FIRST_VIEW