Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
풀이
1. 직업 별 이름들을 서브쿼리로 추출해서 JOIN
- row_number() over(ORDER BY Name) 열 추가: 이름 오름차순 정렬 + JOIN 기준으로 하여, 빈 셀에 Null 삽입
- JOIN 방향은 이름 개수가 가장 많은 Professor 열 기준: 다른 열 기준 시 Professor의 이름이 잘리기 때문
SELECT d.Name, p.Name, s.Name, a.Name
FROM
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Doctor') AS d
RIGHT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Professor') AS p
ON d.r = p.r
LEFT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Singer') AS s
ON p.r = s.r
LEFT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Actor') AS a
ON s.r = a.r
-- 출력
Aamina Ashley Christeen Eve
Julia Belvet Jane Jennifer
Priya Britney Jenny Ketty
NULL Maria Kristeen Samantha
NULL Meera NULL NULL
NULL Naomi NULL NULL
NULL Priyanka NULL NULL
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.
