S_Madanska

week8-РЕШЕНИЯ

Nov 17th, 2021 (edited)
329
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     --------------------------------------------------------------------------------
  2.     --------------------------------   HOMEWORK  CHECK   ---------------------------
  3.     --------------------------------------------------------------------------------
  4.  
  5.     --Задача 0.4*
  6.     --Да се създаде изглед с имената на държавите с повече от 5 клиента от тях.
  7.     CREATE VIEW COUNTRY
  8.     AS
  9.     SELECT CO.NAME[Country],
  10.            COUNT(CU.COUNTRY_ID) [Customers count]
  11.     FROM CUSTOMERS CU JOIN COUNTRIES CO
  12.     ON CU.COUNTRY_ID = CO.COUNTRY_ID
  13.     GROUP BY CO.NAME
  14.     HAVING COUNT(CU.COUNTRY_ID) > 5
  15.  
  16.     SELECT * FROM COUNTRY
  17.  
  18.  
  19.     --Задача 0.5*
  20.     --Да се създаде транзакция, която променя фамилията на служител с
  21.     --идентификатор 101 на 'Гочева', променя фамилията на служител с
  22.     --идентификатор 104 на 'Петрова', както и фамилията на служител с
  23.     --идентификатор 108 на 'Маринова'.
  24.     --Нека след това извлече в резултат име и фамилия само за горепосочените
  25.     --променени служители. Като промените от транзакцията останат постоянни.
  26.  
  27.     SELECT * FROM EMPLOYEES
  28.  
  29. BEGIN TRAN
  30.     SELECT FNAME, LNAME, EMPLOYEE_ID
  31.     FROM EMPLOYEES
  32.     WHERE EMPLOYEE_ID IN (101, 104, 108)
  33.  
  34.     UPDATE EMPLOYEES
  35.     SET LNAME = 'Гочева'
  36.     WHERE EMPLOYEE_ID = 101
  37.  
  38.     UPDATE EMPLOYEES
  39.     SET LNAME = 'Петрова'
  40.     WHERE EMPLOYEE_ID = 104
  41.  
  42.     UPDATE EMPLOYEES
  43.     SET LNAME = 'Маринова'
  44.     WHERE EMPLOYEE_ID = 108
  45.  
  46.     SELECT FNAME, LNAME, EMPLOYEE_ID
  47.     FROM EMPLOYEES
  48.     WHERE EMPLOYEE_ID IN (101, 104, 108)
  49. COMMIT TRAN
  50.  
  51.  
  52. -------------------------------------------------------------------------------------
  53. -------------------------------- ТРАНЗАКЦИИ -----------------------------------------
  54. -------------------------------------------------------------------------------------
  55.     --6.4.Примери
  56.  
  57.     --Пример 6-1.
  58.     --Да се създаде транзакция, която добавя нов клиент и създава поръчка за него,
  59.     --включваща два продукта.
  60.         SELECT * FROM  CUSTOMERS
  61.         SELECT * FROM ORDER_ITEMS
  62.         WHERE ORDER_ID = 1
  63.  
  64.     --НАЧИН 1
  65.     BEGIN TRAN
  66.         --1 добавя нов клиент INSERT
  67.         INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
  68.         VALUES(3, 'BG', 'Марин', 'Петров')
  69.         IF @@ERROR <> 0 ROLLBACK
  70.  
  71.         --2 създава поръчка за него INSERT
  72.         INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
  73.         VALUES(1, GETDATE(), 1001, 174, 'бул. Черхи връх 39, София')
  74.         IF @@ERROR <> 0 ROLLBACK
  75.  
  76.         --3 поръчка 1 включваща продукт 1 INSERT
  77.         INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  78.         VALUES(1, 3400, 789.00, 1)
  79.         IF @@ERROR <> 0 ROLLBACK
  80.  
  81.         --3 поръчка 1 включваща продукт 2 INSERT
  82.         INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  83.         VALUES(1, 2243, 5, 100)
  84.         IF @@ERROR <> 0 ROLLBACK
  85.     COMMIT TRAN
  86.  
  87.         SELECT * FROM  CUSTOMERS
  88.         SELECT * FROM ORDER_ITEMS
  89.         WHERE ORDER_ID = 1
  90.  
  91.     ----НАЧИН 1
  92.     BEGIN TRAN
  93.     SET XACT_ABORT ON
  94.         --1 добавя нов клиент /INSERT/
  95.         INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
  96.         VALUES(3, 'BG', 'Марин', 'Петров')
  97.  
  98.         --2 създава поръчка за него /INSERT/
  99.         INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID, SHIP_ADDRESS)
  100.         VALUES(1, GETDATE(), 1001, 174, 'бул. Черхи връх 39, София')
  101.  
  102.         --3 поръчка 1 включваща продукт /1 INSERT/
  103.         INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  104.         VALUES(1, 3400, 789.00, 1)
  105.  
  106.         --3 поръчка 1 включваща продукт /2 INSERT/
  107.         INSERT INTO ORDER_ITEMS(ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY)
  108.         VALUES(1, 2243, 5, 100)
  109.     COMMIT TRAN
  110.  
  111.  
  112. /*Пример 6-2.
  113. Транзакция, която променя фамилията на клиент с идентификатор = 1001,
  114. след което отхвърля направените промени.*/
  115.  
  116. BEGIN TRAN
  117.     SELECT LNAME AS CURRENTLY
  118.     FROM CUSTOMERS
  119.     WHERE CUSTOMER_ID = 1001
  120.  
  121.     UPDATE CUSTOMERS
  122.     SET LNAME = 'Кастрева'
  123.     WHERE CUSTOMER_ID = 1001
  124.  
  125.     SELECT LNAME AS  AFTER_UPDATE
  126.     FROM CUSTOMERS
  127.     WHERE CUSTOMER_ID = 1001
  128. ROLLBACK TRAN
  129.  
  130.     SELECT LNAME  AS  AFTER_ROLLBACK
  131.     FROM CUSTOMERS
  132.     WHERE CUSTOMER_ID = 1001
  133.  
  134.  
  135. /*Пример 6-3.
  136. Транзакция, която въвежда нов клиент, поставя точка на запис,
  137. въвежда поръчка, след което отхвърля промените до точката на запис, т.е.
  138. отхвърля се само поръчката.*/
  139. SELECT * FROM ORDERS
  140.  
  141. BEGIN TRAN
  142.     --OPERATION 1 /въвежда нов клиент/
  143.     INSERT INTO CUSTOMERS(CUSTOMER_ID, COUNTRY_ID, FNAME, LNAME)
  144.     VALUES(3, 'CA', 'Кейт', 'Рейнолдс')
  145.  
  146. --поставя точка на запис
  147. SAVE TRAN POINT1
  148.  
  149.     --въвежда поръчка
  150.     INSERT INTO ORDERS(ORDER_ID, ORDER_DATE, CUSTOMER_ID, EMPLOYEE_ID)
  151.     VALUES(2, GETDATE(), 3, 174)
  152.  
  153. --отхвърля промените до точката на запис
  154. ROLLBACK TRAN POINT1
  155.  
  156. --ЗАПАЗИ ПРОМЕНИТЕ ОТ ТРАНЗАКЦИЯТА ПРЕДИ ТОЧКАТА НА ЗАПИС
  157. COMMIT TRAN
  158.  
  159. SELECT * FROM ORDERS
  160.  
  161. /*Задача 6-1.
  162. Транзакция, която има за цел да изтрие отдел 90 „Мениджмънт“,
  163. като преди това прехвърли всички служители от него в отдел 10 „Администрация“.*/
  164.  
  165. SELECT * FROM DEPARTMENTS
  166. WHERE DEPARTMENT_ID = 90
  167.  
  168. SELECT * FROM EMPLOYEES
  169. WHERE DEPARTMENT_ID = 10
  170.  
  171. BEGIN TRAN
  172.     -- прехвърли всички служители от 90 в отдел 10
  173.     UPDATE EMPLOYEES
  174.     SET DEPARTMENT_ID = 10
  175.     WHERE DEPARTMENT_ID = 90
  176.  
  177.     --изтрие отдел 90
  178.     DELETE FROM DEPARTMENTS
  179.     WHERE DEPARTMENT_ID = 90
  180. COMMIT TRAN
  181.  
  182. /*Задача 6-2.
  183. Транзакция, която изтрива продукт 1726 -първо го изтрива от всички поръчки
  184. после от таблицата с продукти, и накрая отхвърля направените промени.*/
  185.  
  186. BEGIN TRAN
  187.     DELETE FROM ORDER_ITEMS
  188.     WHERE PRODUCT_ID = 1726
  189.  
  190.     DELETE FROM PRODUCTS
  191.     WHERE PRODUCT_ID = 1726
  192. ROLLBACK TRAN
  193.  
  194.     SELECT *
  195.     FROM ORDER_ITEMS
  196.     WHERE PRODUCT_ID = 1726
  197.  
  198. -------------------------------------------------------------------------------------
  199. ---------------------------------- ПРОЦЕДУРИ ----------------------------------------
  200. -------------------------------------------------------------------------------------
  201.  
  202. --Пример 7-2.
  203. --Да се създаде процедура, която за подадена като входен параметър идентификатор на
  204. --поръчка извежда имена на служител, който я е обработил, както и общата й стойност.
  205.  
  206. CREATE PROC EMPLOYEES_ORDERS_PROC @ORDER INT
  207. AS
  208. SELECT FNAME, LNAME, O.ORDER_ID, SUM(OI.UNIT_PRICE*OI.QUANTITY) TOTAL
  209. FROM EMPLOYEES E JOIN ORDERS O ON O.EMPLOYEE_ID = E.EMPLOYEE_ID
  210.                  JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
  211. WHERE O.ORDER_ID = @ORDER
  212. GROUP BY FNAME, LNAME, O.ORDER_ID
  213.  
  214. EXEC EMPLOYEES_ORDERS_PROC @ORDER = 1
  215.  
  216. -------------------------------------------------------------------------------------
  217. ---------------------------------- ФУНКЦИИ ------------------------------------------
  218. -----1.--Скаларни -------------------------------------------------------------------
  219.  
  220. --Пример 7-4.
  221. --Да се създаде функция, връщаща като скаларна стойност текст, съдържащ името на
  222. --отдел (подаден като параметър) и обща стойност на заплатите в него.
  223.  
  224. CREATE FUNCTION DEPARTMENTS_SALARIES_FUNCTION (@DEPT_ID INT) RETURNS VARCHAR(200)
  225. AS
  226. BEGIN
  227.     DECLARE @SUM_SALARY NUMERIC(10,2), @NAME VARCHAR(50)
  228.  
  229.     SELECT @NAME = D.NAME, @SUM_SALARY = SUM(E.SALARY)
  230.     FROM DEPARTMENTS D JOIN EMPLOYEES E
  231.     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
  232.     WHERE D.DEPARTMENT_ID = @DEPT_ID
  233.     GROUP BY D.NAME, D.DEPARTMENT_ID
  234.  
  235. RETURN 'Сумата на заплатите в отдел: ' + @NAME +
  236.         ' с ID = ' + CAST(@DEPT_ID AS VARCHAR) +
  237.         ' е ' + CAST(@SUM_SALARY AS VARCHAR) + ' лв.'
  238. END
  239.  
  240. SELECT dbo.DEPARTMENTS_SALARIES_FUNCTION(DEPARTMENT_ID) FROM DEPARTMENTS
  241.  
  242. -----2.-- Функции, връщащи резултатен набор------------------------------------------
  243.  
  244. --Пример 7-5.
  245. --Да се създаде функция, връщаща като резултат служителите с техните длъжности.
  246.  
  247. CREATE FUNCTION EMPLOYEES_JOBS_FUNCTION() RETURNS TABLE
  248. AS
  249. RETURN
  250.     SELECT FNAME, LNAME, E.JOB_ID, JOB_TITLE
  251.     FROM EMPLOYEES E JOIN JOBS J
  252.     ON J.JOB_ID = E.JOB_ID
  253.  
  254.     SELECT * FROM dbo.EMPLOYEES_JOBS_FUNCTION()
  255.  
  256. -------------------------------------------------------------------------------------
  257. ----------------------------------- КУРСОРИ -----------------------------------------
  258. -------------------------------------------------------------------------------------
  259.  
  260. /*Пример 8-1.
  261. Да се създаде курсор, който демонстрира прочитане на данни ред по ред от
  262. курсор. Резултатният набор за целта ще съдържа всички клиенти от Германия.
  263.  
  264. Стъпките от жизнения цикъл са обозначени в коментари.*/
  265.  
  266. DECLARE @CUST_ID VARCHAR(10), @F_NAME VARCHAR(20), @L_NAME VARCHAR(20)
  267.  
  268. -- 1 Деклариране на курсора чрез конструкцията DECLARE
  269. DECLARE CUSTOMERS_CURSOR CURSOR FOR
  270. SELECT CUSTOMER_ID, FNAME, LNAME
  271. FROM CUSTOMERS
  272. WHERE COUNTRY_ID = 'DE'
  273.  
  274. -- 2. Отваряне на курсора.
  275. OPEN CUSTOMERS_CURSOR
  276.  
  277. /* 3. Извличане на ред от курсора чрез конструкцията FETCH.
  278. Това обикновено става в цикъл, в който на всяка итерация се проверява
  279. стойността на системната променлива @@FETCH_STATUS след всяко извличане.
  280. Ако стойността й е:
  281.           0 – извличането е било успешно;
  282.          -1 – няма повече редове в курсора;
  283.          -2 – редът вече не съществува в курсора, т.е.
  284.             той е бил изтрит след отваряне на
  285.             курсора или променен така, че вече не отговаря на условията,
  286.             на които отговарят редовете, участващи в резултатния набор на курсора.
  287. */         
  288. FETCH NEXT FROM CUSTOMERS_CURSOR INTO @CUST_ID, @F_NAME, @L_NAME
  289. PRINT 'Клиенти от Германия: '
  290. PRINT '-----------------------------'
  291. WHILE @@FETCH_STATUS = 0
  292. BEGIN
  293.  
  294. -- 4. Прочитане, актуализиране или изтриване на реда, на който е позициониран курсорът.
  295.  PRINT @F_NAME + ' ' + @L_NAME + ' (ID = ' + @CUST_ID + ')'
  296.  
  297.  FETCH NEXT FROM CUSTOMERS_CURSOR INTO @CUST_ID, @F_NAME, @L_NAME
  298. END
  299.  
  300. -- 5. Затваряне на курсора. Това прекратява активното действие на курсора. Той все още
  301. --може да бъде отворен без да се налага да се декларира отново.
  302. CLOSE CUSTOMERS_CURSOR
  303.  
  304. -- 6. Освобождаване на курсора, за да бъдат освободени структурите от данни, които
  305. --съставят курсора.
  306. DEALLOCATE CUSTOMERS_CURSOR
  307.  
  308.  
  309. -------------------------------------------------------------------------------------
  310. ----------------------------------- ТРИГЕРИ -----------------------------------------
  311. -------------------------------------------------------------------------------------
  312. /*Задача 9-1.
  313. Да се създаде тригер, който при всяка промяна на фамилия на клиент
  314. записва ред в нова таблица CUSTOMERS_HISTORY с атрибути:
  315.     • идентификатор на клиент;
  316.     • стара фамилия;
  317.     • нова фамилия.
  318. */
  319.  
  320. CREATE TABLE CUSTOMERS_HISTORY
  321. (
  322.     CUSTOMER_ID INT,
  323.     OLD_LNAME VARCHAR(50),
  324.     NEW_LNAME VARCHAR(50)
  325. )
  326.  
  327.  CREATE TRIGGER FOR_UPDATE_LNAME_TRIGGER  
  328.  ON CUSTOMERS
  329.  FOR UPDATE      
  330.  AS
  331.     IF UPDATE(LNAME)
  332.     BEGIN
  333.         INSERT INTO CUSTOMERS_HISTORY(CUSTOMER_ID, OLD_LNAME, NEW_LNAME)
  334.         SELECT I.CUSTOMER_ID, D.LNAME, I.LNAME
  335.         FROM inserted I, deleted D
  336.         WHERE I.CUSTOMER_ID = D.CUSTOMER_ID
  337.     END
  338.  
  339. UPDATE CUSTOMERS
  340. SET LNAME = LNAME + '-' + 'Стоянова'
  341. WHERE CUSTOMER_ID IN (111, 213, 171)
  342.  
  343. SELECT *
  344. FROM CUSTOMERS
  345. WHERE CUSTOMER_ID IN (111, 213, 171)
  346.  
  347. SELECT * FROM CUSTOMERS_HISTORY
RAW Paste Data Copied