[SQL] HackerRank - The PADS
Generate the following two result sets:
- 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).
- 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. - 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≤3≤3≤3), and then alphabetically by profession (doctor ≤ singer, and actor ≤ professor).
문제요약
OCCUPATIONS 테이블에서 OCCUPATION 컬럼은 Doctor, Singer, Actor, Professor 4가지 직업으로 이루어져 있다.
Name컬럼 알파벳 기준으로 정렬하여 출력하되 [이름(직업 첫 글자)] 형식으로 출력하고
마지막에
There are a total of [occupation_count] [occupation]s. 형식으로 직업 수가 많은 순부터 출력
.
풀이
# 1
select concat(name, '(', substr(occupation,1,1), ')') from occupations
order by name;
# 2
select concat('There are a total of', space(1), count(occupation), space(1), lower(occupation), 's.') from occupations
group by occupation
order by count(occupation), occupation;# 1
첫 번째 쿼리는 Ashely(P) 형식으로 이름에 직업 첫 글자를 붙이는 코드이다.
문자열을 연결시키는 CONCAT 함수를 통해 Name, (, substr(occupation,1,1), ) 를 각각 연결시켜주었다.
SUBSTR 안의 1,1은 첫 글자부터 1개의 글자를 추출하는 것을 의미한다.
# 2
두 번째 쿼리는 마찬가지로 CONCAT함수를 사용해 문자열을 조합하였는데 SPACE(1)은 1칸 공백, COUNT(Occupation)으로 직업 카운트를 센 후 group by를 통해 직업별 카운트 개수를 추출하게 만들었다. output의 모든 직업명이 소문자로 되어 있으므로 lower(Occupation)을 통해 소문자로 변환 해 주었다.
마지막으로 ORDER BY를 활용해 첫 번째 기준을 Occupation의 카운트 개수, 두 번째 기준을 Occupation(직업명) 순 으로정렬하였다.
결과
