




已阅读5页,还剩5页未读, 继续免费阅读
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
销售管理数据库的操作题销售管理数据库的数据查询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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 校园音乐讲座活动方案策划
- 天津成套钢支撑施工方案
- 无人机驾驶员岗位操作技能考核试卷及答案
- 咨询公司的方案部
- 幕墙设计咨询方案模板
- 幼儿职业绘画活动方案策划
- 洋湖移动咨询方案
- 奶茶甜品店营销计划方案
- 草莓节事活动策划方案
- 拼多多线下活动方案策划
- 伙伴计划团队管理制度
- 急救担架员培训
- 计算机科学导论课件第9章网络与安全技术
- 19 .3 跨学科实践:为家庭电路做设计 课件 人教版(2024)九年级物理全一册
- 2025至2030年中国棉柔巾行业市场现状分析及投资机会研判报告
- 运营部排班管理制度
- 通威太阳能(成都)有限公司通威太阳能(成都)有限公司年产1GW晶体硅太阳能电池项目环评报告
- 乳糜漏的护理
- 木粉尘培训试题及答案
- 风电场输变电设备典型故障及异常处理手册
- 《呼吸系统疾病的针灸治疗》课件
评论
0/150
提交评论