1

查询每个银行内的存款总额（不包括贷款、保险）、所有贷款的总额、存款最多的存款金额、贷款总额与存款总额之比；
输出：branch_id，存款总额，贷款总额，存款最多的存款金额，贷款总额:存款总额
示例：
±---------------±---------------±-------------±--------------±-----------+
| OPEN_BRANCH_ID | saving | loans | maxs | precent |
±---------------±---------------±-------------±--------------±-----------+
| 2 | 231201772.6167 | 0.0000 | 99021770.1967 | 0.00000000 |
±---------------±---------------±-------------±--------------±-----------+

注：该示例不是标准答案

SELECT t.*, t.loans/t.saving as ratio from(
SELECT a.OPEN_BRANCH_ID,sum(if(pp.name='存款',a.AVAIL_BALANCE,0)) as deposits,
sum(if(pp.name='贷款',a.AVAIL_BALANCE,0)) as loans,
max(if(pp.name='存款',a.AVAIL_BALANCE,0)) as max_deposit
from account a 
JOIN PRODUCT p on p.PRODUCT_CD = a.PRODUCT_CD 
join product_type pp on pp.product_type_cd=p.product_type_cd
GROUP BY OPEN_BRANCH_ID
) as t 

select branch_id, saving, maxs, coalesce(loan, 0) loan, ( ifnull(sec.loan, 0) / fir.saving) as percent from 
((select BRANCH_ID, sum(avail_balance) saving, max(avail_balance) maxsfrom (branch b, account a, product p) where b.branch_id = a.open_branch_idand a.product_cd = p.product_cdand p.product_type_cd = 'ACCOUNT'group by BRANCH_ID) as fir
left outer join (select BRANCH_ID, IFNULL(sum(avail_balance), 0) loanfrom (branch b, account a, product p) where b.branch_id = a.open_branch_idand a.product_cd = p.product_cdand p.product_type_cd = 'LOAN'group by BRANCH_ID) as sec
using(branch_id)
);

2

请使用with rescusive 递归查询每个员工的上级领导。输出员工id、上级领导｜该员工ID。
答案示例：
±-------±------------+
| emp_id | res |
±-------±------------+
| 1 | 1 |
| 2 | 1|2 |
| 3 | 1|3 |
| 20 | 1|2|20 |
| 4 | 1|3|4 |
±-------±------------+

注：示例是标准答案部分数据

with recursive t(emp_id,res) as( 
(select emp_id ,cast(emp_id as char ) from employee where superior_emp_id is null  )
union all (select e.emp_id,CONCAT(t.res,'|',e.emp_id)as res from t,employee  e where t.emp_id = e.superior_emp_id  )) 
select * from t;

select * from employee;
select emp_id, cast(coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as char(30)) as resfrom employee;
WITH recursive cte(emp_id, res) as
(select e.emp_id , coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as resfrom employee ewhere e.superior_emp_id is NULLunionselect e.emp_id , concat(res, '|', e.emp_id) from employee e inner join ctewhere e.superior_emp_id = cte.emp_id
)
select * from cte;

3

利用游标修改account表的AVAIL_BALANCE；如果该金额是属于存款，则按利息每年利息0.00001增加，以last_activity_date开始计算。
注：先更新一下account表
update account set last_activity_date=‘2022-11-11’ where last_activity_date is null;
提示：使用函数 timestampdiff

CREATE DEFINER = `root` @`localhost` PROCEDURE `proc_cursor2` () DETERMINISTIC BEGIN
DECLARE done INT DEFAULT ( 0 );
DECLARE accountid INT;
DECLARE  n INT;
DECLARE  i INT;
DECLARE ans DECIMAL ( 12, 4 );DECLARE cur_1 CURSOR FOR SELECT
ACCOUNT_ID ,AVAIL_BALANCE FROM account;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 
SET done = 1;OPEN cur_1;REPEATset i = 0;FETCH cur_1 INTO accountid,ans;SELECT timestampdiff(YEAR,last_activity_date,now()) into nFROM account  WHERE ACCOUNT_ID = accountid;WHILE i < n DOset ans = ans * 0.00001;set i = i+1;END WHILE;UPDATE account SET AVAIL_BALANCE = ans WHEREaccount.ACCOUNT_ID = accountid;UNTIL done 
END REPEAT;
CLOSE cur_1;
END 

SET SQL_SAFE_UPDATES = 0;
update account set last_activity_date='2022-11-11' where last_activity_date is null;select * from account;
update account set avail_balance = 10000 where avail_balance is null;
desc account;drop procedure  if exists updateAB;
delimiter $$
create procedure updateAB()
begindeclare ai int;declare ab decimal(12, 4);declare lad date;declare dif int;declare cur cursor forselect account_id, avail_balance, last_activity_datefrom account;DECLARE exit HANDLER FOR NOT FOUND CLOSE cur;open cur;repeat fetch cur into ai, ab, lad;set dif = timestampdiff(year, lad, NOW());while(dif > 0) doset ab = ab * 1.00001;set dif = dif - 1;end while;-- select dif;update account set avail_balance = abwhere account_id = ai;until null end repeat;close cur;
end $$
delimiter ;

4

4
新建日志表

CREATE TABLE `logs` (`Id` int(11) NOT NULL AUTO_INCREMENT,`log` varchar(255) DEFAULT NULL COMMENT '日志说明',PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

在players表(第七次作业中已建好的表)中新建update触发器：

1. 更新了players表数据就要添加一条日志数据信息。如果有数据被更改，则在logs表中增加一条元组，具体格式为：“列名” + “该列旧数据” + “ is updated to ” + “该列新数据;”；如果该列没有被更新，则无需添加说明。
   注：每一次触发只能有增加一条元组。
2. 如果某个player转会，其team_id会做相应更改，在team表中的nums（球队人数）也要需要更新。

写好触发器后，请依次运行如下SQL语句，检查触发器运行结果：

 SELECT * from teams;UPDATE players set salary = 10*salary,team_id=3 WHERE `name`="姚明";SELECT * from teams;SELECT * from logs;

DROP TRIGGER IF EXISTS `player_log`;
delimiter;;
CREATE TRIGGER `player_log` AFTER UPDATE ON `players` FOR EACH ROW
BEGIN 
DECLARE s1 VARCHAR ( 1000 ) CHARACTER 
SET utf8;
DECLAREs2 VARCHAR ( 20 ) CHARACTER SET utf8;# 为避免中文字符编码出现乱码，这里需要设置字符集
DECLAREres VARCHAR ( 40 ) CHARACTER SET utf8;
SET s2 = " is updated to ";
SET s1 = "";# 依次检测一条update操作是否对player的三个属性进行变更（id除外）
IFold.NAME <> new.NAME THEN	SET s1 = CONCAT( s1, "name ", old.NAME, s2, new.NAME, "; " );
END IF;
IFold.salary <> new.salary THEN		SET s1 = CONCAT( s1, "salary ", old.salary, s2, new.salary, "; " );
END IF;
IFold.team_id <> new.team_id THEN		SET s1 = CONCAT( s1, "teams_id ", old.team_id, s2, new.team_id, "; " );UPDATE teams SET nums = nums - 1 WHEREteams.id = old.team_id;UPDATE teams SET nums = nums + 1 WHEREteams.id = new.team_id;
END IF;
INSERT INTO LOGS(log) VALUES(s1);
END;
;;
delimiter;

drop trigger if exists update_player ;
delimiter $$
create trigger update_player
after update
on players
for each row 
beginset @temp = '';if old.id != new.id thenset @temp = concat( @temp, "id ", old.id, " is update to ", new.id, '; ');end if;if old.name != new.name thenset @temp = concat( @temp, " name ", old.name, " is update to ", new.name, '; ');end if;if old.salary != new.salary  thenset @temp = concat( @temp, " salary  ", old.salary , " is update to ", new.salary , '; ');end if;if old.team_id != new.team_id  thenset @temp = concat( @temp, " team_id  ", old.team_id , " is update to ", new.team_id , '; ');update teams set teams.nums = teams.nums + 1 where teams.id = new.team_id;update teams set teams.nums = teams.nums - 1 where teams.id = old.team_id;end if;if @temp != "" theninsert into logs(log) values(@temp);end if;end $$
delimiter ;