# 003.测验.SQL之债权统计
这是一个比较贴近实际业务的SQL练习,技术点比较常用和实用。
作者:史荣久 日期:2014-11-26 许可:CC BY-SA 3.0
## 任务说明
如下图,业务中,存在以下数据和数据关系:
一个顾客(CUST)会有零或多个债权(LOAN),
债权会有零或多个交易(TRANSACTION)。
LOAN表BALANCE为余额,REPAYDAY为下次还款日,
TRANSACTION表中,ID是自增的,AMOUNT为金额,
OPRTIME为操作日,TYPE=1为贷款,TYPE=2为还款。
![ER图和数据](/images/post/actions/003/er-data.png)
## 思考问题
1)各顾客的余额,用SQL得到下图输出。
![各顾客的余额](/images/post/actions/003/result-1.png)
2)还款最多交易,即用SQL得到下图输出。
在2010年从01-01(含)到06-01(不含)时间段,
找出各债权还款最多的交易数据(可能多条),
并显示对应的顾客名,
![还款最多交易](/images/post/actions/003/result-2.png)
3)交易表有10亿数据,检索结果响应很慢,
可能是何原因,如何排查原因,如何改善。
4)本题中3张表,设计上有何缺陷,如何改善。
## 测试数据
通过以下SQL创建表结构和初始数据。
CREATE TABLE `CUST` (
`CUSTID` VARCHAR(12) NOT NULL ,
`NAME` VARCHAR(20) NOT NULL ,
`AGE` INT(11) NOT NULL ,
PRIMARY KEY (`CUSTID`) )
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
CREATE TABLE `LOAN` (
`LOANID` VARCHAR(20) NOT NULL ,
`CUSTID` VARCHAR(12) NOT NULL ,
`BALANCE` DECIMAL(16,4) NOT NULL ,
`REPAYDAY` DATE NULL DEFAULT NULL ,
PRIMARY KEY (`LOANID`) ,
UNIQUE INDEX `ID_UNIQUE` (`LOANID` ASC) ,
INDEX `CUST` (`CUSTID` ASC) ,
CONSTRAINT `CUST`
FOREIGN KEY (`CUSTID` )
REFERENCES `CUST` (`CUSTID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
CREATE TABLE `TRANSACTION` (
`ID` INT(11) NOT NULL ,
`AMOUNT` DECIMAL(16,4) NOT NULL ,
`TYPE` INT(11) NOT NULL ,
`OPRTIME` DATETIME NOT NULL ,
`LOANID` VARCHAR(20) NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `LOAN` (`LOANID` ASC) ,
CONSTRAINT `LOAN`
FOREIGN KEY (`LOANID` )
REFERENCES `LOAN` (`LOANID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
INSERT INTO CUST(CUSTID, NAME, AGE) VALUES
('100000000101', '张三', 23),
('100000000201', '李四', 24),
('100000000301', '王五', 25);
INSERT INTO LOAN(LOANID, CUSTID, BALANCE, REPAYDAY) VALUES
('10000000010100000001', '100000000101', 3000, '2010-07-03'),
('10000000010100000002', '100000000101', 3500, '2010-07-23'),
('10000000020100000001', '100000000201', 4000, '2010-07-04');
INSERT INTO TRANSACTION(ID, AMOUNT, TYPE, OPRTIME, LOANID) VALUES
(1, -10000, 1, '2010-03-03 15:15:15', '10000000010100000001'),
(2, 2000, 2, '2010-04-03 15:15:15', '10000000010100000001'),
(3, 2000, 2, '2010-05-03 15:15:15', '10000000010100000001'),
(4, 3000, 2, '2010-06-03 15:15:15', '10000000010100000001'),
(5, -8000, 1, '2010-04-23 15:15:15', '10000000010100000002'),
(7, 2000, 2, '2010-05-23 15:15:15', '10000000010100000002'),
(8, 2500, 2, '2010-06-23 15:15:15', '10000000010100000002'),
(9, -10000, 1, '2010-01-04 15:15:15', '10000000020100000001'),
(10, 1000, 2, '2010-02-04 15:15:15', '10000000020100000001'),
(11, 1000, 2, '2010-03-04 15:15:15', '10000000020100000001'),
(12, 1500, 2, '2010-04-04 15:15:15', '10000000020100000001'),
(13, 1500, 2, '2010-05-04 15:15:15', '10000000020100000001'),
(14, 1000, 2, '2010-06-04 15:15:15', '10000000020100000001');
-----
题图:《黑客帝国》以很多方式解析真实、超现实,还有人的观点是实质的、物理的世界才是虚幻。
原文:
http://www.moilioncircle.com/actions/003.quiz.sql-loan-sum.html
分享到:
相关推荐
绿色精简版的SQL Server 2000,做测试开发的好工具。原作者网站已无法访问。这是最新的6.5.0.4版本。 SQL Server绿色版是SQL Server 2000绿色精简版,SQL Server绿色版只保留 GSQL.EXE 版权,其他附带文件版权...
003.Mini USB 测试机.zip非标自动化设备solidworks3D图纸机械设计素材资料 003.Mini USB 测试机.zip非标自动化设备solidworks3D图纸机械设计素材资料 003.Mini USB 测试机.zip非标自动化设备solidworks3D图纸机械...
35-35.渗透测试SQL注入之SQL注入防御
003.Mini USB 测试机.rar
1.测试人员发现有效bug数排行。条件:时间 2.研发人员发生bug数排行。条件:时间
C#.sql文件执行sql server数据库安装(源码).如果数据库连接参数无效,则弹出参数设置向导;配置成功后再运行建库脚本。建库脚本文件名称及路径保存在与执行程序同名的配置文件(.config)中。代码测试过无问题,拿来就...
这是文档《SQL基础知识第二版》的附带源码,方便大家直接在数据库中执行进行相关的测试与练习。
到现在都没搞懂,难道Flowable的开发人员都没测试过么,还是说我忽略了啥东西导致失败,项目第一次用Flowable,决定用Flowable6.3.0,因为这个版本官方有中文文档,但是死活跑不起来,最后整理了这个建表脚本,然后...
渗透测试之SQL注入基础(上)
简单点说benchmarksql就是一个通过JDBC 测试OLTP 的TPC-C。 支持PostgreSQL/EnterpriseDB, DB2, Oracle, SQLSvr, MySQL,DM,Kingbase
简单实用的MySQL数据库编辑工具,已测试过win7完全可以使用。
asp.net+SQL基于三层模式体育比赛网站设计毕业源码.zipasp.net+SQL基于三层模式体育比赛网站设计毕业源码.zipasp.net+SQL基于三层模式体育比赛网站设计毕业源码.zipasp.net+SQL基于三层模式体育比赛网站设计毕业源码...
同步更新测试.sql,SDFAS A同步更新测试.sql同步更新测试.sql同步更新测试.sql同步更新测试.sql同步更新测试.sql
System.out.println("sql数据库连接测试成功"); } catch(Exception e){ e.printStackTrace(); System.out.println("sql数据库连接测试失败"); } } } 注意:由于sp4 过大,不让我传,大家可以百度下,到处都是...
│ │ 2.1 日期概念理解中的一些测试.sql │ │ 2.2.4 CONVERT在日期转换中的使用示例.sql │ │ 2.3.3 SET DATEFORMAT对日期处理的影响.sql │ │ 2.3.4 SET LANGUAGE对日期处理的影响示例.sql │ │ 2.4.1 日期...
SQL 远程访问的测试程序 ADO方法 里边包含了ADO 的DLL
执行复杂数据库任务的SQL开发人员和DBA可以使用GUI工具来加速几乎所有数据库体验,例如设计数据库、编写SQL代码、比较数据库、同步模式和数据、生成有意义的测试数据等等。 没有必要浪费时间在Internet上搜索和购买...
除此之外,还有用于所有支持的数据库系统的图形数据库建模和设计工具、性能监控、数据分析和报告、ETL 和数据自动化、可视化数据库记录器、高级模式克隆、多数据库代码部署、数据混淆和清理,数据库基准测试和单元...
SQL Server2019正版软件。微软最新的数据库产品下载。不是预览版本的,不是测试版本的。新品上市的。
SQL语法测验SQL语法测验SQL语法测验SQL语法测验SQL语法测验SQL语法测验SQL语法测验SQL语法测验SQL语法测验