2.2

-----------------------------------------------------------------------------------------
--------------------------------Homework--CHECK------------------------------------------
-----------------------------------------------------------------------------------------
--Изведи идентификаторите на всички продукти, които не са поръчвани [по поне 2 начина]
    --1
    SELECT PRODUCT_ID FROM PRODUCTS
    EXCEPT
    SELECT PRODUCT_ID FROM ORDER_ITEMS

    --2
    SELECT DISTINCT PRODUCT_ID, NAME
    FROM PRODUCTS
    WHERE PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM ORDER_ITEMS)

    --3
    SELECT DISTINCT PRODUCT_ID, NAME
    FROM PRODUCTS
    WHERE PRODUCT_ID <> ALL (SELECT PRODUCT_ID FROM ORDER_ITEMS)

    --4
    SELECT DISTINCT PRODUCT_ID, NAME
    FROM PRODUCTS P
    WHERE NOT EXISTS (SELECT * FROM ORDER_ITEMS OI
                      WHERE OI.PRODUCT_ID = P.PRODUCT_ID )

    --5. OUTER JOIN - LEFT/RIGHT/FULL
    SELECT P.*
    FROM PRODUCTS P LEFT JOIN ORDER_ITEMS OI
    ON P.PRODUCT_ID = OI.PRODUCT_ID
    WHERE OI.PRODUCT_ID IS NULL


-----------------------------------------------------------------------------------------
-------------------------------Видове OUTER JOIN-----------------------------------------
-----------------------------------------------------------------------------------------

    --Пример 4-12.
    --  Да се изведат регионите и държавите, които се намират в тях. Резултатният
    --  набор да включва и регионите, в които няма въведени държави.
    SELECT *
    FROM REGIONS R LEFT JOIN COUNTRIES C
    ON R.REGION_ID = C.REGION_ID

    SELECT *
    FROM COUNTRIES C RIGHT JOIN REGIONS R
    ON R.REGION_ID = C.REGION_ID


    --Пример 4-13.
    --  Да се изведат държавите и регионите, в които се намират.
    --  Резултатният набор да включва държавите, за които няма въведен регион.
    SELECT *
    FROM REGIONS R RIGHT JOIN COUNTRIES C
    ON R.REGION_ID = C.REGION_ID


    /*Пример 4-14.
    Да се изведат държавите и регионите, в които се намират.
    Резултатният набор да включва държавите, за които няма въведен регион и регионите,
    за които няма въведени държави.*/
    SELECT *
    FROM REGIONS R FULL JOIN COUNTRIES C
    ON R.REGION_ID = C.REGION_ID

    -------------------------------------------------------------------------------------
    -------------------  Задачи за препитване и самопрепитване --------------------------
    -------------------------------------------------------------------------------------

    -- 1. Наименуванията на длъжностите с минимална заплата > 5000.
    --    Сортирай резултатния набор по мин. заплата низходящо.
             SELECT J.JOB_TITLE,
                       J.MIN_SALARY
                  FROM JOBS J
                 WHERE J.MIN_SALARY > 5000
              ORDER BY 2 DESC

    -- 2. Имената на регионите, както и колко държави има в тях.
       SELECT R.NAME,
              COUNT(C.COUNTRY_ID) [COUNTRY COUNT]
         FROM REGIONS R JOIN COUNTRIES C
           ON R.REGION_ID = C.REGION_ID
     GROUP BY R.NAME

    -- 3. Извлечи имената и броя поръчки, които са изпълнили служителите,
    --    като резултатният набор да включва всички служители и тези, които все още
    --    не са изпълнявали поръчки. Сортирай по броя на поръчките във възходящ ред.
        SELECT E.FNAME,
               E.LNAME,
               COUNT(O.ORDER_ID) [ORDERS COUNT]
          FROM EMPLOYEES E LEFT JOIN ORDERS O
            ON E.EMPLOYEE_ID = O.EMPLOYEE_ID
      GROUP BY E.LNAME, E.FNAME
      ORDER BY 3 ASC


-----------------------------------------------------------------------------------------
/*----------------------------4.6.6. Други JOIN вариации---------------------------------
-----------------------------------------------------------------------------------------

    Пример 4-15.
        Да се изведат държавите и регионите, в които се намират.*/
        --EQUI-JOIN /=/
        SELECT *
        FROM COUNTRIES C, REGIONS R
        WHERE R.REGION_ID = C.REGION_ID

    /*Пример 4-16.
        Да се изведат отделите, в които има назначени служители.*/
        --SEMI-JOIN /IN/EXISTS/
        SELECT NAME
        FROM DEPARTMENTS
        WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)

    --Пример 4-17.
    --  Да се изведат имената на клиентите, които все още не са правили поръчки.
    --ANTI-JOIN /NOT IN/NOT EXISTS/
    SELECT FNAME, LNAME
    FROM CUSTOMERS C
    WHERE NOT EXISTS (SELECT *
                      FROM ORDERS O
                      WHERE O.CUSTOMER_ID= C.CUSTOMER_ID)

    --Пример 4-18.
    --  Да се изведат комбинациите от всички региони и държави, сортирани по име на държава.
    --CROSS (CARTESIAN) JOIN
    SELECT * FROM REGIONS, COUNTRIES
    SELECT * FROM REGIONS CROSS JOIN COUNTRIES

    --4.6.7. Задачи

    --Задача 4-8.
    --  Извлечи идентификатори, дати на поръчките и имена на служители, които са ги обработили.
    SELECT ORDER_ID,
           ORDER_DATE,
           CONCAT(FNAME, ' ', LNAME) AS служител
    FROM ORDERS O JOIN EMPLOYEES E
    ON E.EMPLOYEE_ID = O.EMPLOYEE_ID


-----------------------------------------------------------------------------------------
---------------------------------4.7.1. TOP ---------------------------------------------
--  TOP връща първите N реда в неопределен ред, за желаната подредба използваме ORDER BY!
-----------------------------------------------------------------------------------------
    --#1 На коя дата е първата направена поръчка за фирмата?
    SELECT TOP 1 WITH TIES *
    FROM ORDERS
    ORDER BY ORDER_DATE DESC

    -- Пример 4-19.
    -- 7-те продукта с най-ниска цена.
    SELECT TOP 7 *
    FROM PRODUCTS
    ORDER BY PRICE

    SELECT TOP 7 WITH TIES *
    FROM PRODUCTS
    ORDER BY PRICE

-----------------------------------------------------------------------------------------
--------------------------------Homework-------------------------------------------------
-----------------------------------------------------------------------------------------

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

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

    -- Задача 4-11.
    -- Да се изведат имената на всички клиенти, които са от държави в регион „Западна Европа“