Subqueries/nested queries.
is why SQL is "structured" query language.
a Select within another statement (Select, Insert, Update, Delete,Create).

Aggregate function can not be in Where clause:
  !! select * from pet where dob=max(dob);
 Select makes only one pass thru the rows, thus doesn't know Max until end of pass,
thus can't be asking if each row equals max because doesn't yet know the max.
Need a Select to find Max then use that value in another Select.

 SELECT .. FROM .. WHERE f1 relOp (SELECT f2 FROM .. [WHERE..][GROUP BY..[HAVING..]])
  parens required around inner Select, which runs before outer Select.

Usually, subquery produces one column, thus it has only one Select item (f2),
  which is often an aggregate function:
  SELECT .. FROM .. WHERE f1 relOp (SELECT aggFunc(f2) FROM...)

Row holding maximum of certain field:
#youngest pet:
select * from pet where dob=(select max(dob) from pet);
 Note these can be done without subquery by using Order By and Limit 1:
select * from pet Order By dob Desc Limit 1;  
but sorting is inefficient and want to minimize queries to server, so subquery is better.

These can not be done without subquery:
Rows relOp an aggregate value:
#pets younger than average age:
select * from pet where dob>(select avg(dob) from pet);
#pets younger than youngest cat:
select * from pet where dob>(select max(dob) from pet where species='cat');
#pets cuter than 10 and younger than youngest cat:
select * from pet where dob>(select max(dob) from pet where species='cat')
                        and cuteness>10;
#pets that are > 25% of total pets weight:
select * from pet where weight>.25*(select sum(weight) from pet);

Rows < > a particular row's column value: No aggregate function in subquery
#pets younger than Claws:
select * from pet where dob>(select dob from pet where name='claws');
#pets born in same month as Fang:
select * from pet where month(dob)=(select month(dob) from pet where name='fang');
#species that occur exactly twice:
select * from pet as t where 2=(select count(*) from pet where pet.species=t.species);

#row of largest weight either subquery or sort and first:
select * from pet order by weight desc limit 1; 
select * from pet where weight=(select max(weight) from pet); 

  subquery can be another table:
SELECT .. FROM t1 WHERE fi relOp (SELECT .. FROM t2 ..)
#pets older than the first (oldest) event:
select * from pet where birth<(select min(date) from event);

values that are [not] IN another table:
SELECT .. FROM t1 WHERE f1 [NOT] IN (SELECT f2 FROM t2)
#names in pet that are not names in event:
select * from pet where name not in (select name from event);
#pets born on the same date as any event:
select name,birth from pet where birth in (select date from event);
#owners of pets who've had birthdays:
select owner from pet where name in (select name from event where type='birthday');

  Cannot have Order By in subquery (would be useless anyway, since result is not seen).

  the result of a  subquery that returns 0 values is Null.
  comparing anything against null is Null:
SELECT .. FROM t1 WHERE fi = (subquery is empty set)
#owners of pets that have had a lobotomy:
select owner from pet where name in (select name from event where type='lobotomy');

  subquery can be in Select clause:
SELECT fi,fj*(SELECT Avg(fk) FROM t) FROM t
  select name,weight,weight-(select avg(weight) from pet) from pet;

subquery as simpler, more readable, easier to maintain than equivalent
complex join or union ?
join often more efficient?