HackerRank - The PADS

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

The PADS | HackerRank

 

문제

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. There are a total of [occupation_count] [occupation]s.

Note: There will be at least two entries in the table for each type of occupation.

Input Format

The OCCUPATIONS table is described as follows: 

 Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

An OCCUPATIONS table that contains the following records:

Sample Output

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

 

풀이

concat(), substring(), lower() 만 알면 풀기 쉬운 문제이다. 바로 위에 있는 샘플 출력값이랑 같이 나오게 문자열을 가공해서 concat 해주면 된다.

 

/*
Enter your query here.
*/
select concat(name, "(", substr(occupation, 1, 1), ")")
from occupations
order by name;

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