实验六SQL嵌套查询语句

实验六SQL嵌套查询语句
实验六SQL嵌套查询语句
⼀、实验⽬的
1.熟练掌握SQL嵌套查询语句；
2.熟练掌握嵌套查询与连接查询的互相转换。

⼆、实验内容
给定⼀个练习数据库和相应的练习题，要求上机完成，并验证结果
实验基础知识提要
⼦查询基础知识
⼦查询是⼀个SELECT 查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE 语句或其它⼦查询中。

任何允许使⽤表达式的地⽅都可以使⽤⼦查询。

下例中，⼀个⼦查询⽤作SELECT 语句中名为MaxUnitPrice 的列表达式。

SELECT Ord.OrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord
⼦查询也称为内部查询或内部选择，⽽包含⼦查询的语句也称为外部查询或外部选择。

许多包含⼦查询的Transact-SQL 语句都可以改为⽤联接表⽰。

⽽其它⼀些问题只能由⼦查询提出。

在Transact-SQL 中，包括⼦查询的语句和不包括⼦查询但语义上等效的语句在性能⽅⾯通常没有区别。

但是，在⼀些必须检查存在性的情况中，使⽤联接会产⽣更好的性能。

否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。

所以在这些情况下，联接⽅式会产⽣更好的效果。

包括⼦查询的语句通常采⽤以下格式中的⼀种：
●WHERE expression [NOT] IN (subquery)
●WHERE expression comparison_operator [ANY | ALL] (subquery)
●WHERE [NOT] EXISTS (subquery)
有三种基本的⼦查询。

它们是：
●在通过IN 引⼊的列表或者由ANY 或ALL 修改的⽐较运算符的列表上进⾏操
作。

●通过⽆修改的⽐较运算符引⼊，并且必须返回单个值。

●通过EXISTS 引⼊的存在测试。

相关⼦查询
许多查询都可以通过执⾏⼀次⼦查询并将结果值代⼊外部查询的WHERE ⼦句进⾏
评估。

在包括相关⼦查询（也称为重复⼦查询）的查询中，⼦查询依靠外部查询获得值。

这意味着⼦查询是重复执⾏的，为外部查询可能选择的每⼀⾏均执⾏⼀次。

下⾯的查询查找获得某本书100 % 共享版税(royaltyper) 的所有作者名。

(该查询使⽤SQL Server安装时默认安装的Pubs数据库)
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id)
下⾯是结果集：
au_lname au_fname
---------------------------------------- --------------------
White Johnson
Green Marjorie
Carson Cheryl
Straight Dean
Locksley Charlene
Blotchet-Halls Reginald
del Castillo Innes
Panteley Sylvia
Ringer Albert
(9 row(s) affected)
与以前提到的⼤多数⼦查询不同，该语句中的⼦查询⽆法独⽴于主查询⽽得到解决。

该⼦查询需要⼀个authors.au_id 值，⽽该值是个变量，随SQL Server 检查authors 表中的不同⾏⽽更改。

下⾯准确说明如何评估该查询：SQL Server 考虑authors 表中的每⼀⾏是否都包括在结果中，⽅法是将每⼀⾏的值都代⼊内部查询中。

例如，如果SQL Server ⾸先检查Cheryl Carson ⾏，那么变量authors.au_id 将取值238-95-7766，SQL Server 将该值代⼊到内部查询中。

USE pubs
SELECT royaltyper
FROM titleauthor
WHERE au_id = '238-95-7766'
结果为100，所以外部查询评估为：
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN (100)
由于这是真的，因此Cheryl Carson ⾏包括在结果中。

对Abraham Bennet ⾏运⾏相同的过程，会发现该⾏没有包括在结果中。

三、实验步骤
构建的数据表作为实验数据内容
结构与Suppliers表相同
运⾏SQL SERVER服务管理器，确认数据库服务器开始运⾏。

运⾏企业管理器，以图⽰⽅式点击“附加数据库”，恢复db⽬录下的数据库⽂件
打开查询分析器，选择刚才恢复的数据库exampleDB，输⼊SQL指令，获得运⾏结果。

任务：完成以下SQL查询语句⽤不相关⼦查询，查出公司名⾸字母为’L’的顾客所下订单的产品总数；
select sum(Quantity)
from OrderDetails
where OrderID in(
select OrderID
from Orders
where CustomerID in(
select CustomerID
from Customers
where CompanyName like 'L%'))
⽤相关⼦查询，查出公司名⾸字母为’L’的顾客所下订单的产品总数；
select sum(Quantity)
from OrderDetails
where exists(
select *
from Orders
where Orders.OrderID=OrderDetails.OrderID and
exists(
select * from Customers
where Customers.CustomerID=Orders.CustomerID and
CompanyName like 'L%'))
⽤连接查询，查出公司名⾸字母为’L’的顾客所下订单的产品总数；
select sum(Quantity)
from OrderDetails
LEFT OUTER JOIN (Orders
LEFT OUTER JOIN Customers
ON(Orders.CustomerID=Customers.CustomerID))
ON(OrderDetails.OrderID=Orders.OrderID)
where /doc/4e63bfcf9e31433239689349.html panyName like 'L%'
⽤带有ANY或ALL谓词的⼦查询，找出所签订的订单运费最⾼的顾客编号，和
公司名；
select Customers.CustomerID,CompanyName
from Orders,Customers
where Orders.CustomerID=Customers.CustomerID and
Freight>=ALL(
select Freight
from Orders)
⽤带有聚集函数的⼦查询，找出所签订的订单运费最⾼的顾客编号，和公司名；select Customers.CustomerID,CompanyName
from Orders,Customers
where Orders.CustomerID=Customers.CustomerID and
Freight=(
select Max(Freight)
from Orders)
⽤相关⼦查询，找出没有下过订单的客户编号和公司名；
select CustomerID,CompanyName
from Customers
where not exists(
select *
from Orders
where Orders.CustomerID=Customers.CustomerID)
⽤集合查询，找出单价⼩于20的及库存⼤于50的产品编号、产品名称，要求保留重复的元组；
select ProductID,ProductName
from Products
where UnitPrice<20
UNION ALL
select ProductID,ProductName
from Products
where UnitsInStock>50
⽤集合查询，找出订货数量⼤于0的，及折扣⼤于0的产品编号，要求去除重复的元组；
select ProductID
from OrderDetails
where Quantity>0
UNION
select ProductID
from OrderDetails
where Discount>0
找出在同⼀个地区，且存在订货关系的客户编号，公司名称和供应商编号，供应商
名称；
Select Customers.CustomerID,/doc/4e63bfcf9e31433239689349.html panyName, Suppliers.SupplierID,/doc/4e63bfcf9e31433239689349.html panyName
from Customers,Suppliers
where Customers.Region=Suppliers.Region and
exists(
select*
from Orders,OrderDetails,Products,Suppliers
where Orders.CustomerID=Customers.CustomerID and
Orders.OrderID=OrderDetails.OrderID and
OrderDetails.ProductID=Products.ProductID and
Products.SupplierID=Suppliers.SupplierID)
找出完成定单数量最多的职⼯姓、名、编号；
select LastName,FirstName,EmployeeID
from Employees
where EmployeeID=(
select EmployeeID
from Orders
group by EmployeeID
having count(*)>=ALL(
select count(*)
from Orders
group by EmployeeID))
四. 实验结果与分析（上交实验报告）
分析思考问题：
1. 分析不相关⼦查询和相关⼦查询的差异；
不相关⼦查询：⼦查询的执⾏不需要提前取得⽗查询的值，只是作为⽗查询的查询条件；相关⼦查询：执⾏查询的时候先取得外层查询的⼀个属性值，然后执⾏与此属性值相关的⼦查询，执⾏完毕后再取得外层⽗查询的下⼀个值，依次再来重复执⾏⼦查询。

2. 分别给出“实验基础知识提要”部分提到的三种基本的⼦查询的⽰例并说明具体含
义。

（其中对第⼆种⼦查询要分别给出ALL和ANY的⽰例）
返回全部单价⽐任何以25% 或更⾼的折扣卖出的产品⾼的产品：
SELECT *
FROM Products
WHERE UnitPrice > ANY(
SELECT UnitPrice
FROM OrderDetails
WHERE Discount >= .25);
返回有25% 或更⾼的折扣的所有产品：
SELECT *
FROM Products
WHERE ProductID IN(
SELECT ProductID
FROM OrderDetails
WHERE Discount >= .25);
五．讨论、⼼得。