上机 2
1、基于第一次上机创建的银行数据库,创建一个视图 branch_detail,能够
显示所有支行的存款客户数量、存款总额、贷款客户数量、贷款总额。
2、在 account 的 account_number 属性上建立索引,并在 account 表
里插入大量元组,比较有无索引在查询速度上的区别。
3、创建角色银行经理 branch_manager,银行经理具有插入、删除和更新
branch 表的权限。
4、自由练习第四章中级 SQL 的其他特性。
创建一个视图 branch_detail
create view branch_detail as
select *
from
(select branch_name as name1,count(distinct (customer_name)) as
count1,sum(balance)
from branch023 natural join account023 natural join depositor023
group by branch_name)as deposite,
(select branch_name as name2,count(distinct (customer_name)) as
count2,sum(amount)
from loan023 natural join borrower023 natural join branch023
group by branch_name)as loan
where name1=name2;
select * from branch_detail;