sql server 题目答案

1.查询年龄最大的员工的姓名及所在部门名
2、查询工资超过平均水平的员工的姓名
3、查询哪些供应商为我们供应了键盘，列出这些供应商的全部供应订单信息。
4、查找订单表中单个订单的订购数量超过1000的客户信息，包括客户编号，公司名称，联系人
5、查找订购了“路由器”的客户所有订购信息。
6、查找年龄最小的员工姓名、性别和工资。
7、查询销售订单数大于参与销售员工平均销售订单数的员工的基本信息，查询结果按员工编号升序排列
8、查询没有订购1号产品的客户的公司名称，联系人。(使用相关子查询)

SELECT top 1 [EmployeeName],[DepartmentName]
FROM [dbo].[Employee] a
	Inner Join [dbo].[Department] b
		on a.DepartmentID =b.DepartmentID
order by [BirthDate]

select [EmployeeName] from [dbo].[Employee] as a,
(select avg([Salary]) as avgsal from [dbo].[Employee] ) as b
where a.[Salary]>b.avgsal


select [PurchaseOrderID],a.[ProductID],[PurchaseOrderNumber],[EmployeeID],[ProviderID],[PurchaseOrderDate] from [dbo].[Purchase_order] as a,
(select [ProductID] from [dbo].[Product] where [ProductName]='键盘' ) as b
where a.ProductID=b.ProductID


select a.[CustomerID],[CompanyName],[ContactName]from [dbo].[Customer] as a,
(select [CustomerID] from [dbo].[Sell_Order]where [SellOrderNumber]>1000) as b
where a.CustomerID=b.CustomerID


select a.[SellOrderID],a.[ProductID],[SellOrderNumber],[EmployeeID],[CustomerID],[SellOrderDate]  from [dbo].[Sell_Order] as a,
(select [ProductID] from [dbo].[Product] where [ProductName]='路由器' ) as b
where a.ProductID=b.ProductID

SELECT top 1 [EmployeeName],[Sex],[Salary]
FROM [dbo].[Employee] 
order by [BirthDate] desc

select a.[EmployeeID],[EmployeeName],[Sex],[BirthDate],[HireDate],[Salary],[DepartmentID] from [dbo].[Employee] as a,
(select [EmployeeID]from [dbo].[Sell_Order] as d,
 (select avg([SellOrderNumber]) as avgNum from [dbo].[Sell_Order] )as c
 where d.SellOrderNumber>c.avgNum) as b
 where a.EmployeeID =b.EmployeeID
 group by a.[EmployeeID],[EmployeeName],[Sex],[BirthDate],[HireDate],[Salary],[DepartmentID]

SELECT * FROM [dbo].[Customer]
WHERE  EXISTS
  (SELECT * FROM [dbo].[Sell_Order]
   WHERE [dbo].[Customer].[CustomerID] = [Sell_Order].[CustomerID]
     AND Sell_Order.[ProductID] !=  1)