· 8 years ago · Feb 22, 2018, 03:48 PM
1USE bank;
2
3INSERT INTO deposit_types(deposit_type_id, deposit_name)
4VALUES
5 (1, 'Time Deposit'),
6 (2, 'Call Deposit'),
7 (3, 'Free Deposit');
8
9INSERT INTO deposits (amount, start_date, end_date, deposit_type_id, customer_id)
10SELECT
11 CASE
12 WHEN c.date_of_birth > '1980-01-01' THEN 1000
13 ELSE 1500
14 END
15
16 +
17
18 CASE
19 WHEN c.gender = 'm' THEN 100
20 ELSE 200
21 END AS amount,
22 NOW() as start_date,
23 NULL as end_date,
24
25 CASE
26 WHEN c.customer_id > 15 THEN 3
27 ELSE
28 CASE
29 WHEN c.customer_id%2 = 0 THEN 2
30 ELSE 1
31 END
32 END AS deposit_type_id,
33 c.customer_id
34
35 FROM customers AS c
36 WHERE c.customer_id < 20
37;
38
39INSERT INTO employees_deposits(employee_id,deposit_id)
40VALUES
41(15,4),
42(20,15),
43(8,7),
44(4,8),
45(3,13),
46(3,8),
47(4,10),
48(10,1),
49(13,4),
50(14,9);
51
52
53
54USE bank;
55UPDATE TABLE employees
56SET manager_id =
57(CASE
58WHEN employee_id BETWEEN 2 AND 10 THEN 1
59WHEN employee_id BETWEEN 12 AND 20 THEN 11
60WHEN employee_id BETWEEN 22 AND 30 THEN 21
61WHEN employee_id IN (11,21) THEN 1
62END);
63
64
65
66
67-- update --
68UPDATE employees AS e
69SET manager_id =
70 CASE
71 WHEN employee_id BETWEEN 2 and 10
72 THEN 1
73 WHEN employee_id BETWEEN 12 and 20
74 THEN 11
75 WHEN employee_id BETWEEN 22 and 30
76 THEN 21
77 WHEN employee_id IN (11,21)
78 THEN 1
79 END
80
81-- DELETE Po3--
82DELETE FROM employees_deposits
83WHERE employee_id = 3 OR deposit_id = 9;
84
85-- SECTION 3 --
86
87SELECT employee_id,hire_date,salary,branch_id
88 FROM employees AS e
89WHERE e.salary > 2000
90AND e.hire_date > '2009-06-15'
91
92
93SELECT c.first_name, c.date_of_birth,
94FLOOR(DATEDIFF('2016-01-10', c.date_of_birth)/360) AS 'age'
95FROM customers AS c
96WHERE FLOOR(DATEDIFF('2016-01-10', c.date_of_birth)/360) BETWEEN 40 AND 50;
97
98SELECT cu.customer_id, first_name, last_name, gender, city_name
99FROM customers AS cu INNER JOIN cities AS ci ON cu.city_id = ci.city_id
100WHERE
101 (cu.last_name LIKE ('Bu%')
102 OR RIGHT(cu.first_name, 1) = 'a')
103 AND
104 CHAR_LENGTH(ci.city_name)>7
105;
106
107
108SELECT
109 e.employee_id, e.first_name, a.account_number
110FROM employees AS e
111 INNER JOIN employees_accounts AS ea
112 ON e.employee_id = ea.employee_id
113 INNER JOIN accounts AS a
114 ON a.account_id = ea.account_id
115WHERE
116 YEAR(a.start_date) > 2012
117LIMIT 5
118;
119
120-- 5 --
121
122SELECT
123 c.city_name,b.name, COUNT(*) AS employees_count
124FROM employees AS e
125 INNER JOIN branches AS b
126 ON e.branch_id = b.branch_id
127 INNER JOIN cities AS c
128 ON c.city_id = b.city_id
129WHERE
130 c.city_id NOT IN (4,5)
131GROUP BY c.city_name, b.name
132HAVING COUNT(*) >=3
133;
134
135-- 6 --
136
137SELECT
138 SUM(l.amount) AS total_loan_amount,
139 MAX(l.interest) AS max_interest,
140 MIN(e.salary) AS min_salary
141FROM employees AS e
142 INNER JOIN employees_loans AS el
143 ON e.employee_id = el.employee_id
144 INNER JOIN loans AS l
145 ON l.loan_id = el.loan_id
146;
147
148
149-- 7 --
150(SELECT
151 e.first_name, c.city_name
152FROM employees AS e
153 INNER JOIN branches AS b
154 ON e.branch_id = b.branch_id
155 INNER JOIN cities AS c
156 ON c.city_id = b.city_id
157LIMIT 3)
158
159UNION ALL
160
161(SELECT cu.first_name, ci.city_name
162 FROM customers AS cu
163 INNER JOIN cities AS ci
164 ON cu.city_id = ci.city_id
165LIMIT 3);
166
167--
168
169
170
171
172CREATE TABLE top_employees AS
173SELECT
174 e.first_name, c.city_name
175FROM employees AS e
176 INNER JOIN branches AS b
177 ON e.branch_id = b.branch_id
178 INNER JOIN cities AS c
179 ON c.city_id = b.city_id
180LIMIT 3;
181
182CREATE TABLE top_customers AS
183SELECT cu.first_name, ci.city_name
184 FROM customers AS cu
185 INNER JOIN cities AS ci
186 ON cu.city_id = ci.city_id
187LIMIT 3;
188
189SELECT * FROM top_employees
190UNION ALL
191SELECT * FROM top_customers;
192
193DROP table top_employees;
194DROP table top_customers;
195
196-- 8
197SELECT c.customer_id,
198c.height
199FROM customers AS c
200LEFT OUTER JOIN accounts AS a
201ON c.customer_id = a.customer_id
202WHERE a.account_id IS NULL
203AND c.height BETWEEN 1.74 AND 2.04
204;
205-- 9
206SELECT c.customer_id, l.amount
207FROM customers AS c
208INNER JOIN loans AS l
209ON c.customer_id = l.customer_id
210WHERE l.amount >
211 (
212 SELECT AVG(l.amount) AS avg_amount
213 FROM customers AS c
214 INNER JOIN loans AS l
215 ON c.customer_id = l.customer_id
216 WHERE c.gender = 'm'
217 )
218ORDER BY c.last_name
219;
220
221
222SELECT c.customer_id, c.first_name, a.start_date
223FROM customers AS c
224INNER JOIN accounts AS a
225ON c.customer_id = a.customer_id
226ORDER BY a.start_date
227LIMIT 1;
228---
229
230SELECT c.customer_id, c.first_name, a.start_date
231FROM customers AS c
232INNER JOIN accounts AS a
233ON c.customer_id = a.customer_id
234WHERE a.start_date =
235 (
236 SELECT MIN(a.start_date)
237 FROM customers AS c
238 INNER JOIN accounts AS a
239 ON c.customer_id = a.customer_id
240 )
241;
242
243SELECT c.customer_id, c.first_name, a.start_date
244FROM customers AS c
245INNER JOIN accounts AS a
246ON c.customer_id = a.customer_id
247WHERE a.start_date =
248 (
249 SELECT MIN(a.start_date)
250 FROM accounts AS a
251 )
252;
253
254-- 10 --
255DELIMITER $$
256CREATE FUNCTION udf_concat_string(str1 VARCHAR(255),
257str2 VARCHAR(255))
258RETURNS VARCHAR(255)
259 BEGIN
260 DECLARE reversed_str1 VARCHAR(255);
261 DECLARE reversed_str2 VARCHAR(255);
262
263 SET reversed_str1 := REVERSE(str1);
264 SET reversed_str2 := REVERSE(str2);
265
266 RETURN CONCAT(reversed_str1, reversed_str2);
267
268 END
269$$
270
271DELIMITER ;
272SELECT udf_concat_string('54321', '09876');
273
274-- 2 --
275DROP PROCEDURE IF EXISTS usp_customers_with_unexpired_loans;
276DELIMITER $$
277CREATE PROCEDURE usp_customers_with_unexpired_loans(customer_id INT)
278 BEGIN
279 SELECT c.customer_id, c.first_name, l.loan_id
280 FROM customers AS c
281 INNER JOIN loans AS l
282 ON c.customer_id = l.customer_id
283 WHERE c.customer_id = customer_id
284 AND l.expiration_date IS NULL;
285 END
286$$
287
288DELIMITER ;
289CALL usp_customers_with_unexpired_loans(9);
290
291------------
292USE bank;
293DROP PROCEDURE IF EXISTS usp_take_loan;
294DELIMITER $$
295CREATE PROCEDURE
296usp_take_loan(
297 customer_id INT, loan_amount DECIMAL(10,2),
298 interest DECIMAL(10,2), start_date DATE
299 )
300 BEGIN
301 START TRANSACTION;
302 INSERT INTO loans(start_date, amount, interest, customer_id)
303 VALUES (start_date, loan_amount, interest, customer_id);
304
305 IF (loan_amount NOT BETWEEN 0.01 AND 100000)
306 THEN
307 ROLLBACK;
308 SIGNAL SQLSTATE '45000'
309 SET Message_Text = 'Invalid Loan Amount.';
310
311 ELSE
312 COMMIT;
313 END IF;
314 END
315$$
316
317DELIMITER ;
318CALL usp_take_loan(1,500.00,1,'2016-09-15');
319CALL usp_take_loan(1,0,1,'2016-09-15');
320
321
322--
323USE bank;
324DROP TRIGGER IF EXISTS tr_hire_employee;
325DELIMITER $$
326CREATE TRIGGER tr_hire_employee
327AFTER INSERT
328ON employees
329FOR EACH ROW
330
331 BEGIN
332 UPDATE employees_loans AS el
333 SET el.employee_id = new.employee_id
334 WHERE el.employee_id + 1= new.employee_id;
335 END
336$$
337
338DELIMITER ;
339
340SELECT * FROM employees_loans AS el
341WHERE el.employee_id = 30;
342
343INSERT INTO employees (employee_id, first_name,
344hire_date,salary,branch_id)
345VALUES (31,'Jake','20161212',500,2);
346
347SELECT * FROM employees_loans AS el
348WHERE el.employee_id = 31;
349
350--
351CREATE TABLE account_logs AS
352SELECT *
353 FROM accounts
354WHERE NULL=NULL;
355
356DROP TRIGGER IF EXISTS tr_log_accountsaccount_logs;
357DELIMITER $$
358CREATE TRIGGER tr_log_accounts
359BEFORE DELETE
360ON accounts
361FOR EACH ROW
362
363BEGIN
364 DELETE FROM employees_accounts
365 WHERE account_id = old.account_id;
366 INSERT INTO account_logs
367 VALUES(old.account_id, old.account_number,
368 old.start_date, old.customer_id);
369END
370$$
371
372DELETE FROM accounts
373WHERE customer_id=4;