数据库考试题答案

测验一、 题目选择：学号最后 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_no char(5) Not null primary key 员工编号emp_name char(10) Not null 员工姓名sex char(1) Not null 性别dept char(4) Not null 所属部门title char(6) Not null 职称date_hired datetime Not null 到职日birthday datetime Null 生日salary int Not null 薪水addr char(50) null 住址（2）/*客户表customer */cust_id char(5) Not null primary key 客户号cust_name char(20) Not null, 客户名称addr char(40) Not null, 客户住址tel_no char(10) Not null, 客户电话zip char(6) null 邮政编码（3）/*销售主表sales */order_no int Not null primary key 订单编号cust_id char(5) Not null, 客户号sale_id char(5) Not null, 业务员编号tot_amt numeric(9,2) Not null, 订单金额order_date datetime Not null, 订货日期ship_date datetime Not null, 出货日期invoice_no char(10) Not null 发票号码（4）/*销货明细表sale_item */order_no int Not null, primary key 订单编号prod_id char(5) Not null, primary key 产品编号qty int Not null 销售数量unit_price numeric(7,2) Not null 单价order_date datetime null 订单日期（5）/*产品名称表product */pro_id char(5) Not null primary key 产品编号prod_name char(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 表中查找出薪水最高的员工信