SQL Server 实验五 数据查询 （二）

本实验基于StoreSales数据库中的各个表，用T-SQL命令按要求完成下列查询。

1、查询员工孙勇诚订单总金额。（三种方法可选择：谓词连接、JOIN连接、IN子查询）

//谓词连接查询
SELECT Cost as '总金额'
FROM Employee,SalesOrder
WHERE Employee. EmplName='孙勇诚' and Employee.EmplID=SalesOrder.EmplID
//JOIN连接查询
SELECT Cost as '总金额'
FROM Employee join SalesOrder
ON Employee.EmplName='孙勇诚' and Employee.EmplID=SalesOrder.EmplID
//IN子查询
SELECT Cost as '总金额'
FROM SalesOrder
WHERE EmplID IN
(SELECT EmplID
FROM Employee
WHERE EmplName='孙勇诚'
)

2、查询比所有D001部门员工年龄都大的员工号和出生日期。

SELECT EmplID as '员工号'，Birthday AS '出生日期'
FROM Employee
WHERE Birthday<ALL
  ( SElECT Birthday
    FROM Employee
    WHERE DeptID='D001'
   )

3、查询人事部门员工信息。（用EXISTS实现）

SELECT *
FROM Employee
WHERE EXISTS
  ( SELECT *
    FROM Department 
    WHERE Employee.DeptID=Department.DeptID AND DeptName='人事部'   
   )

4、查询部门号为D001和D002的员工信息。（用UNION实现）

SELECT *
FROM Employee
WHERE DeptID='D001'
UNION
SELECT *
FROM Employee
WHERE DeptID='D002'

5、查询工资大于4000但小于5000员工的信息。（用EXCEPT实现）

select*
 from Employee
 where Wages>4000
 EXCEPT
 select*
 from Employee
 where Wages>=5000

6、查询部门号为D001且性别为男的员工信息。（用INTERSECT实现）

select *
 from Employee
 where DeptID='D001'
 INTERSECT
 select *
 from Employee
 where  Sex='男'

7、创建表Emp_Dep,包括员工号、姓名、工资、部门名称。（用INTO实现）

SELECT EmplID, EmplName, Wages, DeptName INTO Emp_Dep
 FROM Employee,Department

8、查询员工工资为前两名的员工信息。

SELECT TOP 2 *
FROM Employee
ORDER BY Wages DESC