DB2-SQL测试

考试说明

环境说明：

数据库版本：DB2V9.7

数据库名：TRAMSV3

数据库主机IP：192.168.32.8

数据库端口：50000

数据库用户名：nc001

数据库密码：aaaaaa

除考试相关数据表外，数据库内其他数据表禁止更改

答题格式：

SQL语句:  调试完毕，复制至本试卷

如：SELECT * FROM MONITORBUSSINESSSYSTEM1

输出结果:  屏幕截图，复制至本试卷

 

数据字典说明

业务系统表，MONITORBUSSINESSSYSTEM1

字段名	字段描述	类型	主键	可空	说明
ID	编号	VARCHAR(30)	Y	N	主键
NAMES	名称	VARCHAR(100)
ORDERS	顺序	INTEGER
NOTE	说明	VARCHAR(200)

 

监控主机表，MONITORINSTANCE1

字段名	字段描述	类型	主键	可空	说明
ID	编号	VARCHAR(30)	Y	N	主键
NAMES	名称	VARCHAR(100)		N
IP	Ip地址	VARCHAR(15)		N
ORDERS	顺序	INTEGER		N
BUSSINESSSYSTEMID	业务系统编号	VARCHAR(30)
INSTANCETYPEID	主机类型编号	VARCHAR(30)

主机类型表：INSTANCETYPE

字段名	字段描述	类型	主键	可空	说明
ID	主机类型编号	VARCHAR(30)	Y	N	主键
NAMES	主机类型名称	VARCHAR(100)

监控项表：MONITORITEM1

字段名	字段描述	类型	主键	可空	说明
ID	监控项编号	VARCHAR(30)	Y	N	主键
INSTANCEID	监控主机编号	VARCHAR(30)
NAMES	监控项名称	VARCHAR(100)		N
ORDERS	顺序	INTEGER		N
NOTE	说明	VARCHAR(200)

 

监控项日志每日报警统计表：LOGSTATUS_DAY1

字段名	字段描述	类型	主键	可空	说明
DATES	日期	VARCHAR(10)	Y	N	主键
MONITORITEMID	监控项编号	VARCHAR(30)	Y	N	主键
STATUS	报警状态	VARCHAR(1)	Y	N	主键
NUM	报警数量	INTEGER

 

监控项日志报警明细表：LOGSTATUSALARMHISTORY

字段名	字段描述	类型	主键	可空	说明
ID	编号	VARCHAR(30)	Y	N	主键
MONITORITEMID	监控项编号	VARCHAR(30)
STATUS	报警状态	CHAR(1)
LASTSTATUS	上次状态	CHAR(1)
DATES	报警日期	VARCHAR(10)
TIMES	报警时间	VARCHAR(8)
ALARMCONTENT	报警内容	VARCHAR(600)

说明：

业务系统表，定义业务系统，一个业务系统包含多个监控主机

监控主机表，定义监控主机，BUSSINESSSYSTEMID字段定义归属与哪个业务系统，INSTANCETYPEID定义归属与哪个主机类型，一个监控主机包含多个监控项

监控项表, 定义监控项，INSTANCEID字段定义归属与哪个监控主机。

主机类型表，定义主机类型参数字典。

监控项日志每日报警统计表，存储每个监控项每日对应的各种状态统计数量。

监控项日志报警明细表，存储每个监控项的每日报警明细信息。

报警字段STATUS内容含义：

2	预警
3	报警
4	数据获取异常
5	网络中断

考试题目

答案附题目后即可。

1.新建数据表，表名以个人姓名首字母（如周广刚（ZGG））命名，字段如下：

字段名	字段描述	类型	主键	可空	说明
ID	编号	VARCHAR(30)	Y	N	主键
MONITORITEMID	监控项编号	VARCHAR(30)
STATUS	报警状态	CHAR(1)
DATES	报警日期	VARCHAR(10)
TIMES	报警时间	VARCHAR(5)

将上述表字段TIMES数据类型修改为VARCHAR(8)

将LOGSTATUSALARMHISTORY中日期为2019年7月的数据的对应字段插入到该数据表中，写出建表语句、字段修改语句、数据插入语句。

建表语句:

CREATE TABLE ZRZ (    
    ID VARCHAR(30) NOT NULL PRIMARY KEY,    
    MONITORITEMID VARCHAR(30) ,    
    STATUS VARCHAR(1) ,    
    DATES VARCHAR(10) ,    
    TIMES VARCHAR(5)
) 

 

字段修改语句

ALTER TABLE ZRZ ALTER TIMES SET DATA TYPE VARCHAR(8)

数据插入语句

INSERT INTO ZRZ(ID,MONITORITEMID,STATUS,DATES,TIMES)
SELECT ID,MONITORITEMID,STATUS,DATES,TIMES 
FROM LOGSTATUSALARMHISTORY 
WHERE YEAR(DATE(DATES))=2018 AND MONTH(DATE(DATES))=7

筛选条件或者是 WHERE SUBSTR(DATES,1,7)='2018-07'

WHERE SUBSTR(DATES,1,7)='2018-07'

2.查询主机IP为“192.168.32.25”，日期为2019-07-01，明细报警信息，按照日期时间倒序排列，写出查询语句即可。

SELECT A.ID,A.MONITORITEMID,A.STATUS,A.LASTSTATUS,A.DATES,A.TIMES,A.ALARMCONTENT
FROM LOGSTATUSALARMHISTORY A         
    JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID         
    JOIN MONITORINSTANCE1 C N B.INSTANCEID=C.ID 
WHERE A.DATES='2019-07-01' AND C.IP='192.168.32.25' 
ORDER BY A.TIMES DESC

 

 

3.查询属于业务系统“网银web2”，日期为2019-07-01，报警状态为3的明细信息，写出查询语句即可。

SELECT A.ID,A.MONITORITEMID,A.STATUS,A.LASTSTATUS,A.DATES,A.TIMES,A.ALARMCONTENT 
FROM LOGSTATUSALARMHISTORY A         
JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID         
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID         
JOIN MONITORBUSSINESSSYSTEM1 D ON C.BUSSINESSSYSTEMID=D.ID 
WHERE A.DATES='2019-07-01' AND A.STATUS='3' AND D.NAMES='网银web2'

4.查询所有主机的2019-07-02的报警统计信息，按报警状态（升序），报警数量（降序）排列，显示字段：主机名称，报警状态，报警数量

写出查询语句，截图查询结果

SELECT C.NAMES AS 主机名称,COALESCE(A.STATUS,0) AS 报警状态,COALESCE(SUM(A.NUM),0) AS 报警数量
FROM MONITORINSTANCE1 C 
LEFT JOIN MONITORITEM1 B ON C.ID=B.INSTANCEID  
LEFT JOIN  LOGSTATUS_DAY1 A ON  A.MONITORITEMID=B.ID 
WHERE  A.DATES='2019-07-02' 
GROUP BY C.NAMES,A.STATUS,A.NUM 
ORDER BY A.STATUS ASC,A.NUM DESC

 

 

5.查询各业务系统2018年每月所有报警数量排名（月份升序、报警数量倒序）

显示字段：业务系统名称、月份、报警数量

写出查询语句，截图查询结果。

SELECT D.NAMES,MONTH(A.DATES) AS MONTHS,SUM(A.NUM) AS SUMNUM 
FROM LOGSTATUS_DAY1 A         
JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID         
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID         
JOIN MONITORBUSSINESSSYSTEM1 D ON C.BUSSINESSSYSTEMID=D.ID 
WHERE YEAR(A.DATES)=2018 
GROUP BY D.NAMES, MONTH(A.DATES) 
ORDER BY MONTHS ASC, SUMNUM DESC

 

 

6.查询主机类型为WIN的报警数量统计，显示字段：

主机名称、预警数量、报警数量、数据获取异常数量、网络中断数量

写出查询语句，截图查询结果。

SELECT C.NAMES AS 主机名称, 
SUM(CASE A.STATUS WHEN '2' THEN A.NUM ELSE 0 END) AS 预警数量,        
SUM(CASE A.STATUS WHEN '3' THEN A.NUM ELSE 0 END) AS 报警数量,        
SUM(CASE A.STATUS WHEN '4' THEN A.NUM ELSE 0 END) AS 数据获取异常数量,        
SUM(CASE A.STATUS WHEN '5' THEN A.NUM ELSE 0 END) AS 网络中断数量 
FROM LOGSTATUS_DAY1 A 
LEFT JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID 
LEFT JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID 
WHERE C.INSTANCETYPEID='WIN' 
GROUP BY C.NAMES

 

 

7.统计2019年7月，各个主机类型的所有报警数量，如果没有则显示数量为0，写出查询语句，截图查询结果。

SELECT D.NAMES, COALESCE(SUNNUM,0) AS SUMNUM 
FROM INSTANCETYPE D 
LEFT JOIN (
SELECT C.INSTANCETYPEID, SUM(A.NUM) AS SUNNUM
FROM LOGSTATUS_DAY1 A                         
JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID                     
WHERE SUBSTR(A.DATES,1,7)='2018-07'                     
GROUP BY C.INSTANCETYPEID        
)E ON D.ID=E.INSTANCETYPEID

 

 

8.统计所有报警数量最多的主机前10名，写出查询语句，截图查询结果。

SELECT C.NAMES AS 主机名, SUM(A.NUM) AS 报警数量 
FROM LOGSTATUS_DAY1 A 
JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID 
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID 
GROUP BY C.NAMESORDER BY 报警数量 DESC 
FETCH FIRST 10 ROWS ONLY

 

 

9.按日期统计所有主机类型为LINUX的报警数量，按照报警数量倒序排名，显示字段：日期，数量，写出查询语句，截图查询结果。

SELECT A.DATES AS 日期, SUM(A.NUM) AS 数量 
FROM LOGSTATUS_DAY1 A 
LEFT JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID 
LEFT JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID 
WHERE C.INSTANCETYPEID='LINUX' 
GROUP BY A.DATES 
ORDER BY 数量 DESC

 

 

10.导出报警明细表中2019年7月所有数据，明细数据不包含ID字段，写出导出语句即可。

DB2 EXPORT TO LOGSTATUSALARMHISTORY.txt OF DEL 
SELECT MONITORITEMID,STATUS,LASTSTATUS,DATES,TIMES,ALARMCONTENT 
FROM LOGSTATUSALARMHISTORY 
WHERE SUBSTR(DATES,1,7)=’2019-07’