[해커랭크/MS SQL] The PADS

 

문제 링크입니다!

The PADS | HackerRank

 

문제

 

=> 두가지 결과로 생성해라

1. OCCUPATIONS 테이블에서 NAME 전부를 알파벳 순서대로 정렬하고 바로 뒤에 직업의 첫 글자(예: 괄호로 묶음)를 기재하여 조회해라
예: AnActorName(A), ADoxtorName(D), AProfessorName(P) 및 ASingerName(S)

2. OCCUPATIONS 테이블에서 각 직업의 발생 횟수를 오름차순으로 정렬하고 다음 형식으로 출력해라
형식 : There are a total of [occupation_count][occupation]s.
[operation_count]는 OCCUPATIONS 테이블에서 발생한 직업의 횟수이고 [operation]은 소문자 직업 이름이다.
둘 이상의 직업이 동일한 [occupation_count]을 가진 경우 알파벳 순으로 정렬해라.

 

---

 

풀이

SELECT CONCAT(NAME,'(', LEFT(OCCUPATION,1),')')
FROM OCCUPATIONS
ORDER BY NAME;

SELECT CONCAT('There are a total of ', COUNT(OCCUPATION),' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(OCCUPATION), OCCUPATION

 

• CONCAT(string_value1, string_value2 [, string_valueN ]) : 둘 이상의 문자열 값을 엔드투엔드 방식으로 연결하거나 조인한 결과 문자열을 반환
• LOWER : 소문자로

CONCAT(Transact-SQL) - SQL Server

LOWER(Transact-SQL) - SQL Server