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

6๏ธโƒฃ SQL/Problem Solving8

[MySQL] Programmers Lv.4, 5 ์ „์ฒด ๋ฌธ์ œ ํ’€์ด (2023 ์—…๋ฐ์ดํŠธ!) 23๋…„ ์—…๋ฐ์ดํŠธ๋œ Lv.4, 5 ๋ฌธ์ œ๋“ค๊นŒ์ง€ ๋ชจ๋‘ ํ’€์–ด๋ณด์•˜๋‹ค. :) Programmers์˜ ๋ชจ๋“  SQL ๋ฌธ์ œ๋“ค์„ ํ’€์–ด๋ณธ ํ›„๊ธฐ๋Š”! ํ™•์‹คํžˆ ์ตœ๊ทผ ์˜ฌ๋ผ๊ฐ„ ์ฝ”๋”ฉํ…Œ์ŠคํŠธ ๋‚œ์ด๋„์— ๋งž์ถฐ์„œ ์กฐ๊ธˆ ๋” ์ˆ˜์ค€๋†’์€ ๋ฌธ์ œ๋“ค์ด ์ƒ๊ธด ๊ฒƒ ๊ฐ™๋‹ค. ๊ทธ๋ฆฌ๊ณ  ์˜ˆ์ „์—๋Š” concat, substr, round, truncate, datediff์™€ ๊ฐ™์ด ๋‹ค์–‘ํ•œ ํ•จ์ˆ˜๋“ค์„ ์ด์šฉํ•˜๋Š” ๋ฌธ์ œ๋‚˜ ์„ธ ๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ์กฐ์ธํ•˜๋Š” ๋ฌธ์ œ, ๊ทธ๋ฆฌ๊ณ  ๋‘ ๊ฐœ ์ด์ƒ์˜ ์ปฌ๋Ÿผ์œผ๋กœ group by ํ•˜๋Š” ๋ฌธ์ œ ๋“ฑ ๋ฌธ์ œ์˜ ๋‹ค์–‘์„ฑ์ด ๋งค์šฐ ๋ถ€์กฑํ•ด์„œ Programmers ๋งŒ์œผ๋กœ SQL ์ฝ”๋”ฉํ…Œ์ŠคํŠธ๋ฅผ ๋Œ€๋น„ํ•˜๊ธฐ์—๋Š” ๋ฌด๋ฆฌ๊ฐ€ ์žˆ์—ˆ๋‹ค. ์ด์ œ๋Š” ์ƒˆ๋กญ๊ฒŒ ์—…๋ฐ์ดํŠธ๋œ ๋ฌธ์ œ๋“ค๊นŒ์ง€ ๋ฐ˜๋ณตํ•ด์„œ ํ’€์–ด๋ณธ๋‹ค๋ฉด, SQL ๋ฌธ์ œ ํ’€์ด ๋Œ€๋น„์— ์ถฉ๋ถ„ํ•˜๋‹ค๊ณ  ๋Š๊ปด์ง„๋‹ค. ์งฑ! ๐Ÿ‘ ๊ธฐ์กด ๋ฌธ์ œ๋“ค์˜ ํ’€์ด๋Š” ๐Ÿ‘‰์—ฌ๊ธฐ๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š” ! ์‹ํ’ˆ.. 2023. 6. 29.
[MySQL] Programmers Lv.3 ์ „์ฒด ๋ฌธ์ œ ํ’€์ด (2023 ์—…๋ฐ์ดํŠธ!) 2023๋…„ ์ƒˆ๋กญ๊ฒŒ ์—…๋ฐ์ดํŠธ๋œ Programmers Lv.3 ๋ฌธ์ œ๋“ค์„ ํ’€์ดํ–ˆ๋‹ค. ๊ธฐ์กด ๋ฌธ์ œ๋“ค์˜ ํ’€์ด๋Š” ๐Ÿ‘‰์—ฌ๊ธฐ๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š”! ์กฐ๊ฑด๋ณ„๋กœ ๋ถ„๋ฅ˜ํ•˜์—ฌ ์ฃผ๋ฌธ์ƒํƒœ ์ถœ๋ ฅํ•˜๊ธฐ select order_id, product_id, date_format(out_date, '%Y-%m-%d'), (case when out_date '2022-05-01' then '์ถœ๊ณ ๋Œ€๊ธฐ' else '์ถœ๊ณ ๋ฏธ์ •' end) as ์ถœ๊ณ ์—ฌ๋ถ€ from food_order order by order_id ๐Ÿ–๏ธ ๋‚ ์งœ๋ฅผ ๋น„๊ตํ•  ๋•Œ๋Š” ๋”ฐ์˜ดํ‘œ๊ฐ€ ํ•„์š”ํ•˜๋‹ค. ์ฆ๊ฒจ์ฐพ๊ธฐ๊ฐ€ ๊ฐ€์žฅ ๋งŽ์€ ์‹๋‹น ์ •๋ณด ์ถœ๋ ฅํ•˜๊ธฐ select food_type, rest_id, rest_name, favorites from rest_info where (food_type, favorites) i.. 2023. 6. 28.
[MySQL] Programmers Lv.2 ์ „์ฒด ๋ฌธ์ œ ํ’€์ด (2023 ์—…๋ฐ์ดํŠธ!) ์ตœ๊ทผ ์ถ”๊ฐ€๋œ Programmers์˜ Lv.2 ๋ฌธ์ œ๋“ค์˜ ํ’€์ด๋ฅผ ์—…๋ฐ์ดํŠธํ•œ๋‹ค. ๊ธฐ์กด ๋ฌธ์ œ๋“ค ํ’€์ด๋Š” ๐Ÿ‘‰์—ฌ๊ธฐ๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š”! ๊ฐ€๊ฒฉ์ด ์ œ์ผ ๋น„์‹ผ ์‹ํ’ˆ์˜ ์ •๋ณด ์ถœ๋ ฅํ•˜๊ธฐ select * from food_product where price in (select max(price) from food_product) 3์›”์— ํƒœ์–ด๋‚œ ์—ฌ์„ฑ ํšŒ์› ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐ select member_id, member_name, gender, date_format(date_of_birth, '%Y-%m-%d') as date_of_birth from member_profile where month(date_of_birth) = '3' and gender = 'W' and tlno is not null order by member_id ์นดํ…Œ๊ณ .. 2023. 6. 27.
[MySQL] Programmers Lv.1 ์ „์ฒด ๋ฌธ์ œ ํ’€์ด (2023 ์—…๋ฐ์ดํŠธ!) ์ตœ๊ทผ Programmers์— ์ƒˆ๋กœ์šด SQL ๋ฌธ์ œ๋“ค์ด ์ถ”๊ฐ€๋˜์—ˆ๊ธธ๋ž˜ ํด๋ฆฌ์–ด ํ•ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค. ๐Ÿ™‰ ์ด์ „ ๋ฌธ์ œ๋“ค์€ ๐Ÿ‘‰์—ฌ๊ธฐ๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š”! ๊ฐ•์›๋„์— ์œ„์น˜ํ•œ ์ƒ์‚ฐ๊ณต์žฅ ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐ select factory_id, factory_name, address from food_factory where substr(address, 1, 3) = "๊ฐ•์›๋„" # where left(address, 3) = "๊ฐ•์›๋„" # where address like "๊ฐ•์›๋„%" order by factory_id like๊ฐ€ ๊ฐ€์žฅ ํŽธํ•˜์ง€๋งŒ left, substr ์‚ฌ์šฉ๋ฒ•๋„ ์•Œ์•„๋‘๊ธฐ ! ๊ฒฝ๊ธฐ๋„์— ์œ„์น˜ํ•œ ์‹ํ’ˆ์ฐฝ๊ณ  ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐ select warehouse_id, warehouse_name, address, if(freezer_yn is null.. 2023. 4. 26.
[MySQL] Programmers Lv.4 ์ „์ฒด ๋ฌธ์ œ ํ’€์ด ์šฐ์œ ์™€ ์š”๊ฑฐํŠธ๊ฐ€ ๋‹ด๊ธด ์žฅ๋ฐ”๊ตฌ๋‹ˆ SELECT DISTINCT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk' AND CART_ID IN ( SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') ORDER BY CART_ID ๐Ÿ– DISTINCT ํ‚ค์›Œ๋“œ๋กœ ์ค‘๋ณต ์ œ๊ฑฐ ๊ฐ€๋Šฅ ๋ณดํ˜ธ์†Œ์—์„œ ์ค‘์„ฑํ™”ํ•œ ๋™๋ฌผ SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME FROM ANIMAL_INS AS INS JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME ORDER B.. 2022. 2. 2.