[해커랭크(HackerRank) SQL 풀이] - The PADS

해커 랭크 - https://www.hackerrank.com/
Prepare > SQL > Advanced Select > The PADS

 

HackerRank - Online Coding Tests and Technical Interviews

 

 

다음 두 가지 결과를 생성합니다.

1.  OCCUPATIONS 테이블에 있는 모든 Name의 알파벳순으로 목록을 조회하고 바로 뒤에 각 직업의 첫 글자를 괄호로 묶습니다. (예: AnActorName(A))

2. OCCUPATIONS 테이블에서 조회된 각 직업의 수를 조회합니다. 각 직업을 오름차순으로 정렬하고 다음 형식으로 출력합니다.

There are a total of [occupation_count] [occupation]s.

여기서 occupation_count는 조회된 직업의 수이고, occupations는 소문자로 된 직업의 첫 글자입니다.

동일한 직업이 두 개 이상일 경우 알파벳순으로 정렬해야 합니다.

 

Note: OCCUPATIONS 테이블에는 각 직업에 대해 최소한 두 개의 항목이 있습니다.

 

Input Format

OCCUPATIONS 테이블은 다음과 같습니다.

[출처] https://www.hackerrank.com/challenges/the-pads/problem

OCCUPATIONS 테이블에는 Doctor, Professor, Singer, Actor 값 중 하나만 포함됩니다.

 

Sample Input

[출처] https://www.hackerrank.com/challenges/the-pads/problem

 

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

• 첫 번째 쿼리의 결과는 문제 설명에 따라 형식이 지정됩니다.
• 두 번째 쿼리의 결과는 먼저 각 직업에 해당하는 수를 기준으로 오름차순으로 정렬되고(2 ≤ 2 ≤ 3 ≤ 3), 그다음 직업별 알파벳순으로 정렬됩니다. (doctor ≤ singer ≤ actor ≤ professor)

 

반응형

 

MySQL

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) ASC, Occupation ASC;

 

Oracle

SELECT  Name||'('||SUBSTR(Occupation, 1, 1)||')'
FROM    OCCUPATIONS
ORDER BY Name;

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

 

MS SQL Server

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) ASC, Occupation ASC;

 

 

---

• MySQL, MSSQL에서는 CONCAT() 함수를 사용하여 문자열을 연결.
• ORACLE에서는 || 연산자를 사용하여 문자열을 연결.

 

• COUNT 
  • 지정된 열의 레코드 수를 반환

 

• MySQL과 MSSQL의  문자열 추출.
  • RIGHT() : 문자열에서 오른쪽부터 지정된 개수의 문자를 반환한다.
  • LEFT() : 문자열에서 왼쪽부터 지정된 개수의 문자를 반환한다.

 

• ORACLE의 문자열 추출.
  • SUBSTR() : 문자 단위로 동작하여 문자열의 시작 위치와 길이를 기반으로 추출한다. 끝에서부터 추출할 경우 음수 사용.
  • SUBSTRB() : 바이트 단위로 동작. 주로 멀티 바이트 문자(예: 한글, 중국어 등)를 다룰 때 사용.

 

• 대문자로 치환하여 검색 시 : UPPER()
• 소문자로 치환하여 검색 시 : LOWER()

 

• ORDER BY 구문은 결과를 정렬하는 데 사용.
• 기본적으로 오름차순(ascending)으로 정렬.
• ASC (Ascending) : 오름차순. 숫자의 경우 작은 값부터 큰 값 순서로 정렬. 문자열의 경우 알파벳 순서로 정렬.
• DESC (Descending) : 내림차순. 숫자의 경우 큰 값부터 작은 값 순서로 정렬. 문자열의 경우 알파벳의 역순으로 정렬.

---

 

 

개인 공부를 위한 포스팅입니다.
모든 번역, 코드는 완벽하지 않을 수 있습니다.