数据开发试题.docxVIP

数据开发试题纸卷满分100分，考试时间90分钟，答案请全部写在答题纸上。一 选择题（共12题，含单选跟多选，每题3分，共36分）1. 并发操作会带来那些数据不一致性？（ABC）A：丢失修改B：不可重复读C：读脏数据D: 死锁2. 使用sql数据进行查询操作时若希望查询结果不出现重复元组，应在select语句中使用（D）保留字？A: UNIQUEB: ALLC: EXCEPTD: DISTINCT3DB2中常用的三种JOIN方式ACD）A: MRGE JOINB: PRODUCT JOINC: NEST LOOP JOIND: HASH JOIN4当JOIN字段类型长度不样时DB2唯一会选择JOIN方式是（C）A: MRGE JOINB: PRODUCT JOINC: NEST LOOP JOIND: HASH JOIN5下列哪个命令可以给我们合适的索引建议（D）A: db2explnB: db2exfmtC: db2batchD: db2advis6下列哪两个命令可以让我们看到给定SQL的执行计划？(AB)A. db2explnB. db2exfmtC. db2batchD. db2advis7. 下列哪个命令专用于收集统计信息？ AA. runstatsB. reorgC. describeD. explain8. 对于SELECT C2,C4 FROM TABLE WHERE C1=100 AND C1=200 AND C2=SDC ORDER BY C3这样的语句，下列4个索引中，哪佳的索引是哪一个？BA. INDEX(C1, C2, C4, C3)B. INDEX(C1, C2, C3)C. INDEX(C2, C1, C3, C4)D. INDEX(C2, C1, C4, C3)9. 如果在表TABLEA的C1字段上有索引，下列哪几个SQL可能会让DB2用上索引？A. SELECT COUNT(*) FROM TABLEA ABCB. SELECT COUNT(C1) FROM TABLEAC. SELECT COUNT(0) FROM TABLEAD. COUNT操作不会使用索引10. UNION和UNION ALL的区别是： BCA. 没区别B. UNION会排除重复记录，UNION ALL不会C. UNION对两个集合的字段类型要求更严格D. UNION ALL对两个集合的字段类型要求更严格11. 对DB2 LUW数据库而言，假设TABLEA(ID,NAME)含有两行数据（1,xiaoming），(2,xiaojun)。TABLEB(ID,NAME)也含有两行数据(1,xiaoming)，(null,xiaojun)。对SELECT * FROM TABLEA WHERE ID NOT IN (SELECT ID FROM TABLEB)的结果集描述正确的是？ CA. 返回(1,xiaoming)B. 返回(1,xiaoming)，(2,xiaojun)C. 返回空结果集D. 结果集不确定12. 对DB2 LUW数据库而言，假设TABLEA(ID,NAME)含有两行数据（1,xiaoming），(2,xiaojun)。TABLEB(ID,NAME)也含有两行数据(1,xiaoming)，(null,xiaojun)。对SELECT * FROM TABLEA WHERE ID IN (SELECT ID FROM TABLEB)的结果集描述正确的是？ AA. 返回(1,xiaoming)B. 返回(1,xiaoming)，(2,xiaojun)C. 返回空结果集D. 结果集不确定二、简答题1. 简述DB2中import和load的区别？（6分）两者都可以将数据导入到DB2服务器中，区别是Import实质执行了SQL的DML操作，将激活触发器，强制实现所有约束，使用数据库的缓冲池，记录日记等。 load直接将格式化的数据页写入数据库，不激活触发器，也不记录日记，导入速度比import快。2. 下表选取了empoyee表中的一个雇员信息，请使用DB2 SQL语句计算比JOE刚好大3岁的人员总数，（提供SQL语句即可）。（6分）NAME(姓名)BIRTHDAY(出生日期)JOE2005-03-21Select count(e.*) from employee e where DATE(e.BIRTHDAY) = (select DATE(BIRTHDAY)-3years from employee where NAME=JOE );3. 个人持有银行账户，并通过银行提供的渠道进行相关的金融交易活动，逻辑模型是为了描述各业务实体，属性（主键）以及实体间的关系。以下模型用来描述个人账户金融交易涉及的实体以及相互关系，但只包含了个人客户信息实体，账户信息实体、账户交易信息实体，以及账户信息实体和账户交易信息实体之间的关系，从总体上看，并不完整。已知实体具体说明如下：个人客户信息（客户编号、客户姓名、开户机构、年龄、性别）账户信息（账号、账号名称、币种、开户机构、开户日期、余额）账户交易信息（交易编号、账户名、交易对手账户号、交易日期、交易类型、交易金额）账户信息账户号(PK)账户名称币种开户机构开户日期销户日期余额个人客户信息客户编号(PK)姓名开户机构年龄性别 账户交易信息交易编号(PK)账号账户号（FK）交易对手账户账户号（FK）交易类型代码交易日期(PK)交易金额 图1 逻辑模型图1） 请遵循3NF来补充逻辑模型图【图1】中缺失的关系和属性，使模型能够反映出交易信息实体和个人客户信息实体之间的关系。（6分）2） 请设计一个实体用来描述（存储）账户余额的每天变化过程，包括实体、属性（主键）、关系。（6分）账户余额历史表账户号（PK）(FK)开始日期(PK)结束日期4. 假设北京分行有一张区域联行客户表，表的PK字段为Sys_Cust_Id，抽样数据如下。（6分）CUST_MGN_INFO_100区域代码客户类型系统内外标志客户联行号Region_CdCust_Type_CdSys_IndSys_Cust_Id1108001190110115311103102901101153211025621901101153311025432901101153411045619011011535110763219011011536现总行有一张全量联行客户表（CUST_MGN_INFO），2张表结构完全一致，要求编写 SQL找出CUST_MGN_INFO_110与CUST_MGN_INFO有差异的记录。CUST_MGN_INFO_110表有差异的记录:Select * from CUST_MGN_INFO_110ExceptSelect * from CUST_MGN_INFOCUST_MGN_INFO表有差异的记录:Select * from CUST_MGN_INFOExceptSelect * from CUST_MGN_INFO_110利用表的主键判断：Select * from CUST_MGN_INFO_110 c1 , CUST_MGN_INFO c where c1. Sys_Cust_Id =c. Sys_Cust_Id and (c1.Region_Cdc. Region_Cd or c1.Cust_Type_Cdc.Cust_Type_Cd or c1.Sys_Indc. Cust_Type_Cd)5.竖表转横表【表1】为贷款余额历史表字段，字段类型、字段中文含义说明等。LOAN_ACCT_BAL_HIS(贷款余额历史)NAMEHEADERDataTypeIsPKIsFKNull OptionLn_Acct_Id贷款账号CHAR(21)YESNONOT NULLCurr_Cd币种，人民币：CNYCHAR(3)YESYESNOT NULLStart_Dt开始日期DATEYESNONOT NULLBanlance贷款余额DECIMAL(18,4)NONONOT NULLEnd_Dt结束日期DATENONONOT NULL【表1】贷款账户余额历史 为了得到【表2】结果，请完成SQL编写年度一季度人民币贷款余额二季度人民币贷款余额三季度人民币贷款余额四季度人民币贷款余额201210001000999999992013123141441414346【表2】结果表 表格口径说明： 每季度人民币贷款余额：是所有账户季末日的余额合计； 年度取2012，2013两年。使用一张临时表EMP，然后插入数据：DECLARE GLOBAL TEMPORARY TABLE SESSION.EMP( J_D INTEGER TITLE 年度 , J_D_1 INTEGER TITLE 一季度人民币贷款余额 , J_D_2 INTEGER TITLE 二季度人民币贷款余额 , J_D_3 INTEGER TITLE 三季度人民币贷款余额, J_D_4 INTEGER TITLE 四季度人民币贷款余额 ,)ON COMMIT DELETE ROWS;Insert into EMP values (2012,Select cast ( sum( Banlance ) as integer) from LOAN_ACCT_BAL_HIS Where Start_D t 2012.3.301 ,Select cast ( sum( Banlance ) as integer) from LOAN_ACCT_BAL_HIS Where Start_D t 2012.6.30 ,Select cast ( sum( Banlance ) as integer) from LOAN_ACCT_BAL_HIS Where Start_D t 2012.9.30 ,Select cast ( sum( Banlance ) as integer) from LOAN_ACCT_BAL_HIS Where Start_D t 2012.12.31 ,);2013年同上。6.为了业务查询的需要，需求部门要求数据仓库系统保存银行账户每日的历史时点数据，假设数据字典如下：英文名称中文名称类型长度格式DT_DATE数据日期DATE10YYYY-MM-DDACCT_NO账号CHAR899999999CCY币种CHAR3BAL余额DEC15.299999.99每日银行的账务系统将批量导出当日账户余额文件到数据仓库。文件接口中包含账户、余额两个字段，文件内容示例如下：ACCT_NOCCYBAL32014209CNY2000.0032014211CNY3000.0031014211HKD4000.0031014209USD5000.00数据仓库需要按照上述需求存储这些账户余额信息。并且由于某些限制，账务系统与数据仓库约定如下：l 文件中每个账号一条记录。l 如果文件中缺少某个账户，则表示这个账户销户了，数据仓库需要将这个账户的余额修改为0.l 当天新开户的账户也包含在这个文件中，没有特别标志，需要数据仓库识别。 举例来说：如果数据仓库中前一日的账户及余额信息为：ACCT_NOCCYBAL32014209CNY2000.0032014211CNY3000.0031014211HKD4000.0031014209USD5000.00 而账务系统当日给出的文件为：ACCT_NOCCYBAL32014209CNY1000.0032014211CNY3000.0031014212CNY4000.0031014209USD4000.00 则需要数据仓库识别出：32014209账户余额由2000变为100031014209账户余额由5000变为400032014211账户余额未变化31014212为当日新开账户31014211已经销户（余额置为0）问题1：考虑到账户数量巨大，且并非所有的账户余额均有每日变动，设计人员拟采用拉链方式来存储这些余额历史。请给出这张账户余额历史表的表结构。（4分）账户余额历史表 h英文名称中文名称 类型格式约束ACCT_NO账号CHAR(8)PK FKCCY币种CHAR(3)PK FK Start_Dt开始日期DATE(10)YYYY-MM-DDPKEnd_Dt结束日期DATE(10)YYYY-MM-DDNOT NULLBAL余额DEC (15,2)NOT NULL问题2：请根据约定规则，写出该表每日的ETL加载脚本（给出SQL即可）。（10分）Truncate table xxx_减量; -减量Insert into xxx_减量Select * from ACCT_BAL_HIS aLeft join ACCT_BAL bOn a. acct_no = b. acct_no and a. CCY = b. CCY and a. BAL = b. BALWhere a.end_dt = 30001231 and b.acct_no is null拉链表所有字段要进行匹配，b.acct_no is null 指没有匹配上的记录，即有变化的记录。Truncate table xxx_增量;-增量Insert into xxx_增量 Select * from ACCT_BAL aLeft join ACCT_BAL_HIS bOn a. acct_no = b. acct_no and a. CCY = b. CCY and a. BAL = b. BAL and b.end_dt = 30001231Where b.acct_no is null-销户，更新拉链表Update ACCT_BAL_HIS aSet a.end_dt = etl_dt and case when (Select count( a_b_h.ACCT_NO) from ACCT_BAL_HIS a_b_h where a_b_h.ACCT_NO not exists (Select a_b.ACCT_NO from ACCT_BAL a_b ) )=1then a.bal = 0 else a.bal= b.bal endWhere a.acct_no exists ( select b.acct_no from xxx_减量) -新开账户，更新拉链表Insert into ACCT_BAL_HIS( acct_no , Ccy , Bal , St_dt , End_dt)Select acct_no , Ccy , Bal, Etl_dt,30001231 from xxx_增量; 1、采集当日全量数据到ND(NewDay)表； 2、可从历史表中取出昨日全量数据存储到OD(OldDay)表; 3、(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示； 4、(OD-ND)为状态到此结束需要封链的数据，用W_U表示； 5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值； 6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作；原理解释： 1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据； CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS; CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS; 2、获取当日全量数据 INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce; 3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表； INSERT INTO VT_xxxx_CHG(xx) SELECT xx FROM VT_xxxx_NEW WHERE (xx) NOT IN (select xx from xxxx_HIS where end_date=max_date); 4、更新历史表的失效记录的end_date为max值 UPDATE xxxx_HIS SET End_Date=current_date AND A1.End_Date=max_date; 5、将新增或者有变化的数据插入目标表*/ INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG;问题3：业务人员需要统计每个账户20