ORA-00933: SQL command not properly ended in oracle database

Question

select 
  * 
from 
  (
    select 
      rating, 
      avg(age) as avgage 
    from 
      sailors 
    group by 
      rating
  ) as temp 
where 
  temp.avgage = (
    select 
      min(temp.avgage) 
    from 
      temp
  );

When i am trying to run the above command i am getting the following error

ORA-00933: SQL command not properly ended

The Sailors table looks like this Sailors Table

Could you tell why i am getting this error?

Answer 1

Table aliases in Oracle don't have AS (columns can, but don't have to).

Therefore:

No :   ) as temp 
Yes:   ) temp

---

As of ORA-00942: temp isn't accessible in a subquery. But, if you use it (the temp) as a CTE or an inline view (so that it is a source readable by the whole query), then it should be OK. Something like this:

with temp as
  (select rating, avg(age) as avgage
   from sailors
   group by rating
  )
select *
from temp a
where a.avgage = (select min(b.avgage)
                  from temp b
                 );