二、关系查询(每小题6分,共30分)
一个由5张表组成的订单管理数据库OrderDB,包括客户表Customer、员工表 Employee、商品表Product、订单主表OrderMaster和订单明细表OrderDetail, 关系模式如表1~表5所示。
1.试写出下列各题的关系代数表达式
(1)查找“江西省赣州市”的客户购买商品的明细情况,要求返回客户名称、订单日期、商品编号和销售数量。说明:可通过客户的邮政编码来识别客户,“江西省赣州市”的邮政编码以“3410”开头。
(2)查找购买过所有编号以"TV01”开头的商品的客户编号,以及他/她们在2015年购买商品情况(包括商品名称、购买日期和购买数量)。
1.试用SQL语句创建该数据库、与数据表,并为每张表插入一条自定义数据。
2.试写出下列各题的SQL查询语句
(1)查找每个业务员的销货记录,要求显示业务员姓名、客户名称、商品编号、销货日期、销售数量和销售金额。
(2)找出2015年销售数量超过50的商品对于名称中包含“宏祥”的客户的销售情况,要求显示订单编号、客户名称、商品名称、销售数量和销售金额,并按客户名称升序、销售数量降序排序输出。
(3)查询在同一个订单中既销售了“52 倍速光驱”又销售了“120GB硬盘”商品的业务员的编号、姓名以及他们所销售的每一种 商品的商品名称和销售总金额。
表1 客户表 Customer 的模式
属性含义
属性名
数据类型
客户编号
customerNo
char(9)
客户名称
customerName
varchar(40)
客户住址
address
varchar(40)
客户电话
telephone
varchar(20)
邮政编码
zip
char(6)
表2 员工表 Employee的模式
属性含义
属性名
数据类型
员工编号
employeeNo
char(8)
员工姓名
employeeName
varchar(10)
性别
sex
char(1)
出生日期
birthday
datetime
住址
address
varchar(50)
电话
telephone
varchar(20)
雇佣日期
hireDate
datetime
所属部门
department
varchar(30)
职务
headShip
varchar(10)
薪水
salary
numeric(8,2)
表3 商品基本信息表Produet的模式
属性含义
属性名
数据类型
商品编号
productNo
char(9)
商品名称
productName
varchar(40)
商品类别
productClass
varchar(20)
表4 订单主表 OrderMaster 的模式
属性含义
属性名
数据类型
订单编号
orderNo
char(12)
客户编号
customer No
char(9)
业务员编号
salerNo
char(8)
订单日期
orderDate
datetime
订单金额
orderSum
numeric(9,2)
发票号码
invoiceNo
char(10)
表5 订单明细表 OrderDetail的模式
属性含义
属性名
数据类型
订单编号
orderNo
char(12)
商品编号
productNo
char(9)
销售数量
quantity
int
销售单价
price
numeric(7,2)