Advertisement
S_Madanska

20.02 MASTERS CONDITIONS

Feb 19th, 2021 (edited)
163
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.          -- ПРИМПОМНЯНЕ НА INNER JOIN
  2.    
  3.         ----table1 , table2
  4.         ----where ..........
  5.  
  6.         ----table1 join table2
  7.         ----on.........
  8.  
  9. -------------------------------------------------------------------------------------
  10. --------------------------------5. Изгледи / Views-----------------------------------
  11. -------------------------------------------------------------------------------------
  12. --  Създаване на изгледи
  13.  
  14. -- Пример 5-1.
  15. -- Да се създаде изглед, който съдържа
  16. ----име и фамилия на клиентите, както и номер и дата на поръчките, които те са направили.
  17.  
  18. CREATE VIEW CUSTOMERORDERS
  19. AS
  20.    SELECT C.FNAME + ' ' + C.LNAME AS NAME ,   --ALL COLUMNS HAVE NAME IN VIEWS
  21.           O.ORDER_ID, O.ORDER_DATE
  22.    FROM CUSTOMERS C, ORDERS O
  23.    WHERE O.CUSTOMER_ID=C.CUSTOMER_ID
  24.  
  25. -- Да се изведат данните от изгледа.
  26. SELECT * FROM CUSTOMERORDERS
  27.  
  28. -- Пример 5-2.
  29. -- Да се модифицира горният изглед така, че да съдържа и колона с името на
  30. -- съответния служител, обработил поръчката.
  31. ALTER VIEW CUSTOMERORDERS
  32. AS
  33.    SELECT C.FNAME + ' ' + C.LNAME AS NAME_CUST ,   --ALL COLUMNS HAVE NAME IN VIEWS
  34.           O.ORDER_ID,
  35.           O.ORDER_DATE,
  36.           E.FNAME + ' ' + E.LNAME AS NAME_EMPLO
  37.    FROM CUSTOMERS C, ORDERS O, EMPLOYEES E
  38.    WHERE O.CUSTOMER_ID=C.CUSTOMER_ID AND E.EMPLOYEE_ID=O.EMPLOYEE_ID
  39.  
  40. -- Да се изведат данните от изгледа.
  41. SELECT * FROM CUSTOMERORDERS
  42.  
  43. -- Пример 5-3.
  44. -- Да се модифицира горния изглед така, че да съдържа само поръчките,
  45. -- обработени от служител с идентификатор = 167.
  46. ALTER VIEW CUSTOMERORDERS
  47. AS
  48.    SELECT C.FNAME + ' ' + C.LNAME AS NAME_CUST ,   --ALL COLUMNS HAVE NAME IN VIEWS
  49.           O.ORDER_ID,
  50.           O.ORDER_DATE, e.EMPLOYEE_ID,
  51.           E.FNAME + ' ' + E.LNAME AS NAME_EMPLO
  52.    FROM CUSTOMERS C, ORDERS O, EMPLOYEES E
  53.    WHERE O.CUSTOMER_ID=C.CUSTOMER_ID
  54.    AND E.EMPLOYEE_ID=O.EMPLOYEE_ID
  55.    AND E.EMPLOYEE_ID = 167
  56.  
  57.  -- Да се изведат данните от изгледа.
  58. SELECT * FROM CUSTOMERORDERS
  59.  
  60. -- Пример 5-4.
  61. -- Да се създаде изглед, съдържащ име и фамилия на служител и общата сума
  62. -- на поръчките, които той е обработил.
  63. CREATE VIEW EMPLOYEESORDERS
  64. AS
  65.     SELECT  E.FNAME + ' ' + E.LNAME AS NAME,    
  66.             SUM(OI.QUANTITY * OI.UNIT_PRICE) AS PRICE
  67.     FROM EMPLOYEES E, ORDER_ITEMS  OI, ORDERS O
  68.     WHERE E.EMPLOYEE_ID = O.EMPLOYEE_ID AND OI.ORDER_ID = O.ORDER_ID
  69.     GROUP BY E.FNAME, E.LNAME
  70.  
  71.  
  72. -- Пример 5-5.
  73. -- Да се създаде изглед, който съдържа имена, отдел и заплата на 5-мата
  74. -- служители с най-висока заплата. За да бъдат извлечени служителите,
  75. -- подредени по заплата, очевидно ще трябва да бъдат сортирани по този критерий.
  76.  
  77. CREATE VIEW EMPLOYEES_TOP_5_SALARIES
  78. AS
  79. SELECT TOP 5 E.FNAME, E.LNAME, D.NAME, E.SALARY
  80. FROM EMPLOYEES E , DEPARTMENTS D
  81. WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
  82. ORDER BY E.SALARY DESC
  83.  
  84. SELECT * FROM EMPLOYEES_TOP_5_SALARIES
  85.  
  86.  
  87. ------------------------5.4.Манипулиране на данни чрез изглед -----------------------
  88. -------------------------------------------------------------------------------------
  89. --Следващият изглед CUSTOMERS_COUNTRIES, базиран на JOIN между COUNTRIES и CUSTOMERS,
  90. --ще демонстрира манипулирането на данни.
  91.  CREATE VIEW CUSTOMER_COUNTRIES
  92.  AS
  93.  SELECT C.COUNTRY_ID, C.NAME, C.REGION_ID, M.CUSTOMER_ID, M.FNAME, M.LNAME, M.GENDER, M.COUNTRY_ID AS CUST_COUNTRY_ID, M.EMAIL
  94.  FROM CUSTOMERS M JOIN COUNTRIES C
  95.  ON C.COUNTRY_ID=M.COUNTRY_ID
  96.  
  97. --Пример 5-6.
  98. --Да се добави нов запис в таблицата CUSTOMERS през изгледа CUSTOMERS_COUNTRIES.
  99.  INSERT INTO CUSTOMER_COUNTRIES(CUSTOMER_ID, FNAME, LNAME, CUST_COUNTRY_ID)
  100.  VALUES(10, 'IVAN', 'ILIEV', 'BG')
  101.  
  102.  SELECT * FROM CUSTOMER_COUNTRIES
  103.  SELECT * FROM CUSTOMERS
  104.  
  105. --5.4.2. Променяне на данни през изглед
  106. ---Пример 5-7.
  107. --Да се промени фамилията на клиент с идентификатор 10.
  108. UPDATE CUSTOMER_COUNTRIES
  109. SET LNAME = 'KOLEV'
  110. WHERE CUSTOMER_ID = 10
  111.  
  112.  SELECT * FROM CUSTOMER_COUNTRIES
  113.  SELECT * FROM CUSTOMERS
  114.  
  115. --5.4.3. Изтриване на данни през изглед
  116. --Пример 5-8.
  117. --Да се изтрие клиент с идентификатор 10.
  118.  
  119.  DELETE FROM CUSTOMER_COUNTRIES
  120.  WHERE CUSTOMER_ID = 10
  121.  
  122. --5.5.Задачи
  123. --Задача 5-1.
  124. --Да се създаде изглед, който съдържа имената на продуктите и общо поръчано
  125. --количество от продукт.
  126.  CREATE VIEW PRODUCTS_QUANTITY_VIEW
  127.  AS
  128.  SELECT P.NAME, SUM(OI.QUANTITY) QUANTITY
  129.  FROM PRODUCTS P JOIN ORDER_ITEMS OI
  130.  ON P.PRODUCT_ID=OI.PRODUCT_ID
  131.  GROUP BY P.NAME, P.PRODUCT_ID
  132.  
  133.  SELECT * FROM PRODUCTS_QUANTITY_VIEW
  134.  
  135. --Задача 5-2.
  136. --Да се създаде изглед, който съдържа десетимата клиенти с най-голям брой
  137. --поръчки. Ако последният клиент има равен брой поръчки с други клиенти, те също да участват в изгледа.
  138.  
  139. CREATE VIEW BIGGEST_QUANTITY_ORDERS_VIEW
  140. AS
  141. SELECT TOP 10 WITH TIES C.CUSTOMER_ID, C.FNAME, C.LNAME, COUNT(O.CUSTOMER_ID) COUNT_ORDERS
  142.                    FROM ORDERS O JOIN CUSTOMERS C
  143.                      ON O.CUSTOMER_ID=C.CUSTOMER_ID
  144.                GROUP BY C.CUSTOMER_ID, C.FNAME, C.LNAME
  145.                ORDER BY 4 DESC
  146.  
  147. SELECT * FROM BIGGEST_QUANTITY_ORDERS_VIEW
  148.  
  149. ---------------------------------------------------------------------------------------------------
  150. ----------------------------------------------ТРАНЗАКЦИИ-------------------------------------------
  151. ---------------------------------------------------------------------------------------------------
  152.  
  153. SELECT GETDATE()
  154.  
  155. --6.4.Примери
  156. --Пример 6-1.
  157. --транзакция, която добавя нов клиент и създава поръчка за него, включваща два продукта.
  158.  
  159. BEGIN TRANSACTION
  160.  
  161.     --ОПЕРАЦИЯ 1: добавя нов клиент
  162.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, ADDRESS, EMAIL, GENDER)
  163.     VALUES(1010, 'BG', 'GEORGI', 'PETROV', 'PLOVDIV BUL.BULGARIA', 'gp@abv.bg', 'M')
  164.  
  165.     --ОПЕРАЦИЯ 2: създава поръчка за него
  166.     INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS, ORDER_DATE)
  167.     VALUES(10, 1010, 107, 'PLOVDIV BUL.BULGARIA', GETDATE())
  168.  
  169.     --включваща два продукта:
  170.     --ОПЕРАЦИЯ 3 продукT 1726
  171.     INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  172.     VALUES(10, 1726, 99, 1)
  173.  
  174.     --ОПЕРАЦИЯ 4 продукT 1782
  175.     INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  176.     VALUES(10, 1782, 99, 1)
  177.  
  178.  COMMIT TRANSACTION
  179.  
  180.  
  181.  SELECT * FROM ORDER_ITEMS
  182.  WHERE ORDER_ID = 10
  183.  
  184.  
  185. /*Пример 6-2.
  186. транзакция, която променя фамилията на клиент с идентификатор = 1010,
  187. след което отхвърля направените промени.*/
  188.  
  189. BEGIN TRAN
  190.     PRINT 'ФАМИЛИЯ ПРЕДИ ТРАНЗАКЦИЯ'
  191.     SELECT LNAME
  192.     FROM CUSTOMERS
  193.     WHERE CUSTOMER_ID = 1010
  194.  
  195.     --променя фамилията на клиент с идентификатор = 1010
  196.     UPDATE CUSTOMERS
  197.     SET LNAME = 'MARINOV'
  198.     WHERE CUSTOMER_ID = 1010
  199.  
  200.     PRINT 'ФАМИЛИЯ СЛЕД ПРОМЯНА ОТ ТРАНЗАКЦИЯТА'
  201.     SELECT LNAME
  202.     FROM CUSTOMERS
  203.     WHERE CUSTOMER_ID = 1010
  204.  
  205. ROLLBACK TRAN
  206.  
  207.     PRINT 'ФАМИЛИЯ СЛЕД ОТХВЪРЛЯНЕ (ROLLBACK) НА ТРАНЗАКЦИЯ'
  208.     SELECT LNAME
  209.     FROM CUSTOMERS
  210.     WHERE CUSTOMER_ID = 1010
  211.  
  212. /*Пример 6-3.
  213. транзакция, която въвежда нов клиент, поставя точка на запис,
  214. въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
  215. отхвърля се само поръчката.*/
  216. BEGIN TRAN
  217.     --OPERATION 1  която въвежда нов клиент,
  218.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME, GENDER)
  219.     VALUES(1012, 'BG', 'MIRA', 'ILIEVA', 'F')
  220. -- поставя точка на запис,
  221. SAVE TRAN POINT1
  222.     --OPERATION 2 въвежда поръчка,
  223.     INSERT INTO ORDERS(ORDER_ID, CUSTOMER_ID, EMPLOYEE_ID, ORDER_DATE)
  224.     VALUES(12, 1012, 110, GETDATE())
  225. ROLLBACK TRAN POINT1
  226. COMMIT TRAN
  227.  
  228. SELECT * FROM CUSTOMERS
  229. WHERE CUSTOMER_ID = 1012
  230.  
  231. SELECT * FROM ORDERS
  232. WHERE CUSTOMER_ID = 1012
  233.  
  234. -- Задача 6-1.
  235. -- Транзакция, която има за цел да изтрие отдел „Мениджмънт“,
  236. -- като преди това прехвърли всички служители от него в отдел „Администрация“.
  237.  
  238. -- Задача 6-2.
  239. -- Транзакция, която изтрива продукт 1726 -първо го изтрива от всички поръчки
  240. -- после от таблицата с продукти, и накрая отхвърля направените промени.
  241.  
Advertisement
RAW Paste Data Copied
Advertisement