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

销售管理数据库的操作题销售管理数据库的数据查询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.ProductIDwhere 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 BirthDate>'1980-01-01'补充：查询1980年-1989年间出生的员工的信息（姓名、性别、出生年月和工资）。

select EmployeeName 姓名,Sex 性别,year(BirthDate)出生年月,Salary 工资from Employeewhere convert(char(4),year(BirthDate),102)like'198[0-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='华农楚天')beginselect @customerId=CustomerID FROM Customer where CompanyName='华农楚天'endelsebeginselect @customerId=MAX(CustomerID)FROM Customerselect @customerId=@customerId+1insert Customer values(@customerId,'华农楚天','毛梅捷','1385235423','江夏区臧龙大道','ctxy@')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_Ordervalues(@max_ordId,@productID,25,@employeeId,@customerId,GETDATE())/*库存处理*/update Productset ProductStockNumber=ProductStockNumber-25,ProductSellNumber=ProductSellNumber+25where ProductID=@productID2.查询各位员工接收销售订单明细表以及订单的总金额，并根据订单中商品总金额，生成员工奖励的报表。

当金额订单中商品总超过十万元，奖金10000；金额10000~99999元，奖金为订单中商品总金额的10%，金额10000元~1000元的奖金为880元，1000元以下的没有奖金。

（1）查询员工接收的销售订单明细表，包括订单金额。

SelectpanyName,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 onP.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 thensum(S.SellOrderNumber*P.Price)*0.1when sum(S.SellOrderNumber*P.Price)between 1000 and 9999 then 880 else 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.利用视图查询“牛奶”的订购数量。