with (allows database to treate query as inline view/temporary table

Ch6_2.sql

WITH WT_AVG AS
  (SELECT AVG(WEIGHT) AS AVG_WT FROM CHILDSTAT
  )
SELECT a.FIRSTNAME,
  a.GENDER,
  a.HEIGHT,
  a.WEIGHT
FROM CHILDSTAT a
WHERE a.GENDER = 'm'
AND a.HEIGHT   > 50
AND a.WEIGHT  <=
  (SELECT AVG_WT FROM WT_AVG
  )
UNION ALL
SELECT B.FIRSTNAME,
  B.GENDER,
  B.HEIGHT,
  B.WEIGHT
FROM CHILDSTAT B
WHERE B.GENDER = 'f'
AND B.HEIGHT   > 40
AND B.WEIGHT  <=
  (SELECT AVG_WT FROM WT_AVG
  )