• 数据库实践 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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    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 = '上海市总行'
            -- 在上海市银行存过的
        )
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5

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

    1INSERT into tmp_join(sums) VALUES (1);
    2INSERT into tmp_view(x) VALUES (1);
    3UPDATE tmp_join set x=x+1;
    4UPDATE tmp_join set sums=sums+1;
    5UPDATE tmp_sum set sums = sums+1;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    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 不能修改那些通过计算得到的列
    
    • 1
    • 2
    • 3
    • 4
    • 5

    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
    • 2
    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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    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);
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
  • 相关阅读:
    Java源码分析 | Object
    城市管网污水监测方案,科技助力污水排放管理!
    Jupyter如何开启Debug调试功能
    思科防火墙IPsec配置-野蛮模式方式(基于9.9版本)
    Google Earth Engine(GEE)——全球人类居住区网格数据 1975-1990-2000-2014 (P2016)
    基于单片机的八路抢答器(数码管版)(独立按键、四位共阳极数码管、指示灯)
    mAP,PR-curve解释及计算等
    Linux学习-32-ACL访问控制权限
    JPA联合主键
    智能算力AI破局 英特尔携手百度飞桨,共话AI技术创新引领新征程
  • 原文地址:https://blog.csdn.net/JamSlade/article/details/128063574