Banner

My Tech Blog (sqld)

๐Ÿ“‘ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์กฐํšŒํ•˜๊ธฐ10์ดˆ์ปท ๋ฌธ์ œSELECT *FROM ANIMAL_INSORDER BY ANIMAL_ID; ๐Ÿฆ ์กฐ๊ฑด์— ๋งž๋Š” ํšŒ์›์ˆ˜ ๊ตฌํ•˜๊ธฐSELECT COUNT(*)FROM USER_INFOWHERE YEAR(JOINED) = '2021' AND AGE BETWEEN 20 AND 29;๐Ÿ’ป Python ๊ฐœ๋ฐœ์ž ์ฐพ๊ธฐ ๋ณดํ†ต ์Œฉ์ดˆ๋ณด๋“ค์ด ํ‘ธ๋Š” ๋ฐฉ๋ฒ•SELECT ID, EMAIL, FIRST_NAME, LAST_NAMEFROM DEVELOPER_INFOSWHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'ORDER BY ID; ํ•œ ๋ฒˆ ์ƒ๊ฐ ํ•ด ๋ณด๊ณ  ํ’€๋ฉด ์ด๋ ‡๊ฒŒ ใ…Žใ…Žใ…ŽSELECT ID, EMAIL, FIRST_NAME, L..
๐Ÿ“š์กฐ๊ฑด์— ๋งž๋Š” ๋„์„œ ๋ฆฌ์ŠคํŠธ ์ถœ๋ ฅํ•˜๊ธฐ ์•„๋ž˜๋Š” ์ •๋‹ต์ฒ˜๋ฆฌ ๋˜๋Š” ๋ชจ๋‘ ๋™์ผํ•œ ์ฝ”๋“œ์ด๋‹ค BETWEEN ์—ฐ์‚ฐ์ž๋กœ ํ’€๊ธฐSELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATEFROM BOOKWHERE PUBLISHED_DATE BETWEEN '20210101' AND '20211231' AND CATEGORY LIKE '์ธ๋ฌธ'ORDER BY PUBLISHED_DATE; โญโญโญYEAR() ํ•จ์ˆ˜๋กœ ํ’€๊ธฐ (์ œ์ผ ๊ฐ„๋‹จํ•œ ์ฝ”๋“œ)SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATEFROM BOOKWHERE YEAR(PUBLISHED_DATE) = 2021 ..
๐Ÿฆ ์ธ๊ธฐ ์žˆ๋Š” ์•„์ด์Šคํฌ๋ฆผ ๋„ˆ๋ฌด ์‰ฌ์›Œ์„œ ์„ค๋ช…ํ•  ๊ฒƒ๋„ ์—†๋‹ค...SELECT FLAVORFROM FIRST_HALFORDER BY TOTAL_ORDER DESC, SHIPMENT_ID; ๐Ÿญ ๊ฐ•์›๋„์— ์œ„์น˜ํ•œ ์ƒ์‚ฐ๊ณต์žฅ ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐSELECT FACTORY_ID, FACTORY_NAME, ADDRESSFROM FOOD_FACTORYWHERE ADDRESS LIKE '๊ฐ•์›๋„%'ORDER BY FACTORY_ID;๐Ÿ‘ฉ๐Ÿป‍๐Ÿ‘ง๐Ÿป‍๐Ÿ‘ง๐Ÿป 12์„ธ ์ดํ•˜์ธ ์—ฌ์ž ํ™˜์ž ๋ชฉ๋ก ์ถœ๋ ฅํ•˜๊ธฐ SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO,'NONE') AS TLNOFROM PATIENTWHERE AGE  * SELECT์ ˆ์— ํ•จ์ˆ˜ ์ˆ˜ํ–‰ํ•˜๋ฉด ์ปฌ๋Ÿผ๋ช…์ด ํ•จ์ˆ˜๋ช…์œผ๋กœ ๋‚˜์˜ค๋ฏ€๋กœ ALIAS ๋ถ™์—ฌ์ฃผ๋Š”๊ฑฐ..
NATURAL JOIN์—๋Š” ON ์ ˆ ์“ธ ์ˆ˜ ์—†๋‹ค.SELECT A.COL1, B.COL2FROM SAMPLE1 A NATURAL JOIN SAMPLE BON A.COL1 = B.COL1  Oracle์˜ ๊ฒฝ์šฐ OUTER JOIN ์ž‘์„ฑ ์‹œ (+) ๊ธฐํ˜ธ๋ฅผ ์‚ฌ์šฉํ•˜๋Š”๋ฐ ์ขŒ๋ณ€์ด๋‚˜ ์šฐ๋ณ€ ์ค‘ ํ•˜๋‚˜์—๋งŒ ํ‘œ๊ธฐํ•ด์•ผ ํ•œ๋‹ค.SELECT A.COL1, B.COL2FROM SAMPLE1 A, SAMPLE BWHERE A.COL1(+) = B.COL1(+); FULL OUTER JOIN์„ ๊ตฌํ˜„ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” (+) ๊ธฐํ˜ธ ๋Œ€์‹  ํ‘œ์ค€ SQL ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. Oracle 9i ์ดํ›„๋ถ€ํ„ฐ๋Š” ํ‘œ์ค€ SQL FULL OUTER JOIN ๊ตฌ๋ฌธ์„ ์ง€์›ํ•˜๋ฏ€๋กœ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ํ˜•์‹์œผ๋กœ ์ž‘์„ฑํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค:SELECT A.COL1, B.COL2FROM TA..
์•„ ๋จธ๋ฆฌ ์“ฐ๊ธฐ ์‹ซ๋‹ค..๋‚ด๊ฐ€ ๋ณด๋ ค๊ณ  ์ •๋ฆฌ ํ‚ค์›Œ๋“œ๋ณ„๋กœ ๊ตฌ๊ธ€๊ฒ€์ƒ‰ + GPT + ์ฑ… ๋ณด๊ณ  ์ •๋ฆฌ์ค‘JOIN๊นŒ์ง€๋งŒ ๋ธ”๋กœ๊ทธ์— ์ •๋ฆฌํ•˜๊ณ  ๊ทธ ๋‹ค์Œ๊บผ๋Š” ๊ทธ๋ƒฅ ํ˜ผ์ž์„œ ๋ณด๊ณ  ์•Œ๊ธฐ๋งŒ ํ•ด์•ผ ๊ฒ ๋‹ค...์ •๋ฆฌํ•˜๋‹ค๊ฐ€ ์ปจ๋””์…˜ ๊ด€๋ฆฌ ์•ˆ ๋  ๋“ฏ ใ…‹ใ…‹ใ…‹....์–ด์ฐจํ”ผ ๋ณต์Šตํ•˜๋Š”๊ฑฐ๋ผ ๊ฐœ๋…์€ ๋‹ค ์•Œ๊ณ  ํ—ท๊ฐˆ๋ฆฌ๋Š” ๋ถ€๋ถ„๋งŒ ์ •๋ฆฌํ•œ๊ฑฐ ํ‹ฐ์Šคํ† ๋ฆฌ ํ‘œ ๊ธฐ๋Šฅ ๊ตฌ๋ ค๋„ ๋„ˆ๋ฌด ๊ตฌ๋ฆฌ๋‹คํ‘œ๋ฅผ ๋งŒ๋“ค๋ฉด ์ œ๋ฉ‹๋Œ€๋กœ ์ƒ‰์ƒ์ด ์น ํ•ด์ง€๊ณ  ํ…Œ๋‘๋ฆฌ๊ฐ€ ์•ˆ๋ณด์ž„ ์ธ๋‚ด์‹ฌ ํ…Œ์ŠคํŠธ..ํฌํ† ์ƒต๋งŒ ๊น”๋ ค์žˆ์œผ๋ฉด ๋งŒ๋“œ๋Š”๋ฐใ… ใ… ํ›„..... ๋‹ค์Œ๋ถ€ํ„ฐ๋Š” Google Spread Sheet๋กœ ๋งŒ๋“ค์–ด์•ผ๊ฒ ์›€  ์ฐธ๊ณ ๋กœ 2024.11.17 ๊ธฐ์ค€ ๋‚ด์šฉ๋ฌผ ์ค‘ ํ‘œ์— ์„  ์•ˆ๋ณด์ด๊ณ  ๋ฐฐ๊ฒฝ์ƒ‰ ๋งˆ์Œ๋Œ€๋กœ ์น ํ•ด์ง„ ๊ฑด ๋‚ด๊ฐ€ ๊ณ ์น  ์ˆ˜ ์žˆ๋Š” ๋ถ€๋ถ„์ด ์•„๋‹˜ํ…์ŠคํŠธ์—๋””ํ„ฐ์—์„œ๋Š” ๋ฌธ์ œ ์—†์–ด ๋ณด์—ฌ๋„ ๋ฐœํ–‰ํ•˜๋ฉด ๋‹ค ๊นจ์ ธ์žˆ์Œ, ๋ช‡๋ฒˆ์„ ์ˆ˜์ •ํ•ด๋„ ๋‹ต์ด ์—†๋Š”๋ฐ ๋‚ด์ผ ์˜ค์ „์— ๋‹น์žฅ..
์ค‘์š”ํ•œ๊ฑฐ ์ •๋ฆฌํ•œ๊ฑฐ ์•„๋‹ˆ๊ณ  ๋‚ด๊ฐ€ ๋ณผ๋ ค๊ณ  ํ—ท๊ฐˆ๋ฆฌ๋Š”๊ฑฐ ์ •๋ฆฌํ•จ ๊ธฐ๋ณธ์ ์œผ๋กœ ORACLE ๊ธฐ์ค€์œผ๋กœ ์ •๋ฆฌMySQL์ด๋ž‘ MSSQL์€ ๊ฐœ๋ณ„์ ์œผ๋กœ ๋”ฐ๋กœ ์ •๋ฆฌ ํ•ด ๋†“์Œ1. NVL(์ธ์ˆ˜1, ์ธ์ˆ˜2)`์ธ์ˆ˜1`์˜ ๊ฐ’์ด `NULL`์ผ ๊ฒฝ์šฐ `์ธ์ˆ˜2` ๋ฐ˜ํ™˜, `NULL` ์•„๋‹ ๊ฒฝ์šฐ `์ธ์ˆ˜ 1` ๋ฐ˜ํ™˜ ํ•œ ๋งˆ๋””๋กœ ์—ฐ์‚ฐํ•ด์•ผ ํ•˜๋Š”๋ฐ NULL ๊ฐ’์ด ์žˆ์œผ๋ฉด 0์œผ๋กœ ์น˜ํ™˜ํ•ด์ค˜์•ผ ๋˜๋‹ˆ๊นŒ 0์œผ๋กœ ๋ฐ”๊พธ๋Š” ํ•จ์ˆ˜๋ผ๊ณ  ์™ธ์šฐ๋ฉด ๋œ๋‹ค. ์˜ˆ๋ฅผ ๋“ค๋ฉด ์˜ํ™” ๋ฆฌ๋ทฐํ•˜๋Š”๋ฐ ํ‰์  ์‚ฌ์ด์— NULL์ด ๊ปด ์žˆ์–ด์„œ ํ‰๊ท ์„ ๋ชป ๋‚ด์ค„ ๋•Œ ๊ทธ๋ƒฅ ๋‹ค 0์œผ๋กœ ์น˜ํ™˜ํ•  ๋•Œ ์“ฐ๋Š”๊ฑฐ๋ผ ์ดํ•ดํ•˜๋ฉด ๋œ๋‹ค. NVL(expression, replacement_value)expression: ํ™•์ธํ•  ๊ฐ’.replacement_value: expression์ด NULL์ผ ๋•Œ ๋Œ€์‹  ์‚ฌ์šฉํ•  ๊ฐ’. ORACLE `N..
์ค‘์š”ํ•œ ๋‚ด์šฉ์ด ์•„๋‹Œ ๋‚ด๊ฐ€ ํ—ท๊ฐˆ๋ฆฌ๋Š” ๊ฒƒ๋งŒ ์ •๋ฆฌํ•œ ๊ฒƒ1. SUBSTR๋‹ค๋ฅธ ํ”„๋กœ๊ทธ๋ž˜๋ฐ ์–ธ์–ด๋ž‘ ๋‹ฌ๋ฆฌ Oracle๊ณผ SQL์—์„œ SUBSTR ํ•จ์ˆ˜์˜ ์ธ๋ฑ์Šค๋Š” 1๋ถ€ํ„ฐ ์‹œ์ž‘. ์ฆ‰, ๋ฌธ์ž์—ด์—์„œ ์ฒซ ๋ฒˆ์งธ ๋ฌธ์ž๋Š” ์ธ๋ฑ์Šค 1์ž„.SUBSTR(string, start_position, [length])start_position: ์ถ”์ถœ์„ ์‹œ์ž‘ํ•  ์œ„์น˜ (1๋ถ€ํ„ฐ ์‹œ์ž‘) length: (์„ ํƒ์ ) ์ถ”์ถœํ•  ๊ธธ์ดSELECT SUBSTR('Hello World', 1, 5) AS Substring FROM dual; ์œ„ ์ฟผ๋ฆฌ์—์„œ SUBSTR('Hello World', 1, 5)๋Š” ๋ฌธ์ž์—ด 'Hello World'์—์„œ ์ฒซ ๋ฒˆ์งธ ๋ฌธ์ž๋ถ€ํ„ฐ ์‹œ์ž‘ํ•˜์—ฌ 5๊ฐœ์˜ ๋ฌธ์ž๋ฅผ 'Hello' ์ถ”์ถœ SELECT SUBSTR('๋ธ”๋ž™ํ•‘ํฌ์ œ๋‹ˆ', 3, 2) FROM DUA..
๋‚ด๊ฐ€ ๋ณด๋ ค๊ณ  ํ—ท๊ฐˆ๋ฆฌ๋Š” ๊ฑฐ ์ •๋ฆฌํ•œ ๊ฒƒ 1. ์ •๊ทœํ™”๋ฐ์ดํ„ฐ์˜ ์ •ํ•ฉ์„ฑ(๋ฐ์ดํ„ฐ์˜ ์ •ํ™•์„ฑ๊ณผ ์ผ๊ด€์„ฑ์„ ์œ ์ง€ํ•˜๊ณ  ๋ณด์žฅ)ํ•˜๊ธฐ ์œ„ํ•ด ์—”ํ„ฐํ‹ฐ๋ฅผ ์ž‘์€ ๋‹จ์œ„๋กœ ๋ถ„๋ฆฌํ•˜๋Š” ๊ณผ์ •์ด๋‹ค. ์ •๊ทœํ™”๋ฅผ ํ•  ๋•Œ ๋ฐ์ดํ„ฐ ์กฐํšŒ ์„ฑ๋Šฅ์€ ๋ณด์žฅ ๋ชปํ•˜์ง€๋งŒ ์ž…๋ ฅ, ์ˆ˜์ •, ์‚ญ์ œ ์„ฑ๋Šฅ์€ ์ผ๋ฐ˜์ ์œผ๋กœ ํ–ฅ์ƒ๋œ๋‹ค๊ณ  ๋ณผ ์ˆ˜ ์žˆ๋‹ค.  ๋‚ด๊ฐ€ ์˜ˆ์ „์— ์ •๋ฆฌํ•œ๊ฑฐ [Database] ์ •๊ทœํ™” ์‰ฝ๊ฒŒ ์ดํ•ดํ•˜๊ธฐ, ์ œ1์ •๊ทœํ˜•, ์ œ2์ •๊ทœํ˜•, ์ œ3์ •๊ทœํ˜•, BCNF์ •๊ทœํ™”(Normalization) ๐Ÿ“Œ ์ •๊ทœํ™”๋ž€? ์ •๊ทœํ™”(Normalization)๋ž€ ๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์„ค๊ณ„์—์„œ ์ค‘๋ณต์„ ์ตœ์†Œํ™”ํ•˜๋„๋ก ๋ฐ์ดํ„ฐ๋ฅผ ๊ตฌ์กฐํ™”ํ•˜๋Š” ์ž‘์—…์ด๋‹ค. ์ •๊ทœํ™”๋ฅผ ํ•ด์คŒ์œผ๋กœ์จ ์‚ฝ์ž…, ์‚ญ์ œ, ๊ฐฑ์‹  ๋“ฑ ์ด์ƒํ˜„์ƒawesomepossum.tistory.com 1) ์ œ1์ •๊ทœํ˜• ์œ„๋ฐ˜ - ํ•œ ์นธ์— , ๋กœ ํ•ญ๋ชฉ ์—ฌ๋Ÿฌ๊ฐœ ๋“ค์–ด ๊ฐ„๊ฑฐ - ํ•œ ..
์ธ์ ˆ๋ฏธ์˜€๋˜๊ฒƒ
'sqld' ํƒœ๊ทธ์˜ ๊ธ€ ๋ชฉ๋ก (3 Page)
์ƒ๋‹จ์œผ๋กœ