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

销售管理数据库的操作题 销售管理数据库的数据查询 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=彩色显