Advertisement
user1man

Banks task

Sep 29th, 2022 (edited)
713
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 5.57 KB | None | 0 0
  1. # Task 1
  2. SELECT *
  3. FROM account a
  4.          JOIN product p ON a.product_cd = p.product_cd
  5. WHERE product_type_cd = 'loan';
  6.  
  7. SELECT *
  8. FROM account a
  9. WHERE product_cd IN (SELECT product_cd
  10.                      FROM product p
  11.                      WHERE product_type_cd = 'loan');
  12.  
  13. SELECT *
  14. FROM product p
  15. WHERE product_cd IN (SELECT a.product_cd
  16.                      FROM account a
  17.                      WHERE a.product_cd = p.product_cd
  18.                        AND product_type_cd = 'Loan');
  19.  
  20. SELECT *
  21. FROM account a
  22. WHERE EXISTS(
  23.               SELECT *
  24.               FROM product p
  25.               WHERE a.product_cd = p.product_cd
  26.                 AND product_type_cd = 'loan'
  27.           );
  28.  
  29. # task 2
  30.  
  31. SELECT emp_id, lname, fname, d.name, b.name
  32. FROM employee e
  33.          JOIN department d ON e.dept_id = d.dept_id
  34.          JOIN branch b ON b.branch_id = e.assigned_branch_id
  35. ORDER BY emp_id;
  36.  
  37. # task 2.1
  38.  
  39. SELECT emp_id, lname, fname, department.name, branch.name
  40. FROM employee,
  41.      department,
  42.      branch
  43. WHERE (employee.dept_id = department.dept_id)
  44.   AND (assigned_branch_id = branch.branch_id)
  45. ORDER BY emp_id;
  46.  
  47. # task 3
  48.  
  49. DELIMITER //
  50.  
  51. CREATE FUNCTION GET_COUNT_ACCOUNT_BY_EMP(emp_id INT)
  52.     RETURNS INT
  53.  
  54. BEGIN
  55.  
  56.     #    DECLARE income INT;
  57. #
  58. #    SET income = 0;
  59.  
  60.     RETURN (SELECT COUNT(*)
  61.             FROM account a
  62.             WHERE a.open_emp_id = emp_id);
  63.  
  64. END;
  65. //
  66.  
  67. DELIMITER ;
  68.  
  69. SELECT emp_id, fname, lname, GET_COUNT_ACCOUNT_BY_EMP(emp_id)
  70. FROM employee e;
  71.  
  72. # task 4
  73. SELECT emp_id,
  74.        fname,
  75.        lname,
  76.        GET_COUNT_ACCOUNT_BY_EMP(emp_id),
  77.        IF(GET_COUNT_ACCOUNT_BY_EMP(emp_id) > 9, 'премировать', '')
  78. FROM employee e;
  79.  
  80. # task 5
  81.  
  82. DELIMITER //
  83. CREATE TRIGGER no_more_than_ten_accounts
  84.     BEFORE INSERT
  85.     ON account
  86.     FOR EACH ROW
  87.     IF (GET_COUNT_ACCOUNT_BY_EMP(NEW.open_emp_id) + 1 > 10) = 1 THEN
  88.         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
  89.     END IF;
  90. //
  91. DELIMITER ;
  92. # delete
  93. DROP TRIGGER no_more_than_ten_accounts;
  94.  
  95.  
  96. DELIMITER //
  97. CREATE TRIGGER no_more_than_ten_accounts_on_upd
  98.     BEFORE UPDATE
  99.     ON account
  100.     FOR EACH ROW
  101.     IF (GET_COUNT_ACCOUNT_BY_EMP(NEW.open_emp_id) + 1 > 10) = 1 THEN
  102.         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
  103.     END IF;
  104. //
  105. DELIMITER ;
  106. # delete
  107. DROP TRIGGER no_more_than_ten_accounts_on_upd;
  108.  
  109. SELECT GET_COUNT_ACCOUNT_BY_EMP(1);
  110. SELECT GET_COUNT_ACCOUNT_BY_EMP(1) + 1 > 10;
  111.  
  112.  
  113. # task 6
  114. # Напишите триггер, не позволяющий сотруднику отделения города
  115. # обслуживать клиента из другого города.
  116.  
  117. DELIMITER //
  118. CREATE TRIGGER cant_work_with_person_from_another_city
  119.     BEFORE INSERT
  120.     ON account
  121.     FOR EACH ROW
  122.     IF
  123.             (NEW.open_branch_id = (SELECT assigned_branch_id
  124.                                    FROM employee e
  125.                                    WHERE emp_id = NEW.open_emp_id)) = 0 THEN
  126.         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person from another city cant work with client.';
  127.     END IF;
  128. //
  129. DELIMITER ;
  130. # delete
  131. DROP TRIGGER cant_work_with_person_from_another_city;
  132.  
  133. DELIMITER //
  134. CREATE TRIGGER cant_work_with_person_from_another_city_upd
  135.     BEFORE UPDATE
  136.     ON account
  137.     FOR EACH ROW
  138.     IF
  139.             (NEW.open_branch_id = (SELECT assigned_branch_id
  140.                                    FROM employee e
  141.                                    WHERE emp_id = NEW.open_emp_id)) = 0 THEN
  142.         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person from another city cant work with client.';
  143.     END IF;
  144. //
  145. DELIMITER ;
  146. # delete
  147. DROP TRIGGER cant_work_with_person_from_another_city_upd;
  148.  
  149. SELECT 4 = (SELECT assigned_branch_id
  150.             FROM employee e
  151.             WHERE emp_id = 15);
  152.  
  153. # task 7
  154.  
  155. SELECT e.emp_id, e.fname, e.lname, d.name, e.title,
  156.     (SELECT count(*)
  157.                FROM employee e2
  158.                         JOIN department d2 ON e2.dept_id = d2.dept_id
  159.                WHERE e2.title = e.title
  160.                  AND d2.name = d.name
  161.  
  162.     )
  163. FROM employee e
  164.          JOIN department d ON e.dept_id = d.dept_id
  165. ;
  166. #          JOIN (SELECT *
  167. #                FROM employee e2
  168. #                         JOIN department d2 ON e2.dept_id = d2.dept_id
  169. #                WHERE e2.title = e.title
  170. #                  AND d2.name = d.name) AS sameWorkers on e.emp_id;
  171.  
  172. # task 8
  173. # Напишите запрос для получения информации о каждом клиенте,
  174. # дополненной суммарным остатком на всех открытых счетах клиента.
  175.  
  176. SELECT *,
  177.        (SELECT SUM(avail_balance)
  178.         FROM account a
  179.         WHERE a.cust_id = c.cust_id)
  180. FROM customer c;
  181.  
  182. # task 9
  183.  
  184. DELIMITER //
  185. CREATE TRIGGER transaction_recorder
  186.     AFTER UPDATE
  187.     ON account
  188.     FOR EACH ROW
  189.     INSERT INTO transaction (txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id,
  190.                              funds_avail_date)
  191.     VALUES (CURDATE(), NEW.account_id, NULL, NEW.avail_balance,
  192.             NEW.open_emp_id, NEW.open_branch_id, NULL)
  193. #     IF
  194. #             (NEW.open_branch_id = (SELECT assigned_branch_id
  195. #                                    FROM employee e
  196. #                                    WHERE emp_id = NEW.open_emp_id)) = 0 THEN
  197. #         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
  198. #         END IF;
  199. //
  200. DELIMITER ;
  201.  
  202. # delete
  203. DROP TRIGGER transaction_recorder;
Tags: banks task3
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement