Query exercise

직업별 컬럼을 생성하여 정리하되 doctor, professor, singer, actor순서로 정렬하며
각 컬럼은 alpabet 오름 차순으로 정렬
Data

iSQL> select * from occupations;
NAME                  OCCUPATION
-----------------------------------------------
Ashley                   Professor
Samantha              Actor
Julia                      Doctor
Britney                 Professor
Maria                   Professor
Meera                  Professor
Priya                   Doctor
Priyanka              Professor
Jennifer               Actor
Ketty                   Actor
Belvet                  Professor
Naomi                 Professor
Jane                    Singer
Jenny                  Singer
Kristeen              Singer
Christeen            Singer
Eve                    Actor
Aamina              Doctor
18 rows selected.



Output

DOCTOR                PROFESSOR             SINGER                ACTOR
---------------------------------------------------------------------------------------------
Aamina                Ashley                Christeen             Eve
Julia                     Belvet                Jane                  Jennifer
Priya                    Britney               Jenny                 Ketty
                            Maria                 Kristeen              Samantha
                            Meera
                            Naomi
                            Priyanka
7 rows selected.

문제 출처: hackerrank

My Answer

select doctor, professor, singer, actor from
(
select rownum rown from (select name as doctor from occupations where occupation = 'Doctor' order by name)
union
select rownum rown from (select name as professor from occupations where occupation = 'Professor' order by name)
union
select rownum rown from (select name as singer from occupations where occupation = 'Singer' order by name)
union
select rownum rown from (select name as actor from occupations where occupation = 'Actor' order by name)
) main,
(select rownum rown, doctor from (select name as doctor from occupations where occupation = 'Doctor' order by name)) a,
(select rownum rown, professor from (select name as professor from occupations where occupation = 'Professor' order by name)) b,
(select rownum rown, singer from (select name as singer from occupations where occupation = 'Singer' order by name)) c,
(select rownum rown, actor from (select name as actor from occupations where occupation = 'Actor' order by name)) d
where main.rown=a.rown(+)
and main.rown=b.rown(+)
and main.rown=c.rown(+)
and main.rown=d.rown(+);