14MASTERS

--use DATABASE_NAME
--------------------------------------------------------------------------------
/*Задача 3-2.
    Да се увеличи количеството с 2 броя и да се намали единичната цена с 5% на
    продукт с идентификатор 2254 в поръчка с идентификатор 2354.*/

    --ПРОВЕРЯВАМЕ ПРЕДИ
     SELECT * FROM ORDER_ITEMS

    --ПОРОМЯНА
     UPDATE ORDER_ITEMS
     SET UNIT_PRICE *= 0.95, QUANTITY += 2
     WHERE ORDER_ID = 2354 AND PRODUCT_ID= 2254

     --ПРОВЕРЯВАМЕ СЛЕД
     SELECT * FROM ORDER_ITEMS
--------------------------------------------------------------------------------
--Задача 3-3.
    --Да се изтрие служител с идентификатор 183.
    --ПРОВЕРЯВАМЕ ПРЕДИ
    SELECT * FROM EMPLOYEES

    --ИЗТРИВАНЕ
    DELETE FROM EMPLOYEES
    WHERE  EMPLOYEE_ID = 183

    --ПРОВЕРЯВАМЕ СЛЕД
    SELECT * FROM EMPLOYEES
-------------------------------------------------------------------------------------------
--Пример 4-1.
    --Да се изведат имената, датите на назначаване и заплатите на всички служители.
    SELECT FNAME, LNAME, HIRE_DATE, SALARY
    FROM EMPLOYEES
--------------------------------------------------------------------------------
/*Пример 4-2.
    Да се изведат всички данни за продуктите, с цена по-голяма от 2000. Резултатът
    нека бъде подреден по цена на продукт възходящо.*/

    --НЕКА ВИДИМ ДАЛИ ВСИЧКО ОТ КОЕТО СЕ НУЖДАЕМ Е В PRODUCTS
    SELECT * FROM PRODUCTS

    SELECT *
    FROM PRODUCTS
    WHERE PRICE > 2000
    ORDER BY PRICE ASC  ---DESC              --ПОДРЕДИ/СОРТИРАЙ ПО
 --------------------------------------------------------------------------------
--Пример 4-3.
    --Да се изведе броя на всички служители.
    SELECT COUNT(EMPLOYEE_ID)   --- AS [COUNT EMP] -  ПОСТАВЯ ИМЕ НА КОЛОНАТА
    FROM EMPLOYEES
 --------------------------------------------------------------------------------
--Пример 4-4.
    --Да се изведе броя служители, групирани по отдела, в който работят.

    SELECT COUNT(EMPLOYEE_ID), DEPARTMENT_ID
    FROM EMPLOYEES       ---Column 'DEPARTMENT_ID' is invalid in the select list because it is not contained in either an aggregate function or
                         --- the GROUP BY clause.
    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

 --SELECT ......
 --FROM..........
 --WHERE ...........
 --GROUP BY...........
 --ORDER BY......ASC/DESC

--Задача 4-2.
    --Да се изведат общата сума на заплатите и броя служители в отдел 60.
 SELECT SUM(SALARY)           AS SALARIES,
        COUNT(EMPLOYEE_ID)    AS COUNT_E
 FROM EMPLOYEES
 WHERE DEPARTMENT_ID = 60


--Задача 4-3.
    --За всички поръчки да се изведат идентификатор на поръчка и обща стойност на
    --поръчката. Резултатът да е подреден по стойност на поръчката в низходящ ред.
 SELECT ORDER_ID, SUM(UNIT_PRICE*QUANTITY) AS TOTAL
 FROM ORDER_ITEMS
 GROUP BY ORDER_ID
 ORDER BY 2 DESC


-------------------------------------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 FNAME DESC


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

    SELECT FNAME, LNAME , 'Клиент (' + COUNTRY_ID + ')' AS ID
    FROM CUSTOMERS
    UNION
    SELECT FNAME, LNAME , 'Служител (' + CAST(DEPARTMENT_ID AS VARCHAR) + ')'      -- ТИПОВО ТРЯБВА ДА СА СЪВМЕСТИМИ, ЗАТОВА CAST()
    FROM EMPLOYEES

    --CAST(..... AS VARCHAR)
    --CONVERT(DATE, ......., 105)


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

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


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

    SELECT FNAME FROM CUSTOMERS
    INTERSECT
    SELECT FNAME FROM EMPLOYEES

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

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

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


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

-------------------------------------------------------------------------------------------
------------------------------------ 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  --27

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

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

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

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

SELECT *
FROM COUNTRIES RIGHT JOIN REGIONS
ON COUNTRIES.REGION_ID=REGIONS.REGION_ID --27+1

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


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

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


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

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

 SELECT *
 FROM REGIONS LEFT JOIN COUNTRIES
 ON REGIONS.REGION_ID=COUNTRIES.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


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

Пример 4-15. --EQUI
    Да се изведат държавите и регионите, в които се намират.*/
    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 / EXISTS
    SELECT *
    FROM DEPARTMENTS
    WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)



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

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

--ANTI JOIN
SELECT FNAME, LNAME
FROM CUSTOMERS
WHERE CUSTOMER_ID NOT IN(SELECT CUSTOMER_ID FROM ORDERS)

SELECT FNAME, LNAME
FROM CUSTOMERS
WHERE NOT EXISTS (SELECT * FROM ORDERS WHERE CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID)


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

 --4
 --6
 --4X6

 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 ORDER_ID, O.ORDER_DATE, E.FNAME, E.LNAME
FROM ORDERS O JOIN EMPLOYEES E
ON E.EMPLOYEE_ID=O.EMPLOYEE_ID


--Задача 4-9.
--  Да се изведат имената на всички клиенти и id на поръчките им.
--  В резултатния набор да участват и клиентите, които все още не са правили поръчки.
SELECT C.FNAME, C.LNAME, O.ORDER_ID
FROM CUSTOMERS C LEFT JOIN ORDERS O
ON O.CUSTOMER_ID=C.CUSTOMER_ID


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

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

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

SELECT PRODUCT_ID FROM PRODUCTS
EXCEPT
SELECT PRODUCT_ID FROM ORDER_ITEMS


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

SELECT CU.FNAME, CU.LNAME, CO.NAME, RE.NAME
FROM CUSTOMERS CU, COUNTRIES CO, REGIONS RE
WHERE RE.REGION_ID= CO.REGION_ID
        AND CO.COUNTRY_ID=CU.COUNTRY_ID
        AND RE.NAME = 'Западна Европа'


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

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

 SELECT TOP 7 WITH TIES *
 FROM PRODUCTS
 ORDER BY PRICE ASC


 --ДОМАШНА РАБОТА:
-- Доп.пример:
-- а) Най-поръчваният продукт  е.... (идентификатор)
-- б) Най-поръчваният продукт  е.... (име)

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



-----------------------------------------------------------------------------------------
---------------------------------4.7.2. OFFSET и FETCH ----------------------------------
-----------------------------------------------------------------------------------------

-- Пример 4-20.
-- първите 5 служители, след което вторите 5, сортирани по id.

SELECT EMPLOYEE_ID, FNAME, LNAME, SALARY
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
OFFSET 0 ROWS                     --ПРОПУСНИ БР. РЕДА
FETCH NEXT 5 ROWS ONLY            --ХВАНИ СЛЕДВАЩИТЕ БР. РЕДА

/* Пример 4-21.
   петимата служители, започвайки от 10-ти ред, подредени по дата на постъпване.
   Първата заявка ще покаже всички за демонстрация, втората ще извърши подбора.*/

   --ALL
SELECT EMPLOYEE_ID, FNAME, LNAME, SALARY , HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE
    --ПРОПУСНИ 10 , ХВАНЕМ 5 СЛУЖИТЕЛЯ
SELECT EMPLOYEE_ID, FNAME, LNAME, SALARY , HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

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

/* Задача 4-13.
   Да се изведат име, фамилия и пол на клиентите, направили последните 5 поръчки.*/
   SELECT C.FNAME, C.LNAME, C.GENDER, O.ORDER_DATE
   FROM CUSTOMERS C JOIN ORDERS O
   ON C.CUSTOMER_ID=O.CUSTOMER_ID
   ORDER BY ORDER_DATE DESC
   OFFSET 0 ROWS
   FETCH NEXT 5 ROWS ONLY

-------------------------------------------------------------------------------------
--------------------------------5. Изгледи / Views-----------------------------------
-------------------------------------------------------------------------------------
--  Създаване на изгледи

-- Пример 5-1.
-- Да се създаде изглед, който съдържа

--  име и фамилия на клиентите, както и номер и дата на поръчките, които те са направили. (INNER JOIN)

CREATE VIEW CUSTOMERORDERS
AS
   SELECT C.FNAME + ' ' + C.LNAME AS NAME ,   --ALL COLUMNS HAVE NAME IN VIEWS
          O.ORDER_ID, O.ORDER_DATE
   FROM CUSTOMERS C, ORDERS O
   WHERE O.CUSTOMER_ID=C.CUSTOMER_ID

-- Да се изведат данните от изгледа.
SELECT * FROM CUSTOMERORDERS


-- Пример 5-2.
-- Да се модифицира горният изглед така, че да съдържа и колона с името на
-- съответния служител, обработил поръчката.

-- Пример 5-3.
-- Да се модифицира горния изглед така, че да съдържа само поръчките,
-- обработени от служител с идентификатор = 167.

-- Пример 5-4.
-- Да се създаде изглед, съдържащ име и фамилия на служител и общата сума
-- на поръчките, които той е обработил.

-- Пример 5-5.
-- Да се създаде изглед, който съдържа имена, отдел и заплата на 5-мата
-- служители с най-висока заплата. За да бъдат извлечени служителите,
-- подредени по заплата, очевидно ще трябва да бъдат сортирани по този критерий.



------------------------5.4.Манипулиране на данни чрез изглед -----------------------
-------------------------------------------------------------------------------------
--Следващият изглед CUSTOMERS_COUNTRIES, базиран на JOIN между COUNTRIES и CUSTOMERS,
--ще демонстрира манипулирането на данни.

--Пример 5-6.
--Да се добави нов запис в таблицата CUSTOMERS през изгледа CUSTOMERS_COUNTRIES.

--5.4.2. Променяне на данни през изглед
---Пример 5-7.
--Да се промени фамилията на клиент с идентификатор 10.

--5.4.3. Изтриване на данни през изглед
--Пример 5-8.
--Да се изтрие клиент с идентификатор 10.


--5.5.Задачи
--Задача 5-1.
--Да се създаде изглед, който съдържа имената на продуктите и общо поръчано
--количество от продукт.

--Задача 5-2.
--Да се създаде изглед, който съдържа десетимата клиенти с най-голям брой
--поръчки. Ако последният клиент има равен брой поръчки с други клиенти, те също да участват в изгледа.