[SQLD/SQLP] 전문가가이드 2과목 SQL기본 및 활용 암기 요약 정리

2과목 SQL기본 및 활용 암기 요약 정리

SQLD/SQLP를 준비하면서 객관식/주관식으로 나올 수 있는것들로

꼭 암기 할 필요가 있는것들 정리했습니다.

2과목에 나오는 함수는 의미만 알아서는 안되고

어떻게 사용하는지 어떤 경우에 사용하는지 모두 알아야합니다.

개발하면서도 자주 문제가 되는

0, null, '' 처리 관련한 문제는 항상 나오고있습니다.

2과목이 실제 업무에서는 가장 많은 도움이 되었구요.

도움되셨으면 좋겠습니다!

---

- SQL 문장들의 종류

- 데이터 유형

- 문법

Count(null) = 0

Count(1) = 1

- 시간

1/24/60 = 1분

- 문자열 유형 비교

CHAR유형 : 'AA' = 'AA    '

VARCHAR유형 : 'AA' != 'AA    '

- CTAS(Create Table ~ As Select ~)

Not Null제약조건만 복제테이블에 적용되고 나머지 제약조건은 없어짐

- Alter table 종류

가. ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

나. ALTER TABLE PLAYER DROP COLUMN ADDRESS;

다. ALTER TABLE PLAYER MODIFY(ORIG_YYYY VARCHAR2(8) DEFAULT '20170101' NOT NULL);

라. ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;

마. ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

바. ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);

-합성연산자

concat(A,B), '||'

- SavePoint

SAVEPOINT aaa1;

ROLLBACK TO aaa1;

- DDL문장

DDL문장을 실행하면 전후시점에 

자동 Commit실행

- 연산자 우선순위

1. 괄호()

2. NOT 연산자

3. 비교연산자, SQL비교연산자

4. AND

5. OR

- NULL관련 함수

NULLIF(표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다

COALESCE(표현식1, 표현식2, ..) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다 모든

     표현식이 NULL이라면 NULL을 리턴

- Top(N)쿼리

같은 등급의 차수가 있을 때 WITH TIES 쓰면 다 출력

- Order By

ORDER BY절에는 SELECT목록에 나타나지 않은 문자형 항목이 포함될 수 있다.

단, SELECT DISTINCT, GROUP BY, UNION연산자가 있으면 열 정의가 SELECT 목록에 표시되어야한다.

-> 자동정렬이 일어나는 distinct,group by, union은 select목록에 표시가 되어야함..!

-참조무결성 규정

Delete 관련

1. Cascade : Master 삭제 시 child같이 삭제

2. Set Null : Master 삭제 시 child 해당 필드 Null

3. Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정

4. Restrict : Child테이블에 PK값이 없는 경우만 Master 삭제 허용

5. No Action : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

Inert관련

1. Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력

2. Set Null : Master 테이블에 PK가 없는 경우 Child외부키를 Null값으로 처리

3. Set Default : Master테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력

4. Dependent : Master테이블에 PK가 존재할 때만 Child 입력 허용

5. No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음

- 계층형 질의에서 사용되는 가상컬럼

LEVEL : 루트데이터면 1, 그 하위데이터이면 2이다. 리프데이터까지 1씩 증가

CONNECT_BY_ISLEAF : 전개과장에서 해당데이터가 리프 데이터이면 1, 그렇지 않으면 0

CONNECT_BY_ISCYCLE : 조상으로서 존재하면 1 그렇지 않으면 0

SYS_CONNECT_BY_PATH(컬럼,경로분리자) : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다

CONNECT_BY_ROOT(컬럼) : 현재 전개할 데이터의 루트 데이터를 표시한다.

START WITH : 계층구조 전개의 시작위치 (루트데이터 지정)

CONNECT BY  : 다음 전개될 자식데이터 지정

PRIOR : CONNECT BY절에 사용되며 PRIOR 자식 = 부모 순방향 / PRIOR 부모 = 자식 역방향

NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 전개가 나타나면 런타임오류로 옵션처리

ORDER SIBLINGS BY : 형제 노드사이에서 정렬 수행

- 윈도우함수 window절

(윈도우함수안에 order by가 들어가면 디폴트로 "Rows unbounded preceding (and current row)" 들어가있음 곧 현재행까지)

Rows unbounded preceding (and current row) : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위 지정

Rows Between 1 preceding and 1 following : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재행 뒤의 한건 범위지정

Rows Between current row and unbounded following : 현재 행을 기준으로 파티션 내의 마지막 행까지의 범위를 지정

- 그룹내 비율함수

RATIO_TO_REPORT : 0.29 / 0.22 / 0.22 / 0.27

PERCENT_RANK : 0 / 0.5 / 1

CUME_DIST : 0.3333 / 0.6667 / 1.0000

NTILE : 1/1/1/1/2/2/2/2/3/3/3/3/4/4/4/4 

- 표준편차/분산

표준편차 : STDDEV

표준분산 : VARIAN 분산이 더 큼

- 로우체이닝/마이그레이션

로우체이닝 : 로우길이가 너무 길어 두개 이상블록에 걸쳐 있는 상태..느려짐!

로우마이그레이션 : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록 빈공간에 저장

- JOIN관련

Inner : *으로 select시 조인에 사용된 같은이름은 별개의 컬럼으로 중간이후부터 붙어서 각각나옴

Natural : Natural을 쓰면 On/Using/Alias사용 못함 - *으로 검색 시 조인되는컬럼 맨앞에 합쳐서 나옴

Using : Using한건 접두사 Alias 사용못함 그리고 맨앞에 합쳐서 나옴

-다중행비교연산자(SOME = ANY)

ANY - 뒤에나오는 값리스트와 비교해서 하나라도(or의성격) 만족하면 true (

ex) where salary > ANY ( select ... (9000,6000,4200) 리턴

                                        from ..)

< ANY : 최대값보다 작으면 된다

> ANY : 최소값보다 크면 된다 

= ANY : IN

ALL - 뒤에나오는 값리스트와 비교해서 모두(and의성격) 만족하면 true

ex) where salary > ALL( select ... (9000,6000,4200) 리턴

                                        from ..)

< ALL : 최소값보다 작으면 된다 

> ALL : 최대값보다 크면 된다 

= ALL : NOT IN

CUME_DIST()

PERCENT_RANK()

RATIO_TO_REPORT(SAL)

NTILE(4)

-계층형 구조

where절까지 나오고 나서

계층구조 쿼리 시작해야함 아래는 오류

-참조

참조라 함은 FK 생성시 Reference 하는 대상을 말하는 거임...

FK 설정시 참조하는 대상은 PK 만 된다는 오답. (UK 도 가능함)

-SELECT문 ALIAS사용시

SELECT JOB_NO AS "AAA", BL_NO "BBB"  (O)

SELECT JOB_NO AS "AAA", BL_NO 'BBB'  (X)

홑따옴표는 사용불가능

그리고 " " 묶어서 사용했을 시 ORDER BY절에도 똑같이 명시해야함

ex)

SELECT JOB_NO "a", BL_NO "B" FROM KR0001.C2_BL_TB

ORDER BY "a"

서브쿼리에서 ORDER BY 사용

SELETE 절의 서브쿼리 : 사용 불가

FROM 절의 서브쿼리 : 사용 가능(오라클만 사용가능)(MS-SQL 은 사용 불가)

WHERE 절의 서브쿼리 : 사용 불가

그룹함수 집합개념정리

http://scidb.tistory.com/search/group%20by%20extension

윈도우함수 확인할문제

http://www.dbguide.net/da.db?cmd=snb13_view&boardGroupUid=6&boardConfigUid=81&boardUid=152219

계층형질의 구문

- selec level lvl, a.*

   from emp a

   whee sal > 2000

   start with mgr is null

   connect by prior empno = mgr; 

 ->맞는문장

- selec level lvl, a.*

   from emp a

   start with mgr is null

   connect by prior empno = mgr

   where sal > 2000  

 -> 틀린문장

순수관계연산자

- Select연산은 Where절

- Project연산은 Select절

- (Natural) Join연산은 다양한 Join기능으로

- Divide 연산은 사용 X

Natural Join

- Alias나 테이블명과 같읕 접두사 붙일 수 없다.

-  '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 Natural Join의 기준이 되는 칼럼들이 다른 컬럼보다 먼저 출력된다. 또 Natural Join은 Join에 사용된 같은 이름의 칼럼을 하나로 처리한다

- Inner Join은 Alias나 테이블명과 같은 접두사 붙일 수 있다

- Inner Join의 경우 첫번째 테이블, 두 번째 테이블 칼럼 순서대로 데이터가 출력되고 별개의 칼럼으로 각각 표시한다

Using 조건절

- Natural Join에서는 모든 일치되는 칼럼들에 대해 Join이 이루어지지만, From절의 Using조건절을 이용하면

같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 Equi Join을 할 수 있다(Oracle만)

- Using조건절에 묶은 칼럼이 제일 먼저 하나로 출력된다

- Natural 조인과 마찬가지로 Alias나 테이블 이름과 같은 접두사 붙일 수 없다.

Corss Join

- 일반 집합 연산자의 Product의 개념으로 테이블 간 Join조건이 없는 경우 생길 수 있는 모든 데이터의 집합

Full Outer Join

- Left Join + Right Join 의 Union(중복제거)

Roll Up

- N+1 Level의 Subtotal생성

SELECT A,B,SUM(C)

FROM T

GROUP BY ROLLUP(A,B)

->

SELECT A,B,SUM(C)

FROM T

GROUP BY A,B

UNION ALL

SELECT A,NULL,SUM(C)

FROM T

GROUP BY A,

UNION ALL

SELECT NULL,NULL,SUM(C)

FROM T

GROUP BY NULL (생략가능)

Cube

- 2의 N승 Level의 Subtotal생성

SELECT A,B,SUM(C)

FROM T

GROUP BY CUBE(A,B)

->

SELECT A,B,SUM(C)

FROM T

GROUP BY A,B

UNION ALL

SELECT A,NULL,SUM(C)

FROM T

GROUP BY A

UNION ALL

SELECT NULL, B, SUM(C)

FROM T

GROUP BY B

UNION ALL

SELECT NULL,NULL,SUM(C)

FROM T

GROUP BY NULL (생략가능)

Grouping Sets

- Group by절의 컬럼수 만큼 집합이 생성된다.

SELECT A,B,SUM(C)

FROM T

GROUP BY GROUPING SETS(A,B)

->

SELECT A,NULL,SUM(C)

FROM T

GROUP BY A

UNION ALL

SELECT NULL, B, SUM(C)

FROM T

GROUP BY B

두개가 같음

SELECT DECODE(GROUPING(DNAME),1,'All Departments',DNAME)  AS DNAME,

             DECODE(GROUPING(JOB),1, 'All Jobs',JOB)  AS JOB,

             COUNT(*) "Total Empl", SUM(SAL) "Total Sal"

FROM   SCOTT.EMP, SCOTT.DEPT

WHERE  DEPT.DEPTNO = EMP.DEPTNO

GROUP  BY DNAME, ROLLUP(JOB) ;

SELECT DECODE(GROUPING(DNAME),1,'All Departments',DNAME)  AS DNAME,

             DECODE(GROUPING(JOB),1, 'All Jobs',JOB)  AS JOB,

             COUNT(*) "Total Empl", SUM(SAL) "Total Sal"

FROM   SCOTT.EMP, SCOTT.DEPT

WHERE  DEPT.DEPTNO = EMP.DEPTNO

GROUP  BY GROUPING SETS((DNAME, JOB), DNAME);

순수관계 연산자

- select, divide, project, join

Modify column

컬럼의 크기를 늘릴 수는 있지만 줄일 수는 없다.

해당 컬럼이 NULL 값만 가지고 있거나 아무 행도 없으면 컬럼을 줄일 수 있다.

해당 컬럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다.

해당 컬럼의 DEFUALT 값을 바꾸면 변경 작업 이후 발생하는 행에만 적용된다.

해당 컬럼에 NULL 값이 없을 때에만 NOT NULL 제약조건을 추가할 수 있다.

윈도우 함수 공부

RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

디폴트로 현재행까지가 들어가있는것..아니고

order by가 들어가면

 ORDER BY col2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

뭐가됐던간에 위 구문이 자동으로 붙는것..!

http://cafe.naver.com/sqlpd/6855 확인

Equi Join

Equi Join은 반드시 PK,FK관계에 의해서만 성립되는 조인조건이 아니다

1:1맵핑되면 다 가능

순수관계연산자

Select/Project/Join/Divide

뷰

뷰는 단지 정의만을 가지고 있고 실행시점에 질의를 재작성하여 실행한다

실제 데이터를 저장하고 있는 뷰도 있다