DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the ALL operator?

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

*/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE