Optimizing select union select oracle

I had an interview recently and the recruiter told me to make a query on a table USERS with two fields (name, age) which should return a list with two columns | NAME | MAJOR OR MINOR |

My response was this :

(select NAME, 'MAGOR' as MAJ_MIN from USERS WHERE AGE >= 18 )
union
(select NAME, 'MINOR' as gender from USERS where AGE < 18);

​x
 
(select NAME, 'MAGOR' as MAJ_MIN from USERS WHERE AGE >= 18 )union(select NAME, 'MINOR' as gender from USERS where AGE < 18);​

Then, he told me that is correct, but we can do better!

So, my question is: How to do it in a better way?

Thank you in advance 🙂

Answer

I presume

select name, 
       case when age >= 18 then 'MAGOR' else 'MINOR' end maj_min
from users

 
select name,        case when age >= 18 then 'MAGOR' else 'MINOR' end maj_minfrom users​

Advertisement

Answer