第九章1

--用例4
--1.修改密码
update  cardInfo set pass='123456 '--张三123456
where cardID='1010 3576 1234 5678'
update  cardInfo set pass='222222'--李四123123
where cardID='1010 3576 1212 1130'

----2.办理银行卡挂失-----------------
--描述：李四（卡号为1010357612121134）因银行卡丢失，申请挂失
update  cardInfo set IsReportLoss=1
where cardID='1010 3576 1212 1130'

--3.统计银行流通余额，盈利结算
declare @inmoney money     --声明一个总存入的变量
declare @outmoney money    --声明一个总支取的变量
select @inmoney=SUM(trademoney) from tradeInfo where tradeType='存入'
select @outmoney=SUM(trademoney) from tradeInfo where tradeType='支取'
print '银行流通余额总计为:'+convert(varchar(20),@inmoney-@outmoney)+'RMB'
print '盈利结算为:'+convert(varchar(20),@outmoney*0.008-@inmoney*0.003)+'RMB'
go

--4.查询本周开户信息
select * from cardInfo where  
DATEDIFF(weekday,opendate,GETDATE())<DATEPART(weekday,GETDATE())


--5.查询本月交易金额最多的卡号
--存入最多
select * from tradeInfo where 
tradeMoney=(select MAX(tradeMoney) from tradeInfo where 
tradeType='存入' and DATEDIFF(MONTH,tradeDate,GETDATE())<=1)


--支取最多
select * from tradeInfo where
 tradeMoney=(select MAX(tradeMoney) from tradeInfo where 
 tradeType='支取' and DATEDIFF(MONTH,tradeDate,GETDATE())<=1)


--6.查询挂失的卡号
select * from userInfo where 
customerID in(select customerID from cardInfo where IsReportLoss=1)


--7.催款提醒业务
select customername from userInfo inner join cardInfo 
on cardInfo.customerID=userInfo.customerID where balance<200