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

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

대강해석

1. Occupations 테이블의 모든 이름을 알파벳 순서로 출력해 주세요. 단 이름 뒤에는 직업의 첫 글자와 함께 출력 되어야 합니다.
   • AnActorName(A) -> Actor
2. Occupations 테이블의 각 직업의 명 수를 출력해주시고 오름차순으로 정렬해 주세요. 만약 직업의 인원이 같으면 직업의 알파벳 순서로 정렬해 주세요.
   • There are a total of [직업의 명 수] [직업 소문자]s. 의 형태로 출력해주세요.

필요한 기능

• Upper -> 문자열을 대문자로 변경 해준다.
  • Upper("hello world") = HELLO WORLD
• Lower -> 문자열을 소문자로 변경 해준다.
  • Lower("HELLO WORLD") = hello world
• Concat -> 문자열을 하나로 합쳐준다.
  • Concat(문자열1, 문자열2, 문자열3, ...)
  • Concat("hello"," ","world") = hello world
• Substr -> 문자열의 일부만을 가져온다.
  • Substr(문자열, idx, len)
  • Substr("hello world",2,3) = ell
• Group by -> column를 해당 column의 값으로 묶어준다.

Name	Occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor

 

Select Occupation, Count(Occupation) as Cnt

From Occupations

Group by Occupation;

Occupation	Cnt
Actor	2
Doctor	1
Singer	1
Professor	1

코드

SELECT concat(Name , "(" ,
    SUBSTR(occupation,1,1)
    , ")")
From OCCUPATIONS
Order by Name asc;

SELECT concat("There are a total of ",count(Occupation)," ",lower(Occupation),"s.")
From OCCUPATIONS
Group by Occupation
Order by count(Occupation) asc, Occupation asc;