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

[SQLD] 45 ~ 49ํšŒ ๊ธฐ์ถœ๋ฌธ์ œ ์ฃผ๊ด€์‹ ๋ชจ์Œ ( ์ด 32๋ฌธ์ œ/์ •๋‹ต )

by yunamom 2023. 6. 23.
๋ฐ˜์‘ํ˜•

๐Ÿ“– SQLD 45 ~ 49ํšŒ ๊ธฐ์ถœ๋ฌธ์ œ ๋‹จ๋‹ตํ˜• ๋ชจ์Œ


๋ฌธ์ œ 1.

45ํšŒ ๋‹จ๋‹ตํ˜• 1 (1๊ณผ๋ชฉ). ์—…๋ฌด์—์„œ ํ•„์š”๋กœ ํ•˜๋Š” ์ธ์Šคํ„ด์Šค๋กœ ๊ด€๋ฆฌํ•˜๊ณ ์ž ํ•˜๋Š” ์˜๋ฏธ์ƒ ๋” ์ด์ƒ ๋ถ„๋ฆฌ ๋˜์ง€ ์•Š๋Š” ์ตœ์†Œ์˜ ๋ฐ์ดํ„ฐ ๋‹จ์œ„๋Š”?

(๋…ธ๋žญ์ด 1๊ณผ๋ชฉ 14๋ฒˆ)

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 2.

45ํšŒ ๋‹จ๋‹ตํ˜• 2 (1๊ณผ๋ชฉ). ๊ธฐ๋ณธํ‚ค๊ฐ€ ์•„๋‹Œ ๋ชจ๋“  ์†์„ฑ์ด ๊ธฐ๋ณธํ‚ค์— ์™„์ „ ํ•จ์ˆ˜ ์ข…์†๋œ ์ƒํƒœ๋ฅผ ๋ฌด์—‡์ด๋ผ ํ•˜๋Š”๊ฐ€?

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 3.

45ํšŒ ๋‹จ๋‹ตํ˜• 3 (2๊ณผ๋ชฉ). ์•„๋ž˜ SQL1 ๊ณผ ๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ค๋„๋ก SQL2 ๋นˆ์นธ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQL1]
SELECT*FROM A, B;

[SQL2]
SELECT*FROM A (       ) B;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 4.

45ํšŒ ๋‹จ๋‹ตํ˜• 4 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ ๊ณ„์ธตํ˜• ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ์—์„œ C3์˜ 2๋ฒˆ์งธ ๊ฐ’์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQLD44]
C1  C2   C3
------------
 1 NULL  A
 2  1    B
 3  1    C
 4  2    D

[SQL]
SELECT C1, C2, C3
FROM SQLD44
     CONNECT BY PRIOR C1 = C2
           START WITH C1 = 1
     ORDER SIBLINGS BY C1 DESC;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 5.

45ํšŒ ๋‹จ๋‹ตํ˜• 5 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ ๊ณ„์ธตํ˜• ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQLD45]

 C1  C2  C3
-------------- 
 1       KING
 2   1   JOHN
 3   2   SCOTT

[SQL]
SELECT C3 FROM SQLD45
   WHERE C1 <> 2
   CONNECT BY C1 = PRIOR C2
   START WITH C1 = 2;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 6.

45ํšŒ ๋‹จ๋‹ตํ˜• 6 (2๊ณผ๋ชฉ). GRANT, REVOKE ๋“ฑ์˜ SQL์„ ๋ฌด์—‡์ด๋ผ ํ•˜๋Š”๊ฐ€?

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 7.

45ํšŒ ๋‹จ๋‹ตํ˜• 7 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ์˜ ๋นˆ์นธ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค. 

[SQLD45]
COL1  COL2  COL3
--------------------
 A     ๊ฐ€    10
 A     ๊ฐ€    20
 A     ๋‹ค    25
 B     ๊ฐ€    10
 B     ๋‚˜    30
 B     ๋‚˜    20
 B     ๋‚˜    60
 C     ๋ผ    30

[SQL]
SELECT NTILE_3, COUNT(*) AS ROWCNT
FROM (
      SELECT COL1, COL2, COL3, NTILE(3) OVER(ORDER BY COL3) AS NTILE_3
      FROM SQLD45
      )
GROUP BY NTILE_3;

[RESULT]
NTILE_3   ROWCNT
-----------------
1         ( ใ„ฑ )
2           3
3         ( ใ„ด )

( ใ„ฑ )

( ใ„ด )

 

์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 8.

45ํšŒ ๋‹จ๋‹ตํ˜• 8 (2๊ณผ๋ชฉ). ์•„๋ž˜ SQL์˜ ๋นˆ์นธ(    ?    ) ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQLD45]
V1	    N1
----------------
A	    100
B	    150
C	   1400
D	    450
E	     50

[SQL]
SELECT V1, N1,
  COUNT(N1) OVER 
            (ORDER BY N1 (  ?  ) 
            BETWEEN 0 PRECEDING 
            AND
            50 FOLLOWING) AS CNT 
FROM SQLD45;

โœ๏ธ ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 9.

45ํšŒ ๋‹จ๋‹ตํ˜• 9 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ๋ฅผ ์ ์œผ์‹œ์˜ค. 

[SQLD45]

 N1   ID    N2
--------------
 200  A     T1
 250  B     T2
 300  NULL  T3
 
[SQL]
SELECT COUNT(*) FROM SQLD45
WHERE N1 >= 200 OR N2 IS NULL AND ID = 'B';

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 10.

45ํšŒ ๋‹จ๋‹ตํ˜• 10 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ๋ฅผ ์ ์œผ์‹œ์˜ค.

[SQLD45]

N1   V1
--------
 1  SMITH
 2  JOHN
 3  ALX
 4  CLARE
 5  BLX
 
[SQL]
SELECT COUNT(*) 
FROM SQLD45 
WHERE V1 LIKE '_L%';

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 11.

46ํšŒ ๋‹จ๋‹ตํ˜• 1 (1๊ณผ๋ชฉ). ์†์„ฑ์ด ๊ฐ€์งˆ ์ˆ˜ ์žˆ๋Š” ๊ฐ’์˜ ๋ฒ”์œ„๋ฅผ ๋ฌด์—‡์ด๋ผ ํ•˜๋Š”์ง€ ์ž‘์„ฑํ•˜์‹œ์˜ค.

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 12.

46ํšŒ ๋‹จ๋‹ตํ˜• 2 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ ๋นˆ์นธ(   ?   )์— ์•Œ๋งž์€ ๊ฒƒ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

์ฃผ์‹๋ณ„์ž์˜ 5๊ฐ€์ง€ ํŠน์ง•์ค‘ ํ•˜๋‚˜์ธ (  ?  )์€ 
ํ•˜๋‚˜์˜ ํ‚ค๋กœ ํŠน์ • ํ–‰์„ ๋ฐ”๋กœ ์ฐพ์•„๋‚ผ์ˆ˜ ์žˆ๋Š” ๊ณ ์œ ํ•œ ๋ฐ์ดํ„ฐ ์†์„ฑ์„ ๋งํ•œ๋‹ค.

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 13.

46ํšŒ ๋‹จ๋‹ตํ˜• 3 (2๊ณผ๋ชฉ).  ์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ๋ฅผ ์ ์œผ์‹œ์˜ค.

[TAB1]
COL1
------
4
8
4
8
NULL
NULL

[SQL]
SELECT AVG(COL1) - AVG(NVL(COL1, 0)) FROM TAB1;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 14-1.

46ํšŒ ๋‹จ๋‹ตํ˜• 4 (2๊ณผ๋ชฉ). ์‹ ๊ทœ ์‚ฌ์›์˜ ๊ฒฝ์šฐ ์ผ์‹œ์ ์œผ๋กœ ๋ถ€์„œ์— ์†ํ•ด ์žˆ์ง€ ์•Š๋‹ค๊ณ  ๊ฐ€์ •ํ•˜๊ณ  DEPT์™€ EMP๋ฅผ ์กฐ์ธํ•˜๋˜ ๋ถ€์„œ๊ฐ€ ์—†๋Š” ์‚ฌ์›์„ ์ถœ๋ ฅํ•˜๋„๋ก ํ•  ๋•Œ, ์•„๋ž˜ SQL๋ฌธ์žฅ์˜ (   ?   )์•ˆ์— ๋“ค์–ด๊ฐˆ ๋‚ด์šฉ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQL]
SELECT E.ENAME
FROM DEPT D (   ?   ) EMP E
ON D.DEPTNO = E.DEPTNO
WHERE E.DEPTNO IS NULL;

โœ๏ธ์ •๋‹ตํ™•์ธ

 

๋ฌธ์ œ 14-2. ์œ„์™€ ๊ฐ™์€ ์œ ํ˜•์˜ ( ๋…ธ๋žญ์ด 2๊ณผ๋ชฉ 75๋ฒˆ ๋ฌธ์ œ )

- ์‹ ๊ทœ ๋ถ€์„œ์˜ ๊ฒฝ์šฐ ์ผ์‹œ์ ์œผ๋กœ ์‚ฌ์›์ด ์—†๋Š” ๊ฒฝ์šฐ๋„ ์žˆ๋‹ค๊ณ  ๊ฐ€์ •ํ•˜๊ณ  DEPT์™€ EMP๋ฅผ ์กฐ์ธํ•˜๋˜ ์‚ฌ์›์ด ์—†๋Š” ๋ถ€์„œ ์ •๋ณด๋„ ๊ฐ™์ด ์ถœ๋ ฅํ•˜๋„๋ก ํ•  ๋•Œ, ์•„๋ž˜ SQL๋ฌธ์žฅ์˜ (   ?   ) ์•ˆ์— ๋“ค์–ด๊ฐˆ ๋‚ด์šฉ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

 

[SQL]
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D (   ?   ) EMP E
ON D.DEPTNO = E.DEPTNO;

โœ๏ธ์ •๋‹ตํ™•์ธ

 

๋ฌธ์ œ 14-3. OUTER JOIN ๋ฌธ์ œ

- ๋‹ค์Œ์€ Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ์‚ฌ์šฉํ•˜๋Š” OUTER JOIN์˜ ์˜ˆ์ œ์ด๋‹ค. ๋นˆ์นธ(    ?    )์„ ์ฑ„์›Œ Oracle์˜ OUTER JOIN์„ ํ‘œ์ค€ ANSI SQL๋กœ ๋ณ€๊ฒฝํ•˜์‹œ์˜ค.

[Oracle OUTER JOIN]
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

[ANSI JOIN]
SELECT * FROM EMP (   ?   ) DEPT
ON (EMP.DEPTNO = DEPT.DEPTNO);

โœ๏ธ์ •๋‹ตํ™•์ธ

 


๋ฌธ์ œ 15.

46ํšŒ ๋‹จ๋‹ตํ˜• 5 (2๊ณผ๋ชฉ). ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๊ฒฐ๊ณผ๋ฅผ ์กฐํšŒํ•˜๊ธฐ ์œ„ํ•œ SQL๋ฌธ์˜ ๋นˆ์นธ (   ?   ) ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[TAB1]
JOB
-----
10
20
30


[TAB2]
GRADE
-----
50
60
70

[RESULT]
GRADE    JOB    SUM(GRADE)
--------------------------
50       10       50
50       20       50
50       30       50
50                150
60       10       60
60       20       60
60       30       60
60                180
70       10       70
70       20       70
70       30       70
70                210
[SQL]
SELECT B.GRADE, A.JOB, SUM(B.GRADE)
FROM TAB1 A, TAB2 B GROUP BY (          );

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 16.

 

47ํšŒ ๋‹จ๋‹ตํ˜• 1 (1๊ณผ๋ชฉ)

์ฃผ๋ฌธ๋ฒˆํ˜ธ+์ƒํ’ˆ๋ฒˆํ˜ธ PK, ์ƒํ’ˆ๋ช… ์ปฌ๋Ÿผ, ์ƒํ’ˆ๋ช…์ด ์ฃผ์‹๋ณ„์ž์˜ ํ•˜๋‚˜์— ์ข…์†์ ์ธ ์ƒํƒœ
์ด ์ƒํƒœ๋Š” ์–ด๋–ค ์ •๊ทœํ˜•์„ ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š”๊ฐ€?

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 17.

47ํšŒ ๋‹จ๋‹ตํ˜• 2 (1๊ณผ๋ชฉ). ์•„๋ž˜์˜ ์„ค๋ช…์— ํ•ด๋‹นํ•˜๋Š” ์Šคํ‚ค๋งˆ๋ฅผ ์ ์œผ์‹œ์˜ค.

๋ทฐ ๋‹จ๊ณ„ ์—ฌ๋Ÿฌ ๊ฐœ์˜ ์‚ฌ์šฉ์ž ๊ด€์ ์œผ๋กœ ๊ตฌ์„ฑ ๋˜์–ด ์žˆ์œผ๋ฉฐ, 
๊ฐ ๊ฐœ์ธ์˜ ์ž…์žฅ์—์„œ ํ•„์š”๋กœ ํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋…ผ๋ฆฌ์  ๊ตฌ์กฐ๋ฅผ ์ •์˜ํ•œ ๊ฒƒ

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 18.

47ํšŒ ๋‹จ๋‹ตํ˜• 3 (2๊ณผ๋ชฉ). USER๋ฅผ ์‚ญ์ œํ•  ๋•Œ USER ์— ์†ํ•œ ๋ชจ๋“  OBJECT๋ฅผ ๊ฐ™์ด ์‚ญ์ œํ•˜๋Š” ๋ช…๋ น์–ด๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[SQL]

ALTER DROP USER user_name (   ?   )

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 19.

47ํšŒ ๋‹จ๋‹ตํ˜• 4 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL์„ ์™„์„ฑํ•˜์‹œ์˜ค.

[TAB1]
COL1   
------
1000    
1000   
1000    
3000     
3000   
4000 
9999  
9999   

[RESULT]
COL1
----
3000
9999

[SQL]
SELECT COL1 FROM TAB1
(   ?   ) COL1 HAVING COUNT(*) = 2
ORDER BY (CASE WHEN COL1 = 1000 THEN 0 ELSE COL1 END);

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 20.

47ํšŒ ๋‹จ๋‹ตํ˜• 5 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL์— ๋Œ€ํ•œ ๊ฒฐ๊ณผ๋ฅผ ์ ์œผ์‹œ์˜ค.

[TAB1]
COL1   COL2    COL3   COL4
---------------------------
1       2      200     20
2       3      240     10
3       4      100     20
4       5      230     20
5       6      300     20
6       7      400     10

[SQL]

SELECT COL2, COL1
FROM (SELECT ROW_NUMBER() 
      OVER(PARTITION BY COL4 
      ORDER BY COL3 DESC) RN,
      COL1, COL2 FROM TAB1
      WHERE COL1 <> 5 AND COL4 > 10)
WHERE RN = 1;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 21.

47ํšŒ ๋‹จ๋‹ตํ˜• 6 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ ๊ณ„์ธตํ˜• ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[TAB1]
COL1 COL2
----------
1    NULL
2    NULL
3    1
4    1
5    2
6    2
7    3
8    4
9    5

[SQL]
SELECT COUNT(*)
FROM TAB1
WHERE COL1 <> 4
START WITH COL1= 1
CONNECT BY PRIOR COL1 = COL2;
โ€‹

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 22.

48ํšŒ ๋‹จ๋‹ตํ˜• 1 (2๊ณผ๋ชฉ). ์•„๋ž˜ T1 ํ…Œ์ด๋ธ”์˜ SQL์‹คํ–‰ ํ›„ ๊ฒฐ๊ด๊ฐ’์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[T1]
COL1    COL2    COL3
--------------------
10      NULL     10
NULL    50       10
0       10       10

[SQL]
ใ„ฑ. SELECT SUM(col2) FROM T1;
ใ„ด. SELECT SUM(col2) FROM T1 WHERE col1 > 0;
ใ„ท. SELECT SUM(col2) FROM T1 WHERE col1 IS NOT NULL;
ใ„น. SELECT SUM(col2) FROM T1 WHERE col1 IS NULL;

( ใ„ฑ ) 

( ใ„ด ) 

( ใ„ท ) 

( ใ„น ) 

์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 23.

48ํšŒ ๋‹จ๋‹ตํ˜• 2 (2๊ณผ๋ชฉ). ORDER BY ํ•œ ๊ฒฐ๊ณผ๋กœ 3๋ฒˆ์งธ ์˜ค๋Š” ๊ฐ’์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[TAB1]
ID
----
1
2
3
4


[SQL]
SELECT ID FROM TAB1
ORDER BY (CASE WHEN ID IN(1, 3) THEN ID*3 ELSE ID END) DESC;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 24.

48ํšŒ ๋‹จ๋‹ตํ˜• 3 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL๋ฌธ ์‹คํ–‰ ๊ฒฐ๊ณผ๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[TAB1]

COL1     COL2
-------------
1         1
2
3         1
4         1
5         2
6         2
7         3
8         4

[SQL]
SELECT COUNT(*) FROM TAB1
WHERE COL1 <> 4
START WITH COL1 = 2
CONNECT BY PRIOR COL1 = COL2;

โœ๏ธ

์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 25.

48ํšŒ ๋‹จ๋‹ตํ˜• 4 (2๊ณผ๋ชฉ). ์ฃผ์–ด์ง„ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด์„œ ์•„๋ž˜์™€ ๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ค๋„๋ก SQL๋นˆ์นธ(   ?   )์„ ์™„์„ฑํ•˜์‹œ์˜ค.

[EMPLOYEES]
EMPLOYEE_ID   DEPARTMENT_ID   LAST_NAME   SALARY
------------------------------------------------
107               60           Lorentz    4200.00
106               60           Pataballa  4800.00
105               60           Austin     4800.00
104               60           Ernst      6000.00
103               60           Hunold     9000.00
102               90           De Haan    17000.00
101               90           Kochhar    17000.00
100               90           King       24000.00
109               100          Faviet     9000.00
108               100          Greenberg  12000.00
201               200          ALEX       8000
202               200          SMITH      7000

[๊ฒฐ๊ณผ]
EMPLOYEE_ID  DEPARTMENT_ID  LAST_NAME  SALARY  BEFORE_SALARY
------------------------------------------------------------
107             60           Lorentz   4200.00
106             60           Pataballa 4800.00
105             60           Austin    4800.00    4200
104             60           Ernst     6000.00    4800
103             60           Hunold    9000.00    4800
102             90           De Haan   17000.00
101             90           Kochhar   17000.00
100             90           King      24000.00   17000
109             100          Faviet    9000.00
108             100          Greenberg 12000.00
[SQL]

SELECT EMPLOYEE_ID,
       DEPARTMENT_ID,
       LAST_NAME,
       SALARY,
       ( ? )(SALARY, 2)
       OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY)
       AS BEFORE_SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID < 110;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 26.

48ํšŒ ๋‹จ๋‹ตํ˜• 5 (2๊ณผ๋ชฉ). Oracle ์—์„œ USER1 ์—๊ฒŒ STUDENT_T ํ…Œ์ด๋ธ”์˜ INSERT ๊ถŒํ•œ์„ ๋ถ€์—ฌํ•  ๋•Œ ๋ช…๋ น์–ด๋กœ ์•„๋ž˜ ๋นˆ์นธ(  ใ„ฑ  ), (  ใ„ด  ), (  ใ„ท  ) ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค.

(  ใ„ฑ  ) INSERT ( ใ„ด ) STUDENT_T ( ใ„ท ) USER1;

( ใ„ฑ ) 

( ใ„ด ) 

( ใ„ท ) 

์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 27.

49ํšŒ ๋‹จ๋‹ตํ˜• 1 (1๊ณผ๋ชฉ). ๋‘ ๊ฐœ์˜ ์—”ํ„ฐํ‹ฐ๊ฐ„ ๊ด€๊ณ„์—์„œ ์ฐธ์—ฌ์ž์˜ ์ˆ˜๋ฅผ ํ‘œํ˜„ํ•˜๋Š” ๊ฒƒ์„ ๋ฌด์—‡์ด๋ผ ํ•˜๋Š”๊ฐ€?

 

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 28.

49ํšŒ ๋‹จ๋‹ตํ˜• 2 (2๊ณผ๋ชฉ). ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์—์„œ ํŒŒ์ƒ๋œ ํ…Œ์ด๋ธ”๋กœ, ๋ฌผ๋ฆฌ์  ๋ฐ์ดํ„ฐ๊ฐ€ ์ €์žฅ๋˜๋Š” ๊ฒƒ์ด ์•„๋‹Œ ๋…ผ๋ฆฌ์ ์œผ๋กœ ์กด์žฌํ•˜๋Š”๊ฒƒ์„ ๋ฌด์—‡์ด๋ผ๊ณ  ํ•˜๋Š”๊ฐ€?

 

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 29.

49ํšŒ ๋‹จ๋‹ตํ˜• 3 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL ๊ฒฐ๊ณผ๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[TAB1]

ID     COL1
-----------
A      1
A      2
B      3
B      4
C      5
C      6
C      7
D      8

[SQL]
SELECT COUNT(*)
FROM TAB1
GROUP BY ID
HAVING COUNT(*) > 2;

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 30-1.

49ํšŒ ๋‹จ๋‹ตํ˜• 4 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ฌ ์ˆ˜ ์žˆ๋„๋ก ๋นˆ์นธ์„ ์ฑ„์šฐ์‹œ์˜ค.

 

[TAB1]
C1   C2   C3
-------------
1         A
2    1    B
3    1    C
4    2    D

[RESULT]
C3   C1
-------
A    1
C    3
B    2
D    4

[SQL]
SELECT C3, C1
FROM TAB1
START WITH C2 (  ?  )
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC;

โœ๏ธ์ •๋‹ตํ™•์ธ

๋ฌธ์ œ 30-2.

์•„๋ž˜์˜ SQL๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ฌ ์ˆ˜ ์žˆ๋„๋ก ๋นˆ์นธ์„ ์ฑ„์šฐ์‹œ์˜ค.

[EMP]
EMPNO  ENAME  EDATE       MANAGER
--------------------------------
001    ๊น€์‚ฌ์›   2021-01-01  NULL
002    ์ด์‚ฌ์›   2021-01-01  001
003    ์‚ผ์‚ฌ์›   2022-01-01  001
004    ์‚ฌ์‚ฌ์›   2022-01-01  001
005    ์˜ค์‚ฌ์›   2022-01-01  NULL
006    ์œก์‚ฌ์›   2023-01-01  005
007    ๋‚˜์‚ฌ์›   2023-01-01  005

[RESULT]
EMPNO  ENAME    EDATE      MANAGER
----------------------------------
001    ๊น€์‚ฌ์›    2021-01-01    NULL
003    ์‚ผ์‚ฌ์›    2022-01-01    001
004    ์‚ฌ์‚ฌ์›    2022-01-01    001
005    ์˜ค์‚ฌ์›    2022-01-01    NULL

[SQL]
SELECT EMPNO, ENAME, EDATE, MANAGER
FROM EMP
START WITH MANAGER (  ?  )
CONNECT BY PRIOR EMPNO = MANAGER
AND EDATE BETWEEN '2022-01-01' AND '2022-12-31'
ORDER SIBLINGS BY EMPNO;

โœ๏ธ์ •๋‹ตํ™•์ธ

 


๋ฌธ์ œ 31.

49ํšŒ ๋‹จ๋‹ตํ˜• 5 (2๊ณผ๋ชฉ). ์•„๋ž˜์˜ ๊ฒฐ๊ณผ[RESULT]๋ฅผ ์–ป๊ธฐ ์œ„ํ•œ SQL๋ฌธ์—์„œ ๋นˆ์นธ(  ใ„ฑ  )์— ๋“ค์–ด๊ฐˆ ํ•จ์ˆ˜๋ฅผ ์ž‘์„ฑํ•˜์‹œ์˜ค.

[RESULT]
๊ตฌ๋งค๊ณ ๊ฐ   ๊ตฌ๋งค์›”    ์ด ๊ตฌ๋งค๊ฑด    ์ด ๊ตฌ๋งค์•ก
-----------------------------------
AAA      202301      1       1000
AAA      202302      2       3000
AAA      202303      1       1000
AAA                  4       5000
BBB      202301      3       2000
BBB      202302      5       3000
BBB      202303      1       2000
BBB                  9       7000
CCC      202301      1       2000
CCC      202302      1       5000
CCC      202303      1       1000
CCC                  3       8000
                     16      20000
                     
[SQL]
SELECT ๊ตฌ๋งค๊ณ ๊ฐ, ๊ตฌ๋งค์›”, COUNT(*) "์ด ๊ตฌ๋งค๊ฑด", SUM(๊ตฌ๋งค๊ธˆ์•ก) "์ด ๊ตฌ๋งค์•ก"
FROM ๊ตฌ๋งค์ด๋ ฅ
GROUP BY (   ใ„ฑ   ) (๊ตฌ๋งค๊ณ ๊ฐ, ๊ตฌ๋งค์›”)

โœ๏ธ์ •๋‹ตํ™•์ธ


๋ฌธ์ œ 32.

49ํšŒ ๋‹จ๋‹ตํ˜• 6 (2๊ณผ๋ชฉ).  ์•„๋ž˜ SQL๊ฒฐ๊ณผ์˜ ๋นˆ์นธ(  ใ„ฑ  ), (  ใ„ด  ) ์„ ์ž‘์„ฑํ•˜์‹œ์˜ค. 

[TAB1]
COL1  COL2
----------
1      A
2      B
3      B
4      C
5      C
6      C
7      D

[SQL]
SELECT NT, COUNT(*)
FROM (SELECT NTILE(3) OVER (ORDER BY COL1) AS NT
      FROM TAB1)
WHERE 1=1
GROUP BY NT
ORDER BY 1;
 
[RESULT]

  NT     COUNT(*)
( ใ„ฑ )    ( ใ„ด )
  2         2
  3         2

(ใ„ฑ) (ใ„ด)์ •๋‹ตํ™•์ธ

 


 

1. ์ •๋‹ต :  ์†์„ฑ (ATTRIBUTE)

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

 


2. ์ •๋‹ต :  ์ œ2์ •๊ทœํ˜•

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

 


3. ์ •๋‹ต :  CROSS JOIN

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

 

 

ํ•ด์„ค:

SQL> SELECT*FROM A;

	C1
----------
	 1
	 2

SQL> SELECT*FROM B;

	C1
----------
	 2

SQL> SELECT*FROM A, B;

	C1	   C1
---------- ----------
	 1	    2
	 2	    2

SQL> SELECT*FROM A CROSS JOIN B;

	C1	   C1
---------- ----------
	 1	    2
	 2	    2

CROSS JOIN(์ƒํ˜ธ ์กฐ์ธ)
ํ•œ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰๊ณผ ๋‹ค๋ฅธ ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰์„ ์กฐ์ธ์‹œํ‚จ๋‹ค.
์ƒํ˜ธ ์กฐ์ธ ๊ฒฐ๊ณผ์˜ ์ „์ฒด ํ–‰ ๊ฐœ์ˆ˜๋Š” ๋‘ ํ…Œ์ด๋ธ”์˜ ๊ฐ ํ–‰์˜ ๊ฐœ์ˆ˜๋ฅผ ๊ณฑํ•œ ๊ฐ’์ด ๋˜๋ฉฐ ์นดํ…Œ์‹œ์•ˆ ๊ณฑ(CARTESIAN PRODUCT)๋ผ๊ณ  ํ•จ.


4. ์ •๋‹ต :  C

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

 

ํ•ด์„ค: C1์ด ๊ฐ™์€ ๋ ˆ๋ฒจ์—์„œ DESC๋กœ ์ •๋ ฌ๋จ


5. ์ •๋‹ต :  KING

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

 

ํ•ด์„ค: C1 = PRIOR C2 ์—ญ๋ฐฉํ–ฅ


6. ์ •๋‹ต : DCL (๋ฐ์ดํ„ฐ ์ œ์–ด์–ด)

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

 

๋ฐ์ดํ„ฐ ์ œ์–ด์–ด (DCL : Data Control Language) 

- ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ ‘๊ทผํ•˜๊ณ  ๊ฐ์ฒด๋“ค์„ ์‚ฌ์šฉํ•˜๋„๋ก ๊ถŒํ•œ(GRANT)์„ ์ฃผ๊ณ  ํšŒ์ˆ˜(REVOKE)ํ•˜๋Š” ๋ช…๋ น์–ด


7. ์ •๋‹ต :  3, 2

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

 

ํ•ด์„ค: NTILE(3)์€ ๋ฐ์ดํ„ฐ๋ฅผ 3๋“ฑ๋ถ„ ํ•˜๊ณ , ๊ฐ ๋“ฑ๋ถ„์— ๋Œ€ํ•ด์„œ COUNT๋ฅผ ๊ณ„์‚ฐํ•˜๋ฏ€๋กœ 3, 3, 2๊ฐ€ ๋œ๋‹ค.

[RESULT]
NTILE_3   ROWCNT
----------------
  1          3
  2          3
  3          2

8. ์ •๋‹ต :  RANGE

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

 

ํ•ด์„ค: +0 ~ +50 ์‚ฌ์ด์˜ ๊ฐ’์ด COUNT ๊ฒฐ๊ณผ๋กœ ๋‚˜ํƒ€๋‚จ

SQL> SELECT V1, N1, COUNT(N1) OVER (ORDER BY N1 RANGE BETWEEN 0 PRECEDING AND 50 FOLLOWING) AS CNT FROM SQLD45;

V1    N1    CNT
-----------------
E     50     2
A     100    2
B     150    1
D     450    1
C    1400    1

9. ์ •๋‹ต :  3

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

 

ํ•ด์„ค: 200 ์ด์ƒ์ด 3๊ฐœ ์ด๋ฏ€๋กœ 3 


10. ์ •๋‹ต :  3

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

 

ํ•ด์„ค: ๋‘๋ฒˆ์งธ ์ž๋ฆฌ์˜ ๋ฌธ์ž๊ฐ€ L์ธ ๋ชจ๋“  ํ–‰์„ ์ถœ๋ ฅ

SQL> SELECT COUNT(*) FROM SQLD45 WHERE V1 LIKE '_L%';

  COUNT(*)
----------
	 3

11. ์ •๋‹ต :  ๋„๋ฉ”์ธ

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

 


12. ์ •๋‹ต :  ์œ ์ผ์„ฑ

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

 

ํ•ด์„ค:

์œ ์ผ์„ฑ - ์ฃผ์‹๋ณ„์ž๋Š” ๋ชจ๋“  ์ธ์Šคํ„ด์Šค๋ฅผ ์œ ์ผํ•˜๊ฒŒ ๊ตฌ๋ถ„๊ฐ€๋Šฅ ํ•ด์•ผํ•œ๋‹ค.
์ตœ์†Œ์„ฑ - ์ฃผ์‹๋ณ„์ž๋ฅผ ๊ตฌ์„ฑํ•˜๋Š” ์†์„ฑ ์ˆ˜๋Š” 1๊ฐœ์ด์–ด์•ผ ํ•œ๋‹ค.
๋ถˆ๋ณ€์„ฑ - ์ฃผ์‹๋ณ„์ž๋Š” ์ž์ฃผ ๋ณ€๊ฒฝ๋˜์ง€ ์•Š์•„์•ผ ํ•œ๋‹ค.
์กด์žฌ์„ฑ - ์ฃผ์‹๋ณ„์ž๋Š” ํ•ญ์ƒ ๋ฐ์ดํ„ฐ ๊ฐ’์ด ์žˆ์–ด์•ผ ํ•œ๋‹ค.
๋Œ€ํ‘œ์„ฑ - ์ฃผ์‹๋ณ„์ž๋Š” ์—”ํ„ฐํ‹ฐ๋ฅผ ๋Œ€ํ‘œ ํ•  ์ˆ˜ ์žˆ์–ด์•ผ ํ•œ๋‹ค.


13. ์ •๋‹ต :  2

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

 

SQL> SELECT AVG(COL1), AVG(NVL(COL1, 0)) FROM TAB1;

 AVG(COL1) AVG(NVL(COL1,0))
---------- ----------------
	 6		  4


SQL> SELECT AVG(COL1) - AVG(NVL(COL1, 0)) FROM TAB1;

AVG(COL1)-AVG(NVL(COL1,0))
--------------------------
			 2

14-1. ์ •๋‹ต :  RIGHT OUTER JOIN / RIGHT JOIN

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

 

ํ•ด์„ค: ์‚ฌ์›์ด ์˜ค๋ฅธ์ชฝ์— ์œ„์น˜ํ•ด ์žˆ์œผ๋ฏ€๋กœ RIGHT OUTER JOIN (OUTER ์ƒ๋žต ๊ฐ€๋Šฅ)

14-2. ์ •๋‹ต :  LEFT OUTER JOIN / LEFT JOIN

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

 

14-3. ์ •๋‹ต :  RIGHT OUTER JOIN / RIGHT JOIN

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

 


15. ์ •๋‹ต :  GROUPING SETS(B.GRADE, (B.GRADE, A.JOB))

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

 

ํ•ด์„ค:

SELECT B.GRADE, A.JOB, SUM(B.GRADE) FROM TAB1 A, TAB2 B GROUP BY GROUPING SETS(B.GRADE, (B.GRADE, A.JOB));

     GRADE	  JOB SUM(B.GRADE)
---------- ---------- ------------
	50	   10		50
	50	   20		50
	50	   30		50
	50		       150
	60	   10		60
	60	   20		60
	60	   30		60
	60		       180
	70	   10		70
	70	   20		70
	70	   30		70
	70		       210

12 rows selected.

16. ์ •๋‹ต :  ์ œ2์ •๊ทœํ˜•

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

 


17. ์ •๋‹ต :  ์™ธ๋ถ€์Šคํ‚ค๋งˆ

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

 

- ์™ธ๋ถ€ ์Šคํ‚ค๋งˆ(External Schema) - ์„œ๋ธŒ ์Šคํ‚ค๋งˆ, ์‚ฌ์šฉ์ž ๋ทฐ

1. ์™ธ๋ถ€ ์Šคํ‚ค๋งˆ๋Š” ์‚ฌ์šฉ์ž๋‚˜ ์‘์šฉ ํ”„๋กœ๊ทธ๋ž˜๋จธ๊ฐ€ ๊ฐœ์ธ์˜ ์ž…์žฅ์—์„œ ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋…ผ๋ฆฌ์  ๊ตฌ์กฐ๋ฅผ ์ •์˜ํ•œ๋‹ค.
2. ์™ธ๋ถ€ ์Šคํ‚ค๋งˆ๋Š” ์ „์ฒด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ํ•œ ๋…ผ๋ฆฌ์ ์ธ ๋ถ€๋ถ„์œผ๋กœ ๋ณผ ์ˆ˜ ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ์„œ๋ธŒ ์Šคํ‚ค๋งˆ๋ผ๊ณ ๋„ ํ•œ๋‹ค.
3. ํ•˜๋‚˜์˜ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‹œ์Šคํ…œ์—๋Š” ์—ฌ๋Ÿฌ ๊ฐœ์˜ ์™ธ๋ถ€ ์Šคํ‚ค๋งˆ๊ฐ€ ์กด์žฌํ•  ์ˆ˜ ์žˆ๋‹ค.
4. ํ•˜๋‚˜์˜ ์™ธ๋ถ€ ์Šคํ‚ค๋งˆ๋ฅผ ์—ฌ๋Ÿฌ๊ฐœ์˜ ์‘์šฉ ํ”„๋กœ๊ทธ๋žจ ํ˜น์€ ์‚ฌ์šฉ์ž๊ฐ€ ๊ณต์œ ํ•  ์ˆ˜ ์žˆ๋‹ค.
5. ์ผ๋ฐ˜ ์‚ฌ์šฉ์ž๋Š” SQL๊ณผ ๊ฐ™์€ ์งˆ์˜์–ด๋ฅผ ์ด์šฉํ•˜์—ฌ DB๋ฅผ ์‰ฝ๊ฒŒ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.
6. ์‘์šฉ ํ”„๋กœ๊ทธ๋ž˜๋จธ๋Š” C๋‚˜ JAVA ๋“ฑ์˜ ์–ธ์–ด๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ DB์— ์ ‘๊ทผํ•œ๋‹ค.

- ๊ฐœ๋… ์Šคํ‚ค๋งˆ(Conceptual Schema) - ์ „์ฒด์ ์ธ ๋ทฐ

1. ๊ฐœ๋… ์Šคํ‚ค๋งˆ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์ „์ฒด์ ์ธ ๋…ผ๋ฆฌ์  ๊ตฌ์กฐ๋กœ, ๋ชจ๋“  ์‘์šฉ ํ”„๋กœ๊ทธ๋žจ์ด๋‚˜ ์‚ฌ์šฉ์ž๋“ค์ด ํ•„์š”๋กœ ํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์ข…ํ•ฉํ•œ ์กฐ์ง ์ „์ฒด์˜ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ ํ•˜๋‚˜๋งŒ ์กด์žฌํ•œ๋‹ค.
2. ๊ฐœ๋… ์Šคํ‚ค๋งˆ๋Š” ๊ฐœ์ฒด ๊ฐ„์˜ ๊ด€๊ณ„(Relationship)์™€ ์ œ์•ฝ ์กฐ๊ฑด์„ ๋‚˜ํƒ€๋‚ด๊ณ  ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์ ‘๊ทผ ๊ถŒํ•œ, ๋ณด์•ˆ ๋ฐ ๋ฌด๊ฒฐ์„ฑ ๊ทœ์น™์— ๊ด€ํ•œ ๋ช…์„ธ๋ฅผ ์ •์˜ํ•œ๋‹ค.
3. ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ํŒŒ์ผ์— ์ €์žฅ๋˜๋Š” ๋ฐ์ดํ„ฐ์˜ ํ˜•ํƒœ๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” ๊ฒƒ์œผ๋กœ, ๋‹จ์ˆœํžˆ ์Šคํ‚ค๋งˆ๋ผ๊ณ  ํ•˜๋ฉด ๊ฐœ๋… ์Šคํ‚ค๋งˆ๋ฅผ ์˜๋ฏธํ•œ๋‹ค.
4. ๊ธฐ๊ด€์ด๋‚˜ ์กฐ์ง์ฒด์˜ ๊ด€์ ์—์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ •์˜ํ•œ ๊ฒƒ์ด๋‹ค.
5. DBA์— ์˜ํ•ด์„œ ๊ตฌ์„ฑ๋œ๋‹ค.

- ๋‚ด๋ถ€ ์Šคํ‚ค๋งˆ(Internal Schema) - ์‹œ์Šคํ…œ ์„ค๊ณ„์ž ๋ทฐ

1. ๋‚ด๋ถ€ ์Šคํ‚ค๋งˆ๋Š” ๋ฌผ๋ฆฌ์ ์ธ ์ €์žฅ์žฅ์น˜ ์ž…์žฅ์—์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ์ €์žฅ๋˜๋Š” ๋ฐฉ๋ฒ•์„ ๊ธฐ์ˆ ํ•œ ๊ฒƒ์ด๋‹ค.
2. ๋‚ด๋ถ€ ์Šคํ‚ค๋งˆ๋Š” ์‹ค์ œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ €์žฅ๋  ๋ ˆ์ฝ”๋“œ์˜ ๋ฌผ๋ฆฌ์ ์ธ ๊ตฌ์กฐ๋ฅผ ์ •์˜ํ•œ๋‹ค. 
3. ๋‚ด๋ถ€ ์Šคํ‚ค๋งˆ๋Š” ์ €์žฅ ๋ฐ์ดํ„ฐ ํ•ญ๋ชฉ์˜ ํ‘œํ˜„๋ฐฉ๋ฒ•, ๋‚ด๋ถ€ ๋ ˆ์ฝ”๋“œ์˜ ๋ฌผ๋ฆฌ์  ์ˆœ์„œ, ์ธ๋ฑ์Šค ์œ /๋ฌด ๋“ฑ์„ ๋‚˜ํƒ€๋‚ธ๋‹ค.
4. ์‹œ์Šคํ…œ ํ”„๋กœ๊ทธ๋ž˜๋จธ๋‚˜ ์‹œ์Šคํ…œ ์„ค๊ณ„์ž๊ฐ€ ๊ด€๋ฆฌํ•œ๋‹ค.

18. ์ •๋‹ต :  CASCADE

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

 


19. ์ •๋‹ต :  GROUP BY

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

 


20. ์ •๋‹ต :  5, 4

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

 


21. ์ •๋‹ต :  4

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

 

 

ํ•ด์„ค:

<๋‹ต> 2 null ์€ ์ œ์™ธ๋œ ์ƒํƒœ์—์„œ ์ˆ˜ํ–‰๋˜๋ฉฐ, ๊ทธ ๊ฒฐ๊ณผ์—์„œ 4 ์ธ row ๋ฅผ ์ œ์™ธ
์œ„์˜ ์ƒ˜ํ”Œ ์˜ˆ์ œ๋กœ 1 ์—์„œ ์‹œ์ž‘๋œ ๊ฒฐ๊ณผ๋Š” 1,3,4,7,8 / 5๊ฐœ์˜ rows ์—์„œ COL1<>4๋ฅผ ์ œ์™ธํ•œ 4๊ฐœ

22. ์ •๋‹ต :  60, NULL, 10, 50

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

 

 

 

 


23. ์ •๋‹ต :  1

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

 

SQL> SELECT ID FROM TAB1 ORDER BY (CASE WHEN ID IN(1, 3) THEN ID*3 ELSE ID END) DESC;

  ID
------
 3
 4
 1
 2

24. ์ •๋‹ต :  3

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

 


25. ์ •๋‹ต :  LAG

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

 

ํ•ด์„ค: LAG ํ•จ์ˆ˜๋Š” ์ด์ „ ํ–‰์˜ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜๋กœ LAG(SALARY, 2) => ์ด์ „ ๋‘ ๋ฒˆ์งธ ํ–‰๊ฐ’์„ ์ถœ๋ ฅํ•œ๋‹ค.

 

LAG    : ์ด์ „ ํ–‰์˜ ๊ฐ’์„ ๋ฆฌํ„ด

LEAD  : ๋‹ค์Œ ํ–‰์˜ ๊ฐ’์„ ๋ฆฌํ„ด

 


26. ์ •๋‹ต :  GRANT, ON, TO

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

 

 

 

ํ•ด์„ค:

GRANT : ๊ถŒํ•œ๋ถ€์—ฌ ( GRANT [๊ถŒํ•œ] ON [ํ…Œ์ด๋ธ”] TO [์œ ์ €] )

REVOKE : ๊ถŒํ•œํšŒ์ˆ˜ (REVOKE [๊ถŒํ•œ] ON [ํ…Œ์ด๋ธ”] FROM [์œ ์ €] )


27. ์ •๋‹ต :  ๊ด€๊ณ„์ฐจ์ˆ˜ (Cardinality)

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

 


28. ์ •๋‹ต :  VIEW (๋ทฐ)

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

 


29. ์ •๋‹ต :  3

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

 

 

ํ•ด์„ค: ID, COUNT(*) ๊ฒฐ๊ณผ๋ฅผ HAVING์กฐ๊ฑด์ ˆ ์ฒดํฌํ•œ ์ตœ์ข…๊ฒฐ๊ณผ๋Š” '3'

SQL> SELECT COUNT(*) FROM TAB1 GROUP BY ID HAVING COUNT(*) > 2;

  COUNT(*)
----------
	 3

30-1. ์ •๋‹ต :  IS NULL

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

 

30-2. ์ •๋‹ต :  IS NULL

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

 


31. ์ •๋‹ต :  ROLLUP

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

 


32. ์ •๋‹ต :  1, 3

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

 

 


*ํ•ด๋‹น ํฌ์ŠคํŒ…์€ ๋ฌธ์ œ์œ ํ˜•์„ ์ฐธ๊ณ ํ•˜์—ฌ ๋น„์Šทํ•œ ๋ฌธ์ œ๋“ค๋กœ ์žฌ๊ตฌ์„ฑ ํ•˜์˜€์œผ๋‹ˆ ์ฐธ๊ณ ์šฉ์œผ๋กœ ๋ด์ฃผ์„ธ์š” :)

์˜คํƒ€, SQL์—๋Ÿฌ ๋Œ“๊ธ€ ๋‚จ๊ฒจ์ฃผ์‹œ๋ฉด ๊ฐ์‚ฌํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ๐Ÿ™๐Ÿป

300x250

์ฝ”๋“œ