11级工作室数据库考试答案1级工作室数据库考试答案11级工作室数据库考试答案11级工作室数据库考试答案.doc

2013年（11级）电子商务工作室数据库试卷 答案写在答题纸上。 第1题：SQL操作题（50分） 查找业务部或会计部的女员工的基本信息。select emp_no,emp_name,dept from employee where sex=女 and dept in (业务,会计) 显示每种产品的销售金额总和，并依销售金额由大到小输出。select prod_id ,sum(qty*unit_price) from sale_item group by prod_id order by sum(qty*unit_price) desc 由employee表中查找出薪水最低的员工信息。select * from employee where salary= (select min(salary ) from employee ) 建立视图，该视图包含每种产品的总销售数量及总销售金额，要求显示出产品编号、产品名称，总数量及总金额。create view view22 as Select cust_id,prod_id,sum(qty) tot_qty,sum(qty*unit_price)/sum(qty) avg_price From sales a, sale_item b Where a.order_no=b.order_no Group by cust_id,prod_id 查询订购的产品至少包含了订单中所订购产品的订单。Select distinct order_no From sale_item a Where order_no10003and not exists ( Select * from sale_item b where order_no =10003 and not exists (select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id)) 为订单明细表按产品编号升序建一索引，产品编号相同的再按订单编号降序排列。（5分） create index index1 on sale_item(prod_id,order_no desc) 创建一个触发器，当对sale_item表增、删、改记录时，及时更新sales表中的订单金额tot_amt，以保证数据的完整性。（10分） create trigger update_sales on sale_item after insert,delete,update as update sales set tot_amt=(select sum(unit_price*qty) from sale_item where order_no=sales.order_no) 创建一存储过程，给定某业务员编号，要求查询出该业务员的姓名，所销售订单的订单号，顾客姓名，订货时间及订单金额。（10分） create proc proc1 @sale_id varchar(5) as select emp_name,order_no,cust_name,order_date,tot_amt from employee a,sales b,customer c where a.emp_no=b.sale_id and b.cust_id=c.cust_id and sale_id=@sale_id exec proc1 E0008 第2题：数据库设计题（50分） 1、画出该系统的E-R图（15分） 2、写出关系模式，并标明主、外键字段（主键加下划线，外键加波浪线）（15分） 球队（球队编号、球队名称、代表地区、成立时间、联系人姓名、联系人电话，教练编号） 球员（球员编号、姓名、年龄、身高、特长、性别、籍贯，所属球队） 教练（教练编号、姓名、年龄、联系电话、性别） 裁判（裁判编号、姓名、年龄、级别、联系电话） 比赛（比赛编号、甲队编号、乙队编号、裁判编号、比赛时间、甲队比分、乙队比分） 3、create database basketball go use basketball --创建教练表 create table coach (coach_ID int identity(1,1) primary key, coach_name varchar(20) not null, age smallint , sex char(2) not null check(sex in(男,女)), telephone varchar(20) ) --创建球队表 create table te