27.10.22

-------------------------------------------------------------------------------------------
------------------------------------ JOIN -------------------------------------------------
--JOIN се използва за извличане на данни от две или повече таблици, като редовете им се
--комбинират чрез логическа връзка между таблиците, която може да бъде във FROM или WHERE.
--Обикновено тази връзка е първичен/външен ключ, но не задължително.

-------------------------------------INNER JOIN или просто JOIN-----------------------------
--Извежда редовете от две/повече таблици, които имат съвпадащи стойности в колоните,
--посочени в условието за сравнение.

--Пример 4-10.
--  Да се изведат държавите и регионите, в които се намират.
    SELECT * FROM COUNTRIES  --29
    SELECT * FROM REGIONS    --6

    SELECT *
    FROM COUNTRIES JOIN REGIONS
    ON COUNTRIES.REGION_ID = REGIONS.REGION_ID

    SELECT *
    FROM COUNTRIES , REGIONS
    WHERE COUNTRIES.REGION_ID = REGIONS.REGION_ID

    -- Пример 4-11.
    -- Изведи имена на клиенти,
    -- имена на държавите от които са,
    -- и имена на регионите на държавите.

    --1
    SELECT FNAME, LNAME , REGIONS.NAME, COUNTRIES.NAME
    FROM COUNTRIES , REGIONS, CUSTOMERS
    WHERE COUNTRIES.REGION_ID = REGIONS.REGION_ID
        AND CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID

    --2
    SELECT FNAME, LNAME , R.NAME, CO.NAME
    FROM COUNTRIES CO , REGIONS R, CUSTOMERS CU
    WHERE CO.REGION_ID = R.REGION_ID
        AND CU.COUNTRY_ID = CO.COUNTRY_ID

    --3
    SELECT FNAME, LNAME , R.NAME, CO.NAME
    FROM COUNTRIES CO JOIN REGIONS R    ON CO.REGION_ID = R.REGION_ID
                      JOIN CUSTOMERS CU ON CU.COUNTRY_ID = CO.COUNTRY_ID


-----------------------------------------------------------------------------------------
------------------------OUTER JOIN. Видове: LEFT/RIGHT/FULL------------------------------
-----------------------------------------------------------------------------------------

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

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

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

    --2
    SELECT *
    FROM  COUNTRIES C LEFT JOIN REGIONS R
    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 JOB_TITLE, MIN_SALARY
    FROM JOBS
    WHERE MIN_SALARY > 5000
    ORDER BY MIN_SALARY DESC

    --#2.
    --Изведете имената на служителите, наименованията на длъжностите им,
    --и имената на отделите, в които работят.

            -----------------------
            SELECT * FROM EMPLOYEES      --> JOB_ID IS a FOREIGN KEY
                                         --> DEPARTMENT_ID IS a FOREIGN KEY

            SELECT * FROM JOBS           --> JOB_ID IS the PRIMARY KEY
            SELECT * FROM DEPARTMENTS    --> DEPARTMENT_ID IS the PRIMARY KEY
            -----------------------


    --1
    SELECT FNAME,
           LNAME,
           JOB_TITLE,
           NAME
    FROM EMPLOYEES E, JOBS J, DEPARTMENTS D
    WHERE E.JOB_ID = J.JOB_ID AND D.DEPARTMENT_ID =  E.DEPARTMENT_ID

    --2
    SELECT FNAME + ' ' + LNAME AS СЛУЖИТЕЛ,
           JOB_TITLE ДЛЪЖНОСТ,
           NAME AS ОТДЕЛ
    FROM EMPLOYEES E JOIN  JOBS J ON E.JOB_ID = J.JOB_ID
                     JOIN DEPARTMENTS D ON D.DEPARTMENT_ID =  E.DEPARTMENT_ID

    --#3.
    --Извeдете имената и броя поръчки, които са изпълнили служителите,
    --като резултатният набор да включва всички служители и тези, които все още
    --не са изпълнявали поръчки. Сортирайте по броя на поръчките във възходящ ред.
    SELECT * FROM EMPLOYEES
    SELECT * FROM ORDERS

    SELECT E.FNAME + ' '+ E.LNAME AS служители,
           COUNT(ORDER_ID) 'броя на поръчките'
    FROM EMPLOYEES E LEFT JOIN ORDERS O
    ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
    GROUP BY E.FNAME + ' '+ E.LNAME, E.EMPLOYEE_ID
    ORDER BY 'броя на поръчките'

    --#4.
    --Изведете имена, заплата и идентификатор на длъжност на служителите,
    --които работят в отдел 80 и не са обработвали поръчки до момента;
    SELECT FNAME, LNAME, SALARY, JOB_ID, ORDER_ID
    FROM EMPLOYEES E LEFT JOIN ORDERS O
    ON E.EMPLOYEE_ID = O.EMPLOYEE_ID
    WHERE DEPARTMENT_ID = 80 AND ORDER_ID IS NULL


    --#5.
    --Изведете имената на отделите и съответния брой служители, които работят в тях.
    --Нека в резултатния набор да участват само отделите, които се намират в държави
    --с идентификатор 'BG' или 'DE', като в отделите работят не по-малко от 7 служители.
    --Сортирайте резултатния набор по броя на служителите във възходящ ред.
    SELECT NAME,
           COUNT(EMPLOYEE_ID) AS 'брой служители'
    FROM DEPARTMENTS D JOIN EMPLOYEES E
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
    WHERE COUNTRY_ID IN ('BG', 'DE')
    GROUP BY NAME
    HAVING  COUNT(EMPLOYEE_ID) >= 7
    ORDER BY 'брой служители' ASC

    --#6.
    --Изведете идентификаторите на клиентите и общата стойност на поръчките им.
    --Нека участват само клиенти с обща стойност на поръчките над 900000 и под 1500000.
    SELECT O.CUSTOMER_ID,
           SUM(UNIT_PRICE*QUANTITY)  'общата стойност'
    FROM ORDERS O JOIN ORDER_ITEMS OI
    ON O.ORDER_ID = OI.ORDER_ID
    GROUP BY O.CUSTOMER_ID
    HAVING  SUM(UNIT_PRICE*QUANTITY)  BETWEEN 900000  AND 1500000

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

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

    --COALESCE or ISNULL

    SELECT FNAME, LNAME, ISNULL(CAST(ORDER_ID AS VARCHAR), 'none')
    FROM CUSTOMERS C LEFT JOIN ORDERS O
    ON  O.CUSTOMER_ID = C.CUSTOMER_ID

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

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

    -- Пример 4-15.
    -- Да се изведат държавите и регионите, в които се намират.
        --EQUI-JOIN /=/
        SELECT *
        FROM REGIONS R, COUNTRIES C
        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

        --1
        SELECT * FROM REGIONS CROSS JOIN COUNTRIES

        --2
        SELECT * FROM REGIONS, COUNTRIES

-----------------------------------------------------------------------------------------
---------------------------------4.7.1. TOP ---------------------------------------------
-----------------------------------------------------------------------------------------
-- TOP връща първите N реда в неопределен ред => за желана подредба се използва ORDER BY!

    --#7.
    --На коя дата е първата направена поръчка за фирмата?
    SELECT TOP 1 ORDER_DATE, ORDER_ID
    FROM ORDERS
    ORDER BY ORDER_DATE ASC

    --#8.
    --На коя дата е назначен първият служител на фирмата и какви са неговите имена?
    --Нека в резултатния набор участват и останалите служители назначени на същата дата
    --(ако има такива).
    SELECT TOP 1 WITH TIES FNAME, LNAME, HIRE_DATE
    FROM EMPLOYEES
    ORDER BY HIRE_DATE ASC

    --#9.
    --Изведете седемте продукта с най-ниска складова цена.
    SELECT TOP 7 WITH TIES *
    FROM PRODUCTS
    ORDER BY PRICE ASC

    --#10.
    --Изведете имената и единичната цена на 7-те продукта с най-ниска цена,
    --на която са били продадени.
    SELECT DISTINCT TOP 7 WITH TIES P.NAME, UNIT_PRICE
    FROM PRODUCTS P JOIN ORDER_ITEMS OI
    ON OI.PRODUCT_ID = P.PRODUCT_ID
    ORDER BY UNIT_PRICE ASC