[SQL] 해커랭크 HackerRank The PADS 문제 풀이

문제

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
   where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically
3. Note: There will be at least two entries in the table for each type of occupation.

해석

• 출력문 1 : 이름+(직업의 첫글자) 형태로 출력, 이름을 알파벳순으로 정렬
• 출력문 2: There are a total of [직업수] [직업이름]s. 형태로 출력, 직업 개수순, 직업이름순 으로 정렬

 

정답

select concat(name, '(', substr(occupation, 1,1), ')') name_O
from OCCUPATIONS
order by name;

select concat('There are a total of ', count(*), ' ', lower(occupation), 's.') 
from occupations
group by occupation
order by count(*), occupation;

 

 

배운 점

1. substr 은 문자의 특정 번호만 뽑을 때 사용하면 유용하다.
2. concat은 정말 만능인거 같다 ㅎㅎ
3. occupation으로 정렬했으니 count(occupation) 이나 count(*) 이나 같다.