해커랭크 HackerRank The PADS Advanced Select

The PADS

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).
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 lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

 1번에는 name으로 정렬을 수행하고 concat으로 substring을 붙여 넣기 합니다. LEFT로 occupations의 한 글자만 따오는 쿼리를 작성해야 합니다.

2번은 group by로 occupation을 그룹화하고 count로 occupation 그룹별 개수를 노출하고 occupation 개수로 오름차순으로 정렬 수행 후 직업의 알파벳 순으로 정렬합니다.

---

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