老二牛车教育SQL与存储过程强化训练题十Word格式文档下载.docx

老二牛车教育SQL与存储过程强化训练题十Word格式文档下载.docx

《老二牛车教育SQL与存储过程强化训练题十Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《老二牛车教育SQL与存储过程强化训练题十Word格式文档下载.docx（10页珍藏版）》请在冰豆网上搜索。

CREATETABLEcustomerInfo--客户信息表

customerIDINTIDENTITY（1,1）,--顾客编号，自动编号（标识列），从开始，主键

customerNameVARCHAR（20）NOTNULL,--开户名必填

PIDCHAR（18）NOTNULL,--必填，只能是位或位，身份证号唯一约束

telephoneCHAR（13）NOTNULL,--联系电话，必填，固定电话格式为：

xxxx-xxxxxxxx，13位；

或手机号：

11位

customerAddressVARCHAR（50）--居住地址，可选输入

CREATETABLEcardInfo--银行卡信息表

cardIDCHAR（19）NOTNULL,--卡号格式为：

10103576xxxxxxx开始

curTypeCHAR（5）NOTNULL,--货币，必填，默认为RMB

savingTypeCHAR（8）NOTNULL,--存款种类，活期/定活两便/定期

openDateDATETIMENOTNULL,--开户日期，必填，默认为系统当前日期

openMoneyMONEYNOTNULL,--开户金额，必填，不低于1元

balanceMONEYNOTNULL,--余额必填，不低于1元,否则将销户

passCHAR（6）NOTNULL,--密码，必填，位数字，默认为888888

IsReportLossBITNOTNULL,--是否挂失必填，是/否值，默认为”否”

customerIDINTNOTNULL--顾客编号必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡

备注：

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

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

CREATETABLEtransInfo--交易信息表

transIDINTIDENTITY（1,1）,

OpIDuniqueidentifierNULL,--必填，外健　操作员编号

OpTypeVARCHAR（20）NOTNULL,--必填，只能是ATM取款机,营业员

transDateDATETIMENOTNULL,--交易日期必填，默认为系统当前日期

transTypeVARCHAR（20）NOTNULL,--必填，只能是存入/支取

cardIDCHAR（19）NOTNULL,--必填，外健，可重复索引

transMoneyMONEYNOTNULL,--交易金额必填，大于０

curTransIDVARCHAR（500）NULL,--转帐操作对应的交易号（见业务需求说明）

remarkVARCHAR（50）--备注可选输入，其他说明

CREATETABLELogInfo--日志信息表

logIDINTIDENTITY（1,1）,

opIDuniqueidentifierNOTNULL,--必填，外健　操作员编号

opTypeVARCHAR（50）NOTNULL,--必填，只能是ATM取款机,营业员

opDateDATETIMENOTNULL,--操作日期必填，默认为系统当前日期

transTypeVARCHAR（50）NOTNULL,--必填，只能是存入/支取/转帐/开户/销户/挂失/密码/其他

remarkVARCHAR（50）

业务要求说明：

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

其中转帐操作时需要将每笔的交易号记录在相对应的交易记录的交易号字段中，如卡号1010357612121134转出3000元到其他三个卡中，如1010357612121133，1010357612121233，1010357612121333，需要在交易表中生成四条记录。

交易号对应多个交易时逗号分隔，如下图

这样就可以记录每一个转帐操作中记录对应的取出，存入交易记录。

即交易号字段不空，表示交易为为转账操作。

题目：

100：

要求：

写一个存储过程usp_LostCard，模拟一个营业员给一个持卡客户办理挂失帐号（要求全部完成相关的业务；

输入参数为opID（操作员）,CardID（卡号）。

）---5分

101：

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

---5分

102：

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

103：

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

104：

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

105：

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

106：

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

107：

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

108：

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

109：

写一个存储过程usp_ClearDataBaseAllData，删除userInfo，customerInfo，cardInfo，transInfo，LogInfo中所有的数据。

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语句完成）。

200：

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

201：

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

202：

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

203：

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

204：

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

205：

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

206：

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

207：

写一个存储过程usp_GetTransInfoPageAsCustomerID，查询某个客户的交易信息，要求分页显示（要求使用输入参数为客户ID、查看的页数、每页显示的记录数）。

分页算法，每多写一种方式，加2分（最多加5分）-5分

208：

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

209：

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

210：

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

211：

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

212：

写一个存储过程usp_GetPIDCountStat，查询用户填写的身份证号为15位，18位的客户数量。

213：

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

如：

140104196909091332

214：

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

215：

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

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

--统计说明：

存款代表资金流入，取款代表资金流出。

假定各国的存款利率都为千分之3，贷款利率都为千分之8。

300：

写一个存储过程usp_GetSumMoneyStat，实现资金流入总量、资金流出总量、盈利结算（一条语句实现资金流入总量，资金流出总量，盈利结算）

）。

301：

写一个存储过程usp_GetSumMoneyStatAsCurType，按照货币类型（RMB人民币、EUR欧元、GBP英镑、USD美元）实现资金流入总量，资金流出总量，盈利结算的统计。

302：

写一个存储过程usp_GetCardCountThisWeek，查询本周开户的卡。

303：

写一个存储过程usp_GetUserInfoAsMaxCardCount，查询办卡最多的用户。

304：

写一个存储过程usp_GetUserInfoAsMaxCardCount，查询卡的余额范围在<

1000，1000-5000，5000-10000，>

10000的卡数量。

（一条SQL语句完成）-5分

305：

写一个存储过程usp_GetTransInfoAsCard，查询一张卡的交易记录（输入参数cradID；

如下图支出金额前加“-”号）。

306：

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

307：

写一个存储过程usp_QueryBalanceLess200，查询余额小于200元的客户卡号和余额，用来提示卡费不足（注意业务，不考虑币种）。

308：

写一个存储过程usp_NewCardWoKaoPei，给挂失被盗的客户补办一个新卡，并且办理对挂失后被盗取金额以两倍进行赔偿，将此赔偿金额一并存入新卡中（如客户挂失时账户有金额6000元，但是挂失后被盗取了5000元，需要赔偿10000元，加原有6000元，开一个新卡。

）（输入参数为旧卡号，输出新卡记录）。

-5分

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

400 

:

要求：

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

401：

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

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的所有客户的地址如果为空的话，改为“未知”；

否则在地址后面添加一个48。

409：

写一个存储过程usp_ATM409，随机选择5个中奖客户。

附加题

1：

写一个存储过程usp_GetFirstDay计算会计年月（按自然年月）的第一天，要求一个输入参数为四位的字符串年份，一个输入参数为两位的字符串月份，要求输出参数为日期类型。

（如输入年为2009，月为1，输出日期为2009-01-01）。

2：

写一个存储过程tx_GetNextSubjYM计算下一个会计年月（按自然年月），要求一个输入参数为四位的字符串年份，一个输入参数为两位的字符串月份，要求输出两个输出参数为整形的上一个会计月的年和月（如输入年为2008，月为12，输出年为2009，月为1）。

3：

写一个存储过程tx_GetLastDay计算会计年月（按自然年月）的最后一天，要求一个输入参数为四位的字符串年份，一个输入参数为两位的字符串月份，要求输出参数为日期类型。

（如输入年为2009，月为1，输出日期为2009-01-31）。

4：

写一个存储过程usp_ICanDoItATM模拟银行业务：

（注：

本题不需要写日志信息）

插入一个新操作员，插入一个新客户，并且让新操作员给新客户办理一个要求的存款种类（活期/定活两便/定期）的新卡各一张。

并且在每一张卡上存1000元人民币。

然后将新客户的定期类型新卡上的1000元人民币的币种修改为美元（USD）。

将活期类型新卡上的500元转到定活两便的新卡上。

修改新客户的新卡密码为16816818，将客户的美元存款类型的卡办理挂失。

---15分

5：

因为经常按照名称查询数据，在cardInfo表中添加客户名称信息，要求写一个存储过程usp_AddCustomerNameColumn，在cardInfo添加一个列名称为cName，类型为varchar（20），同时将cName的数据更新为customer表中相应的customerName的数据---10分

6：

写一个存储过程usp_LostCardQueryOp，模拟一个营业员给一个持卡客户查询给他办理挂失帐号的营业员工号。

（输入参数为CardID（挂失的卡号），用一条SQL语句完成）---5分

7：

写一个存储过程usp_QueryCard，模拟一个营业员给一个持卡客户办理查询余额（输入参数为CardID卡号，输出余额）---5分