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

Nobody23
  • 3
  • 1

1 Answers1

3

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
                 );
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • If i removed the aliases i am getting the ORA-00942: table or view does not exist error – Nobody23 Jun 22 '21 at 14:51
  • I added some more info into the answer; have a look, please. – Littlefoot Jun 22 '21 at 14:56
  • Hey! Could tell me where can i learn oracle sql including the pl/sql part. – Nobody23 Jun 22 '21 at 15:04
  • I used documentation. You didn't specify database version you use (and I doubt it is an old 10g), but - this page: https://docs.oracle.com/cd/B19306_01/index.htm contains books placed into useful sections. I'd suggest 3 books: SQL Reference, PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals. Find the ones appropriate to your database version (or start with what I suggested, then learn the differences (new functions etc.)). – Littlefoot Jun 22 '21 at 15:09
  • Sorry for not mentioning the version, I want to learn Oracle 11g version. Thanks for the suggestions. – Nobody23 Jun 22 '21 at 15:16