数据库实训操作题参考答案.doc

销售管理数据库的操作题销售管理数据库的数据查询1.查询员工王娜所在的部门。select DepartmentNamefrom Departmentwhere DepartmentID=(select DepartmentID from Employee where EmployeeName=姚安娜)2.查询年龄最小的员工姓名、性别和工资。select EmployeeName 姓名,Sex 性别,BirthDate 出生年月,Salary 工资from Employeewhere BirthDate=(select MAX(BirthDate) from Employee)-年龄最小就是出生年月最大，利用嵌套查询，查询最大出生年月3.查询已经接收销售订单的员工姓名和工资信息。（用两种方法做：嵌套查询、相关子查询）-嵌套查询方法：Select EmployeeName 姓名,Salary 工资from Employeewhere EmployeeID in(select EmployeeID from Sell_Order)-相关子查询方法：Select Employee.*from Employeewhere exists (select * from Sell_Order where Sell_Order.EmployeeID=Employee.EmployeeID)4.查询订购“牛奶”的客户信息。（用两种方法做：嵌套查询、连接查询）-嵌套查询：select CompanyName 公司名称,ContactName 联系人,Address 地址from Customerwhere CustomerID IN (select CustomerID from Sell_Order whereProductID=(select ProductID from Product where ProductName=牛奶)-连接查询：select CompanyName 公司名称,ContactName 联系人,Address 地址from Customer join Sell_Order on Customer.CustomerID =Sell_Order.CustomerIDjoin Product on Product.ProductID=Sell_order.ProductID where Product.ProductName=牛奶5.查询所有员工姓名、性别、出生年月和所在部门信息。select DepartmentID 部门号,EmployeeName 姓名,Sex 性别,BirthDate 出生年月,部门名称=case DepartmentIDWHEN 1 THEN 销售部WHEN 2 THEN 采购部WHEN 3 THEN 人事部else 其他部门endfrom Employeegroup by DepartmentID,EmployeeName ,Sex,BirthDate6.查询1980年后出生的员工的信息（姓名、性别、出生年月和工资）。select EmployeeName 姓名,Sex 性别,year(BirthDate)出生年月,Salary 工资from Employeewhere BirthDate1980-01-01补充：查询1980年-1989年间出生的员工的信息（姓名、性别、出生年月和工资）。select EmployeeName 姓名,Sex 性别,year(BirthDate)出生年月,Salary 工资from Employeewhere convert(char(4),year(BirthDate),102)like 1980-9销售管理数据库编程1. 员工“王江娜”与“华农楚天”签订了25台显示器订单。编程实现将订单涉及的相关信息写入到数据库中。提示步骤：a）客户处理：根据该订单的相关客户信息，到客户表中查阅“华农楚天”是否为老客户，若为新客户，则将客户信息添加到客户表中。b)订单处理：将这条订单信息添加到订单表中。在添加前必须确定Sell_Order表中各字段的值。c)库存处理:在商品表中检查该商品的库存量，若库存量超过订单中商品数量，修改库存量，即商品当前库存量的值减去订单记录中包含的商品的订货数量，增加商品已销售量。/*定义变量*/declare employeeId intdeclare customerId intdeclare max_ordId intdeclare storePro intdeclare productID int/*客户处理*/if exists(select * from Customer where CompanyName=华农楚天)begin select customerId=CustomerID FROM Customer where CompanyName=华农楚天endelsebeginselect customerId=MAX(CustomerID) FROM Customerselect customerId=customerId+1insert Customer values(customerId,华农楚天,毛梅捷,1385235423,江夏区臧龙大道,)end/*订单处理*/select storePro=ProductStockNumber,productID=ProductIDFROM Product WHERE ProductName=彩色显示器select max_ordId=MAX(SellOrderID)FROM Sell_Orderselect max_ordId=max_ordId+1select employeeId=employeeId from Employee WHERE EmployeeName=王江娜insert Sell_Order values(max_ordId,productID,25,employeeId,customerId,GETDATE()/*库存处理*/update Productset ProductStockNumber=ProductStockNumber-25,ProductSellNumber=ProductSellNumber+25where ProductID=productID 2.查询各位员工接收销售订单明细表以及订单的总金额，并根据订单中商品总金额，生成员工奖励的报表。当金额订单中商品总超过十万元，奖金10000；金额1000099999元，奖金为订单中商品总金额的10%，金额10000元1000元的奖金为880元，1000元以下的没有奖金。（1）查询员工接收的销售订单明细表，包括订单金额。Select C.CompanyName,P.ProductName,P.Price,S.SellOrderNumber,S.SellOrderDate,E.EmployeeName,P.Price*S.SellOrderNumber 订单金额From Employee as E join Sell_Order as S on E.EmployeeID=S.EmployeeID join Customer as C on C.CustomerID=S.CustomerID join Product as P on P.ProductID=S.ProductIDOrder by E.EmployeeIDCOMPUTE sum (P.Price*S.SellOrderNumber) by E.EmployeeID（2）根据员工接收订单的总金额计算员工奖金。select E.EmployeeName ,E.EmployeeID ,SUM(S.SellOrderNumber*P.Price)as 总金额,奖金=casewhen sum(S.SellOrderNumber*P.Price)100000 then 10000when sum (S.SellOrderNumber*P.Price) between 10000 and 99999 then sum(S.SellOrderNumber*P.Price)*0.1when sum(S.SellOrderNumber*P.Price)between 1000 and 9999 then 880else 0endfrom Employee as E ,Product as P,Sell_Order as SWHERE E.EmployeeID=S.EmployeeID AND P.ProductID=S.ProductIDGROUP BY E.EmployeeID,E.EmployeeName销售管理数据库中视图、索引的应用1.创建一个订单详细信息视图Em_Sell_Order，包括员工姓名、订购商品名称、订购数量、单价和订购日期。Create view Em_Sell_OrderAsSelect EM.EmployeeName as 员工姓名,PD.ProductName as 商品名,SO.SellOrderNumber as 订购数量,PD.Price as 单价,SO.SellOrderDate as 订购日期From Employee EM inner join Sell_Order SOON EM.EmployeeID=SO.EmployeeID inner join Product PDON SO.ProductID=PD.ProductID2.创建一个员工统计订单信息视图，包括员工编号、订单数目和订单总金额。SELECT 员工姓名,COUNT(员工姓名) 订单数目,sum(单价*订购数量)总金额From Em_Sell_OrderGroup by 员工姓名-如果不用视图，而直接利用张基本表进行查询语句较为复杂3.创建一个统计商品销售信息视图View_Pro_Sell，包括商品名称、订购总数量。Create view View_Pro_SellAsSelect 商品名,sum(订购数量)总数量From Em_Sell_OrderGroup by 商品名4.利用视图查询“牛奶”的订购数量。Select * from View_Pro_Sell where 商品名=牛奶5. 利用视图查询“王娜”接收销售订单的信息。select * from Em_Sell_Order where 员工姓名=王娜6.创建员工表的索引。Employee(EmployeeID, Employee Name, Sex, BirthDate, HireDate, Salary, DepartmentID)/*分析：在员工表中员工编号为主键列，则自动创建唯一的聚集索引。在员工表中，经常要查找指定姓名的员工信息，为了提高查找效率，为Employee Name列创建非聚集索引。另外，部门编号DepartmentID为连接部门表的列，因而也需要创建非聚集索引。*/Create index IX_name_Employee on Employee(EmployeeName)Create index IX_DepartmentID_Employee on Employee(EmployeeID)7.创建客户表索引。Customer(CustomerID, CompanyName, ContactName, Phone ,address, EmailAddress)/*分析：在客户表中客户编号为主键列，则自动创建唯一聚集索引。在客户表中，经常要按照客户名称查找信息，同时一般客户的名称不同，为了提高查找效率，对CompanyName列创建唯一的非聚集索引。另外，经常查找各客户的联系人得姓名，所以创建ContactName列的非聚集索引。*/Create unique index IX_name_Customer on Customer(CompanyName)Create index IX_ContactName_Customer on Customer(ContactName)销售管理数据库中存储过程的应用1、 创建一个名为Customer_Order的存储过程，用于获取指定客户的信息，包括联系人姓名、联系方式以及该公司订购产品的明细表。create proc customer_ordercustomername varchar(20)as select companyname,contactname,productname,price,sellordernumber,sellorderdatefrom sell_order,product,customerwhere sell_ductID=ductID and sell_order.customerID=customer.customerIDand companyname=customernameexec customer_order customername=林川中学2、 创建名为listEmployee的存储过程，其功能为：在员工表中查找符合性别和超过指定工资条件的员工的详细信息。利用存储过程listEmployee，查找工资超过4000元的男员工和工资超过3500元的女员工的详细信息。Create procedure listEmployeesex varchar(2),salary moneyasselect *from Employeewhere sex=sex and salarysalary exec listEmployee sex=男,salary=4000exec listEmployee sex=女,salary=35003、 根据指定的客户名查询该客户相关订单信息的存储过程。 create proc customer_1 CustomerName varchar(50) as if exists(select*from Customer where CompanyName=CustomerName) select customer.CompanyName 公司名称, ductName 商品名称, product.Price 单价, Sell_order.sellOrderNumber 数量,Sell_order.SellOrderDATE 订货日期from customer join Sell_order on customer.customerID = Sell_order.customerID join product on Sell_ductID = ductID where customer.CompanyName = customername else print 不存在该客户 customer_1 通恒机械有限公司 4、 根据指定客户的查询该客户订购商品总金额的存储过程。 （有输出参数）create proc sumprice CompanyName varchar(50),sum int output as if exists (select *from Customer where CompanyName=CompanyName) SELECT Customer.CompanyName AS 公司名称, Product.ProductName AS 商品名称, Product.Price AS 单价, Sell_Order.SellOrderNumber AS 数量, Product.Price * Sell_Order.SellOrderNumber AS 金额 FROM Product INNER JOIN Sell_Order ON Product.ProductID = Sell_Order.ProductID INNER JOIN Customer ON Sell_Order.CustomerID = Customer.CustomerID where Customer.CompanyName=CompanyName else print 不存在该客户 DECLARE sum int EXEC sumprice 通恒机械有限公司, sum output 5、 根据指定客户和订购产品信息，查询接收订单的员工信息。 create proc Employee_SellOrder CompanyName varchar(50) ,ProductName varchar(50) as if exists (select * from customer where CompanyName=CompanyName) if exists (select *from Product where ProductName =ProductName)select * from Employee where Employee.EmployeeID =(select Employee.EmployeeID from Sell_Order INNER JOIN Product ON Sell_Order.ProductID = Product.ProductID INNER JOIN Customer ON Sell_Order.CustomerID = Customer.CustomerID INNER JOIN Employee ON Sell_Order.EmployeeID = Employee.EmployeeID where Customer.CompanyName=CompanyName and Product.ProductName=ProductName ) else print 该客户没有订购该产品 else Print 不存在该客户 Employee_SellOrder 三川实业有限公司 ,牙刷 6、 创建自动获取商品订购信息的存储过程，包括商品名称、单价、订购的数量、订购公司名称和订购日期等信息。Create procedure p_orderProductName varchar(50)asselect ProductName,price,SellOrderNumber,CompanyName,SellOrderDatefrom Customer,Sell_Order,Productwhere Sell_Order.CustomerID=Customer.CustomerID and Sell_Order.ProductID=Product.ProductID and ProductName=ProductName exec p_order ProductName=鼠标7、 根据指定员工的查询该员工相关接受所有订单信息的存储过程； 如果该员工没有接受订单的话，输出相关信息。create proc employee_sellorder1 employeename varchar(50) as if exists (select *from Employee where EmployeeName=employeename) if exists (select *from Sell_Order INNER JOIN Employee ON Sell_Order.EmployeeID = Employee.EmployeeID where EmployeeName=employeename)SELECT Employee.EmployeeName, Product.ProductName, Product.Price, Sell_Order.SellOrderNumberFROM Sell_Order INNER JOIN Product ON Sell_Order.ProductID = Product.ProductID INNER JOIN Customer ON Sell_Order.CustomerID = Customer.CustomerID INNER JOIN Employee ON Sell_Order.EmployeeID = Employee.EmployeeID where Employee.EmployeeName=employeename else print 该员工没有接受订单 else print 不存在该员工 employee_sellorder1李央 8、 根据员工信息的查询该员工总销售金额的存储过程。 如果该员工没有接收订单的话，则销售金额为 0。 （有输出参数） create proc employee_sumsales employeename varchar(50),sumsales money output as if exists (select *from Employee where employeename=employeename) if exists (select *from Sell_Order INNER JOIN Employee ON Sell_Order.EmployeeID = Employee.EmployeeID where Employee.EmployeeName =employeename) SELECT sumsales = SUM(Product.Price * Sell_Order.SellOrderNumber) FROM Sell_Order INNER JOIN Product ON Sell_Order.ProductID = Product.ProductID INNER JOIN Employee ON Sell_Order.EmployeeID = Employee.EmployeeID where Employee.EmployeeName=employeename else set sumsales=0 else print 不存在该员工 DECLARE sumsales money EXEC employee_sumsales 李央,sumsales output select sumsales 总金额9、 根据雇员的姓名，查询该雇员的奖金情况，奖金根据该雇员接收订单的总金额计算得到。 （奖金=总金额*5%）。create procedure Employee_RewardEmployeeName varchar(50),reward int outputasselect reward=sum(0.05*price*SellOrderNumber)from Sell_Order inner join Employee on Sell_Order.EmployeeID=Employee.EmployeeID inner join Product on Sell_Order.ProductID=Product.ProductIDwhere EmployeeName=EmployeeName declare a int exec Employee_Reward EmployeeName=王孔若, reward=a outputselect a-方法二：存储过程调用存储过程create procedure Employee_RewardEmployeeName varchar(50),reward int outputasdeclare sumsales intexec employee_sumsales EmployeeName ,sumsales outputset reward=0.05*sumsales 奖金declare a int exec Employee_Reward 王孔若, reward=a outputselect a10、 查询所有员工的总薪水信息的存储过程。 （有输出参数） create proc employee_salary sum money output asselect sum=sum(Employee.Salary)from Employee declare sum money exec employee_salary sum output select sum11、 创建一个指定产品的接收订单总金额的存储过程。 create proc Product_sell productname varchar(50) as SELECT sum( Price*ProductSellNumber)as总金额 FROM Product where productname=productname Exec Product_sell 鼠标 12、创建实现商品表的插入操作的存储过程Proc_Inert_Product。并调用存储过程 Proc_Inert_Product，插入一条记录。create proc Proc_Inert_Product productName varchar(50), Price decimal(18,2), ProductStockNumber int, ProductSellNumber int as declare max_productID int select max_productID=MAX(productID)+1 from Product insert into Product (ProductID,ProductName,Price, ProductStockNumber,ProductSellNumber) values (max_productID,productName,Price,ProductStockNumber,ProductSellNumber) exec Proc_Inert_Product 电脑,6000,190,613、在商品表中，创建指定商品编号的删除操作的存储过程。 create proc delete_product productID int as delete from Product where ProductID =productID exec delete_product productID =814、在商品表中，创建将指定编号的商品的销量增加指定量的存储过程。Create procedure P_upID int,push intasupdate Productset ProductSellNumber=ProductSellNumber+pushwhere ProductID=I