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

销售管理数据库的操作题销售管理数据库的数据查询1.查询员工王娜所在的部门。selectDepartmentNamefromDepartmentwhereDepartmentID=(selectDepartmentIDfromEmployeewhereEmployeeName='姚安娜')2.查询年龄最小的员工姓名、性别和工资。selectEmployeeName姓名,Sex性别,BirthDate出生年月,Salary工资fromEmployeewhereBirthDate=(selectMAX(BirthDate)fromEmployee)--年龄最小就是出生年月最大，利用嵌套查询，查询最大出生年月3.查询已经接收销售订单的员工姓名和工资信息。（用两种方法做：嵌套查询、相关子查询）--嵌套查询方法：SelectEmployeeName姓名,Salary工资fromEmployeewhereEmployeeIDin(selectEmployeeIDfromSell_Order)--相关子查询方法：SelectEmployee.*fromEmployeewhereexists(select*fromSell_OrderwhereSell_Order.EmployeeID=Employee.EmployeeID)4.查询订购“牛奶”的客户信息。（用两种方法做：嵌套查询、连接查询）--嵌套查询：selectCompanyName公司名称,ContactName联系人,Address地址fromCustomerwhereCustomerIDIN(selectCustomerIDfromSell_OrderwhereProductID=(selectProductIDfromProductwhereProductName='牛奶'))--连接查询：selectCompanyName公司名称,ContactName联系人,Address地址fromCustomerjoinSell_OrderonCustomer.CustomerID=Sell_Order.CustomerIDjoinProductonProduct.ProductID=Sell_order.ProductIDwhereProduct.ProductName='牛奶'5.查询所有员工姓名、性别、出生年月和所在部门信息。selectDepartmentID部门号,EmployeeName姓名,Sex性别,BirthDate出生年月,部门名称=caseDepartmentIDWHEN1THEN'销售部'WHEN2THEN'采购部'WHEN3THEN'人事部'else'其他部门'endfromEmployeegroupbyDepartmentID,EmployeeName,Sex,BirthDate6.查询1980年后出生的员工的信息（姓名、性别、出生年月和工资）。selectEmployeeName姓名,Sex性别,year(BirthDate)出生年月,Salary工资fromEmployeewhereBirthDate'1980-01-01'补充：查询1980年-1989年间出生的员工的信息（姓名、性别、出生年月和工资）。selectEmployeeName姓名,Sex性别,year(BirthDate)出生年月,Salary工资fromEmployeewhereconvert(char(4),year(BirthDate),102)like'198[0-9]'销售管理数据库编程1.员工“王江娜”与“华农楚天”签订了25台显示器订单。编程实现将订单涉及的相关信息写入到数据库中。提示步骤：a）客户处理：根据该订单的相关客户信息，到客户表中查阅“华农楚天”是否为老客户，若为新客户，则将客户信息添加到客户表中。b)订单处理：将这条订单信息添加到订单表中。在添加前必须确定Sell_Order表中各字段的值。c)库存处理:在商品表中检查该商品的库存量，若库存量超过订单中商品数量，修改库存量，即商品当前库存量的值减去订单记录中包含的商品的订货数量，增加商品已销售量。/*定义变量*/declare@employeeIdintdeclare@customerIdintdeclare@max_ordIdintdeclare@storeProintdeclare@productIDint/*客户处理*/ifexists(select*fromCustomerwhereCompanyName='华农楚天')beginselect@customerId=CustomerIDFROMCustomerwhereCompanyName='华农楚天'endelsebeginselect@customerId=MAX(CustomerID)FROMCustomerselect@customerId=@customerId+1insertCustomervalues(@customerId,'华农楚天','毛梅捷','1385235423','江夏区臧龙大道','[e-mail]')end/*订单处理*/select@storePro=ProductStockNumber,@productID=ProductIDFROMProductWHEREProductName='彩色显示器'select@max_ordId=MAX(SellOrderID)FROMSell_Orderselect@max_ordId=@max_ordId+1select@employeeId=employeeIdfromEmployeeWHEREEmployeeName='王江娜'insertSell_Ordervalues(@max_ordId,@productID,25,@employeeId,@customerId,GETDATE())/*库存处理*/updateProductsetProductStockNumber=ProductStockNumber-25,ProductSellNumber=ProductSellNumber+25whereProductID=@productID2.查询各位员工接收销售订单明细表以及订单的总金额，并根据订单中商品总金额，生成员工奖励的报表。当金额订单中商品总超过十万元，奖金10000；金额10000~99999元，奖金为订单中商品总金额的10%，金额10000元~1000元的奖金为880元，1000元以下的没有奖金。（1）查询员工接收的销售订单明细表，包括订单金额。SelectC.CompanyName,P.ProductName,P.Price,S.SellOrderNumber,S.SellOrderDate,E.EmployeeName,P.Price*S.SellOrderNumber订单金额FromEmployeeasEjoinSell_OrderasSonE.EmployeeID=S.EmployeeIDjoinCustomerasConC.CustomerID=S.CustomerIDjoinProductasPonP.ProductID=S.ProductIDOrderbyE.EmployeeIDCOMPUTEsum(P.Price*S.SellOrderNumber)byE.EmployeeID（2）根据员工接收订单的总金额计算员工奖金。selectE.EmployeeName,E.EmployeeID,SUM(S.SellOrderNumber*P.Price)as总金额,奖金=casewhensum(S.SellOrderNumber*P.Price)100000then10000whensum(S.SellOrderNumber*P.Price)between10000and99999thensum(S.SellOrderNumber*P.Price)*0.1whensum(S.SellOrderNumber*P.Price)between1000and9999then880else0endfromEmployeeasE,ProductasP,Sell_OrderasSWHEREE.EmployeeID=S.EmployeeIDANDP.ProductID=S.ProductIDGROUPBYE.EmployeeID,E.EmployeeName销售管理数据库中视图、索引的应用1.创建一个订单详细信息视图Em_Sell_Order，包括员工姓名、订购商品名称、订购数量、单价和订购日期。CreateviewEm_Sell_OrderAsSelectEM.EmployeeNameas员工姓名,PD.ProductNameas商品名,SO.SellOrderNumberas订购数量,PD.Priceas单价,SO.SellOrderDateas订购日期FromEmployeeEMinnerjoinSell_OrderSOONEM.EmployeeID=SO.EmployeeIDinnerjoinProductPDONSO.ProductID=PD.ProductID2.创建一个员工统计订单信息视图，包括员工编号、订单数目和订单总金额。SELECT员工姓名,COUNT(员工姓名)订单数目,sum(单价*订购数量)总金额FromEm_Sell_OrderGroupby员工姓名--如果不用视图，而直接利用张基本表进行查询语句较为复杂3.创建一个统计商品销售信息视图View_Pro_Sell，包括商品名称、订购总数量。CreateviewView_Pro_SellAsSelect商品名,sum(订购数量)总数量FromEm_Sell_OrderGroupby商品名4.利用视图查询“牛奶”的订购数量。Select*fromView_Pro_Sellwhere商品名='牛奶'5.利用视图查询“王娜”接收销售订单的信息。select*fromEm_Sell_Orderwhere员工姓名='王娜'6.创建员工表的索引。Employee(EmployeeID,EmployeeName,Sex,BirthDate,HireDate,Salary,DepartmentID)/*分析：在员工表中员工编号为主键列，则自动创建唯一的聚集索引。在员工表中，经常要查找指定姓名的员工信息，为了提高查找效率，为EmployeeName列创建非聚集索引。另外，部门编号DepartmentID为连接部门表的列，因而也需要创建非聚集索引。*/CreateindexIX_name_EmployeeonEmployee(EmployeeName)CreateindexIX_DepartmentID_EmployeeonEmployee(EmployeeID)7.创建客户表索引。Customer(CustomerID,CompanyName,ContactName,Phone,address,EmailAddress)/*分析：在客户表中客户编号为主键列，则自动创建唯一聚集索引。在客户表中，经常要按照客户名称查找信息，同时一般客户的名称不同，为了提高查找效率，对CompanyName列创建唯一的非聚集索引。另外，经常查找各客户的联系人得姓名，所以创建ContactName列的非聚集索引。*/CreateuniqueindexIX_name_CustomeronCustomer(CompanyName)CreateindexIX_ContactName_CustomeronCustomer(ContactName)销售管理数据库中存储过程的应用1、创建一个名为Customer_Order的存储过程，用于获取指定客户的信息，包括联系人姓名、联系方式以及该公司订购产品的明细表。createproccustomer_order@customernamevarchar(20)asselectcomp