2.6.R

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

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

SELECT CU.FNAME,
       CU.LNAME,
       CO.NAME AS [COUNTRY NAME] ,
       RE.NAME AS [REGION 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 ='Западна Европа'

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

SELECT * FROM EMPLOYEES
SELECT * FROM ORDERS

--НАЧИН 1
--OUTER JOIN
SELECT E.FNAME, E.LNAME, E.SALARY, E.JOB_ID, E.DEPARTMENT_ID , O.ORDER_ID
FROM EMPLOYEES E LEFT JOIN ORDERS O
ON E.EMPLOYEE_ID=O.EMPLOYEE_ID
WHERE E.DEPARTMENT_ID=80
AND O.ORDER_ID IS NULL

--НАЧИН 2
--HAVING
SELECT E.FNAME,
       E.LNAME,
       E.SALARY,
       E.JOB_ID,
       E.DEPARTMENT_ID,
       COUNT(O.ORDER_ID) AS BROI
FROM EMPLOYEES E LEFT JOIN ORDERS O
ON E.EMPLOYEE_ID=O.EMPLOYEE_ID
WHERE E.DEPARTMENT_ID=80
GROUP BY E.FNAME,  E.LNAME,  E.SALARY,   E.JOB_ID,  E.DEPARTMENT_ID
HAVING   COUNT(O.ORDER_ID) = 0

--НАЧИН 3
SELECT E.FNAME, E.LNAME, E.SALARY, E.JOB_ID, E.DEPARTMENT_ID
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID= 80
AND
NOT EXISTS (SELECT * FROM ORDERS O WHERE E.EMPLOYEE_ID=O.EMPLOYEE_ID)

--------------------------------------------------------------------------------------
-----------------------------------TOP -----------------------------------------------
--------------------------------------------------------------------------------------
-- TOP връща първите N реда в неопределен ред, което налага използване на ORDER BY,
--за да е в желан ред.

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


-- за да присъстват и продуктите с цена, равна на последната изведена,
-- трябва да се използва TOP с WITH TIES
SELECT TOP 7 WITH TIES *
FROM PRODUCTS
ORDER BY PRICE


-- Доп.пример:
-- a) Идентификаторът на най-поръчваният продукт  е ?
SELECT TOP (1) PRODUCT_ID,
               COUNT(PRODUCT_ID)
 FROM ORDER_ITEMS
 GROUP BY PRODUCT_ID



---b) Името на най-поръчваният продукт  е ?
SELECT * FROM ORDER_ITEMS
SELECT * FROM PRODUCTS

SELECT TOP (1) P.NAME, P.PRODUCT_ID, COUNT(OI.PRODUCT_ID)
FROM PRODUCTS P JOIN ORDER_ITEMS OI
ON P.PRODUCT_ID=OI.PRODUCT_ID
GROUP BY P.NAME, P.PRODUCT_ID
ORDER BY 3 DESC


-----------------------------------------------------------------------------------------
---------------------------------4.7.2. OFFSET и FETCH ----------------------------------
-----------------------------------------------------------------------------------------
/*
   Пример 4-21.
   петимата служители, започвайки от 10-ти ред, подредени по дата на постъпване.

*/

--   Първата заявка ще покаже всички за демонстрация,
SELECT EMPLOYEE_ID, FNAME, LNAME, SALARY, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC

---втората ще извърши подбора.
SELECT EMPLOYEE_ID, FNAME, LNAME, SALARY, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC
OFFSET 9 ROWS
FETCH NEXT 5 ROWS ONLY


/* 4.7.3. Задачи

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

/*
    Задача 4-13.
   Да се изведат име, фамилия и пол на клиентите, направили последните 5 поръчки.

*/
SELECT * FROM CUSTOMERS
SELECT * FROM ORDERS

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


--------------------------------------------------------------------------
-----------------------------Изгледи = Views------------------------------
--------------------------------------------------------------------------

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

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

SELECT * FROM CUSTOMERS
SELECT * FROM ORDERS


CREATE VIEW CUSTOMERORDERS
AS
SELECT C.FNAME + ' ' + C.LNAME AS [CUSTOMER NAME],
       O.ORDER_ID,
       O.ORDER_DATE
FROM ORDERS O, CUSTOMERS C
WHERE O.CUSTOMER_ID=C.CUSTOMER_ID


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

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

ALTER VIEW CUSTOMERORDERS
AS
SELECT C.FNAME + ' ' + C.LNAME AS [CUSTOMER NAME],
       O.ORDER_ID,
       O.ORDER_DATE,
       E.FNAME + ' ' + E.LNAME AS [EMPLOYEE NAME]
FROM ORDERS O, CUSTOMERS C, EMPLOYEES E
WHERE O.CUSTOMER_ID=C.CUSTOMER_ID
  AND E.EMPLOYEE_ID=O.EMPLOYEE_ID

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


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


ALTER VIEW CUSTOMERORDERS
AS
SELECT C.FNAME + ' ' + C.LNAME AS [CUSTOMER NAME],
       O.ORDER_ID,
       O.ORDER_DATE,
       E.FNAME + ' ' + E.LNAME AS [EMPLOYEE NAME]
FROM ORDERS O, CUSTOMERS C, EMPLOYEES E
WHERE O.CUSTOMER_ID=C.CUSTOMER_ID
  AND E.EMPLOYEE_ID=O.EMPLOYEE_ID
  AND E.EMPLOYEE_ID = 167

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



------------------------------------------------------------------------------------
--ДОМАШНА РАБОТА:
--Пример 5-4.
--Да се създаде изглед, съдържащ име и фамилия на служител и общата сума на
--поръчките, които той е обработил.