문제
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:
There are a total of [occupation_count] [occupation]s.
Plain Text
복사
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.
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.
Plain Text
복사
정답
SELECT CONCAT(Name,
'(',
LEFT(Occupation, 1),
')')
FROM OCCUPATIONS
ORDER BY Name;
SELECT CONCAT('There are a total of ',
COUNT(*),
' ',
LOWER(Occupation),
's.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(*), Occupation;
SQL
복사
풀이
•
CONCAT의 사용방법을 묻는 문제이다.
•
CONCAT() 함수를 사용하면 여러 문자열을 하나의 문자열로 묶을 수 있다.
CONCAT('expression1', 'expression2', ..., 'expression n')
SQL
복사
•
LOWER() 함수는 문자열을 소문자열로 만들어 주는 함수이다.
•
직업별 사람 수가 필요하므로 GROP BY를 통해 직업별로 그룹화를 하고 COUNT를 통해 개수를 세면 직업별 사람 수를 알 수 있다.
SELECT CONCAT('There are a total of ',
COUNT(*),
' ',
LOWER(Occupation),
's.')
FROM OCCUPATIONS
GROUP BY Occupation
SQL
복사