HackerRank-The PADS

1. 문제

Generate the following two result sets:

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).

 

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.

 

 

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true

 

 

2. 문제 조건

 

1. Generate the following two result sets

 

-> 두 테이블을 합치기 (UNION, UNION ALL)

 

2. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical,   For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

 

-> 첫번째 테이블 형식 

  - 이름 + (직업 첫 글짜)

  - 이름 오름차순 정렬

 

3.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 alphabet

ically.

 

-> 두번째 테이블 형식

  - There are a total of [직업 수] [직업 명]s.에 맞게 출력

  - 직업 수, 직업 명 순으로 정렬

  - 직업 명은 모두 소문자로

 

3. 코드 작성

 

WITH first AS (
            SELECT CONCAT(name, CASE 
                                 WHEN occupation = 'Actor' THEN '(A)'
                                 WHEN occupation = 'Doctor' THEN '(D)'
                                 WHEN occupation = 'Professor' THEN '(P)'
                                 WHEN occupation = 'Singer' THEN '(S)'
                                END) AS namepro
            FROM occupations)
            
    , second AS (
            SELECT LOWER(occupation) AS occupation
                 , COUNT(*) AS cnt
            FROM occupations
            GROUP BY occupation
            ORDER BY cnt, occupation)

SELECT namepro
FROM first

UNION ALL

SELECT CONCAT('There are a total of ', cnt, ' ', occupation, 's.')
FROM second

ORDER BY namepro

 

 

 

4. 회고

 

• UNION, UNION ALL을 할때 각각의 테이블을 정렬하고 싶다면 다음과 같은 방법을 활용해야된다.
  • 1. 맨 마지막에 그 조건을 넣기
  • 2. subquery를 이용하기
• 이렇게 하지 않고 각각의 테이블에 ORDER BY를 넣을 경우 정렬이 적용되지 않는다.