重点SQL

某银行有集中的关系数据库，其中有用下面的SQL语句创建的三张表:

表1：

字段名	类型	说明
BRNNUM	CHAR(4)	网点编号
ACC_NO	CHAR(10)	账户号
ACC_SERIAL	SMALLINT	子账户序号
ACC_TYP	CHAR(2)	账户类别 ‘01’表示存款          ‘02’表示贷款
CRYNUM	CHAR(2)	币种 ‘10’ 表示人民币       ‘21’表示港币       ‘32’表示美元
BLAV	NUMEBER(14,2)	金额

 

 

表2：BRNNUM

字段名	类型	说明
BRNNUM	CHAR(4)	网点编号
BRN_NAM	CHAR(40)	网点名称
BANKNO	CHAR(4)	网点归属分行编号

 

表3：BANKNO

字段名	类型	说明
BANKNO	CHAR(4)	分行编号
BANK_NAM	CHAR(40)	分行名称

 

表4：EXCH_RATE

字段名	类型	说明
CRYNUM	CHAR(2)	币种
RAT_RMB	NUMBER(9,5)	该币种对人民币汇率

 

 

 

其中，表中的数据具有以下特征：、

a.      任何一个账户号都明确的归属某个银行网点。

b.      账户号（ACC_NO）下可以有多个账户序号(ACC_SERIAL)

c.      账户类别(ACC_TYP)和币种(CRYNUM)是ACC_NO+ACC_SERIAL的属性，在特定ACC_NO+ACC_SERIAL 下ACC_TYP+CRYNUM不会重复。

d.      任何一个网点都归属一个上级机构….分行

e.      ACC_BLAV表已经有基于ACC_NO，ACC_TYP，CRYNUM的索引

f.       ACC_BLAV表中记录条数大于1000万。

 

 

请用标注sql语句回答下面问题

1为保证特征c中的要求的完整性(不会重复),有一种实现方法是建立索引，请写出语名。

 

 

 

 

 

 

 

2.列出账户号为’001’的客户的人民币存款余额合计。内容：余额。

 

 

 

 

 

 

 

3.列出分行编号为’7501’的分行下全部账户人民币存款情况。

内容:网点编号，账户号，余额

其中余额是该账户号的人民币存款余额合计

 

 

 

 

 

 

 

 

4．为了最大限度提高第3题中你所写语句的执行效率，请写一条建立该表索引的语句。

 

 

 

 

 

 

 

 

5列出各分行合计人民币存款余额。内容：分行代码，分行名称，余额。

 

 

 

 

 

 

6现有下面的嵌套SQL语句

        Select *

        From ACC_BLAV

        WhereBRNNUM=(select BRNNUM

                       From BRNNUM

                       Where BANKNO=’7501’)

请将其改写为不用嵌套的SQL语句,要求结果完全一样。

 

 

 

 

 

 

 

 

 

 

 

7．列出分行编号为’7501’的分行下全部账户存款情况。

内容：网点编号，账户号，余额

其中余额是该账户号的各币种存款余额都折成人民币后的余额

 

 

 

 

 

 

 

 

 

 

 

 

8.假设在前面表结构和说明基础上，还有一张交易表ACC_TRAN.某一笔交易过程要对

ACC_TRAN表做插入操作。和对ACC_BLAV做更新操作。请写一段SQL流程，完成此笔

交易，不必写出更新和插入细节，更新和插入用自然语言描述即可。

 

 

 

 

create database bankdb
go
use bankdb
go
create table ACC
(
   BRNNUM char(4) not null,   --网点编号
   Acc_No char(10) not null,  --帐户号
   Acc_Serial smallint not null,  --子帐户序号
   Acc_Typ char(2) not null,      --帐户类别 '01'表存款 02不贷款
   CRYNUM char(2) not null,       --币种  10RMB  21 港币 32表美元
   BLAV numeric(14,2) not null    --金额
 )
 
 create table Branch
 (
     BRnNum char(4) not null,   --网点编号
     brn_nam char(40) not null, --网点名称
     bankNo char(4) not null    --网点归属分行编号
 )
 
 create table bank
 (
    bankNo char(4) not null,    --分行编号
    bank_Nam char(40) not null  --分行名称
 )
 
 create table exch_rate
 (
     cryNum char(2) not null,    --币种
     rat_rmp  numeric(9,5) not null   --对人民币汇率
 )
 
 --1.建立Acc_No+Acc_Serial+Acc_TYP +CRYNUM的唯一索引
 
 --2.列出帐户号为'001'的客户的人民币余额合计
 select SUM(blav) as 合计
 from ACC 
 where Acc_No='001' and acc_type='01'
 
 --3
 select acc.brnnum,acc_no,sum(blav) as 合计
 from ACC inner join Branch on acc.BRNNUM= Branch.BRnNum 
          inner join bank on bank.bankNo = Branch.bankNo
 where Branch.bankNo='7501'
 group by acc.brnnum,acc_no
 
 --4建立brnnum +acc_no的索引
CREATE NONCLUSTERED INDEX [idx_accNO_BrnnUM] ON [dbo].[ACC] 
(
[BRNNUM] ASC,
[Acc_No] ASC
) on [PRIMARY]  --文件组Primary
 
   
   
   --5
   select b.bankNO,Bank_Nam,SUM(BLAV)
   from bank as b,
        Branch as br,
        ACC as a
   where b.bankNO = br.bankNO and
         br.brnnum = a.brnnum and
         a.Acc_Typ='01'
   group by b.bankNO,Bank_Nam
   
   
   --6
   select acc.*
   from ACC inner join Branch on acc.BRNNUM = Branch.BRnNum
   where  bankNo ='7501'
   
   --7
   select Branch.BRnNum,ACC.Acc_No,SUM(blav*exch_rate.rat_rmp)
   from acc inner join branch on acc.BRNNUM = branch.BRnNum
            inner join bank on bank.bankNo = branch.bankNo
            inner join exch_rate on acc.CRYNUM = exch_rate.cryNum
   where bank.bankNo='7501' and
 acc.Acc_Typ='01'
    group by Branch.BRnNum,ACC.Acc_No
      
 
 --8