Sqld 38회기출강의정리

sql38회기출강의 정리 유툽

ㅇㅇ계층형 질의
start with로 시작 - 루트노드 (level 1부터시작)
리프노드는 끝에 위치하므로 자식노드를 가지지 않는다.
레벨이 같은 애들은 형제노드 관계 라고 한다.
start with ~~ 루트노드(시작점을 지정)의 조건
connect by <nocycle>~~~ 상하계층이 이루어지는 조건 (노사이클>뱅글뱅글 도는경우가 있어서 조직도가 계속 만들어지는경우가 있는데, 이렇게되면 오류가 생기고 문제가 생길수있음. 계속 사이클이 생기는 것을 방지)
<order by siblings by ~~> 어떤 기준을 가지고 정렬을 할것이다

자식컬럼과 부모컬럼 찾아내기
mgr empno
connect by  prior 자식컬럼 =.       부모컬럼 :부모에서 자식으로 트리 그리기 : 순방향전개 (프 자 = 부)
Connect by           자식컬럼 = prior 부모컬럼: 자식에서 부모로 트리 그리기 : 역방향전개
Conncet by prior 부모컬럼 =          자식컬럼 : 자식에서 부모로 트리그리기 : 역방향전개
connect by.         부모컬럼 = prior 자식컬럼 : 부모에서 자식으로 트리 그리기 : 순방향전개
>>>>>현재 보고있는 개체의 칼럼에 prior적용하는것이다
>>connect by col1 = prior col2 : 다음 레벨로 지금 보고있는 칼럼의 col2와 일치하는 col1값을 넣어준다. .

dcl : data user conrtrol language
Ddl : data set definition language  >>truncate

Tcl. : transaction control language >>트랜잭션을 관리하고 제어ㅣ하는 랭귀지
논리적으로 분리할수없는 dml의 모임 - 트랜잭션
commit rollback savepoint

Truncate > ddl
모든행을 삭제하고 디스크사용량 초기화하며 데이터의 틀만 유지.
일부 dml의 성격을 가지지만 truncate는 ddl로 취급한다!! 헷갈리지말기!!

delete - 안에 값 ,개체 다 지우고 틀만 냄겨놈, 용량도 보존. 디스크 용량 잡아먹고있음 (dml)
truncate(ddl)- 개체 삭제는 하고 용량은 잡아먹지않아. 구조자체는 유지.
drop - 테이블 완전삭제 (ddl)

commit / auto commit
Dml은 auto commit이 안되지만
ddl은 사용하자마자 auto commit이 된다!!  

Count distinct- null 값 제외하고 행의수 출력
Is not null 을 써야함.
Null값은 부등호 쓸수 없음

Count 집계함수 정리- 차이점 체크!!!
Count(*) count(1), count(2)…. > 전체 테이블의 행의 수를 출력한다 null값을 포함해서 행의 수를 계산.
Count(컬럼명) > null값을 제외한 행의수를 계산한다.
Count(distinct 칼럼) > 칼럼이 가지고있는 고유의 종류의 수를 계산한다.null값 제외.

Null값은 반드시 sql연산자 “is null “과 “is not null”로 해결한다. 그 이외에는 불가능. ‘
where 절에서는 객체의 참/거짓을 판단하는거임. 여기에 꼭 컬럼이 들어갈필요 없음. 개체의 평가를 내리는것임.

null 관련함수
Nvl(a,b) > null value 의 약자 >> 인수는 반드시 2개만 넣어줘야함.
a가 null이면 b를넣어준다 아니면 a —오라클
is null(a,b) 도 같음 (sql server)
nullif(a,b) > ab가 같다면 null, 아니면 a
null if (a = b) but then a
Coalesce (a,b,c…) null값 아닌 최초의 값 출력.

union all/ union
Set-operator를 이용한 결합.  집합류 (교집합,합집합,등등)
1 )위치기반 결합 -변수명 기반 결합이 아니기때문에 각 테이블의 첫번째의 값을 비교해서 작동할수가 있음. 컬럼명이 같지 않아도됨. 위치기준!! 컬럼의 숫자만 같으면 되고 컬럼명은 달라도 됨!!!!
2 ) 중복치 제거 / all이 들어가면 중복제거 안하고 다보여줄수 있음.(중복을 허락한다) 오라클에서만!
결과물의 칼럼은 테이블1의 칼럼명을 따른다. !
복수의 set operatio 의 경우 어떻게 되는가? 92페이지 83번 기억!! 위에서 아래로!!!!! 연산이 끝나고 새로운 연산이 들어가는것임.

Union : 합집합
Intersect : 교집함
Minus(orcale)/ except(sql-server) :차집합 >> not in / not exists
Union all 합집합>중복을 인정한다.

오라클하고 sql언어 차이? 몇개 보기

그룹함수 >>자격검정에 나온 문제 풀어보면 충분히됨.
Rollup> 까다로워, 하나씩 증가하는 형태
group by rollup(a) : 전체 합계, 칼럼a소계
group by rollup(a,b) : 전체합계, 칼럼a소계, 칼럼a,b조합 소계
group by rollup(a,b,c) : 전체합계, 칼럼a소계, 칼럼a,b소계, 칼럼 a,b,c조합별 집계

Cube>> 모든 가능한 것의 조합.의 소계
group by cube(a) : 전체합계, 칼럼a소계
group by cube(a,b) : 전체합계, 칼럼a소계, 칼럼b소계(맨밑), 칼럼a,b소계

>>가능한 모든 조합의 소계및 합계를 생성하기 때문에 시스템에 무리가 갈수있다.

Grouping sets >> 딱 그것만 구함.  전체소계가 없다!라고하면 그룹핑셋츠를 쓴거라고 보면된다(?)
group by grouping sets(a) : 칼럼a 소계’
Group by groupinbg sets(a,b) : 칼럼 a 소계, 칼럼 b 소계 (새로생김)

Group by dname, rollup(job)
Dname으로 그룹핑하고 이 안에서 롤업이 작동한다. (각 그룹핑 안을 각각 테이블로 보고 연산된다.) >>이럴때 전체 행에대한 합계가 없는상태가 되는것임.

Grouping sets(a,b) >> a,b
Grouping sets( (a,b) , a, () ) >> (a,b) , a , () =. Rollup(a,b)와 같다
grouping sets( a, rollup(b))>> a,b,()

Group by에 선언된 칼럼은 select 에 바로 들어갈수 있고, 선언되지 않은 칼럼들은 그룹함수에 낑겨서 들어가야 한다!그냥 들어갈수가 없다

range between 10000 preceding and 10000 following
116번문제 참고

서브쿼리 개념
쿼리문장 안에 들어가있는 서브문장을 서브쿼리라 함. 괄호안에 집어넣음.
서브쿼리를 포함하는애를 메인쿼리라 함.
구조적인 문제점때문에 서브쿼리를 사용하는 경우가 많음.
where절에는 집계함수를 쓸수 없음 왜? 집계함수는 group by의 영향,지시를 받기 때문에. 집계함수를 쓸라면 그룹바이를 만나던지 필요로 한다.
그룹바이를 확인하기 전에 집계함수가 나왔다? 그럼 안돌아간다.
이런 문제점을 해결하기 위해 서브쿼리로 값만 간단하게 넣어주고자 함.
서브쿼리는 단일행서브쿼리와 다중행서브쿼리 두가지가 있음.
결과가 1건인서브쿼리 (비교연산자 = 사용가능)
/ 결과가 여러개 나올수있는거 empno 결과값이 많은 결과를 내보내는것 > 반드시 !!!! In all any some exists의 비교연산자를 사용하여야 한다!!!  비교연산자 = 이런거 사용할수없음.

> any*(30,40,50).     > 30
<any (30,40,50).      <50
Any는 부등호르 가장 크게만드는 값으로 선택하면 된다.
=any (30,40,50).      In(30,40,50).       A=30 or a=40 or a=50

>all(30,40,50) 가장 작게 만드는 부등호를 선택 !!! 범위가 가장 작아지는 범위를 선택 >50
<all(30,40,50).    <30

다중행 서브쿼리 비교연산자는 단일행 서브쿼리에서 사용가능 in(30), any(30), all(30)
단일행 서브쿼리 비교연산자는 다중행 서브쿼리에서 사용불가능>>> 쓰고싶으면 any all 이런 연산자를 붙여서 위처럼 쓸수있음 >any/(30,40,50)


Un-correlated 단순히 간단한 값을 뽑아내는 목적(비연관) 서브쿼리
서브쿼리가 메인쿼리칼럼을 가지고있지 않은 형태의 서브쿼리
주의! 서브쿼리는 메인쿼리 칼럼 사용 가능. 메인쿼리는 서브쿼리 칼럼 사용 불가. 정적임.단순히 계산을 해결하기위한 방법으로 사용.

correlated(연관) 연관서브쿼리 - 서브쿼리가 메인쿼리칼럼을 포함하는 서브쿼리인데 . 메인쿼리가 먼저 수행되며 서브쿼리에 조건이 맞는지를 확인하는 용도로 사용된다. 동적.
exists서브쿼리는 항상 연관 서브쿼리로 사용된다 .

where라는거는 한명씩 와서 평가를 받는거임.  서브쿼리가 where에 왔을대 사람에 따라서 단일서브쿼리 값이 바뀌는것임.  -연관서브쿼리

인덱스 생성 구문
CREATE INDEX index_name ON table_name(coulmn_name)

Tcl
트랜잭션 > 현실세계에서 어떤 행위가 일어날때 데이터베이스에 반영하기 위해서 나타나는필수적 발생 데이터 베이스의 조작(manipulation.= dml)들.
트랜잭션은 분리할수 없는 개념. 하나으 ㅣ분리될수없는 논리작업을 구성하는 세부적인 연산작업들의 모임을 트랜잭션이라고 한다.
CREATE TABLE 통장( 입금 NUMBER(8), 출금 NUMBER(8) , 잔액(NUMBER(8) );
CREATE TABLE 지갑( 입금 NUMBER(8), 출금 NUMBER(8) , 잔액(NUMBER(8) );

이 작업을 쪼갤수없다. 트랜잭션을 분리하는 경우 작업의 의미, 논리가 무너짐.
INSERT INTO 통장 VALUES(500, 0 , 3500)
INSERT INTO 지갑 VALUES(0, 500 , 2500)

트랜잭션의 성질 3개 > 완전성, 0 0 외워서 가기
commit  >. 트랜잭션의 완료, 계약완료, 수정불가 commit 전에는 취소시킬수 있는데 이걸 입력하게되면 회사서버에 등록되고, 앞으로 수정할수없음 반영완료.  (ex . 타인이체)
Rollback. 최신 commit까지 복귀 또는 지정 savepoint까지 복귀/ ddl근처로 돌아가게된다.
Savepoint. 복귀 지점 지정
>>>ddl 문장 실행시 auto commit이 수행된다. 자동으로 반영된다는거임.


Exists
Where -개체평가조건 이 행을 테이블로 뽑아내도 되겠습니까/ 참만 되면 테이블로출력해준다.
exists - 존재 한다 뭔가가 존재하면 참 >> 서브쿼리 내부의 where에 만족하는 어떤임의의 개체가 존재하는가? 존재하면 where조건을 만족시킬수 있다.

dual테이블은 dummy테이블로 누구든 사용할수있는 테이블입니다.
dummy라는 문자열 유형의 칼럼에  ‘x’라는 값이 들어있는 행 1건을 포함하고 있다.  having>1

join은 행 기반으로 움직인다는것 기억!
select 구문에대해 적절하지 않는것
Select 문장에서 projection 행위를 할수있다.(열 ,변수 선택하고 파생시키는 행위0 + selection 행위 : 행을 선택하는 행위
from 절에서는 alias 키워드를 사용할수 없다. (0)
Where에서는 집계함수를 사용할수없다. (0)
order by 에서는 컬럼명과 컬럼의 alias만을 사용할수있다(x) > 행번호도 사용할수있음.

오라클 계층형 쿼리에대해 적절하지않은것
start with 계층구조의 시작점
Order siblings by는 같은 노드간의 형제노드간의 정렬.
order by가 전체 결과의 정렬을 지정하는 구문이다.
순방향전개란 부모노드로부터 자식노드방향으로 전개하는것  부모>자식
prior 자식 = 부모
부모 =prior 자식
prior가 현재 행에 붙는다. 현재 행에대한 이야기다.
루트노드의 level값은 1이다.

함수의 계산방법, null값 처리하는 방법?
함수에서는 null값 제외시키고 계산배제한다.
사칙연산에서는 null값 들어가면 다 null이 되어버림. 모르는값에 뭘 연산해 sun(col1+col2)
Sum(col1,col2) >>오류난다, sum은 인수가 하나이다 ㅠ_ㅠ 컴마 쓸수 없음. 오류남.

()
비교연산자 sql연산자(between a and b / in(a,b,c,d)/ like 형태 escape/ is null )
not
and
or

Cross
Cartesian
Like %


references 테이블(열) on delete cascade >> 참조하겠다 ~를  on 어떤 조건으로 .
delete cascade > 문제생기면 죽이겟다
Delete set null > 문제생기면 null값 주겠다.


varchar 가변길이 문자유형을 의미
Char 고정문자 - 남는공간 띄어쓰기
varchar 가변문자 여기서 띄어쓰기는 의미가 있는거임. 하나의 글자로 인식.
오라클에서는 varchar2 / sql server에서는 varchar 로 쓴다.
numeric - 숫자,  정수실수와같은수.
datetime - 날짜 정보를 해결하기위해서 사용하는 데이터 유형


윈도우함수의 이해-
Select window함수.   ( ) over.  (<행을분할><행을정렬><대상행지정>)
from table ; 윈도우함수 ~에게   ((윈도우함수 지정범위)

window_function over (<partition by 칼럼><order by절><windowing절>)

종류 숙지해두기. ‘


windowing 절 : rows ./ range
Rows
Unbounded preceding 맨윗줄
preceding ; 이전행 (윗방향)위에 위치한 행
current row 현재 행의경우
following 다음행/ 한칸아래행
unbounded following 뒷방향으로  

범위 지정 예제
위와같이 row unbounded preceding 이라고 시작점만 적은 경우 current row까지 자동으로 연산이 된다.

unbounded preeding - current row
Current row - unbounded following

Rows에 대한 예시
rows unbounded preceding > 윈도우 함수의 연산을 맨 위에서부터 현재 행(currunt row)까지
rows unbounded following > 윈도우 한수의 연산을 현재행(current row)에서 맨 아래 행까지 포함해서 계산
rows 1 preceding > 윈도우 함수의 연산은 한칸위 행부터 현재 행(current row )까지 포함해서 계산
rows 2 following > 윈도우 함수의 연산을 현재 행(current row)부터 두 칸 아래 행까지 포함해서 계산.

range > 칼럼의 값을 기준으로 연산에 참여할 행을 선택한다.
range 150 preceding > 현재칼럼의 값을 기준으로 적은 값에서 150 이하로 차이가 나는 행들을 선택적으로 계산
range unbounded preceding > 현재 칼럼의 값(포함하여)을 기준으로 작은 값들을 모두선택하여 계산한다.
range between 150 preceding and 150 following.> 현재 칼럼의 값보다 적은 값에서 150이하로 차이가 나고, 현재 컬럼값 기준으로 큰값에서 150 이상 차이가 나는 행들을 선택한다.
range betwenn unbounced preceding and current row
>현재 칼럼의 값보다 적은 값을 가지는 행부터 현재 행까지 모두 선택한다.
= range unbounded preceding 과 동일하다.

rank > 공동 등수를 수여 , 다음등수를 제거
rank() over (order by sal)
괄호 비워져있음.

dense_rank() over (order bu sal desc)
사람많을때 빽빽할떄 쓰는거임.  중복을 허락함. 언더바 무조건 써야한다 중요!!
Row_number() 등수밀리기 없음. 무조건 등수가 존재한다. 연속하는 등수.

롤