MySQL | HackerRank | The PADS

Problem

Generate the following two result sets:

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). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
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.
3. There are a total of [occupation_count] [occupation]s.

Note: There will be at least two entries in the table for each type of occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

An OCCUPATIONS table that contains the following records:

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

The results of the first query are formatted to the problem description's specifications.
The results of the second query are ascendingly ordered first by number of names corresponding to each profession (2≤2≤3≤3), and then alphabetically by profession (doctor≤singer, and actor≤professor).

 

요약하자면,
1. 알파벳 순으로 정렬한 이름과 함께, 해당 사람의 직업 첫글자를 괄호안에 넣어 출력하라
2. There are a total of '테이블 내 직업 수' '해당 직업's. 의 형식으로 출력하라
    (이 때, 직업은 소문자로 출력하며 직업 수와 직업을 오름차순으로 정렬한다.)

 

Solve

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(str1, str2, ...) 문자열 합치는 함수, MySQL의 경우 '||' 로 문자열 합치기 가능
• LEFT('문자열',len) 왼쪽부터 문자열 자르는 함수
• LOWER('문자열') 소문자로 변환하는 함수

Issue

처음 접근한 쿼리는 아래 쿼리이다.

SELECT CONCAT(Name, '(', LEFT(Occupation,1), ')')
FROM OCCUPATIONS
ORDER BY NAME

UNION ALL

SELECT CONCAT('There are a total of ', CONVERT(a.occup_cnt,char), ' ', LOWER(a.Occupation), 's.')
FROM(SELECT Occupation, count(Occupation) occup_cnt 
     FROM OCCUPATIONS 
     GROUP BY Occupation) a
ORDER BY a.occup_cnt, a.Occupation

UNION ALL 로 첫번째 출력과 두번째 출력을 같이 나타내고자 했다.

쿼리를 돌려봤는데 아래와 같은 SQL에러가 났다.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ~~

구글링을 해보니 예약어를 사용하면 만날 수 있는 에러였고, Union All 때문인 걸 확인할 수 있었다.

(그리고 문제에서도 첫번째 쿼리, 두번째 쿼리라고 명시가 되어있다. 문제를 꼼꼼히 읽어보자...나야..^^)

 

Union All 을 제외하고, 각각의 쿼리로 돌려보니, 두번째 쿼리의 2중 쿼리문 때문인지 런타임 에러가 났다.

이래서 쿼리성능이 중요하다고 하는거구나 느끼면서 글을 마무리 해본다.

 

---

출처

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

The PADS | HackerRank

https://henerd.tistory.com/24

[SQL 에러] You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right