一、实验目的:
掌握嵌套查询使我们可以用多个简单查询构成复杂的查询,从而增强SQL的查询能力。
二、实验内容
1、返回一个值的子查询
(1) 查询和“章宏”同一部门的员工号,员工姓名。
查询语句:
SELECT EmployeeID,EmployeeName
FROM Employee
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Employee
WHERE EmployeeName='章宏')
执行结果:
(2)查询年龄最小的员工姓名、性别和工资。
查询语句:
SELECT EmployeeName,Sex,Salary
FROM Employee
WHERE BirthDate IN
( SELECT MAX(BirthDate)
FROM Employee
)
执行结果:
(3)查询比平均工资高的员工姓名和工资。
查询语句:
SELECT EmployeeName,Salary
FROM Employee
WHERE Salary >(SELECT AVG(Salary)
FROM Employee
)
执行结果:
2、返回一组值的子查询(嵌套查询和连接查询分别实现)
(1) 查询目前还没有接收到销售订单的员工姓名和工资信息。
-
嵌套查询
查询语句:
SELECT EmployeeName,Salary FROM Employee WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Sell_Order )
执行结果:
-
连接查询(外连接)
查询语句:
SELECT EmployeeName,Salary FROM Employee LEFT OUTER JOIN Sell_Order ON(Employee.EmployeeID=Sell_Order.EmployeeID) WHERE SellOrderID IS NULL
执行结果:
(2) 查询订购牛奶的客户名称和联系地址。
-
嵌套查询
查询语句:
SELECT ContactName,Address FROM Customer WHERE CustomerID IN ( SELECT CustomerID FROM Sell_Order WHERE ProductID IN ( SELECT ProductID FROM Product WHERE ProductName='牛奶' ) )
执行结果:
-
连接查询
查询语句:
SELECT ContactName,Address FROM Customer,Product,Sell_Order WHERE Customer.CustomerID=Sell_Order.CustomerID AND Sell_Order.ProductID=Product.ProductID AND ProductName='牛奶'
执行结果:
(3) 查询客户表中订购商品总数量超过400的客户信息,输出公司名称,联系电话。
-
嵌套查询
查询语句:
SELECT CompanyName,Phone FROM Customer WHERE CustomerID IN ( SELECT CustomerID FROM Sell_Order GROUP BY CustomerID HAVING SUM(SellOrderNumber)>400 )
执行结果:
-
连接查询
查询语句:
SELECT CompanyName,Phone FROM Customer,Sell_Order WHERE Customer.CustomerID=Sell_Order.CustomerID GROUP BY CompanyName,Phone HAVING SUM(SellOrderNumber)>400
执行结果:
三、实验总结
本次实验掌握了对表单的嵌套查询以及连接查询,还有连接查询和嵌套查询的相互转化,同时也涉及AVG函数、SUM函数等的运用。