๐ฆ ๋ฐ์ดํฐ ๋ฒ ์ด์ค (DB) ๐ฆ/๐ พ ์ค๋ผํด DB & SQL27 ํ ์ด๋ธ TABLE - 1 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816.. 2021. 2. 17. ๋ถ์ํจ์, ์์ํจ์ (RANK, DENSE_RANK, ROW_NUMBER, ROWNUM) 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667-- ๋ถ์ ํจ์/*์์ํจ์RANK 1 2 3 3 5 6 DENSE_RANK() 1 2 3 3 4 5ROW_NUMBER() 1 2 3 4 5 6 ROWNUMํ์ํ ์ด์ : ์์๋ฅผ ๋ฒํธ๋ก ์ค์ ํ๊ธฐ์ํด*/ SELECT employee_id, first_nameFROM employeesWHERE employee_id >= 100 AND employee_id 2021. 2. 15. OVER, PARTITION BY 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 --OVER() ํจ์ --select์ ์์๋ง ์ฌ์ฉ --GROUP BY ๋ฅผ ๋ณด๊ฐํ๊ธฐ ์ํด ๋์จ ํจ์ --select ์ ์์ GROUP์ ๋ง๋ค์ง ์๊ณ ์ฌ์ฉ๊ฐ๋ฅ SELECT count(*), department_id --์๋ฌ FROM employees; SELECT count(*), department_id --์๋ฌ FROM employees GROUP BY department_id; SELECT department_id, COUNT(*)OVER() --์๋ฌ FROM employees; SELECT first_name, department_id, COUNT.. 2021. 2. 15. ์งํฉ (UNION, INTERSECT, MINUS) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 /* ์งํฉ ํฉ์งํฉ: UNION - FULLOUTER ๊ต์งํฉ: INTERSECT - INNER ์ฐจ์งํฉ: MINUS */ --UNION SELECT job_id FROM employees WHERE job_id IN('AD_VP', 'FI_ACCOUNT') UNION ALL SELECT job_id FROM jobs WHERE job_id IN('AD_VP', 'FI_ACCOUNT'); --INTERSECT --๋งค๋์ ์ ๋ํ ์ ๋ณด๋ง ์ถ๋ฆฐ๋ค(๊ต์งํฉ์ผ๋ก) --sorting ์ด ์๋์ ์ผ๋ก ๋๋ค -- ์ค.. 2021. 2. 15. ์ด์ 1 2 3 4 5 6 7 ๋ค์