create database accountInfo/*创建账户信息数据库*/

 /*定义主码、外码、和人数、余额的取值范围。*/

 /*创建储蓄所表*/

 create table bank(

     bank_ID int primary key identity(10001,1),/*从10001开始，每次增加1*/

     bank_name nvarchar(20),    /*储蓄所名称，*/

     bank_address nvarchar(40),

     bank_peopleNum int check(bank_peopleNum between 1 and 30),

     bank_city nvarchar(10)

 )

 /*创建账户表*/

 /*帐户（编号，姓名，余额，建立日期，储蓄所编号）*/

 create table account(

     account_id int primary key identity(1,1),

     account_name nvarchar(15),

     account_balance int check(account_balance>= 0 ),

     account_found_date char(8),

     bank_ID int foreign key references bank(bank_id)

 )

 /*创建借贷表*/

 /*借贷（帐户，借贷类型，金额，日期）*/

 create table borrow(

     borrow_id int primary key identity(1,1),

     account_ID int foreign key references account(account_ID),

     type_borrow nvarchar(10),

     money_sum int check(money_sum>=0),

     date_borrow char(8)

 )

 --插入bank数据

 insert into bank values('长春南湖路','朝阳区',10,'长春市')

 insert into bank values('西安大路','朝阳区',13,'长春市')

 insert into bank values('幸福二路','南关区',11,'长春市')

 insert into bank values('幸福三路','南关区',12,'长春市')

 insert into bank values('桃林街','城关区',18,'灵宝市')

 --插入account数据

 insert into account values('赵大',10000,20170510,10001)

 insert into account values('钱二',5000,20160120,10001)

 insert into account values('张三',50000,20161120,10001)

 insert into account values('李四',50000,20161120,10005)

 insert into account values('孙五',500000,20161110,10005)

 --插入borrow数据

 insert into borrow values(1,'短期',1000,20170516)

 insert into borrow values(2,'长期',3000,20170416)

 insert into borrow values(3,'长期',200000,20170510)

--内连接

select account_name,account_balance,bank_name

from account , bank

where account.bank_ID=bank.bank_id and bank_name='长春南湖路'

--左外连接

select bank.bank_ID,count(account_id)'账户个数',sum(account_balance)'余额总数'

from bank left outer join account on(bank.bank_ID=account.bank_ID)

group by bank.bank_ID

select account_name,account_balance

from account

where bank_ID in(select bank_ID from bank where bank_name='长春南湖路')

select account_name

from account

where account_balance>(select MAX(account_balance)

from account where account_name='长春南湖路')

select*

from account as A,Bank as B

where A.bank_ID=B.bank_ID and B.bank_city in

(select bank_city

from bank

group by bank_city

having count(bank_ID)=(select min(Nm)

from(select bank_city,count(bank_ID)

from bank

group by bank_city

)as _bank(Cy,Nm)

)

);

select account_id from account

where account_id not in

(select id from( select account_ID from borrow

group by account_ID ) as v(id)--,Nm))

select account_id

from account except  (select account_ID from borrow )

 create view view1 as

 select account.bank_ID,

 sum(account_balance)'balanceSum'

 from bank,account

 where bank.bank_ID=account.bank_ID

 group by account.bank_ID

 select v1.bank_ID vid

 from(select max(v.balanceSum)as balanceSum from view1 v)

 su,view1 v1

 where v1.balanceSum=su.balanceSum

--根据上面基本表的信息定义一个存储过程，完成下面功能：

--入口参数：储蓄所编号

--1 显示储蓄所信息

--2 如果没有帐户，删除该储蓄所记录

--3 如果帐户余额总额低于100万元，开一个新帐户

alter PROCEDURE pr_bank(@bank_id int)

as

BEGIN

    select *from bank

    where bank_ID = @bank_id

    if (select count(*) from account where bank_id=@bank_id)=0

         delete  from bank where bank_ID=@bank_id

    if (select sum(account_balance) from account where bank_id=@bank_id group by bank_id)<1000000

        insert into account(bank_id) values(@bank_id)

END

exec pr_bank 10005