数据库实训操作题参考答案.doc
文本预览下载声明
销售管理数据库的操作题
销售管理数据库的数据查询
1.查询员工王娜所在的部门。
select DepartmentName
from Department
where DepartmentID=(select DepartmentID from Employee where EmployeeName=姚安娜)select EmployeeName 姓名,Sex 性别,BirthDate 出生年月,Salary 工资
from Employee
where BirthDate=(select MAX(BirthDate) from Employee)
--年龄最小就是出生年月最大，利用嵌套查询，查询最大出生年月Select EmployeeName 姓名,Salary 工资
from Employee
where EmployeeID in(select EmployeeID from Sell_Order)Select Employee.*
from Employee
where exists (select * from Sell_Order where Sell_Order.EmployeeID=Employee.EmployeeID)
4.查询订购“牛奶”的客户信息。（用两种方法做：嵌套查询、连接查询）
--嵌套查询：
select CompanyName 公司名称,ContactName 联系人,Address 地址
from Customer
where CustomerID IN (select CustomerID from Sell_Order where
ProductID=(select ProductID from Product where ProductName=牛奶))select CompanyName 公司名称,ContactName 联系人,Address 地址
from Customer join Sell_Order on Customer.CustomerID =Sell_Order.CustomerID
join Product on Product.ProductID=Sell_order.ProductID
where Product.ProductName=牛奶
select DepartmentID 部门号,EmployeeName 姓名,Sex 性别,BirthDate 出生年月,部门名称=
case DepartmentID
WHEN 1 THEN 销售部
WHEN 2 THEN 采购部
WHEN 3 THEN 人事部
else 其他部门
end
from Employee
group by DepartmentID,EmployeeName ,Sex,BirthDateselect EmployeeName 姓名,Sex 性别,year(BirthDate)出生年月,Salary 工资
from Employee
where BirthDate1980-01-01
补充：查询1980年-1989年间出生的员工的信息（姓名、性别、出生年月和工资）。
select EmployeeName 姓名,Sex 性别,year(BirthDate)出生年月,Salary 工资
from Employee
where convert(char(4),year(BirthDate),102)like 198[0-9]/*定义变量*/
declare @employeeId int
declare @customerId int
declare @max_ordId int
declare @storePro int
declare @productID int
/*客户处理*/
if exists(select * from Customer where CompanyName=华农楚天)
begin
select @customerId=CustomerID FROM Customer where CompanyName=华农楚天
end
else
begin
select @customerId=MAX(CustomerID) FROM Customer
select @customerId=@customerId+1
insert Customer values(@customerId,华农楚天,毛梅捷,1385235423,江夏区臧龙大道,ctxy@163.com)
end
/*订单处理*/
select @storePro=ProductStockNumber,@productID=ProductID
FROM Product WHERE ProductName=彩色显
显示全部