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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to use the ALL operator?

Execute the following SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the use of ALL, SOME and ANY set comparison operators.

-- SQL ALL, ANY, SOME set operators - SQL inner join - SQL subquery, subselect

USE AdventureWorks;

SELECT ProductName = p.Name,

-- SQL money currency conversion

       TotalOrder = '$' + convert(VARCHAR,TotalDue,1)

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 '1/1/2003' AND '12/31/2003'

       AND sod.OrderQty >

-- T-SQL ALL operator

       ALL (SELECT sod.OrderQty

            FROM   Sales.SalesOrderHeader AS soh

               INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderId = sod.SalesOrderID

            WHERE  soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')

GO

/* Results

 

ProductName                   TotalOrder

Full-Finger Gloves, L         $104,111.35

Women's Mountain Shorts, S    $144,026.40

Women's Mountain Shorts, L    $149,286.68

*/

 

-- SQL ANY

SELECT ProductName = p.Name,

       TotalOrder = '$' + convert(VARCHAR,TotalDue,1)

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 '1/1/2003' AND '12/31/2003'

       AND sod.OrderQty >

-- T-SQL ANY operator

       ANY (SELECT sod.OrderQty

            FROM   Sales.SalesOrderHeader AS soh

               INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderId = sod.SalesOrderID

            WHERE  soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')

GO

/* Partial results

 

ProductName             TotalOrder

Mountain-300 Black, 38  $47,633.19

Mountain-300 Black, 40  $47,633.19

Mountain-300 Black, 48  $47,633.19

Mountain-200 Black, 38  $47,633.19

*/

 

-- SQL SOME

SELECT ProductName = p.Name,

       TotalOrder = '$' + convert(VARCHAR,TotalDue,1)

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 '1/1/2003' AND '12/31/2003'

       AND sod.OrderQty >

-- T-SQL SOME operator

       SOME (SELECT sod.OrderQty

            FROM   Sales.SalesOrderHeader AS soh

               INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderId = sod.SalesOrderID

            WHERE  soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')

GO

/* Partial results

 

ProductName             TotalOrder

Mountain-300 Black, 38  $47,633.19

Mountain-300 Black, 40  $47,633.19

Mountain-300 Black, 48  $47,633.19

Mountain-200 Black, 38  $47,633.19

Mountain-200 Black, 42  $47,633.19

*/

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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