Several SQL syntax questions
re 1) 

With standard SQL the only way to do that is:

select min(cnt)
from (
  select x, count(*) as cnt
  from y
  group by x
) t


re 2)

yes you can join to a query, but you need to give the derived table an alias

Select x 
From y 
  natural join (select z AS y
                from foo) as t;


That assumes that the table y also has a column y - otherwise there wouldn't be two identical columns that the natural join could use. 

But in general you should avoid the natural join. Use an explicit join instead:

select x 
from y 
  join (select z AS y
       from foo
  ) as t on t.y = y.id;


re 3)

No, from * is never allowed. But I have no clue what you intend to do with that. The sub-select used with &gt; (or &lt; or =) has to return exactly one row and exactly one column so you would need something like: 

Select x
From y
Group by x
Having avg(x) &gt; (select count(*) -- no idea what you would want to do here
                 from y
                 where x &gt; 1);


If the subselect returns more then one row you would need to use ANY

Select x
From y
Group by x
Having avg(x) &gt; ANY (select x -- still only ONE column allowed
                     from y
                     where x &gt; 1);