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 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
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