老二牛车教育SQL与存储过程强化训练题十

老二牛车教育www.iniuche.com

SQL与存储过程 强化训练题库十

时间：2小时

总分数：100

如果训练了五套题库后，就应该训练存储过程啦

数据库文件请到百度网盘下载：http://pan.http://www.wodefanwen.com//s/1mgiIMtq

答案请在百度文库中找……

1

老二牛车教育www.iniuche.com

0：数据库还原 下发数据库备份文件后，未能在3分钟之内还原的扣5分

ATM表结构如下

CREATE TABLE userInfo --操作员,ATM机器信息表 (

userID uniqueidentifier NOT NULL, --操作员编号，uniqueidentifier，主键 userType VARCHAR(20) NOT NULL, --操作员类型，必填，ATM取款机,营业员 code VARCHAR(50) NULL --代号信息 操作员或营业员工号 )

CREATE TABLE customerInfo --客户信息表 (

customerID INT IDENTITY(1,1), --顾客编号，自动编号（标识列），从开始，主键 customerName VARCHAR(20) NOT NULL, --开户名 必填

PID CHAR(18) NOT NULL, --必填，只能是位或位，身份证号唯一约束

telephone CHAR(13) NOT NULL, --联系电话，必填，固定电话格式为：xxxx-xxxxxxxx，

13位；或手机号：11位

customerAddress VARCHAR(50) --居住地址，可选输入 )

CREATE TABLE cardInfo --银行卡信息表 (

cardID CHAR(19) NOT NULL, --卡号格式为：1010 3576 xxxx xxx开始 curType CHAR(5) NOT NULL, --货币，必填，默认为RMB

savingType CHAR(8) NOT NULL, --存款种类，活期/定活两便/定期

openDate DATETIME NOT NULL, --开户日期，必填，默认为系统当前日期 openMoney MONEY NOT NULL, --开户金额，必填，不低于1元 balance MONEY NOT NULL, --余额 必填，不低于1元,否则将销户 pass CHAR(6) NOT NULL, --密码，必填，位数字，默认为888888

IsReportLoss BIT NOT NULL, --是否挂失 必填，是/否值，默认为”否”

customerID INT NOT NULL --顾客编号 必填，表示该卡对应的顾客编号，一位顾客

可以办理多张卡

)

备注：数据库中提供产生卡号的存储过程proc_randCardID

本次比赛的业务在存储过程内部可以不考虑事务，在调用的时候可以用事务。

CREATE TABLE transInfo --交易信息表 (

transID INT IDENTITY(1,1),

2

老二牛车教育www.iniuche.com

OpID uniqueidentifier NULL, --必填，外健 操作员编号

OpType VARCHAR(20) NOT NULL, --必填，只能是ATM取款机,营业员 transDate DATETIME NOT NULL, --交易日期 必填，默认为系统当前日期 transType VARCHAR(20) NOT NULL, --必填，只能是存入/支取 cardID CHAR(19) NOT NULL, --必填，外健，可重复索引 transMoney MONEY NOT NULL, --交易金额 必填，大于０

curTransID VARCHAR(500) NULL,--转帐操作对应的交易号（见业务需求说明） remark VARCHAR(50) --备注 可选输入，其他说明 )

CREATE TABLE LogInfo --日志信息表 (

logID INT IDENTITY(1,1),

opID uniqueidentifier NOT NULL, --必填，外健 操作员编号

opType VARCHAR(50) NOT NULL, --必填，只能是ATM取款机,营业员 opDate DATETIME NOT NULL, --操作日期 必填，默认为系统当前日期

transType VARCHAR(50) NOT NULL, --必填，只能是存入/支取/转帐/开户/销户/挂失/密码/其他

remark VARCHAR(50) )

业务要求说明：

ATM取款机设计时要求完成存款，取款，转帐，挂失等功能，其中存款，取款，转帐操作需要记录交易信息(tranInfo)，存款，取款，转帐，开户，销户，挂失，修改密码操作都需要记录日志信息（在logInfo表中）。

其中转帐操作时需要将每笔的交易号记录在相对应的交易记录的交易号字段中，如卡号1010 3576 1212 1134转出3000元到其他三个卡中，如1010 3576 1212 1133，1010 3576 1212 1233，1010 3576 1212 1333，需要在交易表中生成四条记录。交易号对应多个交易时逗号分隔，如下图

这样就可以记录每一个转帐操作中记录对应的取出，存入交易记录。即交易号字段不空，表示交易为为转账操作。

题目：

100：要求：写一个存储过程usp_LostCard，模拟一个营业员给一个持卡客户办理挂失帐号（要求全部完成相关的业务；输入参数为opID（操作员）,CardID（卡号）。）---5分

101：要求：写一个存储过程usp_InsertATMUsersData，对userInfo表模拟插入用户记录，每种用户类型（ATM取款机，营业员）各5条（要求使用循环），如下图：---5分

3

老二牛车教育www.iniuche.com

102：要求：写一个存储过程usp_InsertATMCustomerData，对customerInfo表模拟插入客户记录3条（要求使用循环），如下图：---5分

103：要求：写一个存储过程usp_InsertATMCardInfoData，对CardInfo表模拟插入卡记录（相当于要求给每一位客户办理一个新卡，但其他业务不需要实现），如下图：---5分

104：要求：写一个存储过程usp_InsertATMCardInfoAsTypeData，对CardInfo表模拟插入卡记录（相当于要求给每一位客户办理一个要求的存款种类（如活期/定活两便/定期）的新卡，其他业务不需要实现，输入参数要求为存款种类），如下图：---5分

105：要求：写一个存储过程usp_MoneyToCustomer，模拟一个营业员或一台ATM取款机给一个持卡客户办理取款业务（要求全部完成相关的业务,输入参数为opID（操作员或柜机）,CardID（卡号,金额等）） ---5分

106：要求：写一个存储过程usp_MoneyFromCustomer，模拟一个营业员给一个持卡客户办理存款业务（要求全部完成相关的业务，输入参数为opID（操作员或柜机）,CardID（卡号,金额等））---5分

107：要求：写一个存储过程usp_MoneyFromToCustomer，模拟一个营业员给一个持卡客户办理转帐业务，给另外一个卡转帐（要求全部完成相关的业务,输入参数为opID（操作员或柜机），outCardID（转出卡号），inCradID（转入帐号），money（金额）等）-5分

4

老二牛车教育www.iniuche.com

108：要求：写一个存储过程usp_ATM108，模拟一个营业员给一个持卡客户办理修改密码业务（要求全部完成相关的业务，输入参数为opID（操作员)，CardID（卡号），oldPass（旧密码），newPass（新密码）等）---5分

109：要求：写一个存储过程usp_ClearDataBaseAllData，删除userInfo，customerInfo，cardInfo，transInfo，LogInfo中所有的数据。---5分

110：要求：写一个存储过程usp_LostCardQueryDate，模拟一个营业员给一个持卡客户查询办理挂失帐号的时间 （输入参数为CardID（卡号）） ---5分

111：要求：写一个存储过程usp_MoneyTransferToCustomer，模拟一个营业员给一个持卡客户办理转帐业务，给另外几个卡各转帐1000元（要求全部完成相关的业务,输入参数为opID（操作员或柜机），outCardID（卡号），inCradID，inCradID为以逗号分隔）-5分

112：要求：写一个存储过程usp_GetCurTypeStatCount，查询按照货币类型分别为（RMB人民币，EUR欧元，GBP英镑，USD美元）的卡的数量。（一条SQL语句完成）-5分

113：要求：写一个存储过程usp_GetPID1518Stat，查询客户的身份证号是15位与身份证号是18 位的数量的差(要求使用返回值返回结果，并且要求写出调用该存储过程的步骤)。-5分

114：要求：写一个存储过程usp_GetTelStatCount，查询用户的电话中填写的是手机的用户数，是固定电话的用户数（一条SQL语句完成）。-5分

200：要求：写一个存储过程usp_GetTransInfoStatCount，查询交易记录总次数，存入的交易记录总次数，支取的交易记录总次数（一条SQL语句完成）。-5分

5

老二牛车教育www.iniuche.com

201：要求：写一个存储过程usp_GetTransferStatCount，查询一个帐户的金额转帐到其他帐户的记录情况（输入参数为CardID）。-5分

202：要求：写一个存储过程usp_GetNoTransInfoCardStat，查询只有办卡记录，没有交易记录的卡（一条SQL语句完成）。-5分

203：要求：写一个存储过程usp_GetMaxTransCountCardStat，查询交易记录总次数为最多的卡（一条SQL语句完成）。-5分

204：要求：写一个存储过程usp_GetSaveTypingStatCount，查询存款种类分别为（活期、定活两便、定期）类型的卡的数量（一条SQL语句完成）。-5分

205：要求：写一个存储过程usp_GetMaxTransCountUserStat，查询交易记录总次数最多的客户（一条SQL语句完成）。-5分

206：要求：写一个存储过程usp_GetMaxInTransMoneyUserStat，查询存入交易金额总数最大的用户（一条SQL语句完成）。-5分

207：要求：写一个存储过程usp_GetTransInfoPageAsCustomerID ，查询某个客户的交易信息，要求分页显示（要求使用输入参数为客户ID、查看的页数、每页显示的记录数）。分页算法，每多写一种方式，加2分（最多加5分） -5分

6

老二牛车教育www.iniuche.com

208：要求：写一个存储过程usp_GetMaxBalanceAsSavingType，查询不同的存款类型的卡中的余额最多的用户（输入参数为存款类型）（一条SQL语句完成）。-5分

209：要求：写一个存储过程usp_GetCustomerCountAllSavingType，查询客户办理的卡中三种存款类型（活期、定活两便、定期）的卡都有的客户的数量（一条SQL语句完成）。-5分

210：要求：写一个存储过程usp_GetCustomerMaxSavingTypeCount，查询客户办理的三种存款类型（活期，定活两便，定期）卡的分类型挂失卡总数量（一条SQL语句完成）。-5分

211：要求：写一个存储过程usp_GetPIDCountAsLength，查询客户的身份证号不同位数(如15位/18位)的用户的总数量(要求输入参数为身份证号的位数，输出参数为总用户数)。-5分

212：要求：写一个存储过程usp_GetPIDCountStat，查询用户填写的身份证号为15位，18 位的客户数量。-5分

213：要求：写一个存储过程usp_Get1969PIDCount，查询用户填写的身份证号中1969年出生的人的数量（只考虑18位的身份证）（一条SQL语句完成）。-5分 如：140104196909091332

214：要求：写一个存储过程usp_GetMobileCount，查询用户的电话中填写的是手机的用户数（一条SQL语句完成）。-5分

215：要求：写一个存储过程usp_GetCardCountThisMonth，查询本月（当前操作时间所在的月）开户的卡（一条SQL语句完成）。-5分

7

老二牛车教育www.iniuche.com

/*--------统计银行的资金流通余额和盈利结算------------------------------*/

--统计说明：存款代表资金流入，取款代表资金流出。假定各国的存款利率都为千分之3，贷款利率都为千分之8。

300：要求：写一个存储过程usp_GetSumMoneyStat，实现资金流入总量、资金流出总量、盈利结算（一条语句实现资金流入总量，资金流出总量，盈利结算） ）。-5分

301：要求：写一个存储过程usp_GetSumMoneyStatAsCurType，按照货币类型（RMB人民币、EUR欧元、GBP英镑、USD美元）实现资金流入总量，资金流出总量，盈利结算的统计。（一条SQL语句完成）-5分

302：要求：写一个存储过程usp_GetCardCountThisWeek，查询本周开户的卡。（一条SQL语句完成）-5分

303：要求：写一个存储过程usp_GetUserInfoAsMaxCardCount，查询办卡最多的用户。（一条SQL语句完成）-5分

304：要求：写一个存储过程usp_GetUserInfoAsMaxCardCount，查询卡的余额范围在<1000，1000-5000，5000-10000，>10000的卡数量。（一条SQL语句完成） -5分

305：要求：写一个存储过程usp_GetTransInfoAsCard，查询一张卡的交易记录（输入参数cradID；如下图支出金额前加“-”号）。（一条SQL语句完成） -5分

8

老二牛车教育www.iniuche.com

306：要求：写一个存储过程usp_GetCurTypeAndSaveTypingStatCount，查询按照货币类型分别为（RMB人民币、EUR欧元、GBP英镑、USD美元）以及存款种类分别为（ 活期，定活两便，定期）的卡的数量。（一条SQL语句完成）-5分

307：要求：写一个存储过程usp_QueryBalanceLess200，查询余额小于200元的客户卡号和余额，用来提示卡费不足（注意业务，不考虑币种）。（一条SQL语句完成） -5分

308：要求：写一个存储过程usp_NewCardWoKaoPei ，给挂失被盗的客户补办一个新卡，并且办理对挂失后被盗取金额以两倍进行赔偿，将此赔偿金额一并存入新卡中（如客户挂失时账户有金额6000元，但是挂失后被盗取了5000元，需要赔偿10000元，加原有6000元，开一个新卡。）(输入参数为旧卡号，输出新卡记录) 。 -5分

备注：以下存储过程内全部用一条SQL语句实现

400 : 要求：写一个存储过程usp_ATM400,输出客户信息表中姓氏人数第二多的姓氏-5分

401：要求：写一个存储过程usp_ATM401，查询输出客户信息表中三个字的姓名中除了姓氏之外其它是重复字的人员（如苏帅帅、任倩倩）-5分

9

老二牛车教育www.iniuche.com

402：要求：写一个存储过程usp_ATM402，查询输出客户信息表的姓名中第二个字符为“玉”的人数 -5分

403：要求：写一个存储过程usp_ATM403，查询输出客户信息表的年龄分布统计数据如下信息（只考虑身份证18 位）。（总人数，四十年代以前、四五十年代、五六十年代、六七十年代、七八十年代、80后、90后、二十世纪） -5分

404：要求：写一个存储过程usp_ATM404，查询输出客户信息表中名称姓苏的，名称是三个字，最后一个是“善”的人员 -5分

405：要求：写一个存储过程usp_ATM405，查询输出客户信息表中客户名称按姓氏的排行榜-5分

406：要求：写一个存储过程usp_ATM406，输出客户信息表中客户名字按如下结果 -5分

407：要求：写一个存储过程usp_ATM407，查询输出客户信息表中姓名为：两个字的男性人数、两个字的女性人数、三个字的男性人数、三个字的女性人数、四个字的男性人数、四个字的女性人数-5分

408：要求：写一个存储过程usp_ATM408，将货币类型为RMB的信用卡的号码结尾为48

10

本文来源：https://www.bwwdw.com/article/31do.html