๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

SQL

[SQL] MySQL์—์„œ๋Š” ์•ˆ๋˜๋Š” ํ’€์ด(HackerRank - The PADS ๋ฌธ์ œ)

๋ฐ˜์‘ํ˜•

๐Ÿ”Š ๋ณธ ํฌ์ŠคํŒ…์—์„œ ์‚ฌ์šฉ๋˜๋Š” ํ…Œ์ด๋ธ”์˜ ์ž๋ฃŒ์™€ ์ถœ์ฒ˜๋Š” HackerRank ์ž„์„ ๋ฐํž™๋‹ˆ๋‹ค. ๋” ๋‹ค์–‘ํ•œ SQL ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด์‹œ๋ ค๋ฉด

HackerRank ์‚ฌ์ดํŠธ๋ฅผ ๋ฐฉ๋ฌธํ•ด ๋ณด์„ธ์š”!

 

HackerRank ์‚ฌ์ดํŠธ

 

์ด๋ฒˆ ํฌ์ŠคํŒ…์€ HackerRank ์‚ฌ์ดํŠธ์˜ The PADS ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค. ๋‚œ์ด๋„๋Š” Medium ์ด์ง€๋งŒ ์˜ˆ์ƒ ์™ธ๋กœ ์‰ฝ๊ฒŒ ํ•ด๊ฒฐํ–ˆ๋‹ค. ํ•˜์ง€๋งŒ ์–ด๋ ต์ง€ ์•Š์€ ๋ฌธ์ œ์ž„์—๋„ ๋ธ”๋กœ๊ทธ์— ๊ฒŒ์‹œํ•˜๋Š” ์ด์œ ๋Š” ๋ฐ”๋กœ MySQL์ด ์•„๋‹Œ MS SQL์„ ์ด์šฉํ•ด ํ•ด๊ฒฐํ–ˆ๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ์ฆ‰, MySQL ๋ฌธ๋ฒ•์œผ๋กœ๋Š” ํ•ด๊ฒฐํ•  ์ˆ˜ ์—†๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ๋ฌธ์ œ ์›๋ณธ์€ ์—ฌ๊ธฐ๋ฅผ ํด๋ฆญํ•ด ์‚ดํŽด๋ณด๊ณ  ๋ฌธ์ œ์—์„œ ์š”๊ตฌํ•˜๋Š” ์‚ฌํ•ญ์„ ํ™•์ธํ•ด๋ณด์ž.

 

๋ฌธ์ œ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ 2๊ฐ€์ง€ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•˜๊ธธ ์›ํ•œ๋‹ค.

 

  1. ์ฃผ์–ด์ง„ ํ…Œ์ด๋ธ”์˜ Name์„ ์•ŒํŒŒ๋ฒณ ์ˆœ์„œ๋กœ ์ •๋ ฌํ•ด ์ถœ๋ ฅํ•˜๋Š”๋ฐ, ๋์— ๊ฐ Name์— ํ•ด๋‹นํ•˜๋Š” Occupation์˜ ์ฒซ ๋ฌธ์ž์—ด์„ ์ฒจ๊ฐ€ํ•ด์„œ ์ถœ๋ ฅ์‹œ์ผœ๋ผ. ex) James๋ผ๋Š” ์ด๋ฆ„์ด Professor ์ง์—…์„ ๊ฐ–๊ณ  ์žˆ๋‹ค๋ฉด, James(P) ๋กœ ์ถœ๋ ฅํ•ด๋ผ.
  2. ๊ฐ Occpuation์— ์†ํ•˜๋Š” Name์„ ์นด์šดํŠธํ•ด๋ผ. ์ด ๋•Œ ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ถœ๋ ฅํ•˜๋Š”๋ฐ, 1์ฐจ ์ •๋ ฌ๊ธฐ์ค€์€ [occupation_count] ์˜ค๋ฆ„์ฐจ์ˆœ์œผ๋กœ ํ•˜๊ณ  2์ฐจ ์ •๋ ฌ๊ธฐ์ค€์€ [occpuation]์˜ ์•ŒํŒŒ๋ฒณ ์ˆœ์„œ๋กœ ์ •๋ ฌํ•ด๋ผ.

2๋ฒˆ์—์„œ ์š”๊ตฌํ•˜๋Š” ์ถœ๋ ฅ ํ˜•ํƒœ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

There are a total of [occupation_count] [occpuation]s.

๊ทธ๋ž˜์„œ ์ตœ์ข… ์ถœ๋ ฅ ์˜ˆ์‹œ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

์ถœ๋ ฅ ์˜ˆ์‹œ

 

๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ ๊ฐ€์žฅ ํ•ต์‹ฌ์ ์ธ ๋ฌธ๋ฒ•์€ ๋ฌธ์ž์—ด ์—ฌ๋Ÿฌ๊ฐœ๋ฅผ ์ด์–ด ๋ถ™์ด๋Š” CONCAT() ์ด์—ˆ๋‹ค. ๋˜ ๋ฌธ์ž์—ด ์ผ๋ถ€๋ฅผ ์ถ”์ถœํ•˜๋Š” LEFT() ๋‚˜ ์†Œ๋ฌธ์ž๋กœ ๋ฐ”๊พธ์–ด์ฃผ๋Š” LOWER() ์ด ์‚ฌ์šฉ๋˜์—ˆ๋‹ค. ์ด์™ธ์— ์‚ฌ์šฉ๋œ ๋ฌธ๋ฒ•์€ ํ‰์†Œ์— ์ž์ฃผ ์—ฐ์Šตํ•ด๋ดค๋˜ ๊ธฐ๋ณธ์ ์ธ ๋ฌธ๋ฒ•๋“ค์ด์—ˆ๋‹ค. ํ’€์ด๋ฅผ ์‚ดํŽด๋ณด์ž.

 

SELECT CONCAT(name, '(', LEFT(occupation, 1), ')')
FROM Occupations
ORDER BY name

SELECT CONCAT('There are a total of ', COUNT(name), ' ', LOWER(occupation),'s.')
FROM Occupations
GROUP BY occupation
ORDER BY COUNT(name), occupation

 

ํ’€์ด๊ฐ€ ๋น„๊ต์  ๊ฐ„๋‹จํ•˜๋‹ค. ํ•˜์ง€๋งŒ ํ•„์ž๊ฐ€ ์ฒ˜์Œ ๋ช‡ ๋ฒˆ ์‹œ๋„์—์„œ ๋ง‰ํ˜”๋˜ ๋ถ€๋ถ„์ด ์žˆ์—ˆ๋Š”๋ฐ ๋ฐ”๋กœ MySQL์„ ์‚ฌ์šฉํ•œ ์ ์ด์—ˆ๋‹ค. MySQL์„ ํ™œ์šฉํ•ด ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด ์œ„ ์ฟผ๋ฆฌ ์ƒํƒœ์—์„œ SELECT ๋ฌธ ์‚ฌ์ด์— UNION, UNION ALL์„ ์‚ฌ์šฉํ•˜๊ธฐ๋„ ํ–ˆ๋Š”๋ฐ ์ž‘๋™ํ•˜์ง€ ์•Š์•˜๋‹ค. ์™œ๋ƒํ•˜๋ฉด UNION ๊ฐ™์€ ๊ฒฝ์šฐ, ๋‘ ๊ฐœ์˜ ์ฟผ๋ฆฌ๋ฅผ ์—ฐ์†์ ์œผ๋กœ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ๋‘ ๊ฐœ์˜ ์ฟผ๋ฆฌ๊ฐ€ ์ถ”์ถœํ•˜๋ ค๋Š” ์นผ๋Ÿผ์ด ๋ชจ๋‘ ๊ฐ™์•„์•ผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค. ์ฐธ๊ณ ๋กœ UNION, UNION ALL ๋ฌธ๋ฒ•์˜ ์ฐจ์ด์ ์€ ์ค‘๋ณต๋œ ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜์ง€ ์•Š์œผ๋ฉด UNION, ์ค‘๋ณต๋œ ๋ฐ์ดํ„ฐ๊นŒ์ง€ ๋ชจ๋‘ ์ถœ๋ ฅ์‹œํ‚ค๋ ค๋ฉด UNION ALL์ด๋ผ๋Š” ์ ์ด๋‹ค.

 

๊ทธ๋ž˜์„œ MS SQL์„ ์‚ฌ์šฉํ•ด๋ณด์•˜๊ณ  ์œ„ ์ฟผ๋ฆฌ๋ฅผ ์ œ์ถœํ–ˆ๋”๋‹ˆ ์ •๋‹ต์œผ๋กœ ์ฒ˜๋ฆฌ๋˜์—ˆ๋‹ค. ๋”ฐ๋ผ์„œ ๋‘ ๊ฐœ ์ด์ƒ์˜ ์ฟผ๋ฆฌ๋ฅผ ํ•œ ๋ฒˆ์— ์ถœ๋ ฅํ•  ๋•Œ, ์ถ”์ถœํ•˜๋ ค๋Š” ๊ฐ’๋“ค์ด ์„œ๋กœ ๋‹ค๋ฅผ ๊ฒฝ์šฐ MS SQL์„ ์ด์šฉํ•ด ๋‹จ์ˆœํžˆ ์ฟผ๋ฆฌ๋ฅผ ์—ฐ์†์ ์œผ๋กœ ๋‚˜์—ดํ•ด์ฃผ๋ฉด ๋œ๋‹ค.

 

๋˜ํ•œ MS SQL๋„ UNION, UNION ALL ๋ฌธ๋ฒ•์„ ์ œ๊ณตํ•œ๋‹ค๋Š” ์  ์ฐธ๊ณ ๋กœ ์•Œ์•„๋‘์ž.

๋ฐ˜์‘ํ˜•