某航空公司要开发一个订票信息处理系统，该系统部分关系模式如下：航班(航班编号，航空公司，起飞地，起飞时间，目地，到达时间，票价)折扣(航班编号，开始日期，结束日期，折扣)旅客(身份证号，姓名，性别，出生日期，电话，VIP折扣)购票(购票单号，身份证号，航班编号，搭乘日期，购票金额)有关关系模式属性及相关说明如下：（1）航班表中起飞时间和到达时间不包含日期，同一航班不会在一天出现两次及两次以上；（2）各航空公司会根据旅客出行淡旺季适时调整机票折扣，旅客购买机票购票金额计算公式为：票价×折扣×VIP折扣，其中旅客VIP折扣与该旅客已购买过机票购票金额总和相关，在旅客每次购票后被修改。VIP折扣值计算由函数float vip_value(char[18]身份证号)完成。根据以上描述，回答下列问题。
【问题1】请将如下创建购票关系SQL语句空缺部分补充完整，要求指定关系主键、外键，以及购票金额大于零约束。CREATE TABLE 购票(购票单号 CHAR(15) ___(a)___,身份证号 CHAR(18),航班编号 CHAR(6),搭乘日期 DATE,购票金额 FLOAT __(b)__,___(c)__,___(d)__,);
【问题2】（1）身份证号为210000196006189999客户购买了2013年2月18日CA5302航班机票，购票单号由系统自动生成。下面SQL语句将上述购票信息加入系统中，请将空缺部分补充完整。INSERT INTO 购票(购票单号,身份证号,航班编号,搭乘日期,购票金额)SELECT '201303105555','210000196006189999','CA5302','2013/2/18',__(e)__FROM 航班,折扣,旅客WHERE __(f)__ AND 航班.航班编号='CA5302'AND '2013/2/18' BETWEEN 折扣.开始日期 AND 折扣.结束日期AND 旅客.身份证号='210000196006189999';
（2）需要用触发器来实现VIP折扣修改，调用函数vip_value()来实现。请将如下SQL语句空缺部分补充完整。CREATE TRIGGER VIP_TRG AFTER ___(g)___ ON ___(h)___RE FERENCING new row AS nrowFOR EACH rowBEGINUPDATE 旅客SET ___(i)___WHERE ___(j)___;END
【问题3】请将如下SQL语句空缺部分补充完整。
（1）查询搭乘日期在2012年1月1日至2012年12月31日之间，且合计购票金额大于等于10000元所有旅客身份证号、姓名和购票金额总和，并按购票金额总和降序输出。SELECT 旅客.身份证号,姓名,SUM(购票金额)FROM 旅客,购票WHERE ___(k)___GROUP BY ___(l)___;ORDER BY ___(m)___;
（2）经过中转航班与相同始发地和目地直达航班相比，会享受更低折扣。查询从广州到北京，经过一次中转所有航班对，输出广州到中转地航班编号、中转地、中转地到北京航班编号。SELECT ___(n)___FROM 航班航班1,航班 航班2WHERE ___(o)___
正确答案及解析
正确答案
解析
【问题1】(a) PRIMARYKEY （或NOT NULL UNIQUE)(b) CHECK （购票金额＞ 0)(c) FOREIGN KEY (身份证号） REFERENCES 旅客（身份证号）(d) FOREIGN KEY (航班编号） REFERENCES 航班（航班编号）
【问题2】(e)票价*折扣*VIP折扣(f)航班．航班编号＝折扣．航班编号(g) INSERT(h)购票(i) VIP折扣＝ vip _ value(nrow.身份证号）(j)旅客．身份证号＝ nrow.身份证号
【问题3】(k)身份证号=购票.身份证号 AND 搭乘日期 BETWEEN '2012/1/1' AND '2012/12/31'(l)旅客.身份证号,姓名 HAVlNG SUM(购票金额)＞=10000(m)SUM(购票金额) DESC(n)航班1.航班编号,航班1.目地,航班2.航班编号(o)航班1.起飞地='广州' AND 航班2.目地='北京' AND 航班1.目地=航班2.起飞地
【问题1】(a) PRIMARYKEY （或NOT NULL UNIQUE)(b) CHECK （购票金额＞ 0)(c) FOREIGN KEY (身份证号） REFERENCES 旅客（身份证号）(d) FOREIGN KEY (航班编号） REFERENCES 航班（航班编号）
【问题2】(e)票价*折扣*VIP折扣(f)航班．航班编号＝折扣．航班编号(g) INSERT(h)购票(i) VIP折扣＝ vip _ value(nrow.身份证号）(j)旅客．身份证号＝ nrow.身份证号
【问题3】(k)身份证号=购票.身份证号 AND 搭乘日期 BETWEEN '2012/1/1' AND '2012/12/31'(l)旅客.身份证号,姓名 HAVlNG SUM(购票金额)＞=10000(m)SUM(购票金额) DESC(n)航班1.航班编号,航班1.目地,航班2.航班编号(o)航班1.起飞地='广州' AND 航班2.目地='北京' AND 航班1.目地=航班2.起飞地
你可能感兴趣的试题
The Internet of（ 1）（IoT） describes physical objects that are embedded with Sensor，processing abilities，softwares，and other technologies that connect with other devices and systems over the （ 2） or other communication and exchange data networks .Over the past few years, IoT has become one of the most important technologies of the （ 3 ） centuryWe can connect objects to the Internet via embedded devices. By means of（ 4） computing, the cloud, big data, and mobile technologies, physical things can share and collect data with minimal human intervention. Traditional fields of embedded systems，wireless （ 5） networks （WSNs），control systems，automation, independently and collectively enable IoT.回答5处
-
- A.sensor
- B.searching
- C.service
- D.source
- 查看答案
The Internet of（ 1）（IoT） describes physical objects that are embedded with Sensor，processing abilities，softwares，and other technologies that connect with other devices and systems over the （ 2） or other communication and exchange data networks .Over the past few years, IoT has become one of the most important technologies of the （ 3 ） centuryWe can connect objects to the Internet via embedded devices. By means of（ 4） computing, the cloud, big data, and mobile technologies, physical things can share and collect data with minimal human intervention. Traditional fields of embedded systems，wireless （ 5） networks （WSNs），control systems，automation, independently and collectively enable IoT.回答4处
-
- A.low-level
- B.low-cost
- C.high-cost
- D.high-performance
- 查看答案
The Internet of（ 1）（IoT） describes physical objects that are embedded with Sensor，processing abilities，softwares，and other technologies that connect with other devices and systems over the （ 2） or other communication and exchange data networks .Over the past few years, IoT has become one of the most important technologies of the （ 3 ） centuryWe can connect objects to the Internet via embedded devices. By means of（ 4） computing, the cloud, big data, and mobile technologies, physical things can share and collect data with minimal human intervention. Traditional fields of embedded systems，wireless （ 5） networks （WSNs），control systems，automation, independently and collectively enable IoT.回答3处
-
- A.19th
- B.20th
- C.21th
- D.22th
- 查看答案
The Internet of（ 1）（IoT） describes physical objects that are embedded with Sensor，processing abilities，softwares，and other technologies that connect with other devices and systems over the （ 2） or other communication and exchange data networks .Over the past few years, IoT has become one of the most important technologies of the （ 3 ） centuryWe can connect objects to the Internet via embedded devices. By means of（ 4） computing, the cloud, big data, and mobile technologies, physical things can share and collect data with minimal human intervention. Traditional fields of embedded systems，wireless （ 5） networks （WSNs），control systems，automation, independently and collectively enable IoT.回答2处
-
- A.path
- B.Internet
- C.route
- D.switch
- 查看答案
The Internet of（ 1）（IoT） describes physical objects that are embedded with Sensor，processing abilities，softwares，and other technologies that connect with other devices and systems over the （ 2） or other communication and exchange data networks .Over the past few years, IoT has become one of the most important technologies of the （ 3 ） centuryWe can connect objects to the Internet via embedded devices. By means of（ 4） computing, the cloud, big data, and mobile technologies, physical things can share and collect data with minimal human intervention. Traditional fields of embedded systems，wireless （ 5） networks （WSNs），control systems，automation, independently and collectively enable IoT.回答1处
-
- A.this
- B.thing
- C.think
- D.things
- 查看答案