sqld 개인공부 정리 part2

1. 문제 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY 
2. DML - SELECT, INSERT, DELETE, UPDATE
3. DDL - ALTER, CREATE, MODIFY, DROP
4. TCL - ROLLBACK, COMMIT
5. DCL - GRANT, REVOKE

 

DISTINCT(집약) → 중복된 값들이 존재할때 중복을 제거하고 원하는 정보를 집약함

 

DISTINCT deptno, mgr는 group by(deptno, mgr) 와 비슷하다.

 

select as →  생략가능 && 컬럼명에 띄어쓰기, “직원 번호”

from  → as 사용불가

 

concat 연산자 → +일경우 sql server

→ ||일경우 oracle

 

concat은 인수가 무조건 2개임

 

• 논리연산자

 

and → A and B

or → A or B

not → A와 B 둘다 아니다

 

• 연산순위 

1. NOT → NOT <조건>
2. AND → AND <조건>
3. OR → NOT <조건>

 

• SQL 연산자

A Between 1 and 2 → 1 <= A <= 2

A IN (1,2,3) → A = 1 or A = 2 or A = 3

 

• LIKE(중요)

_ : 미지의 한 글자

% : 0 이상의 글자

→ sc% → sc로 시작하는 모든 단어

→ %sc% → sc가 포함된 모든단어

 

LIKE escape 와일드카드(_ %)를 문자로 취급 ename like ‘A_A’ ⇒ A@_A ‘@’

 escape ‘@’ → @대신 아무문자가능

→ %나 _를 찾고 싶을때 escape로 문자로 취급하도록 변경한 후 찾음

 

• rownum(oracle)

where rownum = 1 포함

 

• TOP (sql server)
• (select) TOP(n) (컬럼명) → n은 상위 n개 라는 뜻

 

select empno, sal from emp where rownum <= 3 order by sal

→ where 절로 3개의 데이터만 추출한 후에 order by 실행

→ order by는 가장마지막에 실행 됨

→ asc 와 desc 차이 구분

 

null의 정의

1. 부재 모르는 값, 산술연산을 하면 null이 나옴
2. null + 2 = null
3. null - 4 = null
4. null * null = null
5. 비교연산을 수행할경우
6. null = 2 → 알수없음
7. null = null → 알수없음
8. where (조건) → unknown → false

 

• null을 정렬할때 쓸경우

oracle : 무한대로 취급

sql server : -무한대(마이너스 무한대로 취급)

 

nvl → 널뛰기(값1, 값2) → 값1이 null이라면 값 2, null이 아니라면 값1

nvl2 → 널뛰기(값1, 값2, 값3) → 값1이 null이라면 값3, null이 아니라면 값2

isnull → 널뛰기(값1, 값2) → nvl과 동일

null if → 같이 놀자(값1, 값2) → 2개의 값이 같다면 null, 다르다면 값1

coalesce → 널아닌 첫번째값(값1, 값2, ….) → 앞순서의 null을 제외한 첫번째 값을 찾음

 

• 정렬의 특성

1. 가장 마지막에 실행
2. 성능이 느려질 수 있다.
3. NULL 값과의 관계

 

• 컬럼번호 정렬(예제 찾아보기)
• 출력되는 컬럼의 수보다 큰값 불러오기

 

• 인수 두개(찾아보기)
• sal desc, ename asc → sal이 같으면 ename 오름차순

 

• select ename order by sal → order by의 column이 select절에 없어도 sal로 정렬가능

 

• 숫자함수

Round(138 94)인수!

Ceil(oracle) / ceilng(sql server)

 

• 문자열 함수(실습)

upper

lower

LPad

Rpad

LTrim

RTrim

substr

instr

 

• 날짜 함수(실습) → 데이터의 형변환을 일으키는 함수

TO_char

TO_date

sysdate (oracle)

Getdate() (sql server)

 

날짜데이터+100 → 100일 이후(day로 인식)

 

• DECODE / CASE(실습)

case

when then 1

when then 2

else → else가 없을때 :  1,2번 둘 다 만족하지 않을 경우 → null 

end

 

• 집계함수 → null과의 관계

 

• null과 숫자를 더하면 → null값이 나옴
• null + 3 = null

 

• 하나의 column에 사용한 집계함수는 null값을 건너뛰고 연산한다.
• BUT 집계함수 하나에 여러개의 컬럼을 연산할 경우 null이 존재하면 null값 출력

 

sum(A) + sum(B) + sum(C) == null값을 제외한 숫자를 더한값 출력 

vs 

sum(A+B+C) == null값이 존재한다면 null 출력

 

• GROUP BY(집약기능)
• where다음에 실행
• 그룹의 정보를 바꾼다

 

• JOIN

natural join && using 

→ alias 사용x

→ 중복된 컬럼 하나로 출력

→  제일 앞에 등장

 left outer join

A left outer join B

→ A col1 = B col1(+)

→ 선행 테이블과 후행테이블이 일치해야 위의 식 구현

 

FROM A,B,C → A와 B가 먼저 join한 뒤 → join된 테이블과 C가 다시 join

 

• 서브쿼리(실습)

select → 스칼라(scalar) 서브쿼리

from → inline view → 메인쿼리의 컬럼 사용 가능

where → 거의 모든 서브쿼리(중첩서브쿼리)

group by → X

having → 거의 모든 서브쿼리(중첩서브쿼리)

order by → scalar

 

in

any / some

all

exist 

→ ‘|’, ‘x’, ‘a’, → (true)

→ o rows → (false)

 

• 집합연산자 

union → 정렬작업o, 느리다, 합집합, 중복배제

intersect → 정렬작업o, 느리다, 교집합

minus(except) → 정렬작업o, 느리다, A-B = B와 중복데이터 제외한 A값 

union all → 중복데이터o, 정렬작업x, 빠르다, 합집합, 중복허용

 

• Truncate(구조남음) vs DROP(구조도 삭제)
• Truncate(DDL) vs delete(DML)

→ rollback과 commit이랑 연관되어서 나옴

 

• DML(실습)

insert, update, delete → TCL(commit, rollback)과 연관되어서 나옴

marge → 실습

 

제약조건(중요) 

→ pk(unique + not null), 

→ unique

→ not null

 

• DCL

정의 많이 물어봄, 문법보기

GRANT

REVOKE

role 특징(명령어x, object의 하나임) → with 어쩌구~

 

• VIEW(중요) → 독편보 → sql 명령문을 저장

독립성

편리성

보안성

 

• 그룹함수(중요) → 실습하깅

ex : 결과값 주고 뭘 적어야 하는지 나옴

roll up

cube

groupingsets

grouping

 

ex) : rollup(A,B)와 rollup(B,A)는 같은 결과x

ex) : cube(A,B) 와 cube(B,A)는 같은 결과

 

null 다 찾기

총합행이 존재하는지 찾기 

→ 있다 → rollup(한쪽 결과, 계층, 행의수가 적음), cube(양쪽 둘다 결과, 행의수가 많음)

→ 없다 → groupingsets

 

• TCL

commit → auto commit off and Begin transaction → DDL commit x

 

rollback

 

• 윈도우 함수(기출문제같은 문제풀기)

rows

range

→ 차이점 확인해보기

→ 같은값 존재하는지 유무 확인

 

• rank 종류 공부

rank → 중복 건너뛴다.

dense.rank → 건너뛰기x

 

partition by

order by

 

• 계층형 질의

1. prior 자식데이터 = 부모데이터(프자부)
2. 부모에서 자식으로 가면 순방향(부자순)

prior은 연산자와 비슷

부모데이터 = prior 자식데이터

• prior empno = mgr(실습)

level 1 king empno

level 2 james mgr

level3 scott

 

• 절차형 PL/SQL

Exception → 생략가능

procedure, trigger, user defined function 차이점

 

• 데이터모델링

공부

• 엔터티

엔터티 도출

특징(암기)

분류(유형,개념,사건) → 유개사기중행

 

• 속성

1. scott 특성

instance

1. 분류 기설파 정의

 

• 도메인

데이터 유형, 크기 제약조건, check, primary key

 

• 관계

IE 표기법 → PK~어쩌구

BARKEA표기법 → # O O O O O

 

식별 / 비식별자 관계, ERD확인

점선 : 비식별자

실선 : 식별자

 

• 식별자

주 식별자 특징(유일성, 최소성, 불변성, 존재성) → 만족시 후보키 → 대표가 기본키 / 대체키

→ 기본키+대체키 = 후보키

 

• 식별자 관계, 비식별자 관계

식별 → 강한 관계

식별 단점 : SQL구문 복잡, PK속성수 증가

 

비식별 → 약한 관계

비식별 단점 : 불필요한 조인 발생(느려짐)

 

ERD에서 그리는 방법도 보기

• ERD 서술 규칙

1. 시선때문에 좌상단에서 우하단으로 움직여야함
2. 관계명 반드시 표기안해도 됨
3. UML은 객체지향에서만 쓰인다.

• 성능 데이터 모델링

아키텍처 모델링 → 테이블과 파티션 분할 등등 ~~

→ 성능이 좋음

 

SQL 명령문 수행 → 조인수행원리, optimizer, 실행계획

• 정규화(중요) → 예시 보고 기출문제 잘 확인

1차 원자성

2차 부분함수종속제거

3차 이행함수종속제거

bcnf 결정함수종속제거

 

삭제이상, 삽입이상과 같은 이상현상 예시 잘 보기

성능 : select절에서는 성능이 떨어질수도(join 때문에), 

insert,update는 테이블이 줄어들기 때문에 빨라짐

 

• 반정규화 → 정의와 어떤것이 있는지 확인

데이터 무결성 해침

→ 대량 범위, 범위처리, 통계처리 

→ 무작정 반정규화를 하는 것이 아닌 응용시스템 변경, 클러스터링 / 인덱스, 뷰를 활용해 대상을 검토 한 후 반정규화 실시

→ 테이블 → 병합(슈퍼, 서브), 분할(부분테이블, 통계테이블, 중복테이블)

→ 속성 → 파생, 오류, 이력컬럼, pk → 속성, 중복속성

→ 관계 → 중복 관계 추가

 

• 대량 데이터에 따른 성능

row migration chaining → list 파티셔닝, range 파티셔닝(가장 많이 쓰임, 관리 쉬움), hash파티셔닝(관리 힘듬) → 특징 공부, 관리방법

 

• 슈퍼 서브타입

→ 트랜잭션 용량 작은경우 → one to one 

→ 트랜잭션 용량 큰 경우 → 트랜잭션 → 공통점과 차이점, 전체 통합(single type, plus type)

 

• 분산데이터베이스

데이터 무결성 해친다

투명성

• 조인 수행 원리(중요)

NL → 랜덤 액세스, 대용량 sort 작업시 유리

Sort merge → 조인키를 기준으로 정렬, 등가, 비등가

Hash → 등가 only, 선행 테이블이 작다, Hash처리 별도 공간 필요

 

• 옵티마이저

CBO : 경로

RBO : 규칙

 

• 인덱스

인덱스가 언제 사용되는지(부정형, LIKE, 묵시적 형변환)

인덱스 사용시 성능이 감소되는 경우(insert, update, delete)

 

• 실행계획

순서(중요)

가장 앞에 있는 들여쓰기가 제일 마지막에 실행됨 들여쓰기가 앞에 있을수록 나중에 실행

(같은 레벨의 순서는 뭉텅이로 처리)

 

PIVOT UNPIVOT MERGE → 정규화

IDENTITY 공부

CHECK 공부

 

Hash Join 은 Non Equal Join 은 불가능함. Equal Join 만 가능함 

사전 Sorting 작업이 필요한 Join 알고리즘은 Sort Merge Join 임

 

------------------------------

1) WHERE COL1 LIKE '2%'

2) WHERE COL1 = 10

3) WHERE COL1 IS NOT NULL

4) WHERE COL1 <> 10

 

1) LIKE 의 경우 컬럼을 무조건 문자로 형변환함. 즉, 해당 조건절은 

    WHERE TO_CHAR(COL1) LIKE '2%' 로 변형되어 인덱스를 사용하지 못함

2) IS NOT NULL 은 해당 인덱스를 FULL SCAN 할 수 있으나 효율이 떨어짐

4) 부정형 비교는 인덱스 사용이 불가함

 

---------------------------------------

CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1,아니면 0

CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0

 여기서 조상이란 자신으로부터 루트까지의 경록에 존재하는 데이터를 말함

SYS_CONNECT_BY_PATH : 하위 레벨의 컬럼까지 모두 표시해줌 (구분자 지정 가능)

CONNECT_BY_ROOT : Root 노드의 정보를 표시

 

UNBOUNDED PRECEDING 공부

 

서브쿼리, 특히 INLINE VIEW 의 컬럼을 메인 쿼리에서도 사용 가능함

 

Oracle 의 결합 함수 : CONCAT / ||

SQL Server : + 

 

VARCHAR 설명

1) 서로 다른 문자가 나올 때까지 비교한다

2) 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다

3) 길이가 같고 다른 것이 없다면 같다고 판단한다

4) 길이가 다르다면 작은 쪽에 SPACE 를 추가하여 길이를 같게 한 후에 비교한다 --> 틀림

SPACE 를 추가하여 길이를 맞춰 비교하는 방법은 CHAR 타입인 경우임

 

정규화 공부

 

그룹 공부, CUBE 공부, 그룹핑 공부

 

조인 공부

 

ANTI SQL 공부

ANSI SQL 에서 조인 조건절 (ON 절) 에 사용된 조건절은 조인 전 조건으로 작용한다

ON 절 이후 WHERE 절에서 쓰인 조건절은 조인후 조건절로 사용된다

 

TRUNC(3.46) = 3, FLOOR(3.46) = 3 , ROUND(3.46)=3

CEIL(3.46) = 4

 

 EXISTS (SELECT 1 공부

NOT IN 공부

NOT EXISTS 공부

 

추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링,

EA 수립 시 많이 이용됨 : 개념적 데이터 모델링

 

엔터티 - 인스턴스 - 속성 - 속성값 공부

 

인덱스 공부 --> 기준으로 조회해야 할 것이 존재할경우 그것을 선두컬럼 인덱스로 지정

 

소문자 / 대문자로 되어 있는 경우 두개의 문자열이 같기 위해서 적용해야 하는 함수 UPPER 를 사용

 

모델링 : 데이터베이스를 구축하기 위한 용도, 업무의 흐름을 설명하고 분석

 

개념적 모델링 : 추상화 수준이 높고 업무 중심적이고 포괄적 수준의 모델링

논리적 모델링 : KEY, 속성, 관계 등을 정확하게 표현, 재사용성 높음

물리적 모델링 : 물리적인 성격을 고려

 

기본엔터티 : 독립적으로 생성가능, 타 엔터티의 부모 역할, 고유한 주식별자

중심엔터티 : 기본엔터티로부터 발생, 중심적 영할, 다른 엔터티와의 관계로 행위엔터티 생성

행위엔터티 : 두개 이상의 부모엔터티로부터 발생되고 자주 내용이 바뀌거나 데이터량이 증가

 

엔터티 : 약어를 사용하면 안되며, 생성되는 의미대로 자연스럽게 부여함

 

속성 : 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

트랜잭션 : 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위

 

기본속성 : 업무로부터 추출한 모든 속성이 해당(원래 가지고 있어야 할 것)

설계속성 : 업무상 필요한 데이터이외의 데이터 모델링을 위해 업무를 규칙화 하기 위해 속성을 새로 만들거나 변형하여 정의

파생속성 : 다른 속성에 영향을 받아 발생하는 속성으로 보통 계산된 값들이 이에 해당함

 

도메인 : 속성이 가질 수 있는 값의 범위, 데이터 타입과 제약사항 지정

 

전체 데이터 모델에서 유일성을 확보하는 것은 반정규화이다.

 

속성 명칭 : 복합명사를 사용하여 구체적으로 명명

 

두 개의 엔터티 사이의 관계 체크

• 두 개의 엔터티 사이에 관심있는 연관규칙이 있는지
• 두 개의 엔터티 사이에 정보의 조합이 발생하였는지
• 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는지
• 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는지

 

주식별자 고려사항

• 주식별자에 의해 엔터티내에 모든 인스턴스들이 구분되어야 한다.
• 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야
• 지정된 주식별자의 값은 자주 변하지 않는 것이어야 한다.
• 주식별자가 지정이 되면 반드시 값이 들어와아 한다.

 

식별자 종류

• 엔터티 내의 대표성에 따라 주 식별자 / 보조 식별자 구분
• 스스로 생성되었는지 여부에 따라 내부 / 외부 식별자 구분
• 단일속성으로 식별이 되는가에 따라 단일 / 복합 식별자 구분
• 원래 업무적으로 의미가 있던 식별자(본질식별자) 
• 새롭게 만든 식별자(인조식별자)

 

명칭, 내역등과 같이 이름으로 기술되는 것들은 주식별자로 지칭하기에는 옮지 않다

→ 주식별자는 명확히 구분지을수 있어야 하며, 중복데이터가 존재할 가능성이 있으면 X

비식별자 : 부모와 자식의 관계가 각각 분리되어 있는 경우 → 거의 남남 같은 느낌

식별자 : 부모가 사라지면 자식도 사라지는 경우, 자식에서 부모의 참조값을 갖는경우

 

성능상 저하된 결과로 데이터모델을 수행해야 하며 문제 발생 시점의 sql을 튜닝하는 것은 성능 데이터 모델링과 무관하다.

 

데이터 모델링을 할 때 정규화를 정확하게 수행한다.

용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.

 

1차 정규화 : 테이블 컬럼이 원자값(하나의 값)을 가지도록 테이블 분해

 

2차 정규화(부분함수종속제거) : 기본키의 부분집합이 결정자가 되어서는 안된다. A와 B는 pk C는 B에서 영향을 받을 경우 → B와 C테이블을 따로 만든다.

 

3차 정규화(이행함수종속제거) : 2차 정규화를 만족하고 A→B, B→C, A→C 관계를 제거

(???)

BCNF : A,B가 PK일 경우 C를 만족할때 C가 B를 만족할때 분리시킴

 

컬럼단위에서 중복이 발생하는 경우도 1차 정규화의 대상이다 → 이 때 관계는 1:M관계이다

 

PK에 대해 반복이 되는 그룹이 존재하지 않으면 1차 정규형 and 부분함수종속의 규칙도 가지고 있음

 

컬럼수가 많아 한 테이블에서 로우체이닝(로우의 길이가 너무 길어서 두 개 이상의 블록에 걸쳐 하나의 로우를 저장

)이 발생할 경우 테이블을 1:1로 분리해야 한다.

 

파티셔닝 : 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 액세스 성능 향상시킴

 

트랜잭션은 하나로 통합되어 있으면 데이터 집적으로 성능 향상 → 항상 전체를 통합하여 분석하므로

 

FK가 있어도 인덱스가 존재하는 것이 좋다.

 

GSI는 한개의 인스턴스 즉 통합 데이터베이스 구조를 의미

 

분산데이터베이스 환경 : 거의 실시간으로 응답 가능, 빠른 응답속도, 마스터 데이터는 복제분산 적용, 지역자치성, 시스템 용량 확장, 효용성과 융통성, 데이터 가용성과 신뢰성 증가

 

CONSTRAINT ??

REFERENCES ??

TRANSACTION??

 

DML : 사용자가 무슨 데이터를 원하는 지만 명세함

 

6번 : TOOO

 

SQL Server는 여러 개의 컬럼을 동시에 수정하는 구문은 지원하지 않는다. 또한 SQL Server에서는 괄호를 사용하지 않는다.

 

학번이 PK 일 경우

→ PK값은 NULL값이 존재할 수 없다. 때문에 COUNT(*)와 COUNT(학번)의 결과는 항상 같다.

 

STADIUM 테이블의 이름을 STADIUM_JSC로 변경하는 SQL

→ RENAME STADIUM TO STADIUM_JSC;

 

22번 TODO : 1, 4? X(???)

 

TRUNCATE와 DROP은 로그를 남기지 않음 → DELETE는 로그를 남김

DISTINCT : 데이터의 중복을 제거하는 명령어

 

DROP(DDL) : ROLLBACK 불가능, 테이블의 정의 자체를 완전히 삭제

TRUNCATE(DDL) : ROLLBACK 불가능, 테이블을 최초 생성된 초기상태로 만듬

DELETE(DML) : COMMIT이전 ROLLBACK 가능, 데이터만 삭제

 

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 상태

isolation : 트랜잭션의 문제점이 아닌 목표 중 하나임

 

② Non-Repeatable Read ： 한 트랜잭션 내에서 같은 쿼리를 두 번 수행

했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에

두 쿼리 결과가 다르게 나타나는 현상을 말한다.

③ Phantom Read： 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데,

첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을

말한다.

 

ROLLBACK은 COMMIT되지 않은 상위의 모든 TRANSACTION을 모두 ROLLBACK한다.

A

A

→ A 줄바꿈 A 이므로 → LENGTH(3) 이다 → 줄바꿈도 LENGTH를 1 올려줌

REPLACE(C1, CHR(10)) → 줄바꿈제거

CHR(10) → ASCII값 줄바꿈을 의미

 

1/24/60 → 1분을 의미한다

1/24/(60/10) → 10분과 같다.

 

SEARCHED_CASE_EXPRESSION

→ CASE WHEN LOC = ‘NEW YORK’ THEN ‘EAST’

 

SIMPLE_CASE_EXPRESSION

→ CASE LOC WHEN ‘NEW YORK’ THEN ‘EAST’

 

ORACLE에서는 ‘’과 NULL모두 NULL로 처리가된다

‘ ‘ 스페이스바는 1로 COUNT됨

 

ISNULL과 NVL은 (식1, 식2) → 식1이 NULL일 경우 식2를 반환한다.

AVG()나 SUM() 같은 그룹함수는 NULL값을 연산에서 제외하고 계산한다.

→ AVG는 NULL을 제외한 ROW를 포함시키고 ROW수 만큼 나눈다(0도 포함)

→ AVG(20 +0) / 2 → 10

 

중첩된 그룹 함수의 경우 최종 결과값은 1건이 될 수 밖에 없기에 GROUP BY에 기술된 메뉴 ID와 사용유형코드는 SELECT에 기술될 수 없다.

→ GROUP BY 명령문 → SUM, AVG, MAX같은 것을 2개 이상 SELECT에서 사용했을 경우

→ GROUP BY의 컬럼을 SELECT절에서 사용하면 에러 발생

→ SELECT 광고ID, MIN(광고매체ID), MIN(광고시작일자) FROM 광고게시 GROUP BY 광고 ID

→ GROUP BY로 광고ID별 최소값을 구해 각 ID별 최소값이 행으로 준비되어 있는데 광고ID를 SELECT에 같이 적을 경우 마치 그 부서의 최소값으로 보이기때문에 에러 발생

→ SELECT MIN(광고매체ID), MIN(광고시작일자) FROM 광고게시 GROUP BY 광고 ID

→ 광고ID를 SELECT절에 넣지 않으면 에러없이 잘 동작함

SELECT MAX(가) AS 가, 나, SUM(다) AS 다

FROM A

GROUP BY 나

HAVING COUNT(*) > 1

ORDER BY 다 DESC;

→ HAVING COUNT(*)로 인해

→ 가,나,다의 컬럼 중 동일한 값이 2행 이상 반복되는 컬럼만 출력됨

→ 그 중 가장 큰 값 MAX(가)와 2행을 더한 값인 SUM(다)가 출력

 

GROUP BY에서 SELECT절에 COUNT가 없어도 HAVING이나 ORDER BY에서 사용할 수 있음

 

SQL 실행 순서에 근거해 SELECT절에 없는 컬럼은 ORDER BY로 정렬할 수 없다.

→ 하지만 FROM 절에 서브쿼리를 사용하면 정렬 가능

 

SQL Server의 TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력되도록 하는 WITH TIES 옵션을 ORDER BY절과 함께 사용하여야 한다.

→ SELECT TOP(3) WITH TIES 팀명, 승리건수 FROM 팀별성적 ORDER BY 승리건수 ASC;

 

SELECT COUNT(*)

FROM EMP_TBL A, RULE_TBL B

WHERE A.ENAME LIKE B.RULE → B.RULE의 ROW에 있는 조건이 A.ENAME을 만족시킬 때마다 COUNT += 1 

 

순수 관계 연산자 : SELECT, PROJECT, JOIN, DIVIDE

 

66번 : TODO\

 

USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

 

JOIN을 그냥 사용할 경우 동일한 컬럼에 대해서만 결과 출력

CROSS JOIN → 카티션 프로덕트 처럼 M*N의 결과값 출력 

 

LEFT OUTER JOIN → 자신이 가진 컬럼수 + 상대방에서 일치하는 컬럼수

RIGHT OUTER JOIN → 자신이 가진 컬럼수 + 상대방에서 일치하는 컬럼수

FULL OUTER JOIN → LEFT OUTER JOIN + RIGHT OUTER JOIN → 단 중복은 배제

 

LEFT OUTER JOIN == LEFT JOIN(OUTER 생략)

 

WHERE A.게시판ID = B.게시판ID(+) AND B.삭제여부(+) = ‘N’

→ OUTER JOIN 구문을 (+)기호를 사용해 처리할 수 있다. → 이를 ANSI문장으로 변경하기 위해서는 Inner쪽 테이블에 조건절을 ON절에 함께 위치시켜야 한다.

INTERSECT : 여러 개의 SQL 결과에 대한 교집합, 중복 배제함

EXCEPT : 두 개의 테이블에서 겹치는 부분을 앞의 테이블에서 제외하여 추출하는 연산이다. 추출 후 중복된 결과를 제거하여 보여준다. → MINUS와 같은 결과

UNION : 합집합, 중복배제

UNION ALL : 합집합, 중복허용

• 조건 : 서비스와 서비스 이용 = 1 : N의 관계인 상태

SELECT A.서비스ID, B.서비스명, B.서비스URL

FROM （SELECT 서비스ID

FROM 서비스

INTERSECT

SELECT 서비스ID

FROM 서비스이용） A, 서비스 B

WHERE A. 서비스ID = B. 서비스ID

→ 이것과 동일한 출력문 추출

 

① SELECT B.서비스ID, A.서비스명, A.서비스URL

FROM 서비스 A, 서비스이용 B

WHERE A. 서비스ID = B. 서비스ID

→ 1 : N의 관계이므로 서비스 이용건수 만큼 출력되지 않아 정답이 아님

→ GROUP BY를 사용하면 동일한 결과임

 

② SELECT X.서비스ID, X.서비스명, X.서비스URL

FROM 서비스 X

WHERE NOT EXISTS （SELECT 1

FROM （SELECT 서비스ID

FROM 서비스

MINUS

SELECT 서비스ID

FROM 서비스이용） Y

WHERE X.서비스ID = Y.서비스ID）；

→ MINUS를 사용해 이용된 적이 없었던 서비스가 추출되고 → 이후에 NOT EXISTS구문을 적용해 이용된 적이 있었던 서비스가 나옴

 

UNION ALL을 사용하는 경우 컬럼 ALIAS는 첫번째 SQL 모듈 기준으로 표시되며 정렬 기준은 마지막 SQL 모듈에 표시

 

ORDER BY 1,2 → 사용된 SELECT 절에서 첫번째 컬럼 기준으로 오름차순한 후에 동일한 값이 존재하면 2번째 컬럼을 기준으로 오름차순

SELECT C0L1, COL2, COUNT(*) AS CNT

FROM (SELECT COL1, COL2

FROM TBL1

UNION ALL

SELECT COL1, COL2

FROM TBL2

UNION

SELECT COL1, COL2

FROM TBL1)

GROUP BY COL1, COL2；

→ 집합연산자는 SQL위에 먼저 정의된 연산자가 먼저 수행된다 → UNION이 나중에 수행되므로 중복 데이터가 모두 제거됨 → 때문에 아래 사진과 같은 데이터만 출력

→ GROUP BY를 해도 동일 레코드가 존재하지 않기 때문에 CNT는 모두 1이다

 

UNION : SELECT 되는 모든 컬럼값이 동일해야만 중복제거

UNION ALL : 중복 포함 다 뽑아냄

→ START WITH C2 IS NULL : 일단 1 NULL A 출력

→ CONNECT BY PRIOR C1 = C2 : 이전 C1값이 C2와 같은것을 찾아라

    즉, C2가 (이전ROW의 C1인) 1인 ROW를 찾는다

→ ORDER SIBLINGS BY C3 DESC

    즉, 같은 계층에선 C3기준 내림차순 정렬

 

→결과 :  A → C → B → D의 결과가 출력된다.

START WITH 매니저사원번호 IS NULL → 매니저 번호가 NULL인 ROW가 존재하면 모든 NULL ROW에서 시작함 → CONNECT BY PRIOR 사원번호 = 매니저사원번호 → 시작 NULL ROW와 같은 사원번호가 있으면 출력

 

PRIOR 입사일자 BETWEEN ‘2013-01-01’ AND ‘2013-12-31’ 

→ 2014-01-01의 결과가 출력됨

 

PRIOR 키워드는 CONNECT BY절만이 아닌 SELECT, WHERE절에서도 사용 가능

 

WHERE절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출

Single Row(단일 행 서브쿼리) : 실행 결과가 항상 1건 이하, 단일행 비교연산자와 사용

→ >, <, =, <=, >=, <>이 존재한다.

 

Multi Row(다중 행 서브쿼리) : 실행 결과가 여러 건인 서브쿼리 

→ IN, ANY, ALL, SOME, EXISTS

 

Multi Column(다중 컬럼 서브쿼리) : 여러 컬럼을 반환, 메인쿼리의 조건절에 여러컬럼을 동시에 비교 → 컬럼개수와 위치가 동일해야함

 

서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능

 

다중컬럼 서브쿼리는 SQL Server에서는 지원하지 않는다.

 

사원 (사번, 이름, 나이)

가족 (이름, 나이, 부양사번)

우 가족 테이블의 부양사번은 사원 테이블의 사번을 참조하는 외래

?1 (Foreign Key) 이다.

［SQL 문장］

SELECT 이름

FROM 사원

WHERE |(5)| (SELECT * FROM 가•족 WHERE | © | )

→ 현재 부양하는 가족들이 없는 사원들의 이름을 구하라

→ NOT EXISTS or LEFT OUTER JOIN or NOT IN

 

GROUP BY 와 집계합수를 사용하지 않고 HAVING를 사용했다해서 SQL에러가 발생하지는 않는다.

 

HAVING COUNT(*) < (SELECT COUNT(*) 

FROM 이벤트

WHERE 시작일자 > =

'2014.10.01'))

→ 이벤트 시작일자가 ‘2014.10.01’과 같거나 큰 이벤트건수와 그 이벤트들을 기준으로 회원별 이메일 발송건수를 비교하는 것이다.

 

다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로 사용된다

→ 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리 연산자로 사용 불가능

 

비연관 서브쿼리는 메인쿼리에 값을 제공하기 위한 목적으로 사용

→ 연관 서브쿼리는 X

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

 

뷰는 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

 

CUBE, ROLLUP, GROUPING SETS 함수들에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환한다.

 

GROUPING SET(자재번호, (발주처ID, 발주일자)) → 자재번호별 SUB TOTAL과 (발주처, 자재번호) SUB TOTAL 출력 → 입력된 인수들에 대한 개별 집계를 구할 수 있음

 

CUBE (자재번호, (발주처ID, 발주일자)) → 나열된 모든 인수의 결합 가능한 집계가 출력됨

→ GROUPING SET보다 2배 많은 결과가 출력

 

GROUP BY GROUPING SETS((상품ID, 월)) → 표시된 인수들에 대한 개별집계 출력

→ 상품ID의 하나의 ROW에 대한 모든 월 ROW 출력

→ CUBE였다면 모든 경우의 수 구함

 

Partition과 Group by 구문은 의미적으로 유사하다

Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의하는 것과 동일하다

윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.

→ 줄어든다X

윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

SELECT 추천경로, 추천인, 피추천인, 추천점수

FROM (SELECT 추천경로, 추천인, 피추천인, 추천점수

,ROW_NUMBER() OVER(PARTITION BY 추천경로

ORDER BY 추천점수 DESC) AS RNUM

FROM 추천내역)

WHERE RNUM = 1；

→ RNUM = 1 → 1등만 뽑기  &>^

→ ROW_NUMBER() : 정렬된 데이터에 동일 값이 존재하더라도 유일한 순위를 부여

→ ROW_NUMBER()는 OVER와 같 사용됨

→ 데이터 그룹 내에 유일한 순위를 추출

 

ROWNUM : ROW_NUMBER처럼 데이터에 순번을 부여하지만 정렬되지 않은 랜덤 값임

→ 정렬하기 위해서는 서브쿼리에 ORDER BY를 사용해야함

→ 때문에 ROW_NUMBER보다 비효율적임

→ SQL Server에서는 TOP가 ROWNUM역할

→ ROWNUM은 OVER와 같이 사용되지 않는다 (X)

 

RANK

DENSE_RANK

ROWNUM

 

ROWS → RANGE와 같은 기능

SELECT 상품분류코드

,AVG(상품가격) AS 상품가격

,COUNT(*) OVER(ORDER BY AVG(상품가•격)

RANGE BETWEEN 10000 PRECEDING

AND 10000 FOLLOWING) AS 유사7H수

FROM 상품

GROUP BY 상품분류코드;

→ 상품분류 코드별(GROUP BY) 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품 분류 코드의 개수를 구한 것이다.

 

118번 

 

→ END_VAL는 START_VAL의 다음값을 비교하고

→ START_VAL은 END_VAL의 전값을 비교함

→  FLAG 1이나 FLAG2가 0이 나온것만을 출력

 

LAG : 현재 읽혀진 데이터의 이전 값

LEAD : 현재 읽혀진 데이터 이후 값

 

UPDATE A_USER.TB_A

SET COL1 =’AAA’

WHERE COL2 = 3

 

UPDATE A_User.TB_A

SET coll='AAA'

WHERE col2=3

→ GRANT SELECT, UPDATE ON A_User.TB_A TO B_User

→ GRANT 명령은 DBMS 사용자에게 권한을 부여할 때 사용한다.

→ A_User.TB_A에 대한 SELECT 권한을 B_User에게 할당한다.

 

 ROLE : DBMS사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹이다.

 

CASCADE : A로부터 받은 B의 권한은 CASCADE 명령어로 받은 권한을 취소 할 수 있다.

 

변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE 절의 조건등으로 대입할 수 있다.

 

PL/SQL로 작성된 Procedure, User Defined Function은 자율 트랜잭션 처리 가능 && 작성자의 기준으로 트랜잭션을 분리할 수 있다.

 

execute immediate 'TRUNCATE TABLE DEPT'；

INSERT /*+ APPEND */ INTO DEPT (DEPTNO, DNAME,

LOC)

→ execute immediate로 동적 SQL을 사용한다고 알려준 후 'TRUNCATE TABLE DEPT' 사용해서 부서 테이블에 입력하기 전에 부서 테이블의 모든 데이터를 미리 삭제함

→ execute immediate → PL/SQL에서는 동적 SQL 또는 DDL 문장을 실행할때 사용해야함

 

저장형함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할

 

데이터의 무결성과 일관성을 위해 사용자 정의 함수 사용 

→ 트리거의 용도

→ 절차형 SQL 모듈은 아님(X)

Trigger은 TCL을 이용하여 트랜잭션을 제어할 수 없다 → Procedure는 가능

Trigger은 데이터베이스에 로그인하는 작업에도 정의 가능

 

DELETE를 사용할 때  : Oracle과 SQL Server는 FROM을 생략가능

 

실행계획을 통해서 알 수 있는 것 : 엑세스 기법, 질의 처리 예상비용, 조인순서, 조인방법

→ 동일 SQL문에 대해 실행계획이 다르다고 해서 실행결과가 달라지지는 않는다

→ CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표현된다

→ SQL 처리를 위한 실행 절차와 방법 표현

→ 실제 처리건수는 트레이스 정보를 통해서 알 수 있다.

실행계획을 읽는 순서는 안에서 밖으로 위에서 아래로 읽는다.

 

규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 ROWID를 활용하여 테이블을 엑세스 하는 방법이다.

 

SQL 처리 흐름도 : SQL문의 처리 절차를 시각적으로 표현한 것으로, 인덱스 스캔 및 전체 테이블 스캔 등의 엑세스 기법을 표현할 수 있으며, 성능적인 측면도 표현할 수 있다

 

인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다

 

기본 인덱스(PK) : UNIQUE & NOT NULL의 제약조건을 가진다. 보조 인덱스는 UNIQUE 인덱스가 아니라면 중복 데이터의 입력 가능하며, 자주 변경되는 속성을 인덱스로 선정할 경우 UPDATE, DELETE 성능에 좋지 않은 영향을 미치므로 인덱스 후보로 적절하지 않다.

 

인덱스 : 조회 속도만 향상시킴, Balance Tree는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스이다. 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.

 

 INSTR(문자열, 찾을 문자열, 시작 위치, 발생 횟수) → 문자의 위치 반환(숫자)

→ 시작위치가 존재하면 ‘문자열’ 시작위치부터 시작

→ 발생횟수가 존재하면 찾을 문자열을 2번 찾은 다음 문자열 위치 출력

 

B-TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다.

 

CLUSTERED 인덱스 : 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.

 

BITMAP 인덱스 : 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계

 

인덱스는 인덱스 구성 컬럼으로 항상 오름차순으로 정렬(X)

→ 인덱스가 생성될 때 정렬 순서를 내림차순으로 하면 내림차순으로 정렬

 

규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다.

 

인덱스 스캔은 데이터를 찾아가는 방식이 랜덤 엑세스 인데, 랜덤 엑세스의 부하가 크기 때문에 매우 많은 양의 데이터를 읽을 경우에는 인덱스 스캔보다 전체스캔이 유리하다.

 

인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을수도 있다.

→ 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE될 때는 인덱스로 인한 부하가 발생하지 않는다.

 

ORACLE의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 Single row by rowid 엑세스 기법이다

 

NL Join은 데이터를 집계하는 업무보다는 OLTP의 목록 처리 업무에 많이 사용된다.

 

DW등의 데이터 집계업무에서 사용되는 Join기법은 Hash Join, Sort Merge Join이다.

 

Sort Merge Join을 사용하기에 두 테이블이 너무 커서 소트(sort)부하가 심할 때는 Hash Join이 유용하다.

 

① HASH ANTI JOIN

② HASH SEMI JOIN

® NESTED LOOP ANTI JOIN

NESTED LOOP SEMI JOIN

→ 이건 무엇..

 

→ EXISTS절은 일반적으로 SEMI JOIN 사용함

 

EQUI 조인에서만 동작하는 JOIN방식은 Hash Join이다

 

Sort Merge Join 

• Non-EQUI JOIN조건에서도 사용할 수 있다. 
• 조인 컬럼에 적당한 인덱스가 없어서 NL조인이 비효율적일때 사용 가능
• Driving Table의 개념이 중요하지 않은 조인 방식
• 조인 조건의 인덱스의 유무에 영향 받지 않음

 

유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 조인할 때는 NL조인이 적합하다.

hash join 효과적일 때 : 자연조인 시 드라이빙 집합 쪽으로 조인 액세스 량이 많아 Random 엑세스 부하가 심할 때

→ 결과 행의 수가 큰 테이블을 선행 테이블로 사용할때 성능에 유리하다

→ Sort Merge Join보다 항상 우수한 성능을 보장 하지는 않는다(ex: join 대상 테이블이 join key 컬럼으로 정렬되어 있을 때)

 

Sort Merge Join은 비 동등 Join에서도 사용할 수 있다. 

 

NL Join은 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리하다.

primary key : 기본키

unique key : 고유키

foreign key : 외래키

 

트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개 이상의 db 조작

 

연산자 우선순위 : () → not → 비교연산자 → and → or

 

SQL은 인덱스가 1부터 시작함

SUBSTR(‘SQL Except’, 5, 3) → 5번째 위치에서 문자 3개 반환

 

LPAD(‘문자열 OR 열이름’, 만들어질자리수, ‘채워질숫자’) → 자릿수 = 숫자지정

RPAD

 

SIGN → 숫자가 양수면 1, 음수라면 -1, 0이면 0 반환

MOD → 숫자 1을 숫자2로 나누어 나머지 반환

CEIL : 소수점 자리는 무조건 올림

ROUND : 반올림

FLOOR : 소수점 자리는 무조건 내림

TRUNC : 지정자리 수 제외 나머지 버림

 

SYSDATE / GETDATE : 현재 날짜와 시각 출력

EXTRACT / DATEPART : 날짜에서 데이터 출력

 

NULLIF(식1, 식2) → 식1이 식2와 같다면 NULL이 아니라면 식1을 출력함

 

STDDEV : 표준편차

SIGN : 양수면 1, 음수면 -1, 0이면 0

VARIAN : 분산

SELECT 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

 

STDDEV : 표준편차

INTERSECT : 교집합, 정렬O

EXCEPT MINUS : 차집합, 정렬O

CROSS JOIN : 곱집합

 

USING 조건절 : 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN 수행, JOIN컬럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음

 

ON 조건절 : 컬럼명이 다르더라도 JOIN조건 사용 가능, ALIAS나 테이블명 반드시 사용

 

RANK : 동일한 값에 대해서 동일한 순위 부여(1,2,2,4)

DENSE_RANK : 동일한 순위를 하나의 등수로 간주 (1,2,2,3)

ROW_NUMBER : 동일한 값이라도 고유한 순위 부여(1,2,3,4)

 

LAG : 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음

LEAD : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있음

 

NULL값과의 비교연산은 FALSE를 리턴한다.

NULL값을 조건절에 사용하는 경우 IS NULL, IS NOT NULL이란 키워드 사용

 

!= 같지않다

^=같지않다

<> 같지않다

 

다중행 함수 : 집계함수, 그룹함수, 윈도우함수

 

NULLIF(식1,식2) : 식1과 식2가 동일하다면 NULL, 아니면 식1 반환, 식1이 NULL이면 안됨

→ 데이터 유형이 같아야함

 

도메인 : 각 속성은 가질 수 있는 값의 범위가 있는데 이를 그 속성의 도메인 이라 한다.

→ 엔터티 내에서 속성에 대한 데이터 타입과 크기 그리고 제약사항을 지정

→ CREATE DOMAIN 도메인명 데이터_타입

       [DEFAULT 기본값]

       [CONSTRAINT 제약조건명 CHECK (범위 값)];

CONSTRAINT : 제약 조건의 이름을 지정

CHECK : 속성 값에 대한 제약조건지정

 

스키마 : 하나의 사용자에 속하는 테이블과 기타 구성 요소등을 그룹지음

PRIOR : CONNECT BY절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아감

→ CONNECT PRIOR 부모 = 자식 → 현재 자식과 부모키 비교(순방향)

→ CONNECT 부모 = PRIOR 자식 → 현재 부모와 자식키 비교(역방향)

→ 좌측에 기술하는 컬럼이 부모이고 우측에 기술하는 컬럼이 자식이다.

→ 기준이 되는 부모 or 자식 키가 살펴보고 있는 반대키와 일치할때 해당 row가 출력됨

 

SELECT 장치이름, 상위장치, LEVEL FROM TAB1

START WITH 상위장치 IS NULL

CONNECT BY PRIOR 장치이름 = 상위장치;

→ 순방향

SELECT 장치이름, 상위장치, LEVEL FROM TAB1

START WITH 상위장치 = '입출력장치'

CONNECT BY PRIOR 상위장치 = 장치이름;

→ 역방향

→ PRIOR이 붙은것이 START WITH에 걸려있으면 역방향이고

→ PRIOR이 붙은것이 START WITH에 없으면 순방향이다.

 

START WITH PARENTMENU = 'ROOT' 

CONNECT BY PRIOR MENUCODE = PARENTMENU

→ PARENTMENU 필드가 'ROOT'인 행을 루트로 간주하여,

→ 루트행이 우선적으로 출력된 후 이후에

→ MENUCODE 필드가 PARENTMENU 필드로 존재하는 자식을 찾아 관계를 맺는다

→ ROOT가 두개라면 루트(시작점) 또한 두개로 출발해 관계를 맺는다.

 

level : 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수이다.

→ 계층쿼리가 아니라면 모든 값이0, 계층쿼리에서는 루트 노드의 level값이 1이다.

 

ORDER SIBLINGS BY : 조회된 컬럼에 대해 ORDER BY 실행

 

ROW_NUMBER : OVER와 함께 사용

ROWNUM : 독립적으로 사용(OVER와 함께 사용되지 않는다.)

 

도메인 특징 : 엔터티 내에서 속성에 대한 데이터 타입과 크기를 지정, 엔터티 내에서 속성에 대한 NOT NULL 지정, 엔터티 내에서 속성에 대한 Check 조건 지정, PK, Unique key

→ 테이블의 속성 간 FK제약 조건을 지정하는 것이 아닌 → 외래키 속성과 참조테이블에 대한 정보 지정 FK

→ CONSTRAINT : 제약 조건의 이름을 지정

 

속성 : 더 이상 쪼개지지 않는 최소의 데이터 단위, 업무에 필요한 데이터이며, 의미상 더 이상 분리되지 않고 엔터티를 설명하는 인스턴스의 구성요소이다.

→ 속성의 특성 : 기본속성, 설계속성, 파생속성

 

SELECT N1 FROM (SELECT * FROM SQLD_34_12) ORDER BY 2;

→ SELECT 절의 컬럼수가 1개 이므로 ORDER BY에 2가 올 수 없음

 

SELECT SUM(COL1 + COL2 + COL3 + COL4) FROM SQLD_34_17;

→ (a + b + c + d) 의 경우는 해당 로우에 대해서 계산이 되고 난 컬럼에 대해서 Sum 이 수행됨 → ROW에 NULL값이 존재해도 COLUMN 4개가 선택 되었으므로 → result : 4이다.

 

• COUNT()는 일치하는 행이 존재하지 않으면 결과는 0이 된다. 그러나 SELECT AVG(age) FROM students WHERE grade = 5; 에서 AVG(age)의 값은 NULL이 된다. 이와 같은 결과는 다른 집계 함수에서는 같지만, COUNT()만 '공집합을 평가한 결과가 0이 된다'라는 동작을 한다. 그 외의 집계 함수 평가는 공집합에 대해 NULL이 된다 → COUNT는 유일하게 조건에 대한 결과값을 만족하지 못할 경우 공집합(0 Rows)를 출력함 → 다른 집계함수는 NULL값 출력

SAVEPOINT 이름;

SAVEPOINT SV1에서 SAVEPOINT의 이름이 같을 경우

→ ROLLBACK TO 돌아갈 세이브포인트 명칭

→ ROLLBACK TO SAVEPOINT SV1; 을 수행하면 마지막으로 수행했던 SAVEPOINT로 되돌아감

→ 단순히 ROLLBACK 만 사용했을 경우는 SAVEPOINT와 관계없이 모든 데이터가 ROLLBACK된다.

 

• 사원과 관리자, 그리고 최상위 관리자가 나오도록 작성된 SQL

SELECT A.EMPLOYEE_ID,

 A.MANAGER_ID AS A_MANAGER_ID,

 B.EMPLOYEE_ID AS B_EMPLOYEE_ID,

 B.MANAGER_ID AS B_MANAGER_ID,

 A.LAST_NAME

FROM HR.EMPLOYEES A

 ( ) HR.EMPLOYEES B ON ( )

WHERE 1=1

AND A.EMPLOYEE_ID < 200

ORDER BY EMPLOYEE_ID ;

→ 최상위 관리자가 나올려면 left outer join 이 되어야 함. 최상위 관리자는 manager_id 가 null 이거나 없기 때문

 

• Window function

 Sum,max, min 등과 같은 집계 window function을 사용할 때 window 절과 함께 사용하면 집계의 대상이 되는 레코드 범위를 지정할 수 있다

→ window function 처리로 인해 결과 건수가 줄어들지는 않는다.

→ GROUP BY구문과 병행해서 사용할 수 없다.

→ 윈도우 함수의 PARTITION구문과 GROUP BY구문은 파티션을 분할한다는 의미에서 유사하다

• 윈도우 함수 구조
• ARGUMENTS(인수) : 윈도우 함수에 따라서 0~N개의 인수 설정
• PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눈다
• ORDER BY : 어떤 항목에 대해서 정렬한다.
• WINDOWING : 행 기준 범위를 정한다, ROWS는 물리적 결과의 행수이고 RANGE는 논리적인 값에 의한 범위이다.

 

• WINDOWING(행 지정)
• ROWS : 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
• RANGE : 논리적 주소에 의해 행 집합을 지정
• BETWEEN~AND : 윈도우의 시작과 끝 위치를 지정한다.
• UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 행임을 의미
• UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 행임을 의미
• CURRENT ROW : 윈도우 시작 위치가 현재 행임을 의미(데이터가 인출도니 현재 행)

 

WITH WITH_TAB (last_name, EMP_ID, MGR_ID, sum_salary )

AS (

 SELECT last_name,EMPLOYEE_ID,MANAGER_ID, salary

 FROM HR.EMPLOYEES

 WHERE MANAGER_ID IS NULL

 UNION ALL

 SELECT a.last_name, a.EMPLoYEE_ID, a.MANAGER_ID, a.salary + b.sum_salary

 FROM HR.EMPLOYEES A, WITH_TAB B

 WHERE B.EMP_ID = A.MANAGER_ID )

SELECT SUM_SALARY FROM WITH_TAB

WHERE EMP_ID = 105;

→ FROM 절이 먼저 동작하므로 FROM에 있는 재귀함수가 모두 동작함 언제까지? → WHERE = EMP_ID =105가 될때까지 → result :  100~105까지의 SUM값이 출력됨

→ a.salary+b.sum_salary : 현재 SAL(EMP_ID = 105)에서 루트 SAL(MANAGER_ID IS NULL)까지 더해라 → 조건은 B.EMP_ID = A.MANAGER_ID

→ 현재 EMP_ID 105에서 루트 100까지 SALARY 더하되, B.EMP_ID = A.MANAGER_ID인 105, 103, 102, 100의 SALARY를 더함 → 4800+9000+17000+24000 = 54800

WHERE SALARY > 200 OR MGR_ID IS NULL AND CODE=’B’

→ MGR_ID IS NULL AND CODE=’B’은 조건을 만족하는 ROW가 존재하지 않음

→ SALARY > 200 OR MGR_ID IS NULL → 3개의 ROW 만족

→ result : 3

 

NTILE : 전체건수를 인수값으로 N등분

 

정규화 : 조회 속도 느림 → 조인 유발

반정규화 : 조회속도는 빠름 → 단 모델의 유연성이 낮아짐

SELECT EMPLOYEE_ID,

 DEPARTMENT_ID,

 LAST_NAME,

 SALARY,

 LAG(SALARY, ( ) ) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS BEFORE_SALARY

FROM HR.EMPLOYEES

WHERE EMPLOYEE_ID < 110;

 

→ LAG는 이전 N번째 행 가져옴, LEAD : 이후 N번째 행

→ DEPARTMENT_ID로 그룹 묶고, SALARY로 오름차순 정렬

→ DEPARTMENT_ID 60의 이전행은 NULL이므로 데이터가 없고 90에서 60값을 가져오며 100에서 90의 값을 가져옴 

→ result : 2

 

데이터 모델링 단계 : 개념적 모델링 → 논리적 모델링 → 물리적 모델링

 

개념적 모델링 : 추상화 수준이 높고 업무중심적, 포괄적 수준의 모델링 진행, EA수립시 많이 사용

 

논리적 모델링 : 시스템에 구축하고자 하는 업무에 대해 key, 속성, 관계 등 표현, 재사용성 높음, 구체적인 업무 중심의 모델링 → 대표적 활동인 정규화가 있음(모델의 일관성 확보, 중복제거)

 

물리적 모델링 : 성능, 저장 등 물리적인 성격 고려(db에 이식할 수 있도록)

 

정규화 : 함수의 종속성을 이용해 무손실 분해

→ 입력/수정/삭제 성능 향상

→ SELECT → 정규화 이후 조인 때문에 느려짐

→ INSERT, UPDATE → 테이블이 작아져서 빨라짐

→ UPDATE는 특정한 조건에서는 반정규화가 빠름

• 정규화
• 1차 : 원자성 : 하나의 ROW에는 하나의 값만
• 2차 : 부분함수 종속제거(=완전종속) → 중복 COLUMN을 사용하지 않고 다른 PK에 종속될 경우 합쳐버림
• 3차 : A→B, B→C, A→C가 되어야함 →  기본키 외에 다른컬럼이 컬럼을 결정하지 않을 것
• BCNF : 모든 결정자는 후보키(FK)여야함 → PK가 되면 안됨

 

• 식별자
• 유일성 : 유일하게 인스턴스 구분O
• 최소성 : 주식별자 속성 수 = 유일성 만족 최소 수
• 불변성 : 주식별자 값 바뀌지X
• 존재성 : NOT NULL

반정규화 : 데이터 무결성을 해침

→ 대량범위, 범위처리, 통계처리

→ 다른 방법 유도(무결성을 해치기때문에) → 응용시스템 변경, 클러스터링/인덱스 조정, 뷰처리

 

반정규화 : 테이블 반정규화(1:1, 1:M, 슈퍼-서브타입), 속성(파생 : 미리 값 계산, PK : 컬럼추가 : PK에 데이터가 있지만 일반속성으로 포함, 중복 : 조인처리 할 때 중복컬럼 위치시킴, 이력테이블 : 대량 처리 위한 기능성 컬럼, 오류 : 사용자 실수 대비 임시 중복값 저장), 관계(중복관계 추가)

 

반정규화 대상 : 빈도수, 대량범위, 통계처리

→ SORTING, ORDER BY는 반정규화 대상X

 

기본엔터티 : 원래 존재, 부모역할, 주식별자

중심엔터티 : 기본에서 발생, 행위 엔터티 생성

행위 : 2개의 부모 엔터티에서 발생, 자주 변경

 

DDL : GRANT CREATE TABLE TO 유저명 →  테이블을 생성할 수 있는 권한

→ DDL 명령어는 GRANT CREATE TABLE TO [유저명] 이다.

 

DML : GRANT SELECT ON 테이블이름 TO 유저명

→ DML 명령어는 GRANT [DML명령어] ON [테이블이름] TO [계정이름]

 

SELECT COUNT(*)

FROM SQLD_30_14_01

WHERE (COL1, COL2) IN ((‘A’, 50));

→ COL1이 A이면서 COL2가 50인 ROW 찾는 쿼리문

 

ORACLE : ALTER TABLE 테이블명 MODIFY (컬럼명, 데이터유형)

SQL Server : ALTER TABLE 테이블명 ALTER (컬럼명, 데이터유형)

→ PRIOR 상위계층번호 = 계층번호 → NULL인 계층번호가 없으므로 종료

→ START WITH 으로 시작되며 PRIOR이 걸려있는 NULL이 START 위치임

→ PRIOR이 계층번호에 걸려있었다면 → 계층번호 1 = 상위계층번호 1을 비교하게 됨

→ 하지만 PRIOR이 상위계층번호에 걸려있으므로 START 위치인 NULL이 계층번호엔 존재하지 않음

→ 때문에 result : 2건 출력

 

ABS(-3.8) → 3.8(절대값)

FLOOR(3.8) → 3(내림)

TRUNC(3.8) → 3(소수점아래버림)

ROUND(3.8) → 4(반올림)

 

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);

→ MERGE 사용 시 DELETE는 UPDATE절에 종속

→ A표를 수정하는데 A.COL1=B.COL1이 같다면 (A~C까지 선택)

→ A.COL3 = 2인 곳을 A.COL3 = 4로 바꾸고

→ A.COL3=4인 줄이 A.COL3 <=2 라면 지워라(안 지워짐)

→ 같지 않다면 (X)(D,E)부분을 A표에 추가

결과에 A(왼쪽)와 B(오른쪽)테이블 중 B값은 모두 나오고 A값은 B와 겹치는 것만 출력

→ 오른쪽 테이블인 B값 모두 출력한 것 : RIGHT OUTER JOIN

 

• 파티셔닝 기법

RANGE : 관리 쉽다 / 가장 많이 쓰임 / 숫자값으로 분리O

LIST : 대량 데이터 / 특정컬럼(생성일자)없음 / PK

HASH : 관리어렵 / 데이터 위치 모름

 

Cross Join : Where절에서 조건을 걸 수 있다. Join Key가 없을 경우 발생

Natual Join : Where 절에서 조건을 걸 수 없다. 특정 Join컬럼을 명시적으로 적을 수 없다.

→ Join key는 컬럼명으로 결정된다.

 

주문 (고객ID) REFERENCES 고객 (고객 ID)

→ 고객이 부모, 주문이 자식

→ 부모에 없는 데이터가 자식에 존재하면 오류 발생

→ 고객ID(FK) 외래키인데 → 참조 무결성 : 외래키는 부모키(기본키)값과 같거나 NULL값일 것

 

Unique Index Scan은 1개의 값을 추출하는 방식 → PK가 2개 이상일 경우

→ KEY1(PK), KEY2(PK)

→ WHERE절에 1개의 조건만 주면,  결과값이 1개이상 출력

→ WHERE KEY1 = 1

 

모든 ~~ 나타내는, 뭐를 꼭 나타내는~

→ 이런 문제의 유형일 경우 INNER JOIN이 오답일 확률이 높다

제일 안쪽부터 실행하되, 계층악필요

(정확히는 모르겠으나 구글링으로 여러 예시를 살펴본 결과

FULL이 아닌 BY INDEX ROWID가 붙은 경우는 가장 안쪽부터 읽으면 되는듯)

(선행테이블ㅡ> 인덱스ㅡ> 조인) (맞는지 모르겠음)

 

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)

=> 5번이 가장 안쪽이지만 위에 TABLE ACCESS(FULL)있음

=> 3ㅡ> 5ㅡ> 4 (3번과 4번은 같은 위치니까 위에 있는 3번ㅡ>4번 순서)

                      (5번이 원래 제일 먼저였는데, 3번을 우선 넣어준 것)

=> 7번이 더 안쪽이지만

     2번 아래에 3~5번 계획이 있었던 것, 계획 다 수행했으니 2번 수행하고 마무리

=> 3ㅡ> 5ㅡ> 4ㅡ> 2ㅡ> 7ㅡ> 6 (5ㅡ> 4와 동일)

=> 나머지는 더 안쪽에 있는 순으로 정리

    3ㅡ> 5ㅡ> 4ㅡ> 2ㅡ> 7ㅡ> 6ㅡ> 1ㅡ> 0

→ 별 다른 조건이 없으므로 SELECT (CO1, CO2)를 한줄씩 읽음

→ 한줄씩 수행

→ 100, 3000, NULL 출력

START WITH MANAGER_ID IS NULL

→ 결과값에서 MANAGER_ID의 첫번째 컬럼이 NULL 값이므로 MANAGER_ID가 NULL이여야만 결과가 출력

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID

→ 이전 자식노드의 값 = 현재 부모노드의 값 비교

MANAGER_ID → NULL → 100

EMPLOYEE_ID → 100 → 101

→ NULL부터 시작해서 MANAGER의 100과 EMPLOYEE의 100을 비교

 

사무소코드가 ‘=’로 상수값이 들어왔기 때문에 PK의 순서를 사무소코드컬럼이 가장 앞으로 바꿔서 생성하는것이 성능에 유리함

 

BOARD_ID → VARCHAR2() 형식일 때 

→ BOARD_ID = 200 WHERE BOARD_ID = ‘100’ → 가능

 

SQL Server에서 NULL데이터 조회 → ‘=’이다

 

VARCHAR2(10) PRIMARY KEY

SELECT * FROM 서비스 WHERE 서비스번호 = 1;

→ 모든 레코드 중에서 ‘001’과 같은 숫자형식으로 하나의 레코드만이라도 입력되어 오류 없이 실행된다.