решения ден 2 зад

---------------------------
---------inserts-----------
---------------------------
use TRADECOMPANY

insert into REGIONS (NAME)
values ('Източна Европа')

select * from CUSTOMERS

insert into COUNTRIES (COUNTRY_ID, name, REGION_ID)
values('BG', 'БЪЛГАРИЯ', 1)

INSERT INTO CUSTOMERS (CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
VALUES (1, 'BG', 'ПЕТЪР', 'МАРИНОВ', 'УЛ.РОДОПИ ГР.ПЛД','[email protected]', DEFAULT)

INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES ('TP', 'ТЪРГОВСКИ ПРЕДСТАВИТЕЛ', 2000, 5000)

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, NAME, MANAGER_ID, COUNTRY_ID, CITY,
STATE, ADDRESS, POSTAL_CODE)
VALUES (1, 'ПРОДАЖБИ', NULL, 'BG', 'ПЛД', 'ПЛД','УЛ.РОДОПИ ПЛД', 4000 )

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FNAME, LNAME, EMAIL,
PHONE, HIRE_DATE,SALARY, JOB_ID, DEPARTMENT_ID)
VALUES (1, 'МАРИЯ', 'ИЛИЕВА', '[email protected]', '0986324667', CONVERT(DATE , '21-09-2022', 105), 3000, 'TP', 1)

INSERT INTO PRODUCTS (PRODUCT_ID, NAME, PRICE, DESCR )
VALUES(1, 'FLASH', 20, 'ADATA')

INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
VALUES (100, GETDATE(), 1, 1, 'УЛ.РОДОПИ ПЛД')

INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
VALUES(100, 1, 20, 2)

--Демо:
--Промяна на заплатата на служител с даден идентификатор:
 UPDATE EMPLOYEES
 SET SALARY = 4000
 WHERE EMPLOYEE_ID = 1

 SELECT * FROM EMPLOYEES

--Демо:
--Поръчката е анулирана -  изтрий реда ѝ  от таблицата ORDERS:
 DELETE FROM ORDERS     --- ПОРАДИ --ON DELETE CASCADE ON UPDATE CASCADE /БЕЗ НЕГО ЩЕШЕ ДА Е НУЖНО ДА СЕ ИЗТРИЕ ПЪРВО ОТ ORDER_ITEMS ПОСЛЕ ОТ ORDERS
 WHERE ORDER_ID = 100


 SELECT * FROM ORDER_ITEMS

/*Задача 3-1.
Да се изтрият всички редове от всички таблици в базата от данни, след което да
се въведат данните чрез командите от inserts_mssql.sql файла. */
 DELETE FROM ORDERS
 DELETE FROM PRODUCTS
 DELETE FROM COUNTRIES
 DELETE FROM EMPLOYEES
 DELETE FROM JOBS
 DELETE FROM DEPARTMENTS
 DELETE FROM CUSTOMERS
 DELETE FROM COUNTRIES
 DELETE FROM REGIONS

 SELECT * FROM EMPLOYEES

/*Задача 3-2.
Да се увеличи количеството с 2 броя
и да се намали единичната цена с 5%
на продукт с идентификатор 2254
в поръчка с идентификатор 2354.*/
SELECT *
FROM ORDER_ITEMS
WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354

UPDATE ORDER_ITEMS
SET QUANTITY +=2,  UNIT_PRICE *= 0.95
WHERE PRODUCT_ID = 2254 AND ORDER_ID = 2354

--Задача 3-3.
--Да се изтрие служител с идентификатор 183.
 DELETE FROM EMPLOYEES
 WHERE EMPLOYEE_ID = 183
-------------------------------------------------------------------------------------------
--Пример 4-1.
--Да се изведат имената, датите на назначаване и заплатите на всички служители.
SELECT FNAME, LNAME, HIRE_DATE, SALARY
FROM EMPLOYEES

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

--Пример 4-3.
--Да се изведе броя на всички служители.
SELECT COUNT(EMPLOYEE_ID) AS [COUNT EMP] -- []
FROM EMPLOYEES

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

--АКО ИСКАМЕ ОТДЕЛЪТ ДА СЕ ИЗВЕДЕ ВМЕСТО С ИДЕНТИФИКАТОР, С ИМЕ:
--SELECT COUNT(E.EMPLOYEE_ID), D.NAME
--FROM DEPARTMENTS D FULL JOIN EMPLOYEES E
--ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
--GROUP BY D.DEPARTMENT_ID,  D.NAME

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

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

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

 -------------------------------------
 ---ПРИМЕРНА ЗАДАЧА:
 ---ИЗВЕДИ КЛИЕНТИ,
 ---КОИТО СА НА АДРЕС В СОФИЯ
 ---/ИЗПОЛЗВАЙ WILDCARDS:
 --https://pastebin.com/TGnyW70m/
SELECT * FROM Customers
WHERE ADDRESS LIKE '%, София%'
--------------------------------------


-------------------------------------------------------------------------------------------
-------------------------------------Set operators-----------------------------------------
--Условия:
--Броят на колоните във всички заявки трябва да бъде еднакъв;
--Колоните трябва да бъдат от съвместими типове от данни.

---------------------------------------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-4.
Да се изведат всички малки имена на клиенти и служители с евентуалните
повторения, сортирани в низходящ ред по име. */
 SELECT FNAME FROM CUSTOMERS
 UNION ALL
 SELECT FNAME FROM EMPLOYEES
 ORDER BY FNAME DESC

----------------------------------INTERSECT(сечение)---------------------------------------
/*Резултатът съдържа общите за двата резултатни набора редове, без дубликати.

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

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

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

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

/*Задача 4-7.
Да се изведат собствени имена на клиенти, които не се срещат сред тези на служители.*/
SELECT FNAME FROM CUSTOMERS
EXCEPT
SELECT FNAME FROM EMPLOYEES
-------------------------------------------------------------------------------------------
------------------------------------ JOIN -------------------------------------------------
--JOIN се използва за извличане на данни от две или повече таблици,
--като редовете им се
--комбинират чрез логическа връзка между таблиците,
--която може да бъде във FROM или WHERE.
--Обикновено тази връзка е първичен/външен ключ, но не задължително.


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

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

SELECT *
FROM REGIONS R  FULL JOIN COUNTRIES C
ON R.REGION_ID = C.REGION_ID
-------------------------------------------------------------------------------------
--1. [INNER] JOIN
                                ---- SYNTAX:     --T1 JOIN T2 ON    ........
                                --           /--OR/
                                --               --T1 ,    T2 WHERE ........
-------------------------------------------------------------------------------------
--2. [OUTER] JOIN
     --2.1 LEFT [OUTER] JOIN
     --2.2 RIGHT[OUTER] JOIN
     --2.3 FULL [OUTER] JOIN

                                ---- SYNTAX:     --T1 LEFT  JOIN T2 ON .......
                                            -- /--OR/
                                                 --T1 RIGHT JOIN T2 ON .......
                                            -- /--OR/
                                                 --T1 FULL  JOIN T2 ON .......
-------------------------------------------------------------------------------------
--3. CROSS JOIN
                                ---- SYNTAX:
                                                 --T1 CROSS JOIN T2
                                            -- /--OR/
                                                 --T1 , T2
 -------------------------------------------------------------------------------------
--Пример 4-11.
--  Изведи имена на клиенти, имена на държавите от които са,
--и имена на регионите на държавите.
SELECT CU.FNAME, CU.LNAME, CO.NAME
FROM CUSTOMERS CU JOIN COUNTRIES CO ON CU.COUNTRY_ID = CO.COUNTRY_ID
                  JOIN REGIONS R ON R.REGION_ID = CO.REGION_ID

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

 --INNER JOIN
 --T1 JOIN T2 ON ........
 --T1 , T2 WHERE ........

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

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

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

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

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

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

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

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

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

 --CROSS JOIN
 --SELECT ... FROM T1 CROSS JOIN T2
 --SELECT ... FROM T1 , T2

--НАЧИН 1
SELECT * FROM REGIONS R, COUNTRIES C
---WHERE R.REGION_ID = C.REGION_ID

--НАЧИН 2:
SELECT * FROM REGIONS R CROSS JOIN COUNTRIES C

--Задача 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 на поръчките им.
--  В резултатния набор да участват и клиентите, които все още не са правили поръчки.
 SELECT C.FNAME, C.LNAME, O.ORDER_ID
 FROM CUSTOMERS C LEFT JOIN ORDERS O
 ON C.CUSTOMER_ID = O.CUSTOMER_ID

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

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

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

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

-- + SIMILAR PRICE TO THE 7-TH
SELECT TOP 7 WITH TIES *
FROM PRODUCTS
ORDER BY PRICE

--Задача 01
--Изведи имента на клиентите, които са от 'DE', но не са правили поръчки до момента

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

/* Пример 4-21.
петимата служители, започвайки от 10-ти ред, подредени по дата на постъпване.
Първата заявка ще покаже всички за демонстрация, втората ще извърши подбора.*/
 SELECT * FROM EMPLOYEES
 ORDER BY HIRE_DATE
 OFFSET 9 ROWS
 FETCH NEXT 5 ROWS ONLY

/*Задача 4-12.
вторите 10 най-добре платени служители (подредени по заплата низходящо).*/
 SELECT * 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 O.ORDER_DATE DESC
 OFFSET 0 ROWS
 FETCH NEXT 5 ROWS ONLY

 SELECT TOP 5 C.FNAME, C.LNAME, C.GENDER, O.ORDER_DATE
 FROM CUSTOMERS C JOIN ORDERS O
 ON C.CUSTOMER_ID = O.CUSTOMER_ID
 ORDER BY O.ORDER_DATE DESC

----------------------------------------------------------------------------------------
-----------------------------Изгледи = Views--------------------------------------------
-----------------------------Създаване на изгледи---------------------------------------

--Пример 5-1.
--Да се създаде изглед, който съдържа име и фамилия на клиентите, както и
--номер и дата на поръчките, които те са направили.
CREATE VIEW MY_FIST_VIEW
AS
SELECT C.FNAME, C.LNAME, O.ORDER_ID, O.ORDER_DATE
FROM CUSTOMERS C JOIN ORDERS O
ON C.CUSTOMER_ID = O.CUSTOMER_ID

SELECT * FROM MY_FIST_VIEW

-----------------------------Промяна на изгледи-----------------------------------------
--Пример 5-2.
--Да се модифицира горният изглед така, че да съдържа и колона с името на
--съответния служител, обработил поръчката.
ALTER VIEW MY_FIST_VIEW
AS
SELECT C.FNAME + ' ' + C.LNAME AS CUSTOMER,
       E.FNAME + ' ' + E.LNAME AS EMPLOYEE,
       O.ORDER_ID, O.ORDER_DATE
FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
                 JOIN EMPLOYEES E ON E.EMPLOYEE_ID = O.EMPLOYEE_ID

SELECT * FROM MY_FIST_VIEW