DataBase Experiment HW06

1

基于年/银行机构名称/客户id （年>银行>客户） 这三个层级查询总金额进行报表输出。（提示：使用account表，rollup操作，这里年份是指LAST_ACTIVITY_DATE）。（输出：年份，银行名称，客户id，总金额）

select c.years,c.BRANCH_NAME ,c.CUST_ID,sum(AVAIL_BALANCE) as sums  from
 (select a.OPEN_BRANCH_ID,B.NAME AS BRANCH_NAME,a.CUST_ID,AVAIL_BALANCE,year(a.LAST_ACTIVITY_DATE) as years 
 from account a join branch B ON B.BRANCH_ID = a.OPEN_BRANCH_ID  ) c  
 group by c.years,c.BRANCH_NAME,c.CUST_ID with rollup;



select  year(a.last_activity_date) 年, name branch_name, account_id
from account a join branch b
on  a.open_branch_id = b.branch_id
group by  year(a.last_activity_date), b.name, cust_id  with rollup;

2

查询所有在上海市总行有存款记录（包括CS，CD等）的账户信息（account表）。（输出：cust_id）
注意：尽管该题可以用一种简单的方法可以查询，但大家请使用“包含关系”查询，即not exists结构；主要考察大家对“包含关系”查询的理解和掌握程度。

 SELECT DISTINCT cust_id from account  a 
 WHERE  not EXISTS 
 (
	SELECT * from account b 
	where b.open_branch_id = '1' 
	and not EXISTS 
	(
		select * from account c WHERE a.cust_id = c.cust_id and  b.open_branch_id  = c.open_branch_id
	)
 );




select distinct cust_id
from account a, product p
where a.product_cd = p.product_cd
and p.product_type_cd = 'ACCOUNT'
and cust_id not in -- 上面的限制为“存款用户” 最后就是要求最后出示的数据不在下面的cust_id中
(
 select cust_id 
 from account ac
 where not exists -- 没有在上海市银行进行任何操作的id
    (
 select *
 from branch b
        where b.branch_id = ac.open_branch_id
        and b.name = '上海市总行'
        -- 在上海市银行存过的
    )
);

3

假设存在下表tmp和视图tmp_view、 tmp_sum、tmp_join。

CREATE TABLE tmp ( x INTEGER );
create view tmp_view as SELECT * from tmp;
CREATE view tmp_sum as (
SELECT sum(avail_balance)as sums from account GROUP BY open_branch_id );
CREATE view tmp_join as SELECT * from tmp_view join tmp_sum;

请测试判断以下五条操作语句是否可行。可执行填T，不可执行则填F；并简要说明为什么？

1）INSERT into tmp_join(sums) VALUES (1);
2）INSERT into tmp_view(x) VALUES (1);
3）UPDATE tmp_join set x=x+1;
4）UPDATE tmp_join set sums=sums+1;
5）UPDATE tmp_sum set sums = sums+1;

1. INSERT into tmp_join(sums) VALUES (1); F。 (因为join视图的一个组件是不可更新的)
2. INSERT into tmp_view(x) VALUES (1); T。 tmp_view是单表、非聚集查询
3. UPDATE tmp_join set x=x+1; T。 x是可更新的部分，尽管tmp_join是连接操作的
4. UPDATE tmp_join set sums=sums+1; F。 sums是不可更新的部分，由聚集查询构成
5. UPDATE tmp_sum set sums = sums+1; F。 sums是不可更新的部分，由聚集查询构成

 无法insert的原因：不能修改那些通过计算得到的列，如 sum和ave
正确 每次修改都只能影响一个基表
正确 理由同2
理由同1 不能修改那些通过计算得到的列
理由同1 不能修改那些通过计算得到的列

4

1). 创建视图tmp_gt，要求查询所有金融产品属性(product_cd)的余额大于80000的账户信息（account表）。
要求保证在视图上的更新都要满足视图定义的限制条件（即余额大于80000）；

2). 以下两条是插入视图tmp_gt的SQL语句，请判断是否能正确插入数据到视图和基本表中，并简要解释原因。

2a）INSERT INTO tmp_gt VALUES ('32', 79999, null, '2011-01-15', '2011-01-15', '正常', '2', '2', '10', 'CD');
2b) INSERT INTO tmp_gt VALUES ('33', 8999999, null, '2011-01-15', '2011-01-15', '正常', '2', '2', '10', 'CD');

1. CREATE view tmp_gt as SELECT * from account where avail_balance > 800000 WITH CHECK OPTION;

2. 第一条不可以插入数据，因为不满足where子句的限制条件；
   第二条可以插入数据。

create or replace view tmp_gt
as select * from account a
where a.avail_balance > 80000
with check option;

select* from tmp_gt;

-- 关键是with check option

INSERT INTO tmp_gt VALUES ('32', 79999, null, '2011-01-15', '2011-01-15', '正常', '2', '2', '10', 'CD');
-- 1Error Code: 1369. CHECK OPTION failed 'bank.tmp_gt'0.000 sec
因为不满足check_option
视图本身只能显示余额大于80000的tuple，所以也不能从view这边插入小于80000的tuple了


 INSERT INTO tmp_gt VALUES ('33', 8999999, null, '2011-01-15', '2011-01-15', '正常', '2', '2', '10', 'CD');
满足check

5

创建函数getParemp_id()，实现用迭代输出某个employee（emp_id为函数输入）的所有上级领导id（superior_emp_id），用‘|’隔开。
输出要求：

1. 输出的顺序按上级领导至下级员工
2. 使用employee表

最后，输入以下指令以查看测试结果
SELECT getParemp_id(11);
返回结果示例：
1|3|4|10|11

CREATE DEFINER=`root`@`localhost` FUNCTION `getParemp_id`(
	path VARCHAR ( 50 )) RETURNS varchar(50) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
	DECLARE
		result VARCHAR ( 50 );
	DECLARE
		pretemp VARCHAR ( 50 );
	SET result = '';
	SET pretemp = path;
	WHILE
			pretemp IS NOT NULL DO
			if result <> '' then 
			SET result = CONCAT( pretemp,'|', result );
			else  set result = pretemp;
			end if;
		SELECT
			SUPERIOR_EMP_ID INTO pretemp 
		FROM
			employee 
		WHERE
			FIND_IN_SET( EMP_ID, pretemp ) > 0;
		
	END WHILE;
	RETURN result;
END


delimiter $$
create function getParemp_id(emp_id int)
returns varchar(50)
begin
 declare res varchar(50);
 declare child varchar(50);
    set res = '';
    set child = '' + emp_id ;
    set res = concat(child, res);
    select superior_emp_id into emp_id 
 from employee
 where emp_id = employee.emp_id;
 set child = '' + emp_id ;
    while child is not null do
 set res = concat(child, "|", res);
        
        select superior_emp_id into emp_id 
        from employee
        where emp_id = employee.emp_id;
        set child = '' + emp_id ;
    end while;
    return res;
end
$$
delimiter ; 
SELECT all getParemp_id(11);