Advertisement
S_Madanska

9.6.R

Jun 9th, 2021
468
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -------------------------------------------------------------------------------------
  2. ----------------------5.4.Създаване на изглед ---------------------------------------
  3. -------------------------------------------------------------------------------------
  4. -------------------------------------------------------------------------------------
  5. --Дoмашна работа:--------------------------------------------------------------------
  6. --Пример 5-4.
  7. --Да се създаде изглед, съдържащ име и фамилия на служител и общата сума на
  8. --поръчките, които той е обработил.
  9. SELECT * FROM ORDER_ITEMS
  10. SELECT * FROM ORDERS
  11. SELECT * FROM EMPLOYEES
  12.  
  13.  
  14. ---INNER JOIN:
  15. ---SELECT * FROM ..... JOIN ....... ON    ............
  16. ---SELECT * FROM .....,     ....... WHERE ............
  17.  
  18. CREATE VIEW EMPLOYEEORDERS
  19. AS
  20. SELECT E.FNAME, E.LNAME, SUM(OI.UNIT_PRICE*OI.QUANTITY) AS ORDERS_TOTAL
  21. FROM EMPLOYEES E , ORDERS O, ORDER_ITEMS OI
  22. WHERE E.EMPLOYEE_ID=O.EMPLOYEE_ID
  23.   AND O.ORDER_ID=OI.ORDER_ID
  24. GROUP BY E.FNAME, E.LNAME
  25.  
  26. SELECT * FROM EMPLOYEEORDERS
  27. ORDER BY ORDERS_TOTAL DESC
  28.  
  29. -------------------------------------------------------------------------------------
  30.  
  31. --Пример 5-5.
  32. --Да се създаде изглед, който съдържа имена, отдел и заплата на 5-мата
  33. --служители с най-висока заплата. За да бъдат извлечени служителите,
  34. --подредени по заплата, очевидно ще трябва да бъдат сортирани по този критерий.
  35.  
  36. SELECT * FROM EMPLOYEES
  37. SELECT * FROM DEPARTMENTS
  38.  
  39. CREATE VIEW EMP_TOP_SALARIES
  40. AS
  41. SELECT TOP 5 E.FNAME,
  42.              E.LNAME,
  43.              D.NAME AS DEPT,
  44.              E.SALARY
  45. FROM EMPLOYEES E, DEPARTMENTS D
  46. WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
  47. ORDER BY SALARY DESC
  48.  
  49. SELECT * FROM EMP_TOP_SALARIES
  50.  
  51. -------------------------------------------------------------------------------------
  52. ----------------------5.4.Манипулиране на данни чрез изглед -------------------------
  53. -------------------------------------------------------------------------------------
  54.  
  55. --Следващият изглед, базиран на JOIN между таблиците COUNTRIES и CUSTOMERS, ще
  56. --демонстрира манипулирането на данни:
  57.  
  58. CREATE VIEW CUSTOMERS_COUNTRIES
  59. AS
  60.  SELECT C.COUNTRY_ID,
  61.         C.NAME,
  62.         C.REGION_ID,
  63.         M.CUSTOMER_ID,
  64.         M.FNAME,
  65.         M.GENDER,
  66.         M.LNAME,
  67.         M.COUNTRY_ID AS CUST_COUNTRY_ID,
  68.         M.EMAIL
  69. FROM COUNTRIES C JOIN CUSTOMERS M
  70. ON C.COUNTRY_ID=M.COUNTRY_ID
  71.  
  72.  
  73. --Пример 5-6.
  74. --Да се добави нов запис в таблицата CUSTOMERS през изгледа CUSTOMERS_COUNTRIES.
  75.  
  76. INSERT INTO CUSTOMERS_COUNTRIES(CUSTOMER_ID, FNAME, LNAME, CUST_COUNTRY_ID)
  77. VALUES(10, 'Иван', 'Петров', 'BG')
  78.  
  79. SELECT * FROM CUSTOMERS_COUNTRIES
  80. SELECT * FROM CUSTOMERS
  81.  
  82. --5.4.2. Променяне на данни през изглед
  83. ---Пример 5-7.
  84. -- Да се промени фамилията на клиент с идентификатор 10.
  85.  
  86. UPDATE CUSTOMERS_COUNTRIES
  87. SET LNAME='Колев'
  88. WHERE CUSTOMER_ID=10
  89.  
  90.  
  91. SELECT * FROM CUSTOMERS_COUNTRIES
  92. SELECT * FROM CUSTOMERS
  93.  
  94. --5.4.3. Изтриване на данни през изглед  
  95. --Пример 5-8.
  96. --Да се изтрие клиент с идентификатор 10.
  97.  
  98. DELETE FROM CUSTOMERS_COUNTRIES
  99. WHERE CUSTOMER_ID=10
  100.  
  101. -------------------------------------------------------------------------------------
  102. --5.5.Задачи
  103.  
  104. --Задача 5-1.
  105. --Да се създаде изглед, който съдържа имената на продуктите и общо поръчано
  106. --количество от продукт.
  107.  
  108. CREATE VIEW PRODUCTS_QUANTITY
  109. AS
  110. SELECT P.NAME, SUM(OI.QUANTITY) AS OBSHTO_KOL
  111. FROM PRODUCTS P JOIN ORDER_ITEMS OI
  112. ON P.PRODUCT_ID=OI.PRODUCT_ID
  113. GROUP BY P.NAME
  114.  
  115. --Задача 5-2.
  116. --Да се създаде изглед, който съдържа десетимата клиенти с най-голям брой
  117. --поръчки. Ако последният клиент има равен брой поръчки с други клиенти, те също да участват в изгледа.
  118.  
  119. CREATE VIEW CUST_ORDER_NUM_TOP_WITH_TIES
  120. AS
  121. SELECT TOP 10 WITH TIES C.FNAME + ' ' + C.LNAME AS CUSTOMER,
  122.                         COUNT(O.ORDER_ID)       AS ORDER_NUMBER
  123. FROM CUSTOMERS C JOIN ORDERS O
  124. ON C.CUSTOMER_ID=O.CUSTOMER_ID
  125. GROUP BY  C.FNAME + ' ' + C.LNAME , C.CUSTOMER_ID
  126. ORDER BY ORDER_NUMBER DESC
  127.  
  128.  
  129. SELECT * FROM CUST_ORDER_NUM_TOP_WITH_TIES
  130.  
  131. ------------------------------------------------------------------------------------------
  132. -- 1. ЗАДАЧА:
  133. -- създай изглед с всичко от таблицата за служителите,
  134. -- но само тези, които работят в отдел 100
  135.  
  136. CREATE VIEW EMPLOYEES_EXAMPLE_1
  137. AS
  138. SELECT *
  139. FROM EMPLOYEES
  140. WHERE DEPARTMENT_ID=100
  141.  
  142.  
  143. SELECT * FROM EMPLOYEES_EXAMPLE_1
  144.  
  145. ------------------------------------------------------------------------------------------
  146. -- 2. ЗАДАЧА:
  147. -- промени изгледа - конкатенирай в една колона име и фамилия на служителите
  148. -- , а от останалите колони покажи само идентификатор на длъжност,
  149. -- година на наемане, заплата и идентификатор на мениджър.
  150.  
  151.  
  152. ALTER VIEW EMPLOYEES_EXAMPLE_1
  153. AS
  154. SELECT  FNAME + ' ' + LNAME AS [FIRST, LAST NAME]  , JOB_ID, HIRE_DATE, SALARY, MANAGER_ID
  155. FROM EMPLOYEES
  156. WHERE DEPARTMENT_ID=100
  157.  
  158. SELECT * FROM EMPLOYEES_EXAMPLE_1
  159.  
  160. -------------------------------------------------------------------------------------------
  161. -- 3. ЗАДАЧА:
  162. -- създай изглед върху изгледа от 2. ЗАДАЧА, като в резултатния набор
  163. -- има само следните колони: имена и идентификатор на мениджър
  164.  
  165. CREATE VIEW VIEW_from_VIEW
  166. AS
  167. SELECT [FIRST, LAST NAME], MANAGER_ID
  168. FROM EMPLOYEES_EXAMPLE_1
  169.  
  170. SELECT * FROM VIEW_from_VIEW
  171.  
  172. -------------------------------------------------------------------------------------------
  173. -- 4. ЗАДАЧА:
  174. -- създай изглед с имената и броя на държавите, които са в регион "Азия"
  175.  
  176. CREATE VIEW NAME_COUNT_COUNTRIES_AND_REGIONS_ASIA
  177. AS
  178. SELECT  COUNT(C.COUNTRY_ID) AS [BROI DARJAVI], C.NAME
  179. FROM COUNTRIES C, REGIONS R
  180. WHERE C.REGION_ID = R.REGION_ID
  181.       AND R.NAME='Азия'
  182. GROUP BY C.NAME
  183.  
  184. SELECT * FROM NAME_COUNT_COUNTRIES_AND_REGIONS_ASIA
  185.  
  186.  
  187.  
  188. ------------------------------------------------------------------------------------------
  189. ---------------------------------------------------------------------------
  190. -------------------------------- ТРАНЗАКЦИИ -------------------------------
  191. ---------------------------------------------------------------------------
  192. --6.4.Примери
  193.  
  194. --Пример 6-1.
  195. --транзакция, която добавя нов клиент и създава поръчка за него, включваща два продукта.
  196.  
  197. BEGIN TRAN
  198.  
  199. ---OPERATION 1 -- добавя нов клиент ---INSERT INTO CUSTOMERS
  200.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
  201.     VALUES (1001, 'BG', 'Иван', 'Николов', 'БУЛ.БЪЛГАРИЯ 236, ПЛОВДИВ', 'ivan@abv.bg', 'M')
  202.  
  203. ---OPERATION 2 -- създава поръчка за него ----INSERT INTO ORDERS
  204.     INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS, ORDER_DATE)
  205.     VALUES(1, 1001, 107, 'БУЛ.МАКЕДОНИЯ 12, ПЛОВДИВ', GETDATE())
  206.  
  207. ---OPERATION 3 -- включваща два продукта.  ---INSERT INTO ORDER_ITEMS...
  208.     INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  209.     VALUES(1, 1726, 99, 1)
  210.  
  211. ---OPERATION 4 -- включваща два продукта.  ---INSERT INTO ORDER_ITEMS...
  212.     INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  213.     VALUES(1, 1782, 600, 1)
  214. COMMIT TRAN
  215.  
  216. SELECT * FROM CUSTOMERS
  217. WHERE CUSTOMER_ID =1001
  218.  
  219. SELECT * FROM ORDER_ITEMS
  220. WHERE ORDER_ID=1
  221.  
  222.  
  223. /*
  224. Пример 6-2.
  225. транзакция, която променя фамилията на клиент с идентификатор = 1001,
  226. след което отхвърля направените промени.
  227. */
  228. BEGIN TRAN
  229.     --OPERATION 1 SELECT  --фамилията на клиент ПРЕДИ ПРОМЯНА
  230.     SELECT LNAME
  231.     FROM CUSTOMERS
  232.     WHERE CUSTOMER_ID=1001
  233.  
  234.     --OPERATION 2 UPDATE --ПРОМЯНА
  235.     UPDATE CUSTOMERS
  236.     SET LNAME= 'ИВАНОВ'
  237.     WHERE CUSTOMER_ID=1001
  238.  
  239.     --OPERATION 3 SELECT  ----фамилията на клиент СЛЕД ПРОМЯНА
  240.     SELECT LNAME
  241.     FROM CUSTOMERS
  242.     WHERE CUSTOMER_ID=1001
  243.  
  244. --отхвърля направените промени
  245. ROLLBACK TRAN
  246.  
  247.     --OPERATION 4 SELECT ----фамилията на клиент СЛЕД ROLLBACK TRAN
  248.     SELECT LNAME
  249.     FROM CUSTOMERS
  250.     WHERE CUSTOMER_ID=1001
  251.  
  252.  
  253. /*
  254. Пример 6-3.
  255. транзакция, която въвежда нов клиент, поставя точка на запис,
  256. въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
  257. отхвърля се само поръчката.
  258. */
  259. BEGIN TRAN
  260.     --въвежда нов клиент
  261.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, GENDER)
  262.     VALUES (1002, 'BG', 'ПЕТЪР','ВАСИЛЕВ', 'M')
  263.  
  264. SAVE TRAN POINT1
  265.     --въвежда поръчка
  266.     INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
  267.     VALUES(2, 1002, 110, GETDATE())
  268.  
  269. ROLLBACK TRAN  POINT1
  270. COMMIT TRAN
  271.  
  272. SELECT * FROM CUSTOMERS
  273. WHERE CUSTOMER_ID=1002
  274.  
  275. SELECT * FROM ORDERS
  276. WHERE ORDER_ID=2
  277.  
  278.  
  279. ---6.5.Задачи
  280.  
  281. -- Задача 6-1.
  282. -- Транзакция, която има за цел да изтрие отдел „Мениджмънт“,
  283. -- като преди това прехвърли всички служители от него в отдел „Администрация“.
  284.  
  285. SELECT * FROM DEPARTMENTS
  286. -- 10 -Администрация
  287. -- 90 -Мениджмънт
  288.  
  289. SELECT *
  290. FROM DEPARTMENTS D JOIN EMPLOYEES E
  291. ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
  292. WHERE E.DEPARTMENT_ID=90
  293.  
  294.  
  295. BEGIN TRAN
  296.     -- служители от отдел „Мениджмънт“ в отдел „Администрация“
  297.     UPDATE EMPLOYEES
  298.     SET DEPARTMENT_ID=10
  299.     WHERE DEPARTMENT_ID=90
  300.  
  301.     --да изтрие отдел „Мениджмънт“
  302.     DELETE DEPARTMENTS
  303.     WHERE DEPARTMENT_ID =90
  304. COMMIT TRAN
  305.  
  306. SELECT *
  307. FROM DEPARTMENTS D JOIN EMPLOYEES E
  308. ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
  309. WHERE E.DEPARTMENT_ID=10
  310.  
  311.  
  312. SELECT * FROM DEPARTMENTS
  313.  
  314. --Задача 6-2.
  315. --Транзакция, която изтрива продукт 1726 -първо го изтрива от всички поръчки
  316. --после от таблицата с продукти, и накрая отхвърля направените промени.
  317.  
  318. SELECT * FROM ORDER_ITEMS
  319. WHERE PRODUCT_ID=1726
  320.  
  321. SELECT * FROM PRODUCTS
  322. WHERE PRODUCT_ID=1726
  323.  
  324.  
  325. SELECT *
  326. FROM ORDER_ITEMS OI FULL JOIN PRODUCTS P
  327. ON P.PRODUCT_ID=OI.PRODUCT_ID
  328. WHERE P.PRODUCT_ID=1726
  329.  
  330. BEGIN TRAN
  331.     --1726 -първо го изтрива от всички поръчки
  332.     DELETE FROM ORDER_ITEMS
  333.     WHERE PRODUCT_ID=1726
  334.  
  335.     --1726 -после от таблицата с продукти
  336.     DELETE FROM PRODUCTS
  337.     WHERE PRODUCT_ID=1726
  338. ROLLBACK TRAN
  339.  
  340.  
  341. ---------------------------------------------------------------------------
  342. ---------------------------- Процедури ------------------------------------
  343. ---------------------------------------------------------------------------
  344. --Пример 7-2.
  345. --Да се създаде процедура, която за подадена като входен параметър идентификатор НА поръчка
  346. --извежда имена на служител, който я е обработил, както и общата й стойност.
  347.  
  348. CREATE PROC EMPLOYEEES_ORDERS @ORDER INT
  349. AS
  350. SELECT FNAME, LNAME, O.ORDER_ID, SUM(OI.UNIT_PRICE*OI.QUANTITY) AS TOTAL
  351. FROM EMPLOYEES E JOIN ORDERS O ON E.EMPLOYEE_ID= O.EMPLOYEE_ID
  352.                  JOIN ORDER_ITEMS OI ON OI.ORDER_ID=O.ORDER_ID
  353. WHERE O.ORDER_ID=2354
  354. GROUP BY FNAME, LNAME, O.ORDER_ID
  355.  
  356.  
  357. EXECUTE EMPLOYEEES_ORDERS 2354
  358.  
  359.  
  360. ---------------------------------------------------------------------------
  361. ---------------------------- Функции --------------------------------------
  362. -----1.--скаларни ---------------------------------------------------------
  363. -----2.-- Функции, връщащи резултатен набор--------------------------------
  364.  
  365. --Пример 7-5.
  366. --Да се създаде функция, връщаща като резултат служителите с техните длъжности.
  367.  
  368. CREATE FUNCTION EMPLOYEE_jobs () RETURNS TABLE
  369. AS
  370. RETURN
  371.     SELECT FNAME, LNAME, JOB_TITLE
  372.     FROM EMPLOYEES E JOIN JOBS J
  373.     ON E.JOB_ID= J.JOB_ID
  374.  
  375.     SELECT * FROM DBO.EMPLOYEE_jobs ()
  376.     ORDER BY FNAME, LNAME
  377.  
  378. ---------------------------------------------------------------------------
  379. ----------------------------- Тригери -------------------------------------
  380. ---------------------------------------------------------------------------
  381. --Задача 9-1.
  382. --Да се създаде тригер, който при всяка промяна на фамилия на клиент
  383. --записва ред в нова таблица CUSTOMERS_HIST с атрибути:
  384. --• идентификатор на клиент;
  385. --• стара фамилия;
  386. --• нова фамилия.
  387.  
  388. CREATE TABLE CUSTOMERS_HISTORY
  389. (
  390.     CUSTOMER_ID INT,
  391.     OLD_NAME VARCHAR(20),
  392.     NEW_NAME VARCHAR(20)
  393. )
  394.  
  395. CREATE TRIGGER TGR_CUSTOMERS_HIST
  396. ON CUSTOMERS
  397. FOR UPDATE
  398. AS
  399.     IF UPDATE(LNAME)
  400.     BEGIN
  401.     INSERT INTO CUSTOMERS_HISTORY(CUSTOMER_ID, OLD_NAME, NEW_NAME)
  402.  
  403.     SELECT I.CUSTOMER_ID, D.LNAME, I.LNAME
  404.     FROM inserted I, deleted D
  405.     WHERE I.CUSTOMER_ID=D.CUSTOMER_ID
  406.     END
  407.  
  408.  
  409. UPDATE CUSTOMERS
  410. SET LNAME = LNAME + '-' + 'КОЛЕВА'
  411. WHERE CUSTOMER_ID IN (103, 111, 114)
  412.  
  413. SELECT * FROM CUSTOMERS
  414. SELECT * FROM CUSTOMERS_HISTORY
Advertisement
RAW Paste Data Copied
Advertisement