Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the ALL operator.

/* Select all Yellow colored products with ListPrice higher than or equal to  

   the ListPrice of ALL Silver/Black products */

-- T-SQL comparison operators, like { > | >= | !> | < | }, modified by ANY, SOME, or ALL

-- SQL comparison operator >= modified by ALL - SQL ALL operator

SELECT ProductName = Name,

       ListPrice

FROM     AdventureWorks.Production.Product

WHERE    Color = 'Yellow'

         AND ListPrice >=  ALL (SELECT ListPrice

                               FROM   AdventureWorks.Production.Product

                               WHERE  Color = 'Silver/Black')

ORDER BY ListPrice DESC,

         ProductName;

/* Partial results

ProductName                   ListPrice

Touring-1000 Yellow, 46       2384.07

Touring-1000 Yellow, 50       2384.07

Touring-1000 Yellow, 54       2384.07

Touring-1000 Yellow, 60       2384.07

Road-350-W Yellow, 40         1700.99

*/

------------

-- SQL return ProductID-s where OrderQty is greater than ALL in another period

-- SQL Comparison Operator > Modified by ALL keyword

-- SQL ALL operator

-- SQL common table expression - CTE

-- SQL inner join

USE AdventureWorks;

WITH cteOrderQty

     AS (SELECT DISTINCT sod.OrderQty

         FROM   Sales.SalesOrderHeader AS soh

                INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderId = sod.SalesOrderID

         WHERE  soh.OrderDate BETWEEN '2003-06-01' AND '2003-06-03')

SELECT   DISTINCT p.ProductID,

                  ProductName = p.Name, OrderQty

FROM     Sales.SalesOrderHeader AS soh

         INNER JOIN Sales.SalesOrderDetail AS sod

           ON soh.SalesOrderID = sod.SalesOrderID

         INNER JOIN Production.Product AS p

           ON p.ProductID = sod.ProductID

WHERE    soh.OrderDate BETWEEN '2003-01-01' AND '2003-05-01'

         AND sod.OrderQty >  ALL (SELECT *

                                  FROM   cteOrderQty)

ORDER BY ProductName

GO

/* Results

ProductID   ProductName                   OrderQty

863         Full-Finger Gloves, L         21

863         Full-Finger Gloves, L         24

863         Full-Finger Gloves, L         25

863         Full-Finger Gloves, L         26

863         Full-Finger Gloves, L         28

863         Full-Finger Gloves, L         31

863         Full-Finger Gloves, L         33

863         Full-Finger Gloves, L         44

862         Full-Finger Gloves, M         22

862         Full-Finger Gloves, M         23

715         Long-Sleeve Logo Jersey, L    21

770         Road-650 Black, 52            21

*/