16d9xL9y

11 - DEBIT
22 - CREDIT
3
4CREATE SCHEMA `kondratenyabank` ;
5
6CREATE TABLE `kondratenyabank`.`user` (
7  `id` INT NOT NULL AUTO_INCREMENT,
8  `name` VARCHAR(45) NOT NULL,
9  `surname` VARCHAR(45) NOT NULL,
10  `patronymic` VARCHAR(45) NULL,
11  `year_of_birth` YEAR NOT NULL,
12  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
13  PRIMARY KEY (`id`));
14
15
16ALTER TABLE `kondratenyabank`.`account_part` 
17ADD INDEX `account_id_idx` (`account_id` ASC);
18ALTER TABLE `kondratenyabank`.`account_part` 
19ADD CONSTRAINT `account_fk`
20  FOREIGN KEY (`account_id`)
21  REFERENCES `kondratenyabank`.`account` (`id`)
22  ON DELETE CASCADE
23  ON UPDATE NO ACTION;
24
25ALTER TABLE `kondratenyabank`.`account_part` 
26ADD INDEX `account_part_fk_idx` (`account_part_type_id` ASC);
27ALTER TABLE `kondratenyabank`.`account_part` 
28ADD CONSTRAINT `account_part_fk`
29  FOREIGN KEY (`account_part_type_id`)
30  REFERENCES `kondratenyabank`.`account_part_type` (`id`)
31  ON DELETE CASCADE
32  ON UPDATE NO ACTION;
33
34
35ALTER TABLE `kondratenyabank`.`account` 
36ADD INDEX `user_fk_idx` (`user_id` ASC),
37ADD INDEX `credit_fk_idx` (`credit_part_id` ASC),
38ADD INDEX `debit_fk_idx` (`debit_part_id` ASC);
39ALTER TABLE `kondratenyabank`.`account` 
40ADD CONSTRAINT `user_fk`
41  FOREIGN KEY (`user_id`)
42  REFERENCES `kondratenyabank`.`user` (`id`)
43  ON DELETE NO ACTION
44  ON UPDATE NO ACTION,
45ADD CONSTRAINT `credit_fk`
46  FOREIGN KEY (`credit_part_id`)
47  REFERENCES `kondratenyabank`.`account_part` (`id`)
48  ON DELETE CASCADE
49  ON UPDATE NO ACTION,
50ADD CONSTRAINT `debit_fk`
51  FOREIGN KEY (`debit_part_id`)
52  REFERENCES `kondratenyabank`.`account_part` (`id`)
53  ON DELETE CASCADE
54  ON UPDATE NO ACTION;
55
56
57ALTER TABLE `kondratenyabank`.`account_part` 
58DROP INDEX `account_id_idx` ,
59ADD INDEX `account_fk_idx` (`account_id` ASC);
60
61
62INSERT INTO `kondratenyabank`.`user`
63(`name`,
64`surname`,
65`patronymic`,
66`year_of_birth`)
67VALUES
68('Дима','Дамоклов','Владимирович','1994');
69
70
71INSERT INTO `kondratenyabank`.`user`
72(`name`,
73`surname`,
74`patronymic`,
75`year_of_birth`)
76VALUES
77('Максим','Миронов','Андреевич','1993');
78
79
80INSERT INTO `kondratenyabank`.`user`
81(`name`,
82`surname`,
83`patronymic`,
84`year_of_birth`)
85VALUES
86('Станислав','Сатир','Семенович','1992');
87
88
89INSERT INTO `kondratenyabank`.`account_part_type`
90(`type`)
91VALUES
92('DEBIT'),('CREDIT');
93
94ALTER TABLE `kondratenyabank`.`account` 
95DROP FOREIGN KEY `credit_fk`,
96DROP FOREIGN KEY `debit_fk`;
97ALTER TABLE `kondratenyabank`.`account` 
98CHANGE COLUMN `credit_part_id` `credit_part_id` INT(11) NULL ,
99CHANGE COLUMN `debit_part_id` `debit_part_id` INT(11) NULL ;
100ALTER TABLE `kondratenyabank`.`account` 
101ADD CONSTRAINT `credit_fk`
102  FOREIGN KEY (`credit_part_id`)
103  REFERENCES `kondratenyabank`.`account_part` (`id`)
104  ON DELETE CASCADE
105  ON UPDATE NO ACTION,
106ADD CONSTRAINT `debit_fk`
107  FOREIGN KEY (`debit_part_id`)
108  REFERENCES `kondratenyabank`.`account_part` (`id`)
109  ON DELETE CASCADE
110  ON UPDATE NO ACTION;
111
112
113INSERT INTO `kondratenyabank`.`account`
114(`user_id`)
115VALUES
116(1),(2),(3);
117
118INSERT INTO `kondratenyabank`.`account_part`
119(`account_id`,
120`account_part_type_id`,
121`balance`)
122VALUES
123('1','1','500'),('1','2','500'),
124('2','1','500'),('2','2','500'),
125('3','1','500'),('3','2','500');
126
127UPDATE `kondratenyabank`.`account`
128SET
129`credit_part_id` = '2',
130`debit_part_id` = '1'
131WHERE `id` = '1';
132
133UPDATE `kondratenyabank`.`account`
134SET
135`credit_part_id` = '4',
136`debit_part_id` = '3'
137WHERE `id` = '2';
138
139UPDATE `kondratenyabank`.`account`
140SET
141`credit_part_id` = '6',
142`debit_part_id` = '5'
143WHERE `id` = '3';
144
145CREATE TABLE `kondratenyabank`.`transaction` (
146  `id` INT NOT NULL AUTO_INCREMENT,
147  `account_from_id` INT NOT NULL,
148  `account_to_id` INT NOT NULL,
149  `ammount` DECIMAL NOT NULL,
150  `transaction_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
151  PRIMARY KEY (`id`),
152  INDEX `account_from_fk_idx` (`account_from_id` ASC),
153  INDEX `account_to_fk_idx` (`account_to_id` ASC),
154  CONSTRAINT `account_from_fk`
155    FOREIGN KEY (`account_from_id`)
156    REFERENCES `kondratenyabank`.`account` (`id`)
157    ON DELETE NO ACTION
158    ON UPDATE NO ACTION,
159  CONSTRAINT `account_to_fk`
160    FOREIGN KEY (`account_to_id`)
161    REFERENCES `kondratenyabank`.`account` (`id`)
162    ON DELETE NO ACTION
163    ON UPDATE NO ACTION);
164
165DELIMITER |
166CREATE TRIGGER `insert_transaction` AFTER INSERT ON `kondratenyabank`.`transaction`
167FOR EACH ROW BEGIN
168   UPDATE `kondratenyabank`.`account_part` ap
169   JOIN `kondratenyabank`.`account` ac ON ap.account_id = ac.id AND ap.id = '2'
170SET
171balance = balance - ammount
172WHERE ac.id = account_from_id;
173UPDATE `kondratenyabank`.`account_part` ap
174   JOIN `kondratenyabank`.`account` ac ON ap.account_id = ac.id AND ap.id = '1'
175SET
176balance = balance + ammount
177WHERE ac.id = account_to_id;
178END;
179
180
181USE `kondratenyabank`;
182
183DELIMITER $$
184
185DROP TRIGGER IF EXISTS kondratenyabank.transaction_AFTER_INSERT_Credit$$
186USE `kondratenyabank`$$
187CREATE DEFINER = CURRENT_USER TRIGGER `kondratenyabank`.`transaction_AFTER_INSERT_Credit` AFTER INSERT ON `transaction` FOR EACH ROW
188BEGIN
189update account_part
190set balance = balance - new.ammount
191where account_id = new.account_from_id and account_part_id = '2';
192END$$
193DELIMITER ;
194
195USE `kondratenyabank`;
196
197DELIMITER $$
198
199DROP TRIGGER IF EXISTS kondratenyabank.transaction_AFTER_INSERT_Debit$$
200USE `kondratenyabank`$$
201CREATE DEFINER = CURRENT_USER TRIGGER `kondratenyabank`.`transaction_AFTER_INSERT_Debit` AFTER INSERT ON `transaction` FOR EACH ROW FOLLOWS `transaction_AFTER_INSERT_Credit`
202$$
203DELIMITER ;
204
205DELIMITER $$
206CREATE TRIGGER `kondratenyabank`.`transaction_AFTER_INSERT_Credit` AFTER INSERT ON `transaction` FOR EACH ROW
207BEGIN
208update account_part
209set balance = balance - new.ammount
210where account_id = new.account_from_id and account_part_type_id = '1';
211END
212
213Select * from user where (YEAR(CURDATE()) - user.year_of_birth) >= 20
214
215Select * from user where user.surname like ('Д%') or user.surname like ('С%') or user.surname like ('М%')
216
217
218Select ac.id as account_id, concat(name,' ' ,surname,' ', patronymic) as user,
219group_concat(apt.type,': ', ap.balance) as balance_info
220from user us
221join ( select * from account) ac on (us.id = ac.user_id)
222join ( select * from account_part) ap on (ap.account_id = ac.id)
223join ( select * from account_part_type) apt on (apt.id = ap.account_part_type_id)
224
225group by ac.id
226
227Select ac.id as account_id, concat(name,' ' ,surname,' ', patronymic) as user, apd.balance as debit, apc.balance as credit
228from user us
229join ( select * from account) ac on (us.id = ac.user_id)
230join ( select * from account_part) apc on (apc.account_id = ac.id and apc.account_part_type_id=2)
231join ( select * from account_part) apd on (apd.account_id = ac.id and apd.account_part_type_id=1)
232where (us.surname like ('Д%') or us.surname like ('С%') or us.surname like ('М%'))
233group by ac.id
234
235
236Select ac.id as account_id, concat(name,' ' ,surname,' ', patronymic) as user, apd.balance as debit, apc.balance as credit
237from user us
238join ( select * from account) ac on (us.id = ac.user_id)
239join ( select * from account_part) apc on (apc.account_id = ac.id and apc.account_part_type_id=2)
240join ( select * from account_part) apd on (apd.account_id = ac.id and apd.account_part_type_id=1)
241where (us.surname like ('Д%') or us.surname like ('С%') or us.surname like ('М%'))
242and (apc.balance + apd.balance) <= 0
243group by ac.id
244
245
246
247Select distinct ac.id as account_id, concat(name,' ' ,surname,' ', patronymic) as user, apd.balance as debit, apc.balance as credit
248from user us
249join ( select * from account) ac on (us.id = ac.user_id)
250join ( select * from account_part) apc on (apc.account_id = ac.id and apc.account_part_type_id=2)
251join ( select * from account_part) apd on (apd.account_id = ac.id and apd.account_part_type_id=1)
252where (apc.balance+apd.balance)>0
253order by (apc.balance+apd.balance) desc limit 10