数据库考试题答案.doc

测验一、 题目选择：学号最后1位试题1,2第一套3,4第二套5,6第三套7,8第四套9,0第五套二、要求1、完成题目后将sql语句帖在对应题目下方。2、所有完成后将word文件保存为 “班级-学号后两位-姓名”方式：如“12-03-XXX”三、提示1、注意完成每一个小题后立即贴在word文档中。2、只能在本机上提交，不能在1个机器上提交多份。3、如有雷同试卷，都取消成绩。数据库附加company数据库，共有五张表，结构如下。按照要求完成以下各题。（1）/*员工人事表employee */emp_nochar(5)Not nullprimary key员工编号emp_namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)Not null所属部门titlechar(6)Not null职称date_hireddatetimeNot null到职日birthdaydatetimeNull生日salaryintNot null薪水addrchar(50)null住址（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)Not null,客户名称addrchar(40)Not null,客户住址tel_nochar(10)Not null,客户电话zipchar(6)null邮政编码（3）/*销售主表sales */order_nointNot nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)Not null,订单金额order_datedatetimeNot null,订货日期ship_datedatetimeNot null,出货日期invoice_nochar(10)Not null发票号码（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,primary key产品编号qtyintNot null销售数量unit_pricenumeric(7,2)Not null单价order_datedatetimenull订单日期（5）/*产品名称表product */pro_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称第一套一、必做题1、 计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。select order_no ,Qty*Unit_price as 销售金额from sale_itemorder by 销售金额2、 检索product 表和sale_item表中数量大于的相同产品的产品编号、产品名称、数量、单价。select pro_id,prod_name,Qty,Unit_pricefrom product,sale_itemwhere Qty2 and _id=sale_item.Prod_id;3、 找出目前业绩未超过50000元的员工。（要求用嵌套查询）Select emp_no, emp_name, sex, employee.dept,.title,employee.date_hired, employee.birthday,employee.salary,employee.addr from employee,sales where employee.emp_no=sales.sale_id and tot_amt5000004、 设置sales 表和sale_item的外键关系为“层叠”。修改sales表中发票号码为I000000004的订单，将其发票号码改为I000000054，其订货明细数据也一并修改。alter table salesadd constraint FK_sales foreign key(order_no) references sale_item(order_no)update sales set invoice_no=I000000054 where invoice_no=I0000000045、 创建视图v1，包含2012-8-12 00:00:00出货的信息（订单号，产品号，产品名，出货日期）create view v1asselect order_no, prod_id, prod_name, ship_datefrom sales,sale_item,product6、 创建一个默认，并绑定到sale_item表的订单日期列，使得该列默认为系统当前日期。create default xq_rule exec sp_bindefault xq_rule,sale_item.order_date alter table sale_itemadd order_date datetime default getdate()二、选做题（必须从中选1道题目）7、 利用存储过程计算出订单编号为10003的订单的销售金额。create proc p1asselect sum(qty*unit_price)from sales_itemwhere order_no= 100038、 编写触发器，如果修改sale_item表中的销售数量或单价字段，级联修改sales表中该订单编号的订单金额字段。第二套一、 必做题1. 查找出职称为经理的女员工的姓名、职称、性别。select emp_name,title,sex from employee where title=经理 and sex=女2. 找出公司女业务员所接的订单。（要求用嵌套查询）select order_nofrom sales,employeewhere sex in(select sexfrom employeewhere sex=f)3. 计算每一产品销售数量总和与平均销售单价。select count (qty)from sale_itemselect prod_id from sale_item group by prod_id; select avg(unit_price)from sale_itemselect prod_id,sum(qty) as销售数量总和, avg(unit_price) as平均销售单价 from sale_item group by prod_id4. 设置sales 表和sale_item的外键关系为“层叠”。删除sales表中作废的订单（其发票号码为I000000004），其订货明细数据也一并删除。alter table salesadd constraint FK_sales foreign key(order_no) references sale_item(order_no)delete from sales where invoice_no=I0000000045. 通过基本表employee和sales，创建一个视图V1，包含业务员的编号、姓名、订单号、销售总金额。create view V1asselect emp_no,emp_name,sum(unit_price)as 销售总金额from employee,sales,sale_itemwhere employee.emp_no=sales.sale_id and sales.order_no=sale_item.order_nogroup by emp_nocreate view v1asselect sale_id,order_no,tot_amtfrom sales6. 创建一个默认约束，使得sale_item表的订单日期默认为系统当前日期。alter table sale_itemadd constraint datetime default GETDATE() for order_date二、选做题（必须从中选1道题目）7. 利用存储过程计算出E0014业务员的销售总金额。create proc p1asselect sum(qty*unit_price)from sales,sales_itemwhere sale_id=E00148. 编写触发器，如果修改sale_item表中的销售数量或单价字段，级联修改sales表中该订单编号的订单金额字段。第三套1、 选取销售数量最多的前条订单订单号、数量。select top 5 order_no,qty from sale_itemorder by qty desc2、 计算出一共销售了几种产品。select count(*) from product3、 查询姓名为a14的员工谈成定订单金额。（用嵌套查询）select tot_amt from sales where sale_id in (select sale_id from sales,employee where emp_name=a14 and emp_no=sale_id)4、 将每个员工的薪水上调3%。update employeeset salary=1+1*0.035、 通过基本表sales_item,product和sales，创建一个视图V1，包含订单号,产品号,产品名称,总价格,订单日期。create view v1(订单号,产品号,产品名称,总价格,订单日期)asselect order_no,prod_id,prod_name,qty*unit_price,order_datefrom sale_item,product,saleswhere sale_d_id=d_id and sale_item.order_no=sales.order_no6、 创建一个默认，并绑定到sale_item表的订单日期列，使得该列默认为系统当前日期。create default xq_rule exec sp_bindefault xq_rule,sale_item.order_date alter table sale_itemadd order_date datetime default getdate()二、选做题（必须从中选1道题目）7、 利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。create proc p1asselect emp_no,order_no,qty*unit_price from employee,sale_itemwhere emp_name=刘刚exec p18、 创建AFTER-UPDATE触发器，在product表上创建触发器updproduct_trg,当对表product的记录进行修改时，触发器将给出修改时间信息。第四套一、 必做题1、在表sales中挑出销售金额大于等于1万元订单。select tot_amt from sales where tot_amt=100002、找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。（要求用嵌套查询）select * from employee where emp_name=(select emp_name from employee group by emp_name having count(emp_name)1)3、计算公司内各个部门的工资支出总和。select dept,sum(salary) as 工资支出总和from employeegroup by dept;4、添加记录E1014,a14,m,办公,经理,2012-01-20,1999-12-23,60000,北京至employee表。INSERT INTO company.dbo.employee (emp_no ,emp_name ,sex ,dept ,title ,date_hired ,birthday ,salary ,addr) VALUES ( , , , , , , , ,)5、创建一个视图V1，包含每一产品销售数量总和。create view v1 as select sum(qty) from sale_item6、 创建一个默认，并绑定到sale_item表的订单日期列，使得该列默认为系统当前日期。create default xq_rule exec sp_bindefault xq_rule,sale_item.order_date alter table sale_itemadd order_date datetime default getdate()二、选做题（必须从中选1道题目）7、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。create procedure p1Emname char(10),tit char(6)asselect Emp_no as 员工号, order_no as 订单号, tot_amt as 销售金额from employee,sales where Emp_name=Emname and title=titexec p1 李%第五套一、必做题1、查找出姓“王”并且姓名的最后一个字为“功”的员工。select *from employeewhere emp_name like 王%;2、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。select d_id,prod_name,unit_price,qtyfrom product,sale_itemwhere d_id=sale_d_id and unit_price2400;3、由employee表中查找出薪水