[SQL문제풀기] The PADS

문제

Weather Observation Station 12 | HackerRank

Query an alphabetically ordered list of CITY names not starting and ending with vowels.

https://www.hackerrank.com/challenges/weather-observation-station-12/problem?isFullScreen=true

MySQL

내가 작성한 오답

(select concat(name,'(',left(occupation,1),')') occ
from OCCUPATIONS 
order by name asc)
union all 
(select concat('There are a total of ',count(*),' ',occupation,'s.') occ
from OCCUPATIONS 
group by occupation
order by count(*) asc);

내가 작성한 정답- left

select occ
from((select concat(name,'(',left(occupation,1),')') occ, 0 sort
        from OCCUPATIONS)
        union all 
        (select concat('There are a total of ',count(*),' ',lower(occupation),'s.') occ,
                count(*) sort
        from OCCUPATIONS 
        group by occupation)) o
order by sort, case when sort = 0 then occ else sort end;

내가 작성한 정답2 - substring, substr 둘 다 사용가능

sselect occ
from((select concat(name,'(',substring(occupation,1,1),')') occ, 0 sort
        from OCCUPATIONS 
        order by name asc)
        union all 
        (select concat('There are a total of ',count(*),' ',lower(occupation),'s.') occ,
                count(*) sort
        from OCCUPATIONS 
        group by occupation
        order by count(*) asc)) o
order by sort, case when sort = 0 then occ else sort end;

Oracle

내가 작성한 오답

select occ
from (select name||'('||substr(occupation,1,1)||')' occ, 0 sort
    from OCCUPATIONS)
    union all
    (select 'There are a total of '||count(*)||' '||lower(occupation)||'s.' occ
            , count(*) sort
    from OCCUPATIONS
    group by occupation)
order by sort asc, case when sort=0 then occ else sort end;

내가 작성한 정답

select occ
from ((select name||'('||substr(occupation,1,1)||')' occ, 0 s, null o
    from OCCUPATIONS)
    union all
    (select 'There are a total of '||count(*)||' '||lower(occupation)||'s.' occ
            , 1 s, count(*) o
    from OCCUPATIONS
    group by occupation))
order by s asc, case when s=0 then occ else to_char(o) end;

MS SQL Server

내가 작성한 오답

select occ
from((select concat(name,'(',left(occupation,1),')') occ, 0 s
        from OCCUPATIONS)
        union all 
        (select concat('There are a total of ',count(*),' ',lower(occupation),'s.') occ,
                count(*) s
        from OCCUPATIONS 
        group by occupation)) o
order by s, case when s = 0 then occ else cast(s as char) end;

내가 작성한 정답 - left

select occ
from((select concat(name,'(',left(occupation,1),')') occ, 0 s, occupation
        from OCCUPATIONS)
        union all 
        (select concat('There are a total of ',count(*),' ',lower(occupation),'s.') occ, count(*) s, occupation
        from OCCUPATIONS 
        group by occupation)) o
order by s, case when s = 0 then occ else cast(s as char) end, occupation;

내가 작성한 정답3 - substring

select occ
from((select concat(name,'(',substring(occupation,1,1),')') occ, 0 s, occupation
        from OCCUPATIONS)
        union all 
        (select concat('There are a total of ',count(*),' ',lower(occupation),'s.') occ, count(*) s, occupation
        from OCCUPATIONS 
        group by occupation)) o
order by s, case when s = 0 then occ else cast(s as char) end, occupation;

DB2 - Oracle과 동일

내가 작성한 정답

select occ
from ((select name||'('||substr(occupation,1,1)||')' occ, 0 s, null o
    from OCCUPATIONS)
    union all
    (select 'There are a total of '||count(*)||' '||lower(occupation)||'s.' occ
            , 1 s, count(*) o
    from OCCUPATIONS
    group by occupation))
order by s asc, case when s=0 then occ else to_char(o) end;

정리!