SQLD 자격 대비 요약

ANSI SQL로 변환 

inner join 일반적인 join, 교집합 개념 
cross join : T1, T2의 모든 행을 대응시킨 카테시안 프로덕트 r1xr2 
left outer join : FROM T1 LEFT OUTER JOIN T2    왼쪽 테이블 T1 중심 조인 
right outer join  FROM T1 LEFT OUTER JOIN T2    오른쪽 테이블 T2 중심 조인 
full outer join: left, right join 합집합 

https://velog.io/@gillog/ANSI-SQL%EC%9D%B4%EB%9E%80 

 

순위함수

top()/ top() with ties
   -top(n) : order by 절 필수아님, n개 행만 출력
   -top with ties: order by 절 필수, 중복 데이터까지 출력 

1000, 2000, 2000, 3000
rank()   1 2 2 4
dense_rank() 1 2 2 3
row_number()  1 2 3 4
ratio_to_report()  해당 컬럼값/ 컬럼의 전체 합 

RATIO_TO_REPORT 함수 (tistory.com)

 

 

해쉬조인

-해쉬조인
한 테이블을 기반으로 메모리에 해시테이블 생성, 해시테이블내 행 대응시키기 위해 해시함수 사용
중첩루프 조인 단점인 랜덤 액세스 부하를 피함
https://lee-mandu.tistory.com/470

 

 

집합 연산자(sql set operation)

UNION : 중복을 제거, 결과 합 
UNION ALL: 중복포함 결과합 
INTERSECT 교집합 결과 PostgreSQL에서만? 
MINUS 차집합결과, 첫번째 검색결과에서 두번째 검색결과 제거  

-sql set operation(집합 연산자) 에서 중복제거를 위해 정렬작업을 하지 않는 연산자: UNION ALL 

https://keep-cool.tistory.com/45

 

TCL(Transanction Control Language) 명령어 

트랜잭션 제어 명령어  
-commit 
-rollback 
-savepoint 작업처리 저장점 지정  

 

계층형 데이터 

SELECT 
	LEVEL ,
	LPAD(' ' ,2*(LEVEL-1)) || NAME AS NAME ,
	SYS_CONNECT_BY_PATH(name,'/') AS PATH 
 FROM TEST_TABLE_ONE 
 START WITH PP_LEVEL IS NULL 
 CONNECT BY PRIOR P_LEVEL=PP_LEVEL;​

SYS_CONNECT_BY_PATH 경로로 사용할 열 지정 -START WITH 계층의 최상위가 될 조건 
CONNECT BY PRIOR 계층을 이어주는 조건

SELECT LEVEL
            ,LPAD(' ' ,2*(LEVEL-1)) || NAME AS NAME
            ,CONNECT_BY_ISLEAF AS LEAF
            FROM  TEST_TABLE_ONE
                START WITH PP_LEVEL IS NULL
                CONNECT BY PRIOR P_LEVEL=PP_LEVEL;​

 CONNECT_BY_ISLEAF 계층형 쿼리에서 해당하는 로우가 자식노드가 있는지 없는지 여부를 체크. 자식노드가 있을 경우 0 , 자식노드가 없을 경우 1

SELECT LEVEL
            ,LPAD(' ' ,2*(LEVEL-1)) || NAME AS NAME
            ,CONNECT_BY_ROOT NAME AS ROOT_NAME 
            FROM  TEST_TABLE_ONE
            WHERE LEVEL>=2
                START WITH PP_LEVEL IS NULL
                CONNECT BY PRIOR P_LEVEL=PP_LEVEL;​

CONNECT_BY_ROOT 계층형 쿼리에서 최상위 노드를 찾고자 할 경우

https://devjhs.tistory.com/166
https://devjhs.tistory.com/171
https://devjhs.tistory.com/172 

 

 

 

 

OVER절

--OVER사용X 
SELECT ENAME, JOB, SAL, (SELECT AVG(SAL) FROM scott.EMP) 
FROM scott.EMP; 
--OVER사용 
SELECT ENAME, JOB, SAL, AVG(SAL) OVER() FROM scott.EMP;​

SELECT 번호 
  , 날짜 
  , 수량 
  , SUM(수량) OVER(PARTITION BY 물품 ORDER BY 날짜) AS 재고 
FROM 창고​

group by 쓰지않고 그룹함수 쓸수 있게함 
PARTITION BY 기준으로 합계 


https://wakestand.tistory.com/243 

 

LAG/LEAD 

LAG 명시된 값을 기준으로 이전 로우의 값 반환
LEAD 명시된 값을 기준으로 이후 로우의 값 반환

LAG( 조건 , 순서, 디폴트) OVER ( PARTITION BY 절) 
LEAD( 조건 , 순서, 디폴트) OVER ( PARTITION BY 절)​

https://devjhs.tistory.com/180

 

 

Row Chaining/ Row Migration

Row Chaining : Row에 저장된 데이터의 크기가 너무 커서, 하나의 블록에 다 담지 못하고 두 개 이상의 데이터 블록에 저장되는 것
Row migration : 수정한 데이터를 원래 블록에 저장하지 못하고 다른 블록에 저장하는 것

Row chaining, Row migration (tistory.com)

 

TRIGGER

- trigger :OLD :NEW 

oracle Trigger(오라클 트리거) 정의 :: 댓츠굿 (tistory.com)

coalesce(), nullif(), decode()

SELECT COALESCE( A, B, C) FROM T1;
SELECT NULLIF(1,1) FROM DUAL;
SELECT DECODE(GENDER, 'M', '남자', 'F', '여자', '기타') FROM T1;

COALESCE() : NVL()과 비슷함. NVL과 달리 파라미터 개수가 정해지지 않음. 파라미터 중 처음으로 NULL 아닌 값을 리턴
NULLIF() : 두 인수가 값으면 null, 그렇지 않으면 첫번째 인수 반환.
DECODE() : 첫번째 인수가 2번째 인수와 일치하면 3번째 인수 반환. 2+2 , 3+2인수에 같은 규칙 적용. 마지막 인수는 어느 경우에도 해당되지 않을때 반환. ELSE 반환 값은 옵션.



[오라클/SQL] DECODE 기본 및 예제 : 조건에 따라 각각 다른 값을 입력/출력하는 분기함수 (* 타 프로그래밍 언어에서의 IF ~ THEN, IFELSE 명령어) : 네이버 블로그 (naver.com)
SQL Server NULLIF() 함수 (w3hmong.com)
[오라클 MySQL] COALESCE 두 개 컬럼 중 존재하는 값으로 합치고 싶을 때, 컬럼 병합하는 함수, 여러 열 NULL아닌 컬럼 찾기 (tistory.com)

 

-sql 실행순서? 일반select  
explain plan for 
-nested loop join, table access 수행문 실행순서 

 

-index? 
-view특징 
-분산 데이터베이스 ~투명성??? 

-unique index scan ?? 

 

그룹함수

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM 
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;​

rollup() : department_id, job_id 별 합계 산출

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY CUBE(JOB_ID, DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID;​

cube(): department_id, job_id별 합계 + 종합 합계 산출

SELECT
    DEPARTMENT_ID
    ,JOB_ID
    ,SUM(SALARY)
FROM
    EMPLOYEES
WHERE DEPARTMENT_ID > 80
GROUP BY GROUPING SETS((DEPARTMENT_ID, JOB_ID), ());

 grouping set()  : 출력 원하는 합계 기준을 두번째 인자에 입력. () 입력하면 종합 합계만 산출

[Oracle] 오라클 GROUP BY ROLLUP, CUBE, GROUPING SETS 정리 :: 마이자몽 (tistory.com)