SQL Server 考试复习资料及综合训练（含答案）

AND Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=Employees.EmployeeID AND Orders.ShipVia=Shippers.ShipperID

AND [Order Details].ProductID=Products.ProductID ORDER BY Products.ProductID,Orders.OrderID ASC

[29] 查询涉及了Chai产品或Chang产品的订单，对结果适当排序。（Orders、Order Details） SELECT *

FROM Orders,[Order Details],Products

WHERE Orders.OrderID =[Order Details].OrderID AND Products.ProductName LIKE 'Chai' UNION SELECT *

FROM Orders,[Order Details],Products

WHERE Orders.OrderID =[Order Details].OrderID AND Products.ProductName LIKE 'Chang' ORDER BY Orders.OrderID

[30] 查询不是Exotic Liquids供货商和Tokyo Traders供货商供货的产品，对结果适当排序。

（Products、Suppliers）

SELECT Suppliers.CompanyName,Products.* FROM Suppliers ,Products

WHERE Suppliers.SupplierID=Products.SupplierID AND Suppliers.SupplierID NOT IN (

SELECT SupplierID FROM Suppliers

WHERE CompanyName LIKE 'Exotic Liquids' OR CompanyName LIKE 'Tokyo Traders' )

ORDER BY Products.ProductID

[31] 查询Speedy Express运输商运输的订单详情，对结果适当排序。（Orders、Shippers） SELECT Orders.*,CompanyName

FROM [Order Details] ,Orders ,Shippers

WHERE [Order Details].OrderID =Orders .OrderID AND CompanyName ='Speedy Express'

ORDER BY [Order Details].OrderID DESC

[32] 查询单价在11.0-20.0之间或在30.0-40.0之间的产品详情，对结果适当排序。

（Products） SELECT Products.* FROM Products

WHERE UnitPrice BETWEEN 11.0 AND 20.0

UNION

SELECT Products.* FROM Products

WHERE UnitPrice BETWEEN 30.0 AND 40.0 ORDER BY UnitPrice DESC

[33] 查询提供的产品单价在11.0-30.0之间但不在20.0-25.0之间的供货商详情，对结果适

当排序。（Products、Suppliers） SELECT Products.UnitPrice,Suppliers.* FROM Products,Suppliers

WHERE Products.UnitPrice BETWEEN 11.0 AND 30.0 AND Products.UnitPrice NOT BETWEEN 20.0 AND 25.0 ORDER BY Products.UnitPrice DESC

1.3.3 集合操作

查询出所有的运输商和供货商，分别列出两者的编号、名称。（Suppliers、Shippers）注意查看结果显示的列名，如果要求显示的列名为“编号”和“名称”如何解决。 方式一： SELECT

Shippers.CompanyName ,Shippers .ShipperID ,Suppliers.SupplierID ,Suppliers.CompanyName FROM Shippers,Suppliers 方式二：

SELECT Shippers.CompanyName AS 名称 ,Shippers .ShipperID AS 编号,Suppliers.SupplierID AS 编号,Suppliers.CompanyName AS 名称 FROM Shippers,Suppliers

[35] 查询出所有的客户和供货商，分别列出两者的编号、名称。（Suppliers、Customers）

注意：参与并操作的结果集必须同质。该题中两个结果集的编号不是同质的，因此需要进行类型转换函数CAST，将整型转换为字符型。 SELECT CompanyName,CustomerID FROM Customers UNION

SELECT CompanyName,CAST(SupplierID AS NVARCHAR(5)) FROM Suppliers

[36] 使用并操作查询出Speedy Express和United Package运输商运输的订单。 SELECT *

FROM Orders ,Shippers

WHERE Orders.ShipVia =Shippers .ShipperID AND Shippers .CompanyName ='Speedy Express' UNION SELECT *

FROM Orders ,Shippers

WHERE Orders.ShipVia =Shippers.ShipperID AND Shippers .CompanyName ='United Package'

[34]

排序： SELECT *

FROM Orders ,Shippers

WHERE Orders .ShipVia =Shippers .ShipperID AND Shippers .CompanyName ='Speedy Express' UNION SELECT *

FROM Orders ,Shippers

WHERE Orders .ShipVia =Shippers .ShipperID AND Shippers .CompanyName ='United Package'

ORDER BY Shippers .CompanyName DESC

[37] 使用并操作查询出Lyngbysild、Tokyo Traders和Leka Trading供货商供货的产品信息。

（能否使用多个union？请尝试，并请尝试对该题和上一题进行排序操作） SELECT *

FROM Products

WHERE SupplierID IN(SELECT SupplierID FROM Suppliers WHERE CompanyName ='Lyngbysild') UNION SELECT *

FROM Products

WHERE SupplierID IN(SELECT SupplierID FROM Suppliers WHERE CompanyName ='Tokyo Traders') UNION SELECT *

FROM Products

WHERE SupplierID IN(SELECT SupplierID FROM Suppliers WHERE CompanyName ='Leka Trading')

ORDER BY SupplierID DESC

[38] 使用交操作查询：既包括了Chai产品又包括Chang产品的订单 SELECT *

FROM [Order Details] WHERE ProductID IN (

SELECT ProductID FROM Products

WHERE ProductName='Chai' )

INTERSECT SELECT *

FROM [Order Details] WHERE ProductID IN

(

SELECT ProductID FROM Products

WHERE ProductName='Chang' )

[39] 使用差操作查询：包括了Chai产品但没有包括Chang产品的订单 SELECT *

FROM [Order Details] WHERE ProductID IN (

SELECT ProductID FROM Products

WHERE ProductName='Chai' ) EXCEPT SELECT *

FROM [Order Details] WHERE ProductID IN (

SELECT ProductID FROM Products

WHERE ProductName='Chang' )

1.3.4 聚集函数

查询出产品的平均单价、总数、平均价格、最高单价、最低单价。是否需要排序？注意显示有意义的列名。

SELECT AVG(UnitPrice) AS 平均单价,SUM(UnitsOnOrder+ReorderLevel) AS 总数, AVG (UnitPrice*(UnitsOnOrder+ReorderLevel)) AS 平均价格, MAX (UnitPrice ) AS 最高单价,MIN (UnitPrice ) AS 最低单价 FROM Products

[41] 查询出不同供货商的产品的平均单价、总数、平均价格、最高单价、最低单价。适当

进行排序，察看不同排序的显示结果。注意显示有意义的列名。 方式一：

SELECT AVG(UnitPrice) AS 平均单价,SUM(UnitsOnOrder+ReorderLevel) AS 总数, AVG (UnitPrice*(UnitsOnOrder+ReorderLevel)) AS 平均价格, MAX (UnitPrice ) AS 最高单价,MIN (UnitPrice ) AS 最低单价 FROM Products

GROUP BY SupplierID ORDER BY 平均单价

[40]