๐๋ณธ ํฌ์คํ
์์ ์ฌ์ฉ๋๋ ํ
์ด๋ธ์ ์๋ฃ์ ์ถ์ฒ๋ HackerRank ์์ ๋ฐํ๋๋ค.
https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=false
๐์กฐ๊ฑด 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).
OCCUPATIONS ํ ์ด๋ธ์ ๋ชจ๋ Name๋ค์ ์ํ๋ฒณ์์ผ๋ก ์ ๋ ฌ๋ list๋ฅผ ์ฟผ๋ฆฌํ ๋ค์, Name ๋ค์ ๊ฐ Name์ ํด๋นํ๋ Occupation์ ์ฒซ ๋ฒ์งธ ๊ธ์๋ฅผ ํ์ํด๋ผ.
SELECT CONCAT(Name, '(', LEFT(Occupation,1), ')')
FROM OCCUPATIONS
ORDER BY Name ASC;
๐์กฐ๊ฑด 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.
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.
OCCUPATIONS ํ ์ด๋ธ์์ ๊ฐ Occupation์ ๋ฐ์ ํ์๋ฅผ ์ฟผ๋ฆฌํด๋ผ. ๋ฐ์ ํ์๋ฅผ ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์ ์ ๋ ฌํ๊ณ , ๋ค์ ํ์์ผ๋ก ์ถ๋ ฅํด๋ผ.
'There are a total of [occupation_count] [occupation]s.'
[occupation_count]๋ OCCUPATIONS ํ
์ด๋ธ์์ ๊ฐ Occupation์ ๋ฐ์ ํ์๋ฅผ ์๋ฏธํ๊ณ , [occupations]๋ฅผ ์๋ฌธ์๋ก ์ถ๋ ฅํด๋ผ.
๋ง์ฝ occupation_count๊ฐ ๊ฐ์ Occupation์ด ํ๋ ์ด์ ์กด์ฌํ ๊ฒฝ์ฐ, Occupation์ ์ค๋ฆ์ฐจ์์ผ๋ก ์ ๋ ฌํด๋ผ.
SELECT CONCAT('There are a total of ', count(*), ' ', Lower(Occupation),'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY count(*) ASC, Occupation ASC;
๐๋ฌธ์ ํด๊ฒฐ์ ์ํ ์์ด๋์ด
CONCAT(str1,str2,...)
LEFT(๋ฌธ์์ด,๊ธธ์ด)