26.05

-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
------------------------------------ JOIN -------------------------------------------------------
-------------------------------------------------------------------------------------------------
 ------------------------------------------------------------------------------------------------

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


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


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

SELECT * FROM COUNTRIES

SELECT * FROM REGIONS

SELECT COUNTRIES.COUNTRY_ID,
       COUNTRIES.NAME,
       COUNTRIES.REGION_ID,
       REGIONS.NAME
FROM COUNTRIES JOIN REGIONS
ON   COUNTRIES.REGION_ID = REGIONS.REGION_ID


---ВЪТРЕШЕН JOIN
SELECT *
FROM COUNTRIES JOIN REGIONS
ON   COUNTRIES.REGION_ID = REGIONS.REGION_ID

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

---SELECT * FROM .......   ,   ......... WHERE.........
---SELECT * FROM ........ JOIN ........ ON    .........


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

SELECT CUSTOMERS.FNAME,
       CUSTOMERS.LNAME,
       COUNTRIES.NAME AS [COUNTRY NAME],
       REGIONS.NAME AS [REGION NAME]
FROM CUSTOMERS, COUNTRIES, REGIONS
WHERE CUSTOMERS.COUNTRY_ID=COUNTRIES.COUNTRY_ID
  AND COUNTRIES.REGION_ID=REGIONS.REGION_ID

  SELECT CUSTOMERS.FNAME,
       CUSTOMERS.LNAME,
       COUNTRIES.NAME AS [COUNTRY NAME],
       REGIONS.NAME AS [REGION NAME]
FROM CUSTOMERS JOIN COUNTRIES ON CUSTOMERS.COUNTRY_ID=COUNTRIES.COUNTRY_ID
                         JOIN REGIONS ON COUNTRIES.REGION_ID=REGIONS.REGION_ID


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

 --ВЪТРЕШЕН JOIN
 SELECT *
 FROM REGIONS JOIN COUNTRIES
 ON REGIONS.REGION_ID=COUNTRIES.REGION_ID

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

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

--Пример 4-13.
--  Да се изведат държавите и регионите, в които се намират.
--  Резултатния набор да включва държавите, за които няма въведен регион.

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

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


/*Пример 4-14.
    Да се изведат държавите и регионите, в които се намират.
    Резултатния набор да включва държавите, за които няма въведен регион и регионите,
    за които няма въведени държави.*/

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

-----------------------------------------------------------------------------------------------------
--1. НАИМЕНОВАНИЯТА НА ДЛЪЖНОСТИТЕ С МИНИМАЛНА ЗАПЛАТА > 5000.
--СОРТИРАЙ РЕЗУЛТАТНИЯ НАБОР ПО МИНИМАЛНА ЗАПЛАТА НИЗХОДЯЩО.
SELECT JOB_TITLE, MIN_SALARY
FROM JOBS
WHERE MIN_SALARY > 5000
ORDER BY MIN_SALARY DESC
-----------------------------------------------------------------------------------------------------
--2. ИМЕНАТА НА РЕГИОНИТЕ, КАКТО И КОЛКО ДЪРЖАВИ ИМА В ТЯХ.
SELECT R.NAME,
       COUNT(C.COUNTRY_ID) AS [COUNTRIES COUNT]
FROM REGIONS R JOIN COUNTRIES C
ON R.REGION_ID=C.REGION_ID
GROUP BY R.NAME
-----------------------------------------------------------------------------------------------------
--3. ИМЕНАТА И БРОЯ ПОРЪЧКИ, КОИТО СА ИЗПЪЛНИЛИ СЛУЖИТЕЛИТЕ,
--КАТО РЕЗУЛТАТНИЯТ НАБОР ДА ВКЛЮЧВА ВСИЧКИ СЛУЖИТЕЛИ И ТЕЗИ, КОИТО ВСЕ ОЩЕ НЕ СА ИЗПЪЛНЯВАЛИ ПОРЪЧКИ.
--СОРТИРАЙ ПО БРОЯ НА ПОРЪЧКИТЕ ВЪВ ВЪЗХОДЯЩ РЕД.
SELECT * FROM EMPLOYEES
SELECT * FROM ORDERS

SELECT E.FNAME,
       E.LNAME,
       COUNT(O.ORDER_ID) AS [ORDERS COUNT]
FROM EMPLOYEES E LEFT JOIN ORDERS O
ON E.EMPLOYEE_ID = O.EMPLOYEE_ID
GROUP BY E.FNAME, E.LNAME
ORDER BY 3 ASC

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


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


/*Пример 4-16.
    Да се изведат отделите, в които има назначени служители.*/
    SELECT * FROM EMPLOYEES
    SELECT * FROM DEPARTMENTS

    SELECT *
    FROM EMPLOYEES E JOIN DEPARTMENTS D
    ON E.DEPARTMENT_ID=D.DEPARTMENT_ID

    --SEMI JOIN
    -- IN/EXIST
    SELECT *
    FROM DEPARTMENTS
    WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)

--Пример 4-17.
--  Да се изведат имената на клиентите, които все още не са правили поръчки.
 SELECT * FROM CUSTOMERS
 SELECT * FROM ORDERS

 --SELECT *
 --FROM CUSTOMERS C LEFT JOIN ORDERS O
 --ON C.CUSTOMER_ID=O.CUSTOMER_ID

 --ANTI JOIN
 --- NOT IN / NOT EXISTS
 SELECT *
 FROM CUSTOMERS
 WHERE CUSTOMER_ID  NOT IN (SELECT CUSTOMER_ID FROM ORDERS)


 SELECT *
 FROM CUSTOMERS C
 WHERE NOT EXISTS (SELECT *
                   FROM  ORDERS O
                   WHERE C.CUSTOMER_ID=O.CUSTOMER_ID)


--Пример 4-18.
--  Да се изведат комбинациите от всички региони и държави, сортирани по име на държава.

 --CROSS JOIN
 --4
 --6
 --4X6=24

 SELECT * FROM REGIONS         --6
 SELECT * FROM COUNTRIES       --29

 SELECT *
 FROM REGIONS, COUNTRIES   --174


 SELECT * FROM REGIONS CROSS JOIN COUNTRIES


 -------------------------------------------------------------------------------------------------
--4.6.7. Задачи

--Задача 4-8.
--  Извлечи идентификатори, дати на поръчките и имена на служители, които са ги обработили.

SELECT * FROM ORDERS
SELECT * FROM EMPLOYEES

SELECT ORDER_ID, ORDER_DATE, FNAME, LNAME
FROM ORDERS O JOIN EMPLOYEES E
ON O.EMPLOYEE_ID=E.EMPLOYEE_ID

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

SELECT FNAME, LNAME, ORDER_ID
FROM CUSTOMERS C LEFT JOIN ORDERS O
ON C.CUSTOMER_ID=O.CUSTOMER_ID


--Задача 4-10.
--  Да се изведат имената на продуктите, които не са поръчвани до момента.

SELECT * FROM PRODUCTS
SELECT * FROM ORDER_ITEMS

SELECT NAME
FROM PRODUCTS P LEFT JOIN ORDER_ITEMS OIT
ON P.PRODUCT_ID=OIT.PRODUCT_ID


SELECT NAME
FROM PRODUCTS P
WHERE NOT EXISTS (SELECT PRODUCT_ID
                    FROM ORDER_ITEMS OI
                    WHERE P.PRODUCT_ID=OI.PRODUCT_ID)

SELECT NAME
FROM PRODUCTS
WHERE PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM ORDER_ITEMS)


-----------------------------------------------------------------------------------------------
-------------------------------------ЗА ДОМАШНА:-----------------------------------------------
-----------------------------------------------------------------------------------------------

--Задача 4-11.
--  Да се изведат имената на всички клиенти, които са от n държави в регион „Западна Европа“
--ИМЕНА НА КЛИЕНТИ, ИМЕНА НА ДЪРЖАВИ И ИМЕНА НА РЕГИОНИ
SELECT * FROM CUSTOMERS
SELECT * FROM COUNTRIES
SELECT * FROM REGIONS


'Западна Европа'
-----------------------------------------------------------------------------------------------