数据库实训操作题参考答案.docx
- 文档编号:4371450
- 上传时间:2022-12-01
- 格式:DOCX
- 页数:13
- 大小:24.24KB
数据库实训操作题参考答案.docx
《数据库实训操作题参考答案.docx》由会员分享,可在线阅读,更多相关《数据库实训操作题参考答案.docx(13页珍藏版)》请在冰豆网上搜索。
数据库实训操作题参考答案
销售管理数据库的操作题
销售管理数据库的数据查询
1.查询员工"王娜"所在的部门。
selectDepartmentName
fromDepartment
whereDepartmentID=(selectDepartmentIDfromEmployeewhereEmployeeName='姚安娜')
2.查询年龄最小的员工姓名、性别和工资。
selectEmployeeName姓名,Sex性别,BirthDate出生年月,Salary工资
fromEmployee
whereBirthDate=(selectMAX(BirthDate)fromEmployee)
--年龄最小就是出生年月最大,利用嵌套查询,查询最大出生年月
3.查询已经接收销售订单的员工姓名和工资信息。
(用两种方法做:
嵌套查询、相关子查询)
--嵌套查询方法:
SelectEmployeeName姓名,Salary工资
fromEmployee
whereEmployeeIDin(selectEmployeeIDfromSell_Order)
--相关子查询方法:
SelectEmployee.*
fromEmployee
whereexists(select*fromSell_OrderwhereSell_Order.EmployeeID=Employee.EmployeeID)
4.查询订购“牛奶”的客户信息。
(用两种方法做:
嵌套查询、连接查询)
--嵌套查询:
selectCompanyName公司名称,ContactName联系人,Address地址
fromCustomer
whereCustomerIDIN(selectCustomerIDfromSell_Orderwhere
ProductID=(selectProductIDfromProductwhereProductName='牛奶'))
--连接查询:
selectCompanyName公司名称,ContactName联系人,Address地址
fromCustomerjoinSell_OrderonCustomer.CustomerID=Sell_Order.CustomerID
joinProductonProduct.ProductID=Sell_order.ProductID
whereProduct.ProductName='牛奶'
5.查询所有员工姓名、性别、出生年月和所在部门信息。
selectDepartmentID部门号,EmployeeName姓名,Sex性别,BirthDate出生年月,部门名称=
caseDepartmentID
WHEN1THEN'销售部'
WHEN2THEN'采购部'
WHEN3THEN'人事部'
else'其他部门'
end
fromEmployee
groupbyDepartmentID,EmployeeName,Sex,BirthDate
6.查询1980年后出生的员工的信息(姓名、性别、出生年月和工资)。
selectEmployeeName姓名,Sex性别,year(BirthDate)出生年月,Salary工资
fromEmployee
whereBirthDate>'1980-01-01'
补充:
查询1980年-1989年间出生的员工的信息(姓名、性别、出生年月和工资)。
selectEmployeeName姓名,Sex性别,year(BirthDate)出生年月,Salary工资
fromEmployee
whereconvert(char(4),year(BirthDate),102)like'198[0-9]'
销售管理数据库编程
1.员工“王江娜”与“华农楚天”签订了25台显示器订单。
编程实现将订单涉及的相关信息写入到数据库中。
提示步骤:
a)客户处理:
根据该订单的相关客户信息,到客户表中查阅“华农楚天”是否为老客户,若为新客户,则将客户信息添加到客户表中。
b)订单处理:
将这条订单信息添加到订单表中。
在添加前必须确定Sell_Order表中各字段的值。
c)库存处理:
在商品表中检查该商品的库存量,若库存量超过订单中商品数量,修改库存量,即商品当前库存量的值减去订单记录中包含的商品的订货数量,增加商品已销售量。
/*定义变量*/
declare@employeeIdint
declare@customerIdint
declare@max_ordIdint
declare@storeProint
declare@productIDint
/*客户处理*/
ifexists(select*fromCustomerwhereCompanyName='华农楚天')
begin
select@customerId=CustomerIDFROMCustomerwhereCompanyName='华农楚天'
end
else
begin
select@customerId=MAX(CustomerID)FROMCustomer
select@customerId=@customerId+1
insertCustomervalues(@customerId,'华农楚天','毛梅捷','1385235423','江夏区臧龙大道','ctxy@')
end
/*订单处理*/
select@storePro=ProductStockNumber,@productID=ProductID
FROMProductWHEREProductName='彩色显示器'
select@max_ordId=MAX(SellOrderID)FROMSell_Order
select@max_ordId=@max_ordId+1
select@employeeId=employeeIdfromEmployeeWHEREEmployeeName='王江娜'
insertSell_Ordervalues(@max_ordId,@productID,25,@employeeId,@customerId,GETDATE())
/*库存处理*/
updateProduct
setProductStockNumber=ProductStockNumber-25,
ProductSellNumber=ProductSellNumber+25
whereProductID=@productID
2.查询各位员工接收销售订单明细表以及订单的总金额,并根据订单中商品总金额,生成员工奖励的报表。
当金额订单中商品总超过十万元,奖金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.ProductID
OrderbyE.EmployeeID
COMPUTEsum(P.Price*S.SellOrderNumber)byE.EmployeeID
(2)根据员工接收订单的总金额计算员工奖金。
selectE.EmployeeName,E.EmployeeID,SUM(S.SellOrderNumber*P.Price)as总金额,奖金=
case
whensum(S.SellOrderNumber*P.Price)>100000then10000
whensum(S.SellOrderNumber*P.Price)between10000and99999thensum(S.SellOrderNumber*P.Price)*0.1
whensum(S.SellOrderNumber*P.Price)between1000and9999then880
else0
end
fromEmployeeasE,ProductasP,Sell_OrderasS
WHEREE.EmployeeID=S.EmployeeIDANDP.ProductID=S.ProductID
GROUPBYE.EmployeeID,E.EmployeeName
销售管理数据库中视图、索引的应用
1.创建一个订单详细信息视图Em_Sell_Order,包括员工姓名、订购商品名称、订购数量、单价和订购日期。
CreateviewEm_Sell_Order
As
SelectEM.EmployeeNameas员工姓名,PD.ProductNameas商品名,
SO.SellOrderNumberas订购数量,PD.Priceas单价,SO.SellOrderDateas订购日期
FromEmployeeEMinnerjoinSell_OrderSO
ONEM.EmployeeID=SO.EmployeeIDinnerjoinProductPD
ONSO.ProductID=PD.ProductID
2.创建一个员工统计订单信息视图,包括员工编号、订单数目和订单总金额。
SELECT员工姓名,COUNT(员工姓名)订单数目,sum(单价*订购数量)总金额
FromEm_Sell_Order
Groupby员工姓名
--如果不用视图,而直接利用张基本表进行查询语句较为复杂
3.创建一个统计商品销售信息视图View_Pro_Sell,包括商品名称、订购总数量。
CreateviewView_Pro_Sell
As
Select商品名,sum(订购数量)总数量
FromEm_Sell_Order
Groupby商品名
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)
as
selectcompanyname,contactname,productname,price,sellordernumber,sellorderdate
fromsell_order,product,customer
wheresell_order.productID=product.productIDandsell_order.customerID=customer.customerID
andcompanyname=@customername
execcustomer_order@customername='林川中学'
2、创建名为listEmployee的存储过程,其功能为:
在员工表中查找符合性别和超过指定工资条件的员工的详细信息。
利用存储过程listEmployee,查找工资超过4000元的男员工和工资超过3500元的女员工的详细信息。
CreateprocedurelistEmployee
@sexvarchar
(2),@salarymoney
as
select*
fromEmployee
wheresex=@sexandsalary>@salary
execlistEmployee@sex='男',@salary=4000
execlistEmployee@sex='女',@salary=3500
3、根据指定的客户名查询该客户相关订单信息的存储过程。
createproccustomer_1@CustomerNamevarchar(50)
asifexists(select*fromCustomerwhereCompanyName=@CustomerName)
selectcustomer.CompanyName公司名称,product.productName商品名称,
product.Price单价,Sell_order.sellOrderNumber数量,
Sell_order.SellOrderDATE订货日期fromcustomerjoinSell_orderon
customer.customerID=Sell_order.customerIDjoinproduct
onSell_order.productID=product.productID
wherecustomer.CompanyName=@customername
else
print'不存在该客户'
customer_1'通恒机械有限公司'
4、根据指定客户的查询该客户订购商品总金额的存储过程。
(有输出参数)
createprocsumprice@CompanyNamevarchar(50),@sumintoutputas
ifexists(select*fromCustomerwhereCompanyName=@CompanyName)
SELECTCustomer.CompanyNameAS公司名称,Product.ProductNameAS商品名称,
Product.PriceAS单价,Sell_Order.SellOrderNumberAS数量,
Product.Price*Sell_Order.SellOrderNumberAS金额
FROMProductINNERJOINSell_OrderONProduct.ProductID=Sell_Order.ProductID
INNERJOINCustomerONSell_Order.CustomerID=Customer.CustomerID
whereCustomer.CompanyName=@CompanyNameelseprint'不存在该客户'
DECLARE@sumintEXECsumprice'通恒机械有限公司',@sumoutput
5、根据指定客户和订购产品信息,查询接收订单的员工信息。
createprocEmployee_SellOrder@CompanyNamevarchar(50),@ProductNamevarchar(50)as
ifexists(select*fromcustomerwhereCompanyName=@CompanyName)
ifexists(select*fromProductwhereProductName=@ProductName)
select*fromEmployeewhereEmployee.EmployeeID=(selectEmployee.EmployeeIDfromSell_Order
INNERJOINProductONSell_Order.ProductID=Product.ProductID
INNERJOINCustomerONSell_Order.CustomerID=Customer.CustomerID
INNERJOINEmployeeONSell_Order.EmployeeID=Employee.EmployeeID
whereCustomer.CompanyName=@CompanyNameandProduct.ProductName=@ProductName)
elseprint'该客户没有订购该产品'
elsePrint'不存在该客户'
Employee_SellOrder'三川实业有限公司','牙刷'
6、创建自动获取商品订购信息的存储过程,包括商品名称、单价、订购的数量、订购公司名称和订购日期等信息。
Createprocedurep_order
@ProductNamevarchar(50)
as
selectProductName,price,SellOrderNumber,CompanyName,SellOrderDate
fromCustomer,Sell_Order,Product
whereSell_Order.CustomerID=Customer.CustomerIDandSell_Order.ProductID=Product.ProductIDand@ProductName=ProductName
execp_order@ProductName='鼠标'
7、根据指定员工的查询该员工相关接受所有订单信息的存储过程;如果该员工没有接受订单的话,输出相关信息。
createprocemployee_sellorder1@employeenamevarchar(50)as
ifexists(select*fromEmployeewhereEmployeeName=@employeename)
ifexists(select*fromSell_OrderINNERJOINEmployeeONSell_Order.EmployeeID=Employee.EmployeeIDwhereEmployeeName=@employeename)
SELECTEmployee.EmployeeName,Product.ProductName,Product.Price,Sell_Order.SellOrderNumber
FROMSell_OrderINNERJOINProductONSell_Order.ProductID=Product.ProductID
INNERJOINCustomerONSell_Order.CustomerID=Customer.CustomerIDINNERJOINEmployeeONSell_Order.EmployeeID=Employee.EmployeeID
whereEmployee.EmployeeName=@employeename
else
print'该员工没有接受订单'
else
print'不存在该员工'
employee_sellorder1'李央'
8、根据员工信息的查询该员工总销售金额的存储过程。
如果该员工没有接收订单的话,则销售金额为0。
(有输出参数)
createprocemployee_sumsales@employeenamevarchar(50),
@sumsalesmoneyoutputas
ifexists(select*fromEmployeewhereemployeename=@employeename)
ifexists(select*fromSell_OrderINNERJOINEmployeeONSell_Order.EmployeeID=Employee.EmployeeIDwhereEmployee.EmployeeName=@employeename)
SELECT@sumsales=SUM(Product.Price*Sell_Order.SellOrderNumber)
FROMSell_OrderINNERJOINProductONSell_Order.ProductID=Product.ProductID
INNERJOINEmployeeONSell_Order.EmployeeID=Employee.EmployeeID
whereEmployee.EmployeeName=@employeename
elseset@sumsales=0
elseprint'不存在该员工'
DECLARE@sumsalesmoney
EXECemployee_sumsales'李央',@sumsalesoutput
select@sumsales总金额
9、根据雇员的姓名,查询该雇员的奖金情况,奖金根据该雇员接收订单的总金额计算得到。
(奖金=总金额*5%)。
createprocedureEmplo
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 操作 参考答案
![提示](https://static.bdocx.com/images/bang_tan.gif)