26.01.22

-------------------------------------------------------------------------------------------
----------------------------------homework check-------------------------------------------
-------------------------------------------------------------------------------------------
--Пример 4-1.
--Да се изведат имената, датите на назначаване и заплатите на всички служители.
SELECT FNAME, LNAME, HIRE_DATE, SALARY
  FROM EMPLOYEES

/*Пример 4-2.
Да се изведат всички данни за продуктите, с цена по-голяма от 2000.
Резултатът нека бъде подреден по цена на продукт възходящо.*/
  SELECT *
    FROM PRODUCTS
   WHERE PRICE > 2000
ORDER BY PRICE

--Пример 4-4.
--Да се изведе броя служители, групирани по отдела, в който работят.
  SELECT DEPARTMENT_ID,
         COUNT(EMPLOYEE_ID) AS EMP_COUNT
    FROM EMPLOYEES
GROUP BY DEPARTMENT_ID

--4.2.1. Задачи
--Задача 4-1.
--Да се изведат имената, заплатите и идентификаторите на длъжностите на
--служителите, работещи в отдели 50 и 80. Резултатът да е подреден по фамилия на служител във възходящ ред.
  SELECT FNAME,
         LNAME,
         SALARY,
         JOB_ID
    FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 50 OR DEPARTMENT_ID = 80
ORDER BY LNAME ASC

------------------------------------------------------------------------------------------
--------------------------------условия на търсене----------------------------------------
------------------------------------------------------------------------------------------

--Обхват – BETWEEN, NOT BETWEEN
SELECT *
 FROM PRODUCTS
WHERE PRICE NOT BETWEEN 1 AND 1000
 ------------------------------------------------------

--Принадлежност към множество – IN, NOT IN.
SELECT *
 FROM CUSTOMERS
WHERE COUNTRY_ID in ('BG', 'DE')
 ------------------------------------------------------
--Търсене на неизвестни стойности – IS [NOT] NULL.

 /*Поле със стойност NULL е празно поле. Това е липса на стойност.
 Стойността на NULL е различна от 0 или интервал.  Ако column в
таблица е разрешено да сърържа null стойности можете да вмъкнете нов запис или да
актуализирате запис, без да добавите стойност в нея. Тогава полето ще бъде запазено с NULL стойност.
Не е. възможно да се тестват стойности за NULL с оператори за сравнение =, <, <>.
 Вместо тях използваме операторите IS NULL и IS NOT NULL.
*/
select *
  from EMPLOYEES
 where MANAGER_ID is not null

select *
  from EMPLOYEES
 where MANAGER_ID is  null
  ------------------------------------------------------
/*Наколко условия за търсене се обединяват с AND или/и OR.

Приоритетът на логическите оператори е
1). NOT
2). AND
3). OR
Скобите променят приоритета.
*/

SELECT *
  FROM Customers
 WHERE COUNTRY_ID='BG'
  AND (GENDER='F' OR LNAME='Петров')

---Wildcards [more info: --https://www.w3schools.com/sql/sql_wildcards.asp ]
SELECT * FROM Customers
WHERE ADDRESS LIKE '%, София%'

--_
--%


-------------------------------------------------------------------------------------------
-------------------------------------Set operators-----------------------------------------
---------------------------------------UNION ----------------------------------------------
/*  Резултатните набори, които се обединяват, трябва да отговарят на следните условия:
        -Всяка заявка в оператора UNION трябва да има еднакъв брой колони;
        -Колоните трябва да имат съвместими типове от данни;
        -В целия израз може да присъства само една клауза ORDER BY накрая,
        сортираща обединения резултат.
*/

--Пример 4-5.
    --Да се изведат идентификаторите на държавите, в които има клиенти или отдели на фирмата.
        SELECT COUNTRY_ID FROM CUSTOMERS
        UNION
        SELECT COUNTRY_ID FROM DEPARTMENTS

--Пример 4-6.
    --Да се изведат идентификаторите на държавите, в които има клиенти или отдели на фирмата.
    --Нека в резултатния набор участват и дублиращите се записи.
        SELECT COUNTRY_ID FROM CUSTOMERS
        UNION ALL
        SELECT COUNTRY_ID FROM DEPARTMENTS

/*
4.3.1. Задачи
Задача 4-4.
    Да се изведат всички малки имена на клиенти и служители с евентуалните
    повторения, сортирани в низходящ ред по име. */
    SELECT FNAME FROM CUSTOMERS
    UNION ALL
    SELECT FNAME FROM EMPLOYEES
    ORDER BY 1 DESC

/*Задача 4-5.
    Да се изведат име и фамилия на клиенти и служители без повторения, а като
    трета колона за клиентите да се използва израз, генериращ низа „Клиент
    (<идентификатор>)“, за служителите – „Служител (<идентификатор>)“. */

    SELECT FNAME, LNAME, 'Клиент (' + COUNTRY_ID + ')' AS ID
    FROM CUSTOMERS
    UNION
    SELECT FNAME, LNAME, 'Служител (' + CAST (DEPARTMENT_ID AS varchar) + ')'
    FROM EMPLOYEES
    ORDER BY 2 DESC

----------------------------------INTERSECT(сечение)---------------------------------------
/*резултата съдържа общите за двата резултатни набора редове, без дубликати.
условия:
     -Броят на колоните във всички заявки трябва да бъде еднакъв;
     -Колоните трябва да бъдат от съвместими типове от данни.

Пример 4-7.
    Да се изведат id на държавите, в които има клиенти и отдели на фирмата едновременно.
*/
--1
SELECT COUNTRY_ID FROM CUSTOMERS
INTERSECT
SELECT COUNTRY_ID FROM DEPARTMENTS

--2
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE COUNTRY_ID IN (SELECT COUNTRY_ID FROM DEPARTMENTS)

--3
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE COUNTRY_ID = ANY (SELECT COUNTRY_ID FROM DEPARTMENTS)

--4
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE EXISTS (SELECT * FROM DEPARTMENTS
              WHERE DEPARTMENTS.COUNTRY_ID = CUSTOMERS.COUNTRY_ID)

--Задача 4-6.
--  Да се изведат общите собствени имена на клиенти и служители.
SELECT FNAME FROM CUSTOMERS
INTERSECT
SELECT FNAME FROM EMPLOYEES

----------------------------------EXCEPT---------------------------------------------------
/*връща редовете, върнати от първата заявка, които не се срещат измежду редове от втората.
условия:
    -Броят на колоните във двете заявки трябва да бъде еднакъв;
    -Колоните трябва да бъдат от съвместими типове от данни. */

--Пример 4-8.
--  Изведи id на държавите, в които има клиенти и в същото време няма отдели на фирмата.
--1
SELECT COUNTRY_ID FROM CUSTOMERS
EXCEPT
SELECT COUNTRY_ID FROM DEPARTMENTS

--2
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE COUNTRY_ID NOT IN (SELECT COUNTRY_ID FROM DEPARTMENTS)

--3
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE COUNTRY_ID <> ALL (SELECT COUNTRY_ID FROM DEPARTMENTS)

--4
SELECT DISTINCT COUNTRY_ID
FROM CUSTOMERS
WHERE NOT EXISTS (SELECT * FROM DEPARTMENTS
                   WHERE DEPARTMENTS.COUNTRY_ID = CUSTOMERS.COUNTRY_ID)

/*4.5.1. Задачи
Задача 4-7.
    Да се изведат собствени имена на клиенти, които не се срещат сред тези на служители.*/

    SELECT FNAME FROM EMPLOYEES
    WHERE FNAME ='Александър'

    SELECT FNAME FROM CUSTOMERS
    WHERE FNAME LIKE '%Александър'

    SELECT TRIM(FNAME) FROM CUSTOMERS
    EXCEPT
    SELECT TRIM(FNAME) FROM EMPLOYEES


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

    -------------------------------------
        --join types:
    -------------------------------------
        --1. INNER JOIN
        --2. OUTER JOIN
                        --2.1 RIGHT
                        --2.2 LEFT
                        --2.3 FULL
        --3. CROSS JOIN
    -------------------------------------


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

        -------------------------------------
        --INNER JOIN ONLY: [SYNTAX 2 TYPES]
        -------------------------------------
        --- [1.]  T1 ,    T2  WHERE  _____
        --- [2.]  T1 JOIN T2  ON     _____


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


--- T1 JOIN T2  ON  _____
SELECT *                             ---27 ROWS
FROM COUNTRIES JOIN REGIONS
ON COUNTRIES.REGION_ID = REGIONS.REGION_ID
--WHERE REGIONS.NAME = 'Северен полюс'


--- T1 ,    T2  WHERE  _____
SELECT *
FROM COUNTRIES C , REGIONS R
WHERE C.REGION_ID = R.REGION_ID




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

--- T1 JOIN T2  ON  _____
---    JOIN T3  ON  _____


--С ЦЕЛИТЕ ИМЕНА НА ТАБЛИЦИ:
SELECT CUSTOMERS.FNAME +' '+ CUSTOMERS.LNAME AS клиент,
       COUNTRIES.NAME                        AS [име на държава],
       REGIONS.NAME                          AS [име на регион]
  FROM CUSTOMERS JOIN  COUNTRIES ON CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID
                 JOIN REGIONS    ON COUNTRIES.REGION_ID = REGIONS.REGION_ID


--С ЛОКАЛНИ ЗА ЗАЯКАТА ИМЕНА НА ТАБЛИЦИ
SELECT CU.FNAME +' '+ CU.LNAME AS клиент,
       CO.NAME                 AS [име на държава],
       R.NAME                  AS  [име на регион]
  FROM CUSTOMERS CU JOIN  COUNTRIES CO ON CU.COUNTRY_ID = CO.COUNTRY_ID
                    JOIN  REGIONS R    ON CO.REGION_ID = R.REGION_ID



--- T1 ,  T2, T3  WHERE _____ AND _____
SELECT CU.FNAME +' '+ CU.LNAME AS клиент,
       CO.NAME AS [име на държава],
       R.NAME AS  [име на регион]
  FROM CUSTOMERS CU ,  COUNTRIES CO, REGIONS R
 WHERE CU.COUNTRY_ID = CO.COUNTRY_ID AND  CO.REGION_ID = R.REGION_ID



--------------------------------Homework----------------------------------------------------
--Изведи идентификаторите на всички продукти, които не са поръчвани по поне 2 начина
--P.S. без да използваш OUTER JOIN