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