Solution 2

USE bank;

INSERT INTO deposit_types(deposit_type_id, deposit_name)
VALUES
	(1, 'Time Deposit'),
	(2, 'Call Deposit'),
	(3, 'Free Deposit');

INSERT INTO deposits (amount, start_date, end_date, deposit_type_id, customer_id)
SELECT
	CASE
		WHEN c.date_of_birth > '1980-01-01' THEN 1000
		ELSE 1500
	END

	+

	CASE
		WHEN c.gender = 'm' THEN 100
		ELSE 200
	END AS amount,
	NOW() as start_date,
	NULL as end_date,

	CASE
		WHEN c.customer_id > 15 THEN 3
		ELSE
			CASE
				WHEN c.customer_id%2 = 0   THEN 2
				ELSE 1
			END
	END AS deposit_type_id,
	c.customer_id

	FROM customers AS c
	WHERE c.customer_id < 20
;

INSERT INTO employees_deposits(employee_id,deposit_id)
VALUES
(15,4),
(20,15),
(8,7),
(4,8),
(3,13),
(3,8),
(4,10),
(10,1),
(13,4),
(14,9);



USE bank;
UPDATE TABLE employees
SET manager_id =
(CASE
WHEN employee_id BETWEEN 2 AND 10 THEN 1
WHEN employee_id BETWEEN 12 AND 20 THEN 11
WHEN employee_id BETWEEN 22 AND 30  THEN 21
WHEN employee_id IN (11,21) THEN 1
END);




-- update --
UPDATE employees AS e
SET manager_id =
	CASE
		WHEN employee_id BETWEEN 2 and 10
			THEN 1
		WHEN employee_id BETWEEN 12 and 20
			THEN 11
		WHEN employee_id BETWEEN 22 and 30
			THEN 21
		WHEN employee_id IN (11,21)
			THEN 1
	END

-- DELETE Po3--
DELETE FROM employees_deposits
WHERE employee_id = 3 OR deposit_id = 9;

-- SECTION 3 --

SELECT employee_id,hire_date,salary,branch_id
 FROM employees AS e
WHERE e.salary > 2000
AND e.hire_date > '2009-06-15'


SELECT c.first_name, c.date_of_birth,
FLOOR(DATEDIFF('2016-01-10', c.date_of_birth)/360) AS 'age'
FROM customers AS c
WHERE FLOOR(DATEDIFF('2016-01-10', c.date_of_birth)/360) BETWEEN 40 AND 50;

SELECT cu.customer_id, first_name, last_name, gender, city_name
FROM customers AS cu INNER JOIN cities AS ci ON cu.city_id = ci.city_id
WHERE
	(cu.last_name LIKE ('Bu%')
		OR RIGHT(cu.first_name, 1) = 'a')
	AND
	CHAR_LENGTH(ci.city_name)>7
;


SELECT
	e.employee_id, e.first_name, a.account_number
FROM employees AS e
	INNER JOIN employees_accounts AS ea
	ON e.employee_id = ea.employee_id
	INNER JOIN accounts AS a
	ON a.account_id = ea.account_id
WHERE
	YEAR(a.start_date) > 2012
LIMIT 5
;

-- 5 --

SELECT
	c.city_name,b.name, COUNT(*) AS employees_count
FROM employees AS e
	INNER JOIN branches AS b
	ON e.branch_id = b.branch_id
	INNER JOIN cities AS c
	ON c.city_id = b.city_id
WHERE
	c.city_id NOT IN (4,5)
GROUP BY c.city_name, b.name
HAVING COUNT(*) >=3
;

-- 6 --

SELECT
	SUM(l.amount) AS total_loan_amount,
	MAX(l.interest) AS max_interest,
	MIN(e.salary) AS min_salary
FROM employees AS e
	INNER JOIN employees_loans  AS  el
	ON  e.employee_id  =  el.employee_id
	INNER JOIN  loans  AS l
	ON  l.loan_id  =  el.loan_id
;


-- 7 --
(SELECT
	e.first_name, c.city_name
FROM employees AS e
	INNER JOIN branches AS b
	ON e.branch_id = b.branch_id
	INNER JOIN cities AS c
	ON c.city_id = b.city_id
LIMIT 3)

UNION ALL

(SELECT cu.first_name, ci.city_name
	FROM customers AS cu
	INNER JOIN cities AS ci
	ON cu.city_id = ci.city_id
LIMIT 3);

--




CREATE TABLE top_employees AS
SELECT
	e.first_name, c.city_name
FROM employees AS e
	INNER JOIN branches AS b
	ON e.branch_id = b.branch_id
	INNER JOIN cities AS c
	ON c.city_id = b.city_id
LIMIT 3;

CREATE TABLE top_customers AS
SELECT cu.first_name, ci.city_name
	FROM customers AS cu
	INNER JOIN cities AS ci
	ON cu.city_id = ci.city_id
LIMIT 3;

SELECT * FROM top_employees
UNION ALL
SELECT * FROM top_customers;

DROP table top_employees;
DROP table top_customers;

-- 8
SELECT c.customer_id,
c.height
FROM customers AS c
LEFT OUTER JOIN accounts AS a
ON c.customer_id = a.customer_id
WHERE a.account_id IS NULL
AND c.height BETWEEN 1.74 AND 2.04
;
-- 9
SELECT c.customer_id, l.amount
FROM customers AS c
INNER JOIN loans AS l
ON c.customer_id = l.customer_id
WHERE l.amount >
 (
	SELECT AVG(l.amount) AS avg_amount
		FROM customers AS c
		INNER JOIN loans AS l
		ON c.customer_id = l.customer_id
		WHERE c.gender = 'm'
  )
ORDER BY c.last_name
;


SELECT c.customer_id, c.first_name, a.start_date
FROM customers AS c
INNER JOIN accounts AS a
ON c.customer_id = a.customer_id
ORDER BY a.start_date
LIMIT 1;
---

SELECT c.customer_id, c.first_name, a.start_date
FROM customers AS c
INNER JOIN accounts AS a
ON c.customer_id = a.customer_id
WHERE a.start_date =
 (
	SELECT MIN(a.start_date)
		FROM customers AS c
		INNER JOIN accounts AS a
		ON c.customer_id = a.customer_id
	)
;

SELECT c.customer_id, c.first_name, a.start_date
FROM customers AS c
INNER JOIN accounts AS a
ON c.customer_id = a.customer_id
WHERE a.start_date =
 (
	SELECT MIN(a.start_date)
		FROM accounts AS a
	)
;

-- 10 --
DELIMITER $$
CREATE FUNCTION udf_concat_string(str1 VARCHAR(255),
str2 VARCHAR(255))
RETURNS VARCHAR(255)
	BEGIN
		DECLARE reversed_str1 VARCHAR(255);
		DECLARE reversed_str2 VARCHAR(255);

		SET reversed_str1 := REVERSE(str1);
		SET reversed_str2 := REVERSE(str2);

	RETURN CONCAT(reversed_str1, reversed_str2);

	END
$$

DELIMITER ;
SELECT udf_concat_string('54321', '09876');

-- 2 --
DROP PROCEDURE IF EXISTS usp_customers_with_unexpired_loans;
DELIMITER $$
CREATE PROCEDURE usp_customers_with_unexpired_loans(customer_id INT)
	BEGIN
		SELECT c.customer_id, c.first_name, l.loan_id
		FROM customers AS c
		INNER JOIN loans AS l
		ON c.customer_id = l.customer_id
		WHERE c.customer_id = customer_id
		AND l.expiration_date IS NULL;
	END
$$

DELIMITER ;
CALL usp_customers_with_unexpired_loans(9);

------------
USE bank;
DROP PROCEDURE IF EXISTS usp_take_loan;
DELIMITER $$
CREATE PROCEDURE
usp_take_loan(
	customer_id INT, loan_amount DECIMAL(10,2),
	interest DECIMAL(10,2), start_date DATE
	)
	BEGIN
		START TRANSACTION;
		INSERT INTO loans(start_date, amount, interest, customer_id)
		VALUES (start_date, loan_amount, interest, customer_id);

		IF (loan_amount NOT BETWEEN 0.01 AND 100000)
		THEN
			ROLLBACK;
			SIGNAL SQLSTATE '45000'
			SET Message_Text = 'Invalid Loan Amount.';

		ELSE
			COMMIT;
		END IF;
	END
$$

DELIMITER ;
CALL usp_take_loan(1,500.00,1,'2016-09-15');
CALL usp_take_loan(1,0,1,'2016-09-15');


--
USE bank;
DROP TRIGGER IF EXISTS tr_hire_employee;
DELIMITER $$
CREATE TRIGGER tr_hire_employee
AFTER INSERT
ON employees
FOR EACH ROW

	BEGIN
			UPDATE employees_loans AS el
			SET el.employee_id = new.employee_id
			WHERE el.employee_id + 1= new.employee_id;
	END
$$

DELIMITER ;

SELECT * FROM employees_loans AS el
WHERE el.employee_id = 30;

INSERT INTO employees (employee_id, first_name,
hire_date,salary,branch_id)
VALUES (31,'Jake','20161212',500,2);

SELECT * FROM employees_loans AS el
WHERE el.employee_id = 31;

--
CREATE TABLE account_logs AS
SELECT *
	FROM accounts
WHERE NULL=NULL;

DROP TRIGGER IF EXISTS tr_log_accountsaccount_logs;
DELIMITER $$
CREATE TRIGGER tr_log_accounts
BEFORE DELETE
ON accounts
FOR EACH ROW

BEGIN
	DELETE FROM employees_accounts
	WHERE account_id = old.account_id;
	INSERT INTO account_logs
	VALUES(old.account_id, old.account_number,
	old.start_date, old.customer_id);
END
$$

DELETE FROM accounts
WHERE customer_id=4;