问答题
阅读下列说明，回答下列问题。
[说明]
某健身俱乐部要开发一个信息管理系统，该信息系统的部分关系模式如下：
员工( 员工身份证号 ，姓名，工种，电话，住址)
会员( 会员手机号 ，姓名，折扣)
项目( 项目名称 ，项目经理，价格)
预约单( 会员手机号，预约日期 ，
，使用时长)
消费( 流水号 ，
，
[说明]
某健身俱乐部要开发一个信息管理系统，该信息系统的部分关系模式如下：
员工( 员工身份证号 ，姓名，工种，电话，住址)
会员( 会员手机号 ，姓名，折扣)
项目( 项目名称 ，项目经理，价格)
预约单( 会员手机号，预约日期 ，
，使用时长)
消费( 流水号 ，
，
问答题
请将下面创建消费关系的SQL语句的空缺部分补充完整，要求指定关系的主码、外码，以及消费金额大于零的约束。
CREATE TABLE 消费 (
流水号 CHAR(12) __(a)__ ,
会员手机号 CHAR(11),
项目名称 CHAR (8),
消费金额 NUMBER __(b)__ ,
消费日期 DATE,
__(c)__ ,
__(e)__ ,
);
CREATE TABLE 消费 (
流水号 CHAR(12) __(a)__ ,
会员手机号 CHAR(11),
项目名称 CHAR (8),
消费金额 NUMBER __(b)__ ,
消费日期 DATE,
__(c)__ ,
__(e)__ ,
);
【正确答案】
【答案解析】(a)PRIMARY KEY(或NOTNULL UNIQUE)
(b)CHECK (消费金额＞0)
(c)FOREIGN KEY (会员手机号) REFERENCES 会员 (会员手机号)
(d)FOREIGN KEY (项目名称) REFERENCES 项目 (项目名称) [解析] 本题考查SQL的应用，属于比较传统的题目，考查点也与往年类似。
本问题考查数据定义语言DDL和完整性约束。根据题意，需要对“消费”表的“流水号”加主键(或非空)约束，考查实体完整性约束，对应的语法为：
PRIMARY KEY (或NOT NULL UNIQUE)
“消费金额”需要大于0，所以需要加Check约束，对应的语法为：
CHECK (消费金额＞0)
“会员手机号”是“会员”关系的主键，是“消费”关系的外键，考查参照完整性约束，需要增加外键约束，对应的语法为：
FOREICNKEY (会员手机号) REFERENCES 会员 (会员手机号)
“项目名称”是“项目”关系的主键，是“消费”关系的外键，考查参照完整性约束，需要增加外键约束，对应的语法为：
FOREIGN KEY (项目名称) REFERENCES 项目 (项目名称)、
(b)CHECK (消费金额＞0)
(c)FOREIGN KEY (会员手机号) REFERENCES 会员 (会员手机号)
(d)FOREIGN KEY (项目名称) REFERENCES 项目 (项目名称) [解析] 本题考查SQL的应用，属于比较传统的题目，考查点也与往年类似。
本问题考查数据定义语言DDL和完整性约束。根据题意，需要对“消费”表的“流水号”加主键(或非空)约束，考查实体完整性约束，对应的语法为：
PRIMARY KEY (或NOT NULL UNIQUE)
“消费金额”需要大于0，所以需要加Check约束，对应的语法为：
CHECK (消费金额＞0)
“会员手机号”是“会员”关系的主键，是“消费”关系的外键，考查参照完整性约束，需要增加外键约束，对应的语法为：
FOREICNKEY (会员手机号) REFERENCES 会员 (会员手机号)
“项目名称”是“项目”关系的主键，是“消费”关系的外键，考查参照完整性约束，需要增加外键约束，对应的语法为：
FOREIGN KEY (项目名称) REFERENCES 项目 (项目名称)、
问答题
(1)手机号为18812345678的客户预约了2014年3月18日两个小时的羽毛球场地，消费流水号由系统自动生成。请将下面SQL语句的空缺部分补充完整。
INSERT INTO 消费(流水号，会员手机号，项目名称，消费金额，消费日期)
SELECT‘201403180001’,‘18812345678’,‘羽毛球’, __(e)__ ,
‘2014/3/18’
FROM 会员, 项目, 预约单
WHERE 预约单项目名称=项目.项目名称 AND __(f)__
AND 项目.项目名称=‘羽毛球’
AND 会员.会员手机号=‘18812345678’;
(2)需要用触发器来实现会员等级折扣的自动维护，函数float vip_value(char(11)会员手机号)依据输入的手机号计算会员的折扣。请将下面SQL语句的空缺部分补充完整。
CREATE TRIGGER VIP_TRG AFTER __(g)__ ON __(h)__
REFERENCING new row AS nrow
FOR EACH ROW
BEGIN
UPDATE 会员
SET __(i)__
WHERE __(j)__ ;
END
INSERT INTO 消费(流水号，会员手机号，项目名称，消费金额，消费日期)
SELECT‘201403180001’,‘18812345678’,‘羽毛球’, __(e)__ ,
‘2014/3/18’
FROM 会员, 项目, 预约单
WHERE 预约单项目名称=项目.项目名称 AND __(f)__
AND 项目.项目名称=‘羽毛球’
AND 会员.会员手机号=‘18812345678’;
(2)需要用触发器来实现会员等级折扣的自动维护，函数float vip_value(char(11)会员手机号)依据输入的手机号计算会员的折扣。请将下面SQL语句的空缺部分补充完整。
CREATE TRIGGER VIP_TRG AFTER __(g)__ ON __(h)__
REFERENCING new row AS nrow
FOR EACH ROW
BEGIN
UPDATE 会员
SET __(i)__
WHERE __(j)__ ;
END
【正确答案】
【答案解析】(1)(e)价格*使用时长*折扣
(f)预约单.会员手机号=会员.会员手机号
(2)(g)INSERT
(h)消费
(i)折扣=vip_value(nrow.会员手机号)
(j)会员.会员手机号=nrow.会员手机号 [解析] 本问题考查数据操纵语言DML。
(1)本题考查一个较完整的查询语句，需要向“消费”关系插入新元组。
SELECT子句缺少“消费金额”。消费金额=价格*使用时长*折扣。
WHERE子句缺少“预约单”关系和“会员”关系按照“会员手机号”的连接，因此应该增加“预约单.会员手机号=会员.会员手机号”。
(2)本题考查触发器，触发器是一个能由系统自动执行对数据库修改的语句。一个触发器由事件、条件和动态三部分组成：事件是指触发器将测试条件是否成立，若成立就执行相应的动作，否则就什么也不做；动态是指若触发器测试满足预定的条件，那么就由数据库管理系统执行这些动作。本题首先定义触发器的事件，用触发器来实现会员等级折扣的自动维护。
(g)和(h)缺少向“消费”关系插入的语句，因此应该分别补充“INSERT”和“消费”。
(i)语句调用vip_value函数实现会员折扣的更新，函数参数为会员手机号，因此应该补充“折扣=vip_valeLe(nrow. 会员手机号)”。
(j)语句实现“会员”关系和“nrow”关系按照“会员手机号”的连接，因此应该补充“会员. 会员手机号=nrow. 会员手机号”。
(f)预约单.会员手机号=会员.会员手机号
(2)(g)INSERT
(h)消费
(i)折扣=vip_value(nrow.会员手机号)
(j)会员.会员手机号=nrow.会员手机号 [解析] 本问题考查数据操纵语言DML。
(1)本题考查一个较完整的查询语句，需要向“消费”关系插入新元组。
SELECT子句缺少“消费金额”。消费金额=价格*使用时长*折扣。
WHERE子句缺少“预约单”关系和“会员”关系按照“会员手机号”的连接，因此应该增加“预约单.会员手机号=会员.会员手机号”。
(2)本题考查触发器，触发器是一个能由系统自动执行对数据库修改的语句。一个触发器由事件、条件和动态三部分组成：事件是指触发器将测试条件是否成立，若成立就执行相应的动作，否则就什么也不做；动态是指若触发器测试满足预定的条件，那么就由数据库管理系统执行这些动作。本题首先定义触发器的事件，用触发器来实现会员等级折扣的自动维护。
(g)和(h)缺少向“消费”关系插入的语句，因此应该分别补充“INSERT”和“消费”。
(i)语句调用vip_value函数实现会员折扣的更新，函数参数为会员手机号，因此应该补充“折扣=vip_valeLe(nrow. 会员手机号)”。
(j)语句实现“会员”关系和“nrow”关系按照“会员手机号”的连接，因此应该补充“会员. 会员手机号=nrow. 会员手机号”。
问答题
请将下面SQL语句的空缺部分补充完整。
(1)俱乐部年底对各种项目进行绩效考核，需要统计出所负责项目的消费总金额大于等于十万元的项目和项目经理，并按消费金额总和降序输出。
SELECT 项目.项目名称, 项目经理, SUM(消费金额)
FROM 项目, 消费
WHERE __(k)__
GROUP BY __(l)__
ORDER BY __(m)__ ;
(2)查询所有手机号码以“888”结尾，姓“王”的员工姓名和电话。
SELECT 姓名, 电话
FROM 员工
WHERE 姓名 __(n)__ AND 电话 __(o)__ .
(1)俱乐部年底对各种项目进行绩效考核，需要统计出所负责项目的消费总金额大于等于十万元的项目和项目经理，并按消费金额总和降序输出。
SELECT 项目.项目名称, 项目经理, SUM(消费金额)
FROM 项目, 消费
WHERE __(k)__
GROUP BY __(l)__
ORDER BY __(m)__ ;
(2)查询所有手机号码以“888”结尾，姓“王”的员工姓名和电话。
SELECT 姓名, 电话
FROM 员工
WHERE 姓名 __(n)__ AND 电话 __(o)__ .
【正确答案】
【答案解析】(1)(k)项目.项目名称=消费.项目名称
(1)项目.项目名称，项目经理 HAVING SUM(消费金额)＞=100000
(m)SUM(消费金额) DESC
(2)(n)LIKE‘王%’
(o)LIKE‘%888’ [解析] 本问题考查数据操纵语言DML。
(1)本题考查一个较完整的查询语句，知识点包括多表查询、集函数、查询分组、分组条件和排序查询结果。查询涉及“项目”和“消费”关系模式。用集函数SUM(消费金额)求消费总金额，若有GROUP BY子句，则集函数作用在每个分组上，且GROUP BY之后应包含除了集函数之外的所有结果列。若GROUP BY之后跟有HAVING子句，则只有满足条件的分组才会输出。“ORDER BY列名[ASCD|DESC]”对输出结果进行升序或降序的排列，若不明确制定法升序或降序，则默认升序排列。
(2)本题考查用关键字LIKE进行字符匹配。
LIKE的语法为：
[NOT] LIKE‘＜匹配串＞’
其中，匹配串可以是一个完整的字符串，也可以含有通配符%和，其中%代表任意长度(包括0长度)的字符串， 代表单个字符。手机号码以“888”结尾，姓“王”的员工对用的表示为：姓名LIKE‘王%’AND电话LIKE‘%888’。
(1)项目.项目名称，项目经理 HAVING SUM(消费金额)＞=100000
(m)SUM(消费金额) DESC
(2)(n)LIKE‘王%’
(o)LIKE‘%888’ [解析] 本问题考查数据操纵语言DML。
(1)本题考查一个较完整的查询语句，知识点包括多表查询、集函数、查询分组、分组条件和排序查询结果。查询涉及“项目”和“消费”关系模式。用集函数SUM(消费金额)求消费总金额，若有GROUP BY子句，则集函数作用在每个分组上，且GROUP BY之后应包含除了集函数之外的所有结果列。若GROUP BY之后跟有HAVING子句，则只有满足条件的分组才会输出。“ORDER BY列名[ASCD|DESC]”对输出结果进行升序或降序的排列，若不明确制定法升序或降序，则默认升序排列。
(2)本题考查用关键字LIKE进行字符匹配。
LIKE的语法为：
[NOT] LIKE‘＜匹配串＞’
其中，匹配串可以是一个完整的字符串，也可以含有通配符%和，其中%代表任意长度(包括0长度)的字符串， 代表单个字符。手机号码以“888”结尾，姓“王”的员工对用的表示为：姓名LIKE‘王%’AND电话LIKE‘%888’。