Banks task

# Task 1
SELECT *
FROM account a
         JOIN product p ON a.product_cd = p.product_cd
WHERE product_type_cd = 'loan';

SELECT *
FROM account a
WHERE product_cd IN (SELECT product_cd
                     FROM product p
                     WHERE product_type_cd = 'loan');

SELECT *
FROM product p
WHERE product_cd IN (SELECT a.product_cd
                     FROM account a
                     WHERE a.product_cd = p.product_cd
                       AND product_type_cd = 'Loan');

SELECT *
FROM account a
WHERE EXISTS(
              SELECT *
              FROM product p
              WHERE a.product_cd = p.product_cd
                AND product_type_cd = 'loan'
          );

# task 2

SELECT emp_id, lname, fname, d.name, b.name
FROM employee e
         JOIN department d ON e.dept_id = d.dept_id
         JOIN branch b ON b.branch_id = e.assigned_branch_id
ORDER BY emp_id;

# task 2.1

SELECT emp_id, lname, fname, department.name, branch.name
FROM employee,
     department,
     branch
WHERE (employee.dept_id = department.dept_id)
  AND (assigned_branch_id = branch.branch_id)
ORDER BY emp_id;

# task 3

DELIMITER //

CREATE FUNCTION GET_COUNT_ACCOUNT_BY_EMP(emp_id INT)
    RETURNS INT

BEGIN

    #    DECLARE income INT;
#
#    SET income = 0;

    RETURN (SELECT COUNT(*)
            FROM account a
            WHERE a.open_emp_id = emp_id);

END;
//

DELIMITER ;

SELECT emp_id, fname, lname, GET_COUNT_ACCOUNT_BY_EMP(emp_id)
FROM employee e;

# task 4
SELECT emp_id,
       fname,
       lname,
       GET_COUNT_ACCOUNT_BY_EMP(emp_id),
       IF(GET_COUNT_ACCOUNT_BY_EMP(emp_id) > 9, 'премировать', '')
FROM employee e;

# task 5

DELIMITER //
CREATE TRIGGER no_more_than_ten_accounts
    BEFORE INSERT
    ON account
    FOR EACH ROW
    IF (GET_COUNT_ACCOUNT_BY_EMP(NEW.open_emp_id) + 1 > 10) = 1 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
    END IF;
//
DELIMITER ;
# delete
DROP TRIGGER no_more_than_ten_accounts;


DELIMITER //
CREATE TRIGGER no_more_than_ten_accounts_on_upd
    BEFORE UPDATE
    ON account
    FOR EACH ROW
    IF (GET_COUNT_ACCOUNT_BY_EMP(NEW.open_emp_id) + 1 > 10) = 1 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
    END IF;
//
DELIMITER ;
# delete
DROP TRIGGER no_more_than_ten_accounts_on_upd;

SELECT GET_COUNT_ACCOUNT_BY_EMP(1);
SELECT GET_COUNT_ACCOUNT_BY_EMP(1) + 1 > 10;


# task 6
# Напишите триггер, не позволяющий сотруднику отделения города
# обслуживать клиента из другого города.

DELIMITER //
CREATE TRIGGER cant_work_with_person_from_another_city
    BEFORE INSERT
    ON account
    FOR EACH ROW
    IF
            (NEW.open_branch_id = (SELECT assigned_branch_id
                                   FROM employee e
                                   WHERE emp_id = NEW.open_emp_id)) = 0 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person from another city cant work with client.';
    END IF;
//
DELIMITER ;
# delete
DROP TRIGGER cant_work_with_person_from_another_city;

DELIMITER //
CREATE TRIGGER cant_work_with_person_from_another_city_upd
    BEFORE UPDATE
    ON account
    FOR EACH ROW
    IF
            (NEW.open_branch_id = (SELECT assigned_branch_id
                                   FROM employee e
                                   WHERE emp_id = NEW.open_emp_id)) = 0 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person from another city cant work with client.';
    END IF;
//
DELIMITER ;
# delete
DROP TRIGGER cant_work_with_person_from_another_city_upd;

SELECT 4 = (SELECT assigned_branch_id
            FROM employee e
            WHERE emp_id = 15);

# task 7

SELECT e.emp_id, e.fname, e.lname, d.name, e.title,
    (SELECT count(*)
               FROM employee e2
                        JOIN department d2 ON e2.dept_id = d2.dept_id
               WHERE e2.title = e.title
                 AND d2.name = d.name

    )
FROM employee e
         JOIN department d ON e.dept_id = d.dept_id
;
#          JOIN (SELECT *
#                FROM employee e2
#                         JOIN department d2 ON e2.dept_id = d2.dept_id
#                WHERE e2.title = e.title
#                  AND d2.name = d.name) AS sameWorkers on e.emp_id;

# task 8
# Напишите запрос для получения информации о каждом клиенте,
# дополненной суммарным остатком на всех открытых счетах клиента.

SELECT *,
       (SELECT SUM(avail_balance)
        FROM account a
        WHERE a.cust_id = c.cust_id)
FROM customer c;

# task 9

DELIMITER //
CREATE TRIGGER transaction_recorder
    AFTER UPDATE
    ON account
    FOR EACH ROW
    INSERT INTO transaction (txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id,
                             funds_avail_date)
    VALUES (CURDATE(), NEW.account_id, NULL, NEW.avail_balance,
            NEW.open_emp_id, NEW.open_branch_id, NULL)
#     IF
#             (NEW.open_branch_id = (SELECT assigned_branch_id
#                                    FROM employee e
#                                    WHERE emp_id = NEW.open_emp_id)) = 0 THEN
#         SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'There must be no more then 10 acoounts by emp.';
#         END IF;
//
DELIMITER ;

# delete
DROP TRIGGER transaction_recorder;