Subquery ALL SQL Server Example: Subquery ALL modifier Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

Subquery ALL SQL Server Example: Subquery ALL modifier Example

Purpose: – Illustrates the SQL Server syntax for the subquery all modifier.

Syntax: WHERE expression comparison_operator [ANY | ALL] (subquery) – ALL in subquery means all rows in the subquery must meet the condition

 T-SQL | 
 copy code |
?
 
-- SUBQUERY ALL 
-- PURPOSE: ALL in subquery means all rows in the subquery must meet the condition 
-- SYNTAX For Subquery ALL
-- Statements that include a subquery usually take one of these formats: 
-- •WHERE expression [NOT] IN (subquery)
-- •WHERE expression comparison_operator [ANY | ALL] (subquery)
-- •WHERE [NOT] EXISTS (subquery)
 
create table people(
     ID          int,
     name        varchar (20),
     height      int )
 GO
 
 create table countries(
     ID              int,
     country varchar  (20),
     averageheight   int)
 GO
 
 
 insert into people (ID, name, height) values (1,  'Paul', 72)
 GO
 
 insert into people (ID, name, height) values (2,  'John', 69)
 GO
 
 insert into people (ID, name, height) values (3,  'Mark', 75)
 GO
 
 insert into people (ID, name, height) values (4,  'Steve', 65)
 GO
 
 
 
 
 
 insert into countries(ID, country, averageheight) values(1,'US',70)
 GO
 
 insert into countries(ID, country, averageheight) values(2,'Canada',69)
 GO
 
 insert into countries(ID, country, averageheight) values(3,'Mexico',67)
 GO
 
 insert into countries(ID, country, averageheight) values(4,'India',73)
 GO
 
 
 
 
 
 
 
 select * from people;
 GO
select * from countries;
 GO
 
 -- The height of the people selected needs to be greater than the height 
 -- of all of the countries
 
 SELECT p.ID,p.name
 FROM People p
 WHERE p.height > ALL (SELECT averageHeight FROM countries c)
 GO
 
 
 drop table people;
 drop table countries;
 GO
 
 
 

Sample Output for Subquery ALL Example

Related posts:

1. SQL Server 2005:(10 Hours) Backup SQL Server 2005, SQL Server Insert, SQL Server Training, SQL Server Profiler, Download SQL Server 2005, SQL Server Script, SQL Server Tutorial 10 Free One-Hour Videos From Microsoft Expert, Bryan Von Axelson....
2. SQL Server 2005 Express Tutorial: (13 Videos) Database mirroring, backup sql server 2005, sql server 2005 replication, sql server standard edition, download sql server 2005, compare sql server, sql server instance Excellent SQL Server 2005 Express series of 13 free videos...
3. SQL Server / T-SQL Examples Here is a library of SQL Server / T-SQL Examples....
4. SQL Server School (17 Hours): Sql Server Connection, Sql Server Stored Procedure, Sql Server Profiler, Trusted Sql Server, Sql Query Analyzer, and More I have found these 17 hours of video tutorials from...
5. Visual Studio, Visual Basic.NET, C# Database, SQL Server, and ASP.NET Video Tutorials – August 23, 2009 Visual Studio 2010 and .NET Framework 4 Beta 1...

Related posts brought to you by Yet Another Related Posts Plugin.