[SQL문제풀기] The PADS

silver

Apr 13, 2025

Contents

문제 MySQL Oracle MS SQL Server DB2 - Oracle과 동일 정리!

문제

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

내가 작성한 오답

:union all 한 후의 정렬이 이상해졌다


(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

: union all을 하려는 각각의 select문 내에 order by 사용불가

내가 작성한 오답

: case when then으로 occ(문자)와 o(숫자)를 비교해서 정렬하려고 했다. → 타입이 맞지 않아 오류( MySql에서는 숫자를 먼저 정렬하고 문자를 나중에 순서대로 정렬해줬다)


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

: union all을 하려는 각각의 select문 내에 order by 사용불가

내가 작성한 오답

: singer가 actors보다 먼저 나왔다.


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과 동일

: union all을 하려는 각각의 select문 내에 order by 사용불가

내가 작성한 정답


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;

정리!

💡

union을 하고 정렬을 하기 위해서는 union문을 서브쿼리나 cte로 묶어준 후 정렬해야 한다.

정렬 시 숫자와 문자를 비교하는 것은 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);

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;

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;

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;

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;

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;

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;