常规业务模拟.sql

QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校的 SQL项目,模拟 银行ATM(取款机）系统

use bankDB
GO
--修改密码：
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
GO

--挂失账号：
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
GO

--统计银行的资金流通余额和盈利结算：
declare @inMoney money
declare @outMoney money
declare @sumBalance money
declare @sumProfit money

select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
set @sumBalance=@inMoney-@outMoney
print '银行流通余额总计为: '+convert(varchar(30),@sumBalance)+' RMB'
set @sumProfit=@outMoney*0.008-@inMoney*0.003
print '盈利结算为: '+convert(varchar(30),@sumProfit)+' RMB'
GO

--查询本周开户的卡号,显示该卡的信息：
select * from cardInfo where DATEDIFF ( wk ,openDate ,getdate())=0
GO

--查询本月交易额最高的卡号:
select distinct 交易额最高的卡号=cardID from transInfo where transMoney=(select max(transMoney) from transInfo) and DATEDIFF ( mm ,transDate ,getdate())=0
GO

--查询挂失帐号的客户信息:
select 客户姓名=u.customerName,身份证号=u.PID,地址=u.address,联系电话=u.telephone,帐上余额=c.balance from userInfo as u inner join cardInfo as c on u.customerID in(
select c.customerID from cardInfo where c.IsReportLoss=1)
GO

--催款提醒业务：
select * from userInfo where customerID=(select customerID from cardInfo where balance<200)