[HackerRank] The PADS(MySQL)

문제

 

"이름" || "직업" 으로 구성된 테이블에서

1. "이름 (직업의 첫 글자)" 형태로 추출(이름 오름차순)

2. 각 직업의 수를 세서 "There are a total of 3 doctors"와 같이 출력(직업 count 오름차순, 직업명 오름차순)

 

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.

 

풀이

1. "이름 (직업의 첫 글자)"를 이름 기준 오름차순으로 출력

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

-- 출력
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)

 

2. 각 직업의 수를 세서 "There are a total of 3 doctors"를 직업 수 오름차순, 직업명 오름차순으로 출력

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

-- 출력
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.

 

3. 두 쿼리를 UNION으로 결합하여 한 줄로 출력하려고 했지만 MySQL에서 UNION을 사용할 때 ORDER BY가 적용되지 않아 오류 발생

SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
FROM OCCUPATIONS 
ORDER BY Name
UNION
SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), Occupation

-- 출력
-- ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual
-- that corresponds to your MySQL server version for the right syntax to use near 'UNION
-- SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occup' at line 4

 

4. 각 쿼리를 서브쿼리에 넣어 UNION 시도했지만 MySQL에서 UNION하면 ORDER BY로 한 정렬이 풀려버림

SELECT a.*
FROM (SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
      FROM OCCUPATIONS 
      ORDER BY Name) AS a
UNION
SELECT b.*
FROM (SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
      FROM OCCUPATIONS
      GROUP BY Occupation
      ORDER BY COUNT(Occupation), Occupation) AS b

-- 출력
Ashley(P)
Samantha(A)
Julia(D)
Britney(P)
Maria(P)
Meera(P)
Priya(D)
Priyanka(P)
Jennifer(A)
Ketty(A)
Belvet(P)
Naomi(P)
Jane(S)
Jenny(S)
Kristeen(S)
Christeen(S)
Eve(A)
Aamina(D)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.

 

정답

5. 두 쿼리를 UNION으로 묶지않고 ";"로 각 쿼리를 끝내 두 번 출력하는 방법으로 변경

SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,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;

-- 출력
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.