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 apply correlated subquery with averaging?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor demonstrate correlated subqueries.

-- SQL correlated (o.ProductID = ProductID) subquery
-- list all the order ids with order quantity in the bottom quartile

SELECT DISTINCT o.OrderId, o.ProductID

FROM   Northwind.dbo.[Order Details] o

WHERE  Quantity <= (SELECT avg(Quantity) * .25

                    FROM   Northwind.dbo.[Order Details]

                    WHERE  o.ProductID = ProductID)

                   

/* OrderId ProductID

10609       1

10700       1

10838       1

11003       1

11005       1

10851       2

10722       2  .... */

 

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

 

-- Create table for demo purposes

SELECT * INTO dbo.Department from AdventureWorks.HumanResources.Department

GO

 

-- UPDATE with correlated subquery

UPDATE D

SET Name =

(

 SELECT Name FROM dbo.Department

 WHERE DepartmentID = D.DepartmentID

)

FROM AdventureWorks.HumanResources.Department D;

 

 

Related article:

 

http://www.sqlusa.com/bestpractices/derivedcorrelated/

 

 
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