[HackerRank/SQL] Advanced Select - The PADS

Problem. Advanced Select - The PADS

---

Link.

https://www.hackerrank.com/challenges/the-pads/problem?utm_campaign=challenge-recommendation&utm_medium=email&utm_source=24-hour-campaign 

The PADS | HackerRank

---

Description.

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.

 

 

다음 두 결과 set를 반환하는 SQL문을 작성하시오.

 

 

 

OCCUPATIONS 테이블

OCCUPATIONS 예시

---

Key Point. 

CONCAT, 여러 문자열 혹은 컬럼 값을 합쳐서 반환하는 함수

• ex) CONCAT('Hi', ' ', 'I am', ' ', 'Joy') → ('Hi I am Joy)

SUBSTRING, 문자열을 추출하는 함수

• SUBSTRING(원본 분자열, 시작 위치 값, 가져올 문자열의 길이 값)
• ex) SUBSTRING('Doctor', 1, 1) → 'D'

LOWER, 소문자 변경 함수

• LOWER(문자열)
• ex) LOWER('AbcD') → 'abcd'

UPPER, 소문자 변경 함수

• UPPER(문자열)
• ex) UPPER('abCd') → 'ABCD'

---

My Answer. 

SELECT CONCAT(NAME, '(', Substring(OCCUPATION, 1, 1), ')') as Name
FROM OCCUPATIONS
ORDER BY NAME;
SELECT CONCAT('There are a total of', ' ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION),'s.') as TOTAL
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY TOTAL;

---

References. 

CONCAT: https://extbrain.tistory.com/52

[MySQL] 여러 문자열를 하나의 문자열로 합치기 (CONCAT 함수)