SQL | HackerRank 문자열을 이어붙이기 (CONCAT)

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 lowercaseoccupation 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:

 

MySQL에서는 문자열을 연결할 때 `||` 연산자를 사용할 수 없습니다.  대신 `CONCAT()` 함수를 사용해야 합니다.  이 포스팅에서는 `OCCUPATIONS`라는 테이블을 기반으로 다음 두 가지 작업을 실습해봅니다:

 

1. 이름 뒤에 직업의 첫 글자를 괄호로 붙이기 (예: `Meera(S)`)

2. 각 직업별 인원 수 출력하기 (예: `There are a total of 3 singers.`)

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

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

• CONCAT(): 문자열을 이어붙이는 MySQL 함수
• LEFT(OCCUPATION, 1): 직업의 첫 글자 추출
• ORDER BY NAME: 이름 기준 정렬
• COUNT(*): 각 직업별 인원 수 계산
• LOWER(): 직업명을 소문자로
• 's.': 직업명 뒤에 ‘s.’ 붙여서 복수형 처리
• ORDER BY COUNT(*): 인원 수 기준 오름차순 정렬
• 인원 수가 같을 경우, ORDER BY OCCUPATION 으로 사전순 정렬