一、实验目的：
掌握嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。
二、实验内容
- 返回一个值的子查询
- 查询和“章宏”同一部门的员工号，员工姓名。
嵌套查询：
SELECT EmployeeID,EmployeeName
FROM Employee
WHERE DepartmentID=
(
SELECT DepartmentID
FROM Employee
WHERE EmployeeName='章宏'
)
连接查询
SELECT E2.EmployeeID ,E2.EmployeeName
FROM Employee E1 JOIN Department D
ON E1.EmployeeName='章宏'
JOIN Employee E2
ON E2.DepartmentID=E1.DepartmentID
GROUP BY E2.EmployeeID,E2.EmployeeName
- )查询年龄最小的员工姓名、性别和工资。
SELECT EmployeeName,Sex,Salary
FROM Employee
WHERE BirthDate=
(
SELECT MAX(BirthDate)
FROM Employee
)
- 查询比平均工资高的员工姓名和工资。
SELECT EmployeeName,Salary
FROM Employee
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
)
- 返回一组值的子查询(嵌套查询和连接查询分别实现)
- 查询目前还没有接收到销售订单的员工姓名和工资信息。
嵌入查询：
SELECT EmployeeName,Salary
FROM Employee
WHERE EmployeeID NOT IN
(
SELECT EmployeeID
FROM Sell_Order
)
连接查询：
SELECT E.EmployeeName,E.Salary
FROM Employee E LEFT OUTER JOIN Sell_Order S
ON (E.EmployeeID=S.EmployeeID)
WHERE S.SellOrderID IS NULL
- 查询订购牛奶的客户名称和联系地址。
嵌套查询：
SELECT CompanyName,Address
FROM Customer
WHERE CustomerID IN
(
SELECT CustomerID
FROM Sell_Order
WHERE ProductID=
(
SELECT ProductID
FROM Product
WHERE ProductName='牛奶'
)
)
连接查询：
SELECT C.CompanyName,C.Address
FROM Customer C JOIN Sell_Order S
ON C.CustomerID=S.CustomerID
JOIN Product P
ON S.ProductID=P.ProductID AND P.ProductName='牛奶'
- 查询客户表中订购商品总数量超过400的客户信息，输出公司名称，联系电话。
嵌套查询：
SELECT CompanyName,Phone
FROM Customer
WHERE CustomerID IN
(
SELECT CustomerID
FROM Sell_Order
GROUP BY CustomerID
HAVING SUM(SellOrderNumber)>400
)
连接查询：
SELECT C.CompanyName,C.Phone
FROM Customer C JOIN Sell_Order S
ON C.CustomerID=S.CustomerID
GROUP BY C.CompanyName,C.Phone
HAVING SUM(S.SellOrderNumber)>400
)