๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
IT์ž๊ฒฉ์ฆ ๊ณต๋ถ€/SQLD ๊ธฐ์ถœ๋ฌธ์ œ

[SQLD] 30ํšŒ ๊ธฐ์ถœ ๋ฌธ์ œ ( 50๋ฌธ์ œ / ์ •๋‹ต,ํ•ด์„คํฌํ•จ )

by yunamom 2022. 4. 17.
๋ฐ˜์‘ํ˜•

๐Ÿ“–SQLD. ์ œ 30 ํšŒ ๊ธฐ์ถœ ๋ฌธ์ œ


๋ฌธ์ œ 1. ์•„๋ž˜ ๋‚ด์šฉ์˜ ํŠน์ง•์€ ๋ฌด์—‡์„ ์„ค๋ช…ํ•˜๊ณ  ์žˆ๋Š”์ง€ ๊ณ ๋ฅด์‹œ์˜ค.

์ •๋‹ตํ™•์ธ๐ŸŒผ

[ ์„ค๋ช… ] ์—”ํ„ฐํ‹ฐ ๋‚ด์—์„œ ์†์„ฑ์— ๋Œ€ํ•œ ๋ฐ์ดํ„ฐ ํƒ€์ž…๊ณผ ํฌ๊ธฐ ๊ทธ๋ฆฌ๊ณ  ์ œ์•ฝ์‚ฌํ•ญ์„ ์ง€์ •ํ•˜๋Š” ๊ฒƒ
1) ์ œ์•ฝ์กฐ๊ฑด

2) ๋„๋ฉ”์ธ

3) ์ •๊ทœํ™”

4) ์‹๋ณ„์ž

 

๋ฌธ์ œ 2. ์•„๋ž˜์˜ ๋ฐ˜์ •๊ทœํ™”์— ๋Œ€ํ•œ ์„ค๋ช… ์ค‘ ๋ถ€์ ์ ˆํ•œ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•  ๋•Œ ๋””์Šคํฌ I/O ๋Ÿ‰์ด ๋งŽ์•„์„œ ์„ฑ๋Šฅ์ด ์ €ํ•˜๋˜๊ฑฐ๋‚˜ ๊ฒฝ๋กœ๊ฐ€ ๋„ˆ๋ฌด ๋ฉ€์–ด ์กฐ์ธ์œผ๋กœ ์ธํ•œ ์„ฑ๋Šฅ์ €ํ•˜๊ฐ€ ์˜ˆ์ƒ๋  ๋•Œ ์ˆ˜ํ–‰ํ•œ๋‹ค.

2) ์ปฌ๋Ÿผ์„ ๊ณ„์‚ฐํ•˜์—ฌ ์ฝ์„ ๋•Œ ์„ฑ๋Šฅ์ด ์ €ํ•˜๋  ๊ฒƒ์ด ์˜ˆ์ƒ๋˜๋Š” ๊ฒฝ์šฐ ์ˆ˜ํ–‰ํ•œ๋‹ค.

3) ๋ฐ˜์ •๊ทœํ™”๋ฅผ ์ ์šฉํ•  ๋•Œ๋Š” ๊ธฐ๋ณธ์ ์œผ๋กœ ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ์ด ๊นจ์งˆ ๊ฐ€๋Šฅ์„ฑ์ด ๋งŽ์ด ์žˆ์œผ๋ฏ€๋กœ ๋ฐ˜๋“œ์‹œ ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ์„ ๋ณด์žฅํ•  ์ˆ˜ ์žˆ๋Š” ๋ฐฉ๋ฒ•์„ ๊ณ ๋ คํ•ด์•ผํ•œ๋‹ค.

4) ๋ฐ˜์ •๊ทœํ™” ์ „์— ํ…Œ์ด๋ธ” ์ถ”๊ฐ€(ํ†ต๊ณ„ ํ…Œ์ด๋ธ”, ์ค‘๋ณต ํ…Œ์ด๋ธ”, ์ด๋ ฅ ํ…Œ์ด๋ธ” ์ถ”๊ฐ€)๋ฅผ ํ†ตํ•ด ๋ฐ˜์ •๊ทœํ™”๋ฅผ ํšŒํ”ผํ•œ๋‹ค.

 

๋ฌธ์ œ 3. ๋ฐœ์ƒ์‹œ์ ์— ๋”ฐ๋ฅธ ์—”ํ„ฐํ‹ฐ ๋ถ„๋ฅ˜์— ์˜ํ•œ ์ค‘์‹ฌ ์—”ํ„ฐํ‹ฐ๊ฐ€ ์•„๋‹Œ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ๋งค์ถœ

2) ๊ณ„์•ฝ

3) ์‚ฌ์›

4) ์ฃผ๋ฌธ


๋ฌธ์ œ 4. ๋ฐœ์ƒ์‹œ์ ์— ๋”ฐ๋ฅธ ์—”ํ„ฐํ‹ฐ ๋ถ„๋ฅ˜์— ๋Œ€ํ•ด์„œ ์•„๋ž˜์˜ ๋‚ด์šฉ ์ค‘ ์ž˜๋ชป ์ง์ง€์›Œ์ง„ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

A : ๊ธฐ๋ณธ/ํ‚ค ์—”ํ„ฐํ‹ฐ : ์กฐ์ง, ์‚ฌ์›
B : ๊ธฐ๋ณธ/ํ‚ค ์—”ํ„ฐํ‹ฐ : ๋ถ€์„œ
C : ์ค‘์‹ฌ ์—”ํ„ฐํ‹ฐ : ์ƒํ’ˆ, ์ฃผ๋ฌธ์ƒํ’ˆ
D : ํ–‰์œ„ ์—”ํ„ฐํ‹ฐ : ์ฃผ๋ฌธ๋‚ด์—ญ, ๊ณ„์•ฝ์ง„ํ–‰
1) A

2) B

3) C

4) D

 

๋ฌธ์ œ 5. ๋ฐ์ดํ„ฐ ๋ชจ๋ธ๋ง์ด ์ตœ์ข…์ ์œผ๋กœ ์™„๋ฃŒ๋œ ์ƒํƒœ๋ผ๊ณ  ์ •์˜ํ•  ์ˆ˜ ์žˆ๋Š”, ์ฆ‰ ๋ฌผ๋ฆฌ์ ์ธ ์Šคํ‚ค๋งˆ ์„ค๊ณ„๋ฅผ ํ•˜๊ธฐ ์ „ ๋‹จ๊ณ„๋ฅผ ๊ฐ€๋ฆฌํ‚ค๋Š” ๋ง์€ ๋ฌด์—‡์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ๊ฐœ๊ด„์  ๋ชจ๋ธ๋ง

2) ๊ฐœ๋…์  ๋ชจ๋ธ๋ง

3) ๋…ผ๋ฆฌ์  ๋ชจ๋ธ๋ง

4) ๋ฌผ๋ฆฌ์  ๋ชจ๋ธ๋ง

 

๋ฌธ์ œ 6. ์•„๋ž˜์˜ ERD ์— ๋Œ€ํ•œ ์„ค๋ช…์ค‘ ๊ฐ€์žฅ ๋ถ€์ ์ ˆํ•œ ๊ฒƒ์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) SQL ์ž‘์„ฑ ์‹œ B ๋ฅผ ์ œ์™ธํ•  ๊ฒฝ์šฐ A ์™€ C ๋Š” ์นดํ…Œ์‹œ์•ˆ ์กฐ์ธ์ด ๋œ๋‹ค.

2) 3๊ฐœ ํ…Œ์ด๋ธ”์„ ๋ชจ๋‘ ์กฐ์ธํ•  ๊ฒฝ์šฐ ์กฐ์ธ ์ตœ์†Œ ์กฐ๊ฑด์€ 3๊ฐœ์ด๋‹ค.

3) B, C์—์„œ C์˜ ๋ฐ์ดํ„ฐ๋Š” ๋ชจ๋‘ B์— ์กด์žฌํ•˜๋ฏ€๋กœ Outer Join ์„ ์•ˆํ•ด๋„ ๋œ๋‹ค.

4) B๋Š” A์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๋ชจ๋‘ ํฌํ•จํ•˜์ง€ ์•Š๋Š”๋‹ค.

 

๋ฌธ์ œ 7. ์•„๋ž˜์˜ ์„ค๋ช… ์ค‘ ๋ฐ˜์ •๊ทœํ™” ๋Œ€์ƒ์ด ์•„๋‹Œ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ์ž์ฃผ ์‚ฌ์šฉ๋˜๋Š” ํ…Œ์ด๋ธ”์— ์ ‘๊ทผํ•˜๋Š” ํ”„๋กœ์„ธ์Šค์˜ ์ˆ˜๊ฐ€ ๋งŽ๊ณ  ํ•ญ์ƒ ์ผ์ •ํ•œ ๋ฒ”์œ„๋งŒ์„ ์กฐํšŒํ•˜๋Š” ๊ฒฝ์šฐ

2) ํ…Œ์ด๋ธ”์˜ ๋Œ€๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ์žˆ๊ณ  ๋Œ€๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ ๋ฒ”์œ„๋ฅผ ์ž์ฃผ ์ฒ˜๋ฆฌํ•˜๋Š” ๊ฒฝ์šฐ์— ์ฒ˜๋ฆฌ๋ฒ”์œ„๋ฅผ ์ผ์ •ํ•˜๊ฒŒ ์ค„์ด์ง€ ์•Š์œผ๋ฉด ์„ฑ๋Šฅ์„ ๋ณด์žฅํ•  ์ˆ˜ ์—†์„ ๊ฒฝ์šฐ

3) ํ†ต๊ณ„์„ฑ ํ”„๋กœ์„ธ์Šค์— ์˜ํ•ด ํ†ต๊ณ„ ์ •๋ณด๋ฅผ ํ•„์š”๋กœ ํ•  ๋•Œ ๋ณ„๋„์˜ ํ†ต๊ณ„ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ

4) ํ…Œ์ด๋ธ”์— ์ง€๋‚˜์น˜๊ฒŒ ๋งŽ์€ ์กฐ์ธ๊ณผ Sorting, Order by ํ”„๋กœ์„ธ์Šค๊ฐ€ ๋งŽ์€ ๊ฒฝ์šฐ

 

๋ฌธ์ œ 8. ์•„๋ž˜์˜ ์‹๋ณ„์ž์˜ ๋ถ„๋ฅ˜์ฒด๊ณ„์— ๋Œ€ํ•œ ์„ค๋ช… ์ค‘ ๊ฐ€์žฅ ๋ถ€์ ์ ˆํ•œ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ๋Œ€ํ‘œ์„ฑ ์—ฌ๋ถ€์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜๋˜๋Š” ์ฃผ์‹๋ณ„์ž์™€ ๋ณด์กฐ์‹๋ณ„์ž์—์„œ ์ฃผ์‹๋ณ„์ž๋Š” ๋Œ€ํ‘œ์„ฑ์„ ๊ฐ€์ง€์ง€ ๋ชปํ•ด ์ฐธ์กฐ๊ด€๊ณ„ ์—ฐ๊ฒฐ์„ ๋ชปํ•œ๋‹ค.

2) ์Šค์Šค๋กœ ์ƒ์„ฑ์—ฌ๋ถ€์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜๋˜๋Š” ์‹๋ณ„์ž๋Š” ๋‚ด๋ถ€ ์‹๋ณ„์ž์™€ ์™ธ๋ถ€ ์‹๋ณ„์ž์ด๋‹ค.

3) ๋‘˜ ์ด์ƒ์˜ ์†์„ฑ์œผ๋กœ ๊ตฌ์„ฑ๋œ ์‹๋ณ„์ž๋ฅผ ๋ณตํ•ฉ์‹๋ณ„์ž๋ผ ํ•˜๋ฉฐ ์†์„ฑ์˜ ์ˆ˜์— ๋”ฐ๋ฅธ ์‹๋ณ„์ž ๋ถ„๋ฅ˜์ด๋‹ค.

4) ์—…๋ฌด์ ์œผ๋กœ ๋งŒ๋“ค์–ด์ง€์ง€๋Š” ์•Š์ง€๋งŒ ํ•„์š”์— ๋”ฐ๋ผ ์ธ์œ„์ ์œผ๋กœ ๋งŒ๋“  ์‹๋ณ„์ž๋ฅผ ์ธ์กฐ ์‹๋ณ„์ž๋ผ ํ•œ๋‹ค.

 

๋ฌธ์ œ 9. ๋ถ„์‚ฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ํŠน์ง• ์ค‘ ์‚ฌ์šฉํ•˜๋ ค๋Š” ๋ฐ์ดํ„ฐ์˜ ์ €์žฅ ์žฅ์†Œ ๋ช…์‹œ๊ฐ€ ๋ถˆํ•„์š”ํ•˜๋‹ค๋Š” ํŠน์ง•์€ ๋ฌด์—‡์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) ๋ถ„ํ•  ํˆฌ๋ช…์„ฑ

2) ์œ„์น˜ ํˆฌ๋ช…์„ฑ

3) ์ง€์—ญ์‚ฌ์ƒ ํˆฌ๋ช…์„ฑ

4) ์ค‘๋ณต ํˆฌ๋ช…์„ฑ

 

๋ฌธ์ œ 10. Row migration๊ณผ Row Chaining ์— ๋Œ€ํ•œ ์•„๋ž˜์˜ ์„ค๋ช… ์ค‘ ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) Row Chaining ๊ณผ Row Migration ์ด ๋งŽ์•„์ง€๊ฒŒ ๋˜๋”๋ผ๋„ ์„ฑ๋Šฅ ์ €ํ•˜๋Š” ์ผ์–ด๋‚˜์ง€ ์•Š๋Š”๋‹ค.

2) ๋กœ์šฐ ๊ธธ์ด๊ฐ€ ๋„ˆ๋ฌด ๊ธธ์–ด์„œ ๋ฐ์ดํ„ฐ ๋ธ”๋ก ํ•˜๋‚˜์— ๋ฐ์ดํ„ฐ๊ฐ€ ๋ชจ๋‘ ์ €์žฅ๋˜์ง€ ์•Š๊ณ  ๋‘ ๊ฐœ ์ด์ƒ์˜ ๋ธ”๋ก์— ๊ฑธ์ณ ํ•˜๋‚˜์˜ ๋กœ์šฐ๊ฐ€ ์ €์žฅ๋˜๋Š” ํ˜„์ƒ์„ Row Chaining ์ด๋ผ๊ณ  ํ•œ๋‹ค.

3) Row Migration ์€ ์‹ ๊ทœ ๋ฐ์ดํ„ฐ์˜ ์ž…๋ ฅ์ด ๋ฐœ์ƒํ•  ๋•Œ ๋ฐœ์ƒ๋˜๋Š” ํ˜„์ƒ์ด๋‹ค.

4) Row Chaining ๊ณผ Row Migration ์ด ๋ฐœ์ƒ๋˜๋ฉด ๋””์Šคํฌ I/O ๊ฐ€ ๋ฐœ์ƒ ๋  ๋•Œ I/O๊ฐ€ ๋งŽ์•„์ง€๊ฒŒ ๋˜์–ด ์„ฑ๋Šฅ์ด ํ–ฅ์ƒ๋  ์ˆ˜ ์žˆ๋‹ค.

 

๋ฌธ์ œ 11. ์•„๋ž˜์˜ ERD๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ๋ณด๊ธฐ์˜ SQL์ค‘ ์˜ค๋ฅ˜๊ฐ€ ๋‚  ์ˆ˜ ์žˆ๋Š” SQL์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_11_01           SQLD_30_11_02
COL1 VARCHAR2(30)       COL1 VARCHAR2(30)
COL2 NUMBER             COL3 VARCHAR2(30)
                        COL4 NUMBER

1)
SELECT A.COL1, A.COL2
FROM SQLD_30_11_01 A
WHERE A.COL1 > 0;

2)
SELECT A.COL1, B.COL4
FROM SQLD_30_11_01 A

       INNER JOIN SQLD_30_11_02 B
              ON (A.COL1 = B.COL1)

WHERE B.COL3 > 'X';
3)
SELECT COUNT(*)
FROM SQLD_30_11_01 A.
WHERE EXISTS (SELECT 'X'

                FROM SQLD_30_11_02 B
                WHERE A.COL2 = B.COL4);
4)
SELECT SUM(A.COL2)
FROM SQLD_30_11_01 A

      INNER JOIN SQLD_30_11_02 B
                  ON (A.COL1 = B.COL1)

WHERE B.COL4 > '1';

 

๋ฌธ์ œ 12. ์•„๋ž˜์˜ ERD ๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ๋ณด๊ธฐ์˜ SQL ์ค‘ ์˜ค๋ฅ˜๊ฐ€ ๋‚  ์ˆ˜ ์žˆ๋Š” SQL ์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

1)
SELECT A.COL1,
            (SELECT COL3 FROM SQLD_30_12_02 B
            WHERE A.COL1 = B.COL1) AS B_COL3

FROM SQLD_30_12_01 A
WHERE A.COL2 > 10;
2)
SELECT A.COL1,
            (SELECT COL5 FROM SQLD_30_12_03 B
            WHERE A.COL4 = B.COL4) AS B_COL3

FROM SQLD_30_12_02 A
WHERE A.COL4 > 0;
3)
SELECT A.COL1, B.COL3
FROM SQLD_30_12_01 A

             INNER JOIN SQLD_30_12_02 B
                         ON (A.COL1 = B.COL1)

             INNER JOIN SQLD_30_12_03 C
                         ON (B.COL4 = C.COL4)

WHERE C.COL4 > 5
AND A.COL2 > 10;
4)
SELECT A.COL1, B.COL3
FROM SQLD_30_12_01 A

            INNER JOIN SQLD_30_12_02 B
                  ON (A.COL1 = B.COL1)

WHERE EXISTS (SELECT 'X'
                  FROM SQLD_30_12_03 C

                  WHERE B.COL4 = C.COL4);

 

๋ฌธ์ œ 13. ์•„๋ž˜์˜ SQL ๊ฒฐ๊ณผ๋ฅผ ๋ณด๊ณ  Group by ๋’ค ๋นˆ์นธ์— ๋“ค์–ด๊ฐ€๋Š” ๋‚ด์šฉ์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

SELECT JOB_ID, MANAGER_ID, SUM(SALARY)
FROM HR.EMPLOYEES
WHERE JOB_ID IN('SH_CLERK','ST_CLERK','SA_REP')
GROUP BY (     );

1) ROLLUP (JOB_ID, MANAGER_ID)

2) CUBE(JOB_ID, MANAGER_ID)

3) GROUPING SET(JOB_ID, MANAGER_ID)

4) ROLLUP (MANAGER_ID, JOB_ID)

 

๋ฌธ์ œ 14. ์•„๋ž˜์˜ SQL ๊ฒฐ๊ณผ๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_14_01
COL1 VARCHAR2(30)
COL2 NUMBER

SQLD_30_14_01
COL1      COL2
--------------
Y          20
X          30
A          40
A          50


SQL>
SELECT COUNT(*)
FROM SQLD_30_14_01
WHERE (COL1, COL2) IN (('A',50));

1) 0

2) 1

3) 2

4) 3

 

๋ฌธ์ œ 15. ์•„๋ž˜์˜ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์—์„œ ์•„๋ž˜์™€ ๊ฐ™์€ SQL ์ด ์ˆ˜ํ–‰๋˜์—ˆ์„๋•Œ์˜ ๊ฒฐ๊ณผ๋Š”?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_15_01
COL1 VARCHAR2(30)
COL2 NUMBER

SQLD_30_15_01
COL1      COL2
--------------
Y          20
X          30
A          40
A          50
           10
           80
           
SQL>
SELECT SUM(COL2)
FROM SQLD_30_15_01
WHERE COL1 IN ('A','X',NULL);

1) 210

2) 120

3) 230

4) 90

 

๋ฌธ์ œ 16. ์•„๋ž˜์˜ ์‹คํ–‰๊ณ„ํš์— ๋Œ€ํ•œ SQL ์ˆ˜ํ–‰ ์ˆœ์„œ๋ฅผ ์˜ฌ๋ฐ”๋ฅด๊ฒŒ ์ ์€ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[๋ณด๊ธฐ]
0- SELECT ..
1-  NESTED LOOP JOIN
2-   NESTED LOOP JOIN
3-     TABLE ACCESS (FULL)
4-     TABLE ACCESS (BY INDEX ROWID)
5-        INDEX (RANGE SCAN)
6-   TABLE ACCESS (BY INDEX ROWID)
7-     INDEX (RANGE SCAN)
1) 3-5-4-2-7-6-1-0

2) 5-3-4-2-7-6-1-0

3) 3-4-5-2-7-6-1-0

4) 3-5-4-2-6-7-1-0

 

๋ฌธ์ œ 17. ์•„๋ž˜ SQL ์˜ ์ˆ˜ํ–‰๊ฒฐ๊ณผ๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_17_01
COL1 VARCHAR2(30)
COL2 NUMBER


SQLD_30_17_01
COL1    COL2
------------
Y        20
X        30
A        40
A        50
         10
         80
         
SQL>
SELECT NVL(COUNT(*), 9999)
FROM SQLD_30_17_01
WHERE 1=2;

1) 0

2) 9999

3) 1

4) ERROR

 

๋ฌธ์ œ 18. ์•„๋ž˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ์•„๋ž˜ SQL์˜ ์ˆ˜ํ–‰๊ฒฐ๊ณผ๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_18_01
COL1 VARCHAR2(30)
COL2 NUMBER


SQLD_30_18_01
COL1       COL2
---------------
1          100
1          NULL
2          100
2          200


SQL>
SELECT COL1, SUM(COL2)
FROM SQLD_30_18_1
GROUP BY COL1;

1)
COL1
1
2

SUM(COL2)
NULL
300
2)
COL1
1
2

SUM(COL2)
100
300
3)
COL1
2



SUM(COL2)
300


4)
COL1
1
1
2

SUM(COL2)
100
NULL
300

 

๋ฌธ์ œ 19. ์•„๋ž˜์™€ ๊ฐ™์€ SQL ์ด ์ˆœ์„œ๋Œ€๋กœ ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ ์ตœ์ข… ๊ฒฐ๊ณผ๊ฐ’์œผ๋กœ ์•Œ๋งž์€ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_19
COL1 NUMBER

[SQL]
1) INSERT INTO SQLD_30_19 VALUES(1);
2) INSERT INTO SQLD_30_19 VALUES(2);
3) SAVEPOINT SV1;
4) UPDATE SQLD_30_19 SET COL1=4 WHERE COL1=2;
5) SAVEPOINT SV1;
6) DELETE SQLD_30_19 WHERE COL1 >= 2;
7) ROLLBACK TO SV1;
8) INSERT INTO SQLD_30_19 VALUES(3);
9) SELECT MAX(COL1) FROM SQLD_30_19;

1) 1

2) 2

3) 3

4) 4

 

๋ฌธ์ œ 20. ์•„๋ž˜์˜ Trigger ์— ๋Œ€ํ•œ ์„ค๋ช… ์ค‘ ๊ฐ€์žฅ ๋ถ€์ ์ ˆํ•œ ๊ฒƒ์€ ? ***

์ •๋‹ตํ™•์ธ๐ŸŒผ

 

1) DELETE ON TRIGGER ์˜ ๊ฒฝ์šฐ :OLD ๋Š” ์‚ญ์ œ ์ „ ๋ฐ์ดํ„ฐ๋ฅผ, :NEW๋Š” ์‚ญ์ œ ํ›„ ๋ฐ์ดํ„ฐ๋ฅผ ๋‚˜ํƒ€๋‚ธ๋‹ค.

2) ํŠน์ • ํ…Œ์ด๋ธ”์— DML๋ฌธ์ด ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ ์ž๋™์œผ๋กœ ๋™์ž‘ํ•˜๋„๋ก ์ž‘์„ฑ๋œ ํ”„๋กœ๊ทธ๋žจ์ด๋‹ค.

3) ํ…Œ์ด๋ธ”, ๋ทฐ์—๋งŒ ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ž‘์—…์—๋Š” ์ •์˜ํ•  ์ˆ˜ ์—†๋‹ค.

4) UPDATE TRIGGER ์—์„œ :OLD ์—๋Š” ์ˆ˜์ • ์ „, :NEW ์—๋Š” ์ˆ˜์ • ํ›„ ๊ฐ’์ด ๋“ค์–ด๊ฐ„๋‹ค.

 

๋ฌธ์ œ 21. ์•„๋ž˜์˜ SELECT ๊ฒฐ๊ณผ๊ฐ€ NULL ์ด ์•„๋‹Œ ๊ฒฝ์šฐ๋Š”?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) SELECT COALESCE ('AB','BC','CD') FROM DUAL;

2) SELECT CASE 'AB' WHEN 'BC' THEN 'CD' END FROM DUAL;

3) SELECT DECODE ('AB','CD','DE') FROM DUAL;

4) SELECT NULLIF ('AB','AB') FROM DUAL;

 

๋ฌธ์ œ 22. ์•„๋ž˜์™€ ๊ฐ™์€ ํ…Œ์ด๋ธ”์ด ์žˆ์„๋•Œ ์•„๋ž˜์™€ ๊ฐ™์€ SQL ์˜ ์ˆ˜ํ–‰๊ฒฐ๊ณผ์— ๋Œ€ํ•ด ์ž‘์„ฑํ•˜์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_22

COL1     COL2
--------------
100       100
NULL      60
NULL      NULL


SELECT COALESCE(COL1, COL2*50, 50) FROM SQLD_30_22;

1) 100, 3000, 50

2) 100, 60, NULL

3) 100, NULL, NULL

4) 100, 3000, NULL

 

๋ฌธ์ œ 23. ์•„๋ž˜์˜ DML, DCL, DDL ์ด ์ž˜๋ชป ์ง์ง€์›Œ์ง„ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) DDL : CREATE

2) DML : UPDATE

3) DCL : ROLLBACK

4) DCL : SELECT

 

๋ฌธ์ œ 24. ๋ณด๊ธฐ์˜ SQL ๋ช…๋ น์–ด์ค‘ TCL ์ธ ๊ฒƒ์€ ์–ด๋–ค ๊ฒƒ์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[๋ณด๊ธฐ]
UPDATE
GRANT
SELECT
COMMIT
1) UPDATE

2) GRANT

3) SELECT

4) COMMIT

 

๋ฌธ์ œ 25. ์•„๋ž˜์˜ ํŠน์ง•์„ ๊ฐ€์ง€๋Š” ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ ๋ฌด์—‡์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[๋ณด๊ธฐ]
๊ณ ์ • ๊ธธ์ด ๋ฌธ์ž์—ด ์ •๋ณด๋กœ S๋งŒํผ ์ตœ๋Œ€ ๊ธธ์ด๋ฅผ ๊ฐ–๊ณ  ๊ณ ์ • ๊ธธ์ด๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ์œผ๋ฏ€๋กœ 
ํ• ๋‹น๋œ ๋ณ€์ˆ˜ ๊ฐ’์˜ ๊ธธ์ด๊ฐ€ S๋ณด๋‹ค ์ž‘์„ ๊ฒฝ์šฐ์—๋Š” ๊ทธ ์ฐจ์ด ๊ธธ์ด ๋งŒํผ ๊ณต๊ฐ„์œผ๋กœ ์ฑ„์›Œ์ง„๋‹ค.
1) CHARACTER

2) VARCHAR

3) NUMERIC

4) DATETIME

 

๋ฌธ์ œ 26. ์•„๋ž˜์˜ SQL ๊ณผ ํ•ญ์ƒ ๋™์ผํ•œ ๊ฒฐ๊ณผ๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” SQL์„ ๊ณ ๋ฅด์‹œ์˜ค 

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQL>
SELECT TOP(10) FIRST_NAME, JOB_ID
FROM HR.EMPLOYEES
ORDER BY SALARY;

1)
SELECT FIRST_NAME, JOB_ID
         FROM HR.EMPLOYEES
WHERE ROWNUM <= 10
ORDER BY SALARY;
2)
SELECT TOP(10) WITH TIES FIRST_NAME, JOB_ID
         FROM HR.EMPLOYEES
ORDER BY SALARY;
3)
SELECT FRIST_NAME,JOB_ID
FROM (

       SELECT FIRST_NAME, JOB_ID, ROWNUM RN
       FROM HR.EMPLOYEES
       ORDER BY SALARY
       )
WHERE RN <= 10;
4)
SELECT FRIST_NAME,JOB_ID
FROM (

       SELECT FIRST_NAME, JOB_ID
       FROM HR.EMPLOYEES
       ORDER BY SALARY
       )

WHERE ROWNUM <= 10;

๋ฌธ์ œ 27. ์•„๋ž˜์˜ ๊ณ„์ธตํ˜• SQL ์„ ์ˆ˜ํ–‰ํ•˜์—ฌ ์•„๋ž˜์™€ ๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ฌ ๋•Œ () ์— ๋งž๋Š” ๊ฒƒ์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL]

SELECT LEVEL,
    LPAD('** ',(LEVEL -1)*2,'**') || EMPLOYEE_ID AS EMP_TREE,
    MANAGER_ID,
    EMPLOYEE_ID
  FROM HR.EMPLOYEES
WHERE 1 = 1
START WITH (   )
CONNECT BY PRIOT (   );

[RESULT]

LEVEL     EMP_TREE      MANAGER_ID      EMPLOYEE_ID
----------------------------------------------------
    1     100                               100
    2     **101               100           101
    3     *** 108             101           108
    4     ***** 109           108           109
    4     ***** 110           108           110
    4     ***** 111           108           111
    4     ***** 112           108           112
    
    .
    .
    .
๋’ท ๊ฒฐ๊ณผ ์ƒ๋žต

1) MANAGER_ID IS NULL,     MANAGER_ID = EMPLOYEE_ID

2) MANAGER_ID IS NOT NULL,     MANAGER_ID = EMPLOYEE_ID

3) MANAGER_ID IS NULL,     EMPLOYEE_ID = MANAGER_ID

4) MANAGER_ID IS NOT NULL,     EMPLOYEE_ID = MANAGER_ID

 

๋ฌธ์ œ 28. SQL Set Operation ์—์„œ ์ค‘๋ณต ์ œ๊ฑฐ๋ฅผ ์œ„ํ•ด ์ •๋ ฌ ์ž‘์—…์„ ํ•˜์ง€ ์•Š๋Š” ์ง‘ํ•ฉ ์—ฐ์‚ฐ์ž๋Š”?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) UNION

2) UNION ALL

3) INTERSECT

4) MINUS

 

๋ฌธ์ œ 29. SQL Set Operation ์—์„œ ์ค‘๋ณต ์ œ๊ฑฐ๋ฅผ ์œ„ํ•ด ์ •๋ ฌ ์ž‘์—…์„ ํ•˜์ง€ ์•Š๋Š” ์ง‘ํ•ฉ ์—ฐ์‚ฐ์ž๋Š”? 

์ •๋‹ตํ™•์ธ๐ŸŒผ

[๋ณด๊ธฐ]

๋Œ€์šฉ๋Ÿ‰ ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•˜์—ฌ ์กฐ์ธํ•œ๋‹ค
๋™๋“ฑ ์กฐ์ธ, ๋น„๋™๋“ฑ ์กฐ์ธ์—์„œ ๋ชจ๋‘ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•˜๋‹ค
๊ฐ ํ…Œ์ด๋ธ”์„ ์ •๋ ฌํ•œ ํ›„ ์กฐ์ธํ•œ๋‹ค.

1) Sort Merge Join

2) Hash Join

3) Nested Loop Join

4) Cartesian Join

 

๋ฌธ์ œ 30. ์•„๋ž˜ ERD ๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ๋ชจ๋“  ํšŒ์›์˜ ์ด ์ฃผ๋ฌธ๊ธˆ์•ก์„ ๊ตฌํ•˜๋Š” SQL ์ค‘ ์ž˜๋ชป๋œ SQL ์€? 

์ •๋‹ตํ™•์ธ๐ŸŒผ

 

1)
SELECT A.๊ณ ๊ฐ๋ช…,
          NVL(SUM(C.์ˆ˜๋Ÿ‰*B.์ƒํ’ˆ๊ฐ€๊ฒฉ),0) AS ์ด์ฃผ๋ฌธ๊ธˆ์•ก
FROM SQLD_30_30_๊ณ ๊ฐ A

    INNER JOIN SQLD_30_30_์ฃผ๋ฌธ C ON A.๊ณ ๊ฐID = C.๊ณ ๊ฐID
    INNER JOIN SQLD_30_30_์ƒํ’ˆ B ON C.์ƒํ’ˆID = B.์ƒํ’ˆID 
WHERE 1=1

GROUP BY A.๊ณ ๊ฐ๋ช… 
ORDER BY 1,2;




2)
SELECT A.๊ณ ๊ฐ๋ช…,
         NVL(SUM(์ด๊ฐ€๊ฒฉ),0) AS ์ด์ฃผ๋ฌธ๊ธˆ์•ก
FROM SQLD_30_30_๊ณ ๊ฐ A

         LEFT OUTER JOIN
         (

         SELECT C.๊ณ ๊ฐID, C.์ˆ˜๋Ÿ‰ * B.์ƒํ’ˆ๊ฐ€๊ฒฉ AS ์ด๊ฐ€๊ฒฉ
         FROM SQLD_30_30_์ฃผ๋ฌธ C

    INNER JOIN SQLD_30_30_์ƒํ’ˆ B ON C.์ƒํ’ˆID = B.์ƒํ’ˆID) D
       ON A.๊ณ ๊ฐID = D.๊ณ ๊ฐID

WHERE 1=1 
GROUP BY A.๊ณ ๊ฐ๋ช… 
ORDER BY 1,2;
3)
SELECT A.๊ณ ๊ฐ๋ช…,
      NVL((
        SELECT SUM(C.์ˆ˜๋Ÿ‰ * B.์ƒํ’ˆ๊ฐ€๊ฒฉ)
      FROM SQLD_30_30_์ฃผ๋ฌธ C

         LEFT JOIN SQLD_30_30_์ƒํ’ˆ B ON C.์ƒํ’ˆID = B.์ƒํ’ˆID
      WHERE C.๊ณ ๊ฐID = A.๊ณ ๊ฐID),0 ) AS ์ด์ฃผ๋ฌธ๊ธˆ์•ก

FROM SQLD_30_30_๊ณ ๊ฐ A 
WHERE 1=1
ORDER BY 1,2;
4)
SELECT A.๊ณ ๊ฐ๋ช…,
      NVL(SUM(C.์ˆ˜๋Ÿ‰*B.์ƒํ’ˆ๊ฐ€๊ฒฉ),0) AS ์ด์ฃผ๋ฌธ๊ธˆ์•ก
FROM SQLD_30_30_๊ณ ๊ฐ A

LEFT OUTER JOIN SQLD_30_30_์ฃผ๋ฌธ C ON A.๊ณ ๊ฐID = C.๊ณ ๊ฐID
LEFT OUTER JOIN SQLD_30_30_์ƒํ’ˆ B ON C.์ƒํ’ˆID = B.์ƒํ’ˆID 
WHERE 1=1

GROUP BY A.๊ณ ๊ฐ๋ช… 
ORDER BY 1,2;

 

๋ฌธ์ œ 31. ์•„๋ž˜์˜ SQL ์˜ ๋กœ์šฐ์˜ ๊ฑด์ˆ˜๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_31_01
COL1      COL2
--------------
  1         2
  1         2
  1         3

SQLD_30_31_02
COL1      COL2
--------------
  1         2
  1         4
  1         5

SQL>
SELECT DISTINCT COL1, COL2
FROM SQLD_30_31_01
	UNION ALL
SELECT COL1, COL2
FROM SQLD_30_31_02;

1) 3

2) 4

3) 5

4) 6

๋ฌธ์ œ 32. ์•„๋ž˜์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ๋‹ค์Œ SQL์˜ ๋กœ์šฐ์˜ ๊ฑด์ˆ˜๊ฐ€ ๋งž๋Š” ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_32_01
COL1     COL2      COL3
-----------------------
  1         2      NULL
  2      NULL         1
  4      NULL      NULL
  
SQLD_30_32_02
COL1     COL2      COL3
-----------------------
  1      NULL        -1
  2      NULL         1
  3         5         2
  
SQL>
SELECT COUNT(*)
  FROM SQLD_30_32_01 A, SQLD_30_32_02 B
WHERE A.COL1 = B.COL1
AND NVL(A.COL2, -1) = NVL(B.COL2, -1)
AND NVL(A.COL3, -1) = NVL(B.COL3, -1)

1) 1

2) 2

3) 3

4) 4

๋ฌธ์ œ 33. ์•„๋ž˜์™€ ๊ฐ™์€ ๊ตฌ์กฐ๋ฅผ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์—์„œ Unique Index Scan ์„ ํ•  ์ˆ˜ ์—†๋Š” ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[TABLE]
SQLD_30_33
KEY1 (PK) NUMBER
KEY2 (PK) NUMBER
COL1      VARCHAR2
COL2      VARCHAR2
COL3      VARCHAR2

1) ๋ชจ๋‘ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†๋‹ค

2) WHERE KEY1 =1 AND KEY2 = 2

3) WHERE (KEY1, KEY2) IN ( (1,2) )

4) WHERE KEY1 = 1

๋ฌธ์ œ 34. ์•„๋ž˜์˜ ERD ์—์„œ ์•„๋ž˜ SQL ๋ฌธ์„ ์ˆœ์„œ๋Œ€๋กœ ์ˆ˜ํ–‰ ํ•  ๊ฒฝ์šฐ ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•˜๋Š” ๊ตฌ๊ฐ„์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[ํ…Œ์ด๋ธ” ์ •๋ณด]
์ฃผ๋ฌธ (๊ณ ๊ฐID) REFERENCES ๊ณ ๊ฐ (๊ณ ๊ฐID)

[SQL]

1) INSERT INTO ๊ณ ๊ฐ VALUES ('C001','AAA');
2) INSERT INTO ์ฃผ๋ฌธ VALUES ('O001','C001','XXX');
3) UPDATE ์ฃผ๋ฌธ SET ๊ณ ๊ฐID = NULL WHERE ์ฃผ๋ฌธID = 'O001';
4) INSERT INTO ์ฃผ๋ฌธ VALUES ('O002','C002','YYY');

1) 1 ๋ฒˆ SQL

2) 2 ๋ฒˆ SQL

3) 3 ๋ฒˆ SQL

4) 4 ๋ฒˆ SQL

 

๋ฌธ์ œ 35. Cross Join ๊ณผ Natural Join ์— ๋Œ€ํ•œ ์ฐจ์ด์ ์— ๋Œ€ํ•ด์„œ ๊ฐ€์žฅ ๋ถ€์ ์ ˆํ•œ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) Cross Join ๊ณผ Natural Join ์€ Where ์ ˆ์—์„œ ์กฐ๊ฑด์„ ๊ฑธ ์ˆ˜ ์—†๋‹ค.

2) Natural Join ์—์„œ๋Š” ํŠน์ • Join ์ปค๋Ÿผ์„ ๋ช…์‹œ์ ์œผ๋กœ ์ ์„ ์ˆ˜ ์—†๋‹ค.

3) Cross Join ์€ Join ์— ์ฐธ์—ฌํ•˜๋Š” ํ…Œ์ด๋ธ”์˜ Join Key ๊ฐ€ ์—†์„ ๊ฒฝ์šฐ ๋ฐœ์ƒ ํ•œ๋‹ค.

4) Natural Join ์—์„œ Join Key ๋Š” ์ปฌ๋Ÿผ๋ช…์œผ๋กœ ๊ฒฐ์ •๋œ๋‹ค.

 

๋ฌธ์ œ 36. ์•„๋ž˜์™€ ๊ฐ™์ด PK์ปฌ๋Ÿผ์ด ๊ตฌ์„ฑ๋˜์–ด ์žˆ์„ ๋•Œ ๊ฐ€์žฅ ์ ํ•ฉํ•œ ํŒŒํ‹ฐ์…”๋‹ ๋ฐฉ๋ฒ•์€ ๋ฌด์—‡์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[ํ…Œ์ด๋ธ” ์ •๋ณด]
ํŠน์ง• : ๋ฐ์ดํ„ฐ์˜ ์–‘์ด ๋งค์šฐ ๋งŽ์€ ๋Œ€์šฉ๋Ÿ‰ ํ…Œ์ด๋ธ”, ๋ฐ์ดํ„ฐ์˜ ์ƒ์„ฑ์ผ์ž๋ฅผ ๊ตฌ๋ถ„์ง“๋Š” ํŠน์ • ์ปฌ๋Ÿผ์ด ์—†๋Š” ํ˜•ํƒœ์ด๋‹ค.

PK : ์ง€์ , ์ฝ”๋“œ

1) Range

2) List

3) Hash

4) Interval

 

๋ฌธ์ œ 37. ์•„๋ž˜ 2๊ฐœ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ Join ํ›„ ๋‚˜ํƒ€๋‚œ ๊ฒฐ๊ณผ์ด๋‹ค. ํ•ด๋‹น Join ์˜ ๋ฐฉ๋ฒ•์„ ๊ณ ๋ฅด์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_37_01
COL1     COL2
--------------
   1        2
   2        2
   3        3
   
SQLD_30_37_02
COL1     COL2
--------------
   1        2
   2        4
   4        5
   
SQL>
SELECT *
FROM SQLD_30_37_01 A
    (    ) SQLD_30_37_02 B
          ON (A.COL1 = B.COL1)
WHERE 1 = 1;

[RESULT]

A.COL1    A.COL2    B.COL1    B.COL2
------------------------------------
     1         2         1         2
     2         2         2         4
  NULL      NULL         4         5

1) LEFT OUTER JOIN

2) RIGHT OUTER JOIN

3) FULL OUTER JOIN

4) INNER JOIN

 

๋ฌธ์ œ 38. ํŠน์ • ๊ทธ๋ฃน์—์„œ ํŠน์ • ์ปฌ๋Ÿผ์œผ๋กœ ์ •๋ ฌ๋œ ๊ฒฐ๊ณผ์—์„œ ์ฒซ๋ฒˆ์งธ ๊ฐ’์„ ๊ตฌํ•˜๋Š” Window Function ์„ ๊ณ ๋ฅด์‹œ์˜ค.

์ •๋‹ตํ™•์ธ๐ŸŒผ

1) LAST_VALUE () OVER

2) FIRST_VALUE () OVER

3) MAX() OVER

4) ์—†๋‹ค

 

๋ฌธ์ œ 39. ์•„๋ž˜์˜ SQL ๊ตฌ๋ฌธ์ด ์ˆœ์„œ๋Œ€๋กœ ์ˆ˜ํ–‰๋œ ํ›„์˜ ๊ฒฐ๊ณผ๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL]
CREATE TABLE SQLD_30_39 (N1 NUMBER); INSERT INTO SQLD_30_39 VALUES(1);
INSERT INTO SQLD_30_39 VALUES(2);
CREATE TABLE TMP_SQLD_30_39 (N1 NUMBER); 
INSERT INTO TMP_SQLD_30_39 VALUES(1); 
TRUNCATE TABLE TMP_SQLD_30_39; ROLLBACK;
COMMIT;
SELECT SUM(N1) FROM SQLD_30_39;
1) 1

2) 2

3) 3

4) 4

 

๋ฌธ์ œ 40. ์•„๋ž˜์™€ ๊ฐ™์€ ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•  ๋•Œ SQL ์˜ ์‹คํ–‰ ๊ฒฐ๊ณผ๋กœ ์˜ฌ๋ฐ”๋ฅธ ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_40
COL1     COL2
-------------
   1       10
   2       20
   3     NULL
   
SQL>
SELECT AVG( NVL (COL2, 0)) AS AVG_COL
FROM SQLD_30_40;

1) 10

2) 15

3) NULL

4) 20

 

๋ฌธ์ œ 41. ์•„๋ž˜์˜ SQL ์—์„œ ORDER BY ๋กœ ์‚ฌ์šฉ ํ•  ์ˆ˜ ์—†๋Š” ๊ฒƒ์€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL] 
SELECT JOB, COUNT(*) AS CNT 
FROM TAB_A
GROUP BY JOB;

 

1) ORDER BY JOB

2) ORDER BY CNT DESC

3) ORDER BY COUNT(*)

4) ORDER BY 3

 

๋ฌธ์ œ 42. ์•„๋ž˜์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•  ๋•Œ ๋ณด๊ธฐ์˜ SQL ์„ ์ˆ˜ํ–‰ํ•œ ํ›„ SQLD_30_42_01 ํ…Œ์ด๋ธ”์˜ ๊ฑด์ˆ˜๋Š”?

์ •๋‹ตํ™•์ธ๐ŸŒผ

SQLD_30_42_01
COL1     COL2     COL3
   A        X        1
   B        Y        2
   C        Z        3
   X        T        1
   
SQLD_30_42_02
COL1     COL2     COL3
   A        X        1
   B        Y        2
   C        Z        3
   D        ๊ฐ€       4
   E        ๋‚˜       5
   
SQL>
MERGE INTO SQLD_30_42_01 A
  USING SQLD_30_42_02 B
    ON (A.COL1 = B.COL1)
  WHEN MATCHED THEN
    UPDATE SET A.COL3 = 4
         WHERE A.COL3 = 2
    DELETE WHERE A.COL3 <= 2
  WHEN NOT MATCHED THEN
    INSERT (A.COL1, A.COL2, A.COL3)
    VALUES (B.COL1, B.COL2, B.COL3);

1) 3

2) 4

3) 5

4) 6

- - - - - - - - ์ฃผ๊ด€์‹ - - - - - - - -

๋ฌธ์ œ 43. SELECT ABS(-3.8), FLOOR(3.8), TRUNC(3.8), ROUND(3.8) FROM DUAL; ์— ๋Œ€ํ•œ ๊ฐ’์„ ๊ตฌํ•˜์‹œ์˜ค.

์ •๋‹ตํ™•์ธ๐ŸŒผ

โœ๏ธ

 

๋ฌธ์ œ 44. ์•„๋ž˜์—์„œ ์„ค๋ช…ํ•˜๋Š” ๊ฒƒ์€ ๋ฌด์—‡์ธ๊ฐ€?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[๋ณด๊ธฐ]
๋ณต์žกํ•œ ์งˆ์˜๋ฅผ ๋‹จ์ˆœํ•˜๊ฒŒ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ๋‹ค.๋˜ํ•œ ํ•ด๋‹น ํ˜•ํƒœ์˜ SQL๋ฌธ์„ ์ž์ฃผ ์‚ฌ์šฉํ•  ๋•Œ ์ด์šฉํ•˜๋ฉด ํŽธ๋ฆฌํ•˜๊ฒŒ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.
์‚ฌ์šฉ์ž์—๊ฒŒ ์ •๋ณด๋ฅผ ๊ฐ์ถœ ์ˆ˜ ์žˆ๋‹ค

์‹ค์ œ ๋ฐ์ดํ„ฐ ๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ์ง€ ์•Š๋‹ค

โœ๏ธ

 

๋ฌธ์ œ 45. ์•„๋ž˜์™€ ๊ฐ™์€ ๋ฐ์ดํ„ฐ๊ฐ€ ๋“ค์–ด์žˆ๋Š” ํ…Œ์ด๋ธ”์—์„œ ์•„๋ž˜ sql์ด ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ์˜ ๊ฒฐ๊ณผ ๊ฑด์ˆ˜๋Š”?

์ •๋‹ตํ™•์ธ๐ŸŒผ

[TABLE] SQLD_30_45
๊ณ„์ธต๋ฒˆํ˜ธ     ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ
--------------------
     1         NULL
     2         NULL
     4            1
     5            1
     6            2
     7            2
     8            4
     9            5
    10            6
    11            7
    
SQL>
SELECT LEVEL,
   LPAD('** ',(LEVEL - 1)*2,' ') || ๊ณ„์ธต๋ฒˆํ˜ธ AS ๊ณ„์ธตํŠธ๋ฆฌ,
   ๊ณ„์ธต๋ฒˆํ˜ธ,
   ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ
FROM SQLD_30_45
START WITH ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ IS NULL
CONNECT BY ๊ณ„์ธต๋ฒˆํ˜ธ = PRIOR ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ;

โœ๏ธ

 

๋ฌธ์ œ 46. ์•„๋ž˜์˜ SQL ๊ตฌ๋ฌธ์€ ์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ๋ณ€ํ˜•ํ•˜๋Š” SQL ๊ตฌ๋ฌธ์ด๋‹ค. SQL ๊ตฌ๋ฌธ์„ ์™„์„ฑํ•˜์‹œ์˜ค. (SQL SERVER ๊ธฐ์ค€์ž„)

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL] ( ) DEPT ( ) VARCHAR(30) NOT NULL;

โœ๏ธ

 

๋ฌธ์ œ 47. ์•„๋ž˜์™€ ๊ฐ™์€ ํ…Œ์ด๋ธ”์ด ์žˆ์„ ๋•Œ ์•„๋ž˜์˜ SQL ๊ตฌ๋ฌธ์ด ์ˆœ์„œ๋Œ€๋กœ ์ˆ˜ํ–‰๋˜์—ˆ์„ ๋•Œ ๋งˆ์ง€๋ง‰ SQL์˜ ์ˆ˜ํ–‰ ๊ฒฐ๊ณผ๋ฅผ ์ ์œผ์‹œ์˜ค.

์ •๋‹ตํ™•์ธ๐ŸŒผ

[TABLE]
SQLD_47
COL1   VARCHAR2(30)
COL2   NUMBER

[SQL]
INSERT INTO SQLD_47(COL1, COL2) VALUES('ABCD',NULL);
INSERT INTO SQLD_47(COL1, COL2) VALUES('BC',NULL);
ALTER TABLE SQLD_47 MODIFY COL2 DEFAULT 10;
INSERT INTO SQLD_47(COL1, COL2) VALUES('XY',NULL);
INSERT INTO SQLD_47(COL1) VALUES('EXD');
SELECT SUM(COL2) FROM SQLD_47;

โœ๏ธ

 

๋ฌธ์ œ 48. ํ…Œ์ด๋ธ” ์ƒ์„ฑ์„ ์œ„ํ•œ ๊ถŒํ•œ์„ ์ฃผ๊ธฐ ์œ„ํ•ด ์•„๋ž˜์˜ SQL ์„ ์™„์„ฑํ•˜์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL] ( ) CREATE TABLE TO USER01;

โœ๏ธ

 

๋ฌธ์ œ 49. ์•„๋ž˜์™€ ๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ค๋„๋ก SQL ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

[RESULT]
TABLE : SQLD_30_49
ํšŒ์›ID    RANK    ์ฃผ๋ฌธ๊ธˆ์•ก
-----------------------
    B       1      450
    G       2      255
    F       2      255
    H       3      100

SQL>
SELECT ํšŒ์›ID,
    DENSE_RANK() OVER(ORDER BY (    )),
    ์ฃผ๋ฌธ๊ธˆ์•ก
FROM SQLD_30_49;

โœ๏ธ

 

๋ฌธ์ œ 50. ์•„๋ž˜์˜ SQL ๊ฒฐ๊ณผ๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค

์ •๋‹ตํ™•์ธ๐ŸŒผ

[SQL] SELECT UPPER("Sqldeveloper") FROM DUAL;

โœ๏ธ


๐Ÿ“SQLD. ์ œ 30 ํšŒ ๊ธฐ์ถœ ๋ฌธ์ œ(์ด 50) - ์ •๋‹ต / ํ•ด์„ค

 

1. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

๊ตฌ๋ถ„ ์„ค๋ช…
๋„๋ฉ”์ธ ๋ฐ์ดํ„ฐํƒ€์ž…, ํฌ๊ธฐ, ์ œ์•ฝ์‚ฌํ•ญ ์ง€์ •
์ œ์•ฝ์กฐ๊ฑด PK(๊ธฐ๋ณธํ‚ค), Unique Key(๊ณ ์œ ํ‚ค), Foreign Key(์™ธ๋ž˜ํ‚ค), Check(์ž…๋ ฅ๊ฐ’ ๋ฒ”์œ„์ œํ•œ) 
์‹๋ณ„์ž ์ฃผ์‹๋ณ„์žํŠน์ง• : ์œ ์ผ์„ฑ, ์ตœ์†Œ์„ฑ, ๋ถˆ๋ณ€์„ฑ(๊ฐ’์ด ๋ฐ”๋€Œ์ง€ ์•Š์Œ), ์กด์žฌ์„ฑ(Not null)
์ •๊ทœํ™” ํ•จ์ˆ˜์˜ ์ข…์†์„ฑ์„ ์ด์šฉํ•ด ๋ฌด์†์‹ค ๋ถ„ํ•ดํ•œ๋‹ค. (์ž…๋ ฅ/์ˆ˜์ •/์‚ญ์ œ ์„ฑ๋Šฅ ํ–ฅ์ƒ) *๋ฐ˜์ •๊ทœํ™”๋Š” ์กฐ์ธ์„ฑ๋Šฅ ํ–ฅ์ƒ

 

2. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ๋ฐ˜์ •๊ทœํ™” ์ „์— ํ…Œ์ด๋ธ” ์ถ”๊ฐ€ (ํ†ต๊ณ„, ์ค‘๋ณต, ์ด๋ ฅ ํ…Œ์ด๋ธ” ์ถ”๊ฐ€) ๋ฅผ ํ†ตํ•ด ๋ฐ˜์ •๊ทœํ™”๋ฅผ ํšŒํ”ผํ•œ๋‹ค. - ํ•ด๋‹น๋‚ด์šฉ์€ ๋ฐ˜์ •๊ทœํ™” ๊ธฐ๋ฒ•์ž„.

 

3. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ์‚ฌ์› (๊ธฐ๋ณธ ์—”ํ„ฐํ‹ฐ์ด๋‹ค.)

 

4. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ์ค‘์‹ฌ ์—”ํ„ฐํ‹ฐ : ์ƒํ’ˆ, ์ฃผ๋ฌธ์ƒํ’ˆ -> ์ƒํ’ˆ์€ ๊ธฐ๋ณธ ์—”ํ„ฐํ‹ฐ์ด๋‹ค.

 

5. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ๋…ผ๋ฆฌ์  ๋ชจ๋ธ๋ง

 

6. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : N๊ฐœ ํ…Œ์ด๋ธ”์„ ์กฐ์ธํ•  ๊ฒฝ์šฐ ์กฐ์ธ ์ตœ์†Œ ์กฐ๊ฑด์€ N-1๊ฐœ์ด๋‹ค.

 

7. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ํ…Œ์ด๋ธ”์— ์ง€๋‚˜์น˜๊ฒŒ ๋งŽ์€ ์กฐ์ธ๊ณผ Sorting, Order by ํ”„๋กœ์„ธ์Šค๊ฐ€ ๋งŽ์€ ๊ฒฝ์šฐ

 

8. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ๋Œ€ํ‘œ์„ฑ ์—ฌ๋ถ€์— ๋”ฐ๋ผ ๋ถ„๋ฅ˜๋˜๋Š” ์ฃผ์‹๋ณ„์ž์™€ ๋ณด์กฐ์‹๋ณ„์ž์—์„œ ์ฃผ์‹๋ณ„์ž๋Š” ๋Œ€ํ‘œ์„ฑ์„ ๊ฐ€์ง€์ง€ ๋ชปํ•ด ์ฐธ์กฐ๊ด€๊ณ„ ์—ฐ๊ฒฐ์„ ๋ชปํ•œ๋‹ค.

 

9. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

๊ตฌ๋ถ„ ์„ค๋ช…
๋ถ„์‚ฐ DB ๋…ผ๋ฆฌ์ ์œผ๋กœ ๊ฐ™์€ ์‹œ์Šคํ…œ, ๋ฌผ๋ฆฌ์  ๋ถ„์‚ฐ, ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ ํ•ด์นจ
๋ถ„ํ•  ํˆฌ๋ช…์„ฑ ๋ถ„ํ• ๋˜์„œ ์—ฌ๋Ÿฌ๊ตฐ๋ฐ ์ €์žฅ
์œ„์น˜ ํˆฌ๋ช…์„ฑ ์ €์žฅ์žฅ์†Œ ๋ช…์‹œ ๋ถˆํ•„์š”, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์‹ค์ œ ์œ„์น˜๋ฅผ ์•Œ ํ•„์š”์—†์ด ๋‹จ์ง€ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋…ผ๋ฆฌ์ ์ธ ๋ช…์นญ๋งŒ์œผ๋กœ ์—‘์„ธ์Šค ํ•  ์ˆ˜ ์žˆ๋‹ค.
์ง€์—ญ์‚ฌ์ƒ ํˆฌ๋ช…์„ฑ ์ง€์—ญ DBMS์™€ ๋ฌผ๋ฆฌ์  DB ์‚ฌ์ด Mapping ๋ณด์žฅ
์ค‘๋ณต ํˆฌ๋ช…์„ฑ ๋ฐ์ดํ„ฐ๊ฐ€ ์—ฌ๋Ÿฌ ๊ณณ์— ์ค‘๋ณต๋˜์–ด ์žˆ๋”๋ผ๋„ ์‚ฌ์šฉ์ž๋Š” ๋งˆ์น˜ ํ•˜๋‚˜์˜ ๋ฐ์ดํ„ฐ๋งŒ ์กด์žฌํ•˜๋Š” ๊ฒƒ์ฒ˜๋Ÿผ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•˜๋‹ค.
์žฅ์•  ํˆฌ๋ช…์„ฑ ํŠธ๋žœ์žญ์…˜, DBMS, ๋„คํŠธ์›Œํฌ, ์ปดํ“จํ„ฐ ์žฅ์• ์—๋„ ํŠธ๋žœ์žญ์…˜์„ ์ •ํ™•ํ•˜๊ฒŒ ์ฒ˜๋ฆฌํ•œ๋‹ค.
๋ณ‘ํ–‰ ํˆฌ๋ช…์„ฑ ๋‹ค์ˆ˜์˜ ํŠธ๋žœ์žญ์…˜์ด ๋™์‹œ์— ์‹คํ˜„๋˜๋”๋ผ๋„ ๊ทธ ๊ฒฐ๊ณผ๋Š” ์˜ํ–ฅ์„ ๋ฐ›์ง€ ์•Š๋Š”๋‹ค.

 

10. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : Row Chaining (row ์˜ ๊ธธ์ด๊ฐ€ ๋„ˆ๋ฌด ๊ธธ๋•Œ ์—ฌ๋Ÿฌ ๋ธ”๋ก์— ๊ฑธ์ณ์„œ ์ €์žฅํ•œ๋‹ค.)

 

11. ์ •๋‹ต :  1

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ORA-01722: ์ˆ˜์น˜๊ฐ€ ๋ถ€์ ํ•ฉํ•ฉ๋‹ˆ๋‹ค. (VARCHAR : ๊ฐ€๋ณ€ ๋ฌธ์ž์—ด์ด๋ฏ€๋กœ ๋น„๊ต์—ฐ์‚ฐ์„ ํ• ์ˆ˜์—†๋‹ค.)

 

12. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ORA-01427: ๋‹จ์ผ ํ–‰ ํ•˜์œ„ ์งˆ์˜์— 2๊ฐœ ์ด์ƒ์˜ ํ–‰์ด ๋ฆฌํ„ด๋˜์—ˆ์Šต๋‹ˆ๋‹ค. ( ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๋‹จ 1๊ฐœ์˜ ํ–‰๋งŒ ๊ฐ€์ ธ์˜ฌ์ˆ˜์žˆ๋‹ค. )

SQL> SELECT A.COL1,
            (SELECT COL3 FROM SQLD_30_12_02 B 
            WHERE A.COL1 = B.COL1) AS B_COL3
FROM SQLD_30_12_01 A 
WHERE A.COL2 > 10;
ERROR:
ORA-01427: single-row subquery returns more than one row



no rows selected

SQL> SELECT A.COL1,
            (SELECT COL5 FROM SQLD_30_12_03 B 
            WHERE A.COL4 = B.COL4) AS B_COL3
FROM SQLD_30_12_02 A 
WHERE A.COL4 > 0;

COL1  B_COL
----- -----
1     A
2     B
3     C
A     D

SQL> SELECT A.COL1, B.COL3 
FROM SQLD_30_12_01 A
             INNER JOIN SQLD_30_12_02 B 
                         ON (A.COL1 = B.COL1)
             INNER JOIN SQLD_30_12_03 C 
                         ON (B.COL4 = C.COL4)
WHERE C.COL4 > 5 
AND A.COL2 > 10;

COL1  COL3
----- -----
1     ABC
2     DEF
3     XY

SQL> SELECT A.COL1, B.COL3 
FROM SQLD_30_12_01 A
            INNER JOIN SQLD_30_12_02 B 
                  ON (A.COL1 = B.COL1)
WHERE EXISTS (SELECT 'X'
                  FROM SQLD_30_12_03 C
                  WHERE B.COL4 = C.COL4);

COL1  COL3
----- -----
1     ABC
2     DEF
3     XY
A     1

 

13. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ROLLUP (JOB_ID, MANAGER_ID)

 

14. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : COL1 = A , COL2 = 50 ์˜ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๊ฐœ์ˆ˜๋ฅผ ์ถœ๋ ฅํ•˜๋ฏ€๋กœ ๋‹ต์€ 1๊ฐœ์ด๋‹ค.

 

15. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : COL1 ์ด A or X or Null ์ธ COL2 ์˜ ์ปฌ๋Ÿผ๊ฐ’์„ ํ•ฉํ•ด์ฃผ์„ธ์š”.(X, 30) (A,40) (A,50) Null์€ ํฌํ•จ๋˜์ง€์•Š๋Š”๋‹ค X 

30+40+50 = 120

 

16. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : (๊ณ„์ธต๊ตฌ์กฐ) ์„ ํ–‰ํ…Œ์ด๋ธ” -> ์ธ๋ฑ์Šค ->์กฐ์ธ

 

17. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : WHERE 1 = 2 ( count(*) = 0 ๊ณต์ง‘ํ•ฉ์ด๋‹ค. )

NVL -> ( count(*) is null ์ด๋ฉด 9999, ์•„๋‹ˆ๋ฉด count(*) ๋ฐ˜ํ™˜ 0์ด๋ฏ€๋กœ ์ •๋‹ต 0 )

18. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ์ง‘๊ณ„ํ•จ์ˆ˜ SUM์€ NULL ์„ ํฌํ•จํ•˜์ง€์•Š๋Š”๋‹ค.

 

19. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : SAVEPOINT ์ด๋ฆ„์ด ๊ฐ™์„๋•Œ ๋งˆ์ง€๋ง‰์— ์ €์žฅํ•œ ๊ฐ’์œผ๋กœ ROLLBACK ํ•œ๋‹ค.

 

20. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ํŠธ๋ฆฌ๊ฑฐ๋Š” DB ์ž์ฒด์— ์ €์žฅ, ํ…Œ์ด๋ธ”, ๋ทฐ์— ์‚ฌ์šฉ๊ฐ€๋Šฅ 

Procedure Trigger
EXECUTE ๋ช…๋ น์–ด๋กœ ์‹คํ–‰ ์ž๋™ ์‹คํ–‰(์ด๋ฒคํŠธ ๋ฐœ์ƒํ• ๋•Œ)
CREATE Procedure CREATE Trigger
COMMIT, ROLLBACK ๊ฐ€๋Šฅ COMMIT, ROLLBACK ๋ถˆ๊ฐ€๋Šฅ
๋ฐ˜๋“œ์‹œ ๊ฐ’ RETURN ํ•„์š” X DML ์„ ์ฃผ๋กœ ์‚ฌ์šฉ

 

21. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : COALESCE ํ•จ์ˆ˜๋Š” ์ฒ˜์Œ์œผ๋กœ NULL์ด ์•„๋‹Œ ์ปฌ๋Ÿผ ๊ฐ’์„ ๋งŒ๋‚˜๋ฉด ๊ทธ ์ปฌ๋Ÿผ ๊ฐ’์„ ๋ฆฌํ„ด ํ•œ๋‹ค. coalesce(ํ•ฉ์น˜๋‹ค) 

 

22. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : COALESCE(ํ‘œํ˜„์‹1, ํ‘œํ˜„์‹2, โˆ™โˆ™โˆ™) : ์ž„์˜์˜ ๊ฐœ์ˆ˜ ํ‘œํ˜„์‹์—์„œ NULL์ด ์•„๋‹Œ ์ตœ์ดˆ์˜ ํ‘œํ˜„์‹์„ ์ถœ๋ ฅ

(๋ชจ๋“  ํ‘œํ˜„์‹์ด NULL ์ด๋ฉด NULL ๋ฆฌํ„ด)

SELECT COALESCE(COL1, COL2*50, 50) FROM SQLD_30_11;

-> (100, 100) COL1 = 100 ์ด๋ฏ€๋กœ 100 

-> (NULL, 60) COL1 = NULL ์ด๋ฏ€๋กœ pass, COL2 = 60 ์ด๋ฏ€๋กœ 60*50 = 3000 

-> (NULL, NULL) ์ด๋ฉด pass, 50

( 100, 3000, 50 )

 

23. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : SELECT ๋Š” ๋ฐ์ดํ„ฐ ์กฐ์ž‘์–ด(Data Manipulation Language) DML ์— ์†ํ•œ๋‹ค.

๊ตฌ๋ถ„ ์ข…๋ฅ˜
DDL CREATE, DROP, MODIFY(์˜ค๋ผํด), ALTER(SQL์„œ๋ฒ„)
DML SELECT, INSERT, DELETE, UPDATE
DCL GRANK, REVOKE
TCL COMMIT, ROLLBACK, SAVEPOINT

DCL์€ ํŠธ๋žœ์žญ์…˜ ์„ ์ œ์–ดํ•˜๋Š”๋ฐ ํŠนํ™”๋œ ๋ช…๋ น์–ด๋ฅผ TCL(Transaction Control Language) 'ํŠธ๋žœ์žญ์…˜์„ ์ œ์–ดํ•˜๋Š” SQL ์–ธ์–ด' ๋ผ๊ณ  ํ•œ๋‹ค.

์„ธ๋ถ€์ ์œผ๋กœ๋Š” DCL์ด TCL์„ ํฌํ•จํ•˜๊ณ  ์žˆ๋‹ค.

24. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : TCL(Transaction Control Language) - COMMIT, ROLLBACK, SAVEPOINT

 

25. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : CHARACTER (๊ณ ์ •๊ธธ์ด ๋ฌธ์ž์—ด, S๋งŒํผ ์ตœ๋Œ€๊ธธ์ด(๊ทธ๋ณด๋‹ค ์ž‘์„์‹œ ๊ณต๋ฐฑ์œผ๋กœ ์ฑ„์šด๋‹ค.)

 

26. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ROWNUM( ์ƒ์œ„ N๊ฐœ ์ถ”์ถœ ) , TOP( ORDER BY๊ฐ€ ์žˆ์„์‹œ ์ •๋ ฌ ํ›„ ์ƒ์œ„ N๊ฐœ ์ถ”์ถœ )

 

27. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

28. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : UNION ALL (ํ•ฉ์ง‘ํ•ฉ & ์ค‘๋ณตํ—ˆ์šฉ)

 

29. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : Sort Merge Join ( ์ •๋ ฌ ๋ณ‘ํ•ฉ ์กฐ์ธ )

 

30. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

31. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : DISTINCT(์ค‘๋ณต์ œ๊ฑฐ), UNION ALL(์ค‘๋ณต ํ—ˆ์šฉ ํ•ฉ์ง‘ํ•ฉ)

 

32. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

33. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : WHERE KEY 1 = 1

 

34. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 'C002' ๊ณ ๊ฐID ๊ฐ€ ์ฃผ๋ฌธ์—๋งŒ ์žˆ์„ ์ˆ˜ ์—†์Œ -- ORA-02291 : ๋ฌด๊ฒฐ์„ฑ ์ œ์•ฝ์กฐ๊ฑด(LSS.๊ณ ๊ฐ_FK)์ด ์œ„๋ฐฐ... - ๋ถ€๋ชจ Key๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.

 

35. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : Cross Join ์€ Where ์ ˆ์—์„œ ์กฐ๊ฑด์„ ๊ฑธ ์ˆ˜ ์žˆ๋‹ค.

 

36. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : LIST ( ๋Œ€๋Ÿ‰ ๋ฐ์ดํ„ฐ / ํŠน์ •์ปฌ๋Ÿผ(์ƒ์„ฑ์ผ์ž) ์—†์Œ / PK )

 

37. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : RIGHT OUTER JOIN

 

38. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค :

1) LAST_VALUE() OVER : ๊ฐ€์žฅ ๋งˆ์ง€๋ง‰ ๊ฐ’

2) FIRST_VALUE () OVER : ์ œ์ผ ์ฒ˜์Œ๊ฐ’

3) MAX() OVER : ์ œ์ผ ํฐ ๊ฐ’

 

39. ์ •๋‹ต :  3 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

40. ์ •๋‹ต :  1 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 10

 

41. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

42. ์ •๋‹ต :  4 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 6 -- MERGE ์—์„œ์˜ DELETE ๊ตฌ๋ฌธ์€ DELETE ๋‹จ๋… ๊ตฌ๋ฌธ์ด ์•„๋‹Œ UPDATE ๊ตฌ๋ฌธ์— ์ข…์†๋จ UPDATE ์‹คํ–‰๋œ ๊ฑด์— ํ•œํ•ด์„œ DELETE ๊ตฌ๋ฌธ์ด ์ˆ˜ํ–‰๋œ๋‹ค.

 

- - - - - - - - ์ฃผ๊ด€์‹ - - - - - - - -

 

43. ์ •๋‹ต :  3.8 , 3 , 3 , 4

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : ABS(์ ˆ๋Œ“๊ฐ’), FLOOR(ceil์˜ ๋ฐ˜๋Œ€, ์ •์ˆ˜๋กœ ๋‚ด๋ฆผ), TRUNC(์†Œ์ˆ˜์  ๋ฒ„๋ฆผ), ROUND(๋ฐ˜์˜ฌ๋ฆผ)

 

44. ์ •๋‹ต :  View

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : view (ํ•˜๋‚˜ ์ด์ƒ์˜ ๊ธฐ๋ณธ ํ…Œ์ด๋ธ”์ด๋‚˜ ๋‹ค๋ฅธ ๋ทฐ๋ฅผ ์ด์šฉํ•˜์—ฌ ์ƒ์„ฑ๋˜๋Š” ๊ฐ€์ƒ ํ…Œ์ด๋ธ”)

 

45. ์ •๋‹ต :  2 

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

-> FROM ํ•ด๋‹นํ‘œ ์„ ํƒ

-> START WITH ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ IS NULL(1, 2 ์„ ํƒ)

-> CONNECT BY ๊ณ„์ธต๋ฒˆํ˜ธ = PRIOR ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ

     PRIOR ์ž์‹ = ๋ถ€๋ชจ

-> PRIOR ์ƒ์œ„๊ณ„์ธต๋ฒˆํ˜ธ = NULL์ธ ๊ณ„์ธต๋ฒˆํ˜ธ๊ฐ€ ์—†์œผ๋ฏ€๋กœ ์ข…๋ฃŒ

-> 2๊ฑด ์ถœ๋ ฅ

 

46. ์ •๋‹ต :  ALTER TABLE, ALTER COLUMN

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

47. ์ •๋‹ต :  10

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : 

 

48. ์ •๋‹ต :  GRANT

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : GRANT( ๊ถŒํ•œ๋ถ€์—ฌ ) / REVORK( ๊ถŒํ•œํšŒ์ˆ˜ )

 

49. ์ •๋‹ต :  ์ฃผ๋ฌธ๊ธˆ์•ก DESC

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : DENSE_RANK() (๋™์ผ ์ˆœ์œ„, ์ˆœ์œ„ ๊ฑด๋„ˆ๋›ฐ๊ธฐ X (1์œ„, 2์œ„, 2์œ„, 3์œ„)

-> ์ฃผ๋ฌธ๊ธˆ์•ก์— ๋”ฐ๋ผ ์ˆœ์œ„๋ถ€์—ฌ

-> DENSE_RANK() OVER(ORDER BY ์ฃผ๋ฌธ๊ธˆ์•ก DESC) ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌ

 

50. ์ •๋‹ต :  SQLDEVELOPER

๐Ÿ“–๋ฌธ์ œํ™•์ธ

 

ํ•ด์„ค : UPPER ( ๋Œ€๋ฌธ์ž๋กœ ๋ณ€ํ™˜ ), LOWER ( ์†Œ๋ฌธ์ž๋กœ ๋ณ€ํ™˜ ), INITCAP ( ์ฒซ ๋ฒˆ์งธ ๋ฌธ์ž๋งŒ ๋Œ€๋ฌธ์ž๋กœ ๋ณ€ํ™˜ )

 

300x250

์ฝ”๋“œ