[HackerRank] MySQL - 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. 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.

Explanation

The results of the first query are formatted to the problem description's specifications.
The results of the second query are ascendingly ordered first by number of names corresponding to each profession (2≤2≤3≤3), and then alphabetically by profession (doctor ≤ singer, and actor ≤ professor).

 

 

 

 

풀이

 

select
    concat(name, '(', substring(occupation,1,1), ')')
from occupations
order by name, substr(occupation,1,1);

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

 

 

 

노트

 

이 문제에서는 문자열을 합치는 CONCAT 함수를 사용한다.

CONCAT 함수는 둘 이상의 문자열이나 컬럼을 순서대로 합쳐 반환해주는 함수이다.

CONCAT함수는 SELECT 문과 함께 사용되어 2개 이상의 열 데이터를 단일 열로 결합할 수 있다.

 

[SYNTAX]
CONCAT(문자열1, 문자열2, 문자열3, ...)

 

결합하고 싶은 문자열 또는 숫자를 쉼표( , )로 구분하여 입력해주면 된다.