내가 보려고 만든 sqld 정리

NVL(값1, 값2) - 값1이 NULL 이면 값2, 아니면 값1 (null or value) 

COALESCE(값1, 값2) - 앞에 있는 값부터 NULL이 아닌 값이 있으면 그거 반환

NULLIF(값1, 값2) - 값1==값2 이면 NULL, 아니면 값1

DECODE(expr, search, result) - expr이 search와 같으면 result, 아니면 NULL

CASE THEN - 순서대로 조건 검사 후 처음으로 만족하는 조건의 결과값 반환

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ELSE 기본값
END

2.WHEN THEN ELSE END 에서 ELSE를 안쓴다면?

ELSE 조건을 따로 만들어놓지 않는다면 조건에 만족하지않는 모든 튜플들은 NULL이 된다.
위 문제와는 다른 내용이긴 하지만 NULL값은 = 와 같은 연산자와의 리턴이 전부 다 FALSE(UNKNOWN)이다.

EXISTS vs NOT EXISTS

EXISTS	서브쿼리에 결과가 있으면 참
NOT EXISTS	서브쿼리에 결과가 없으면 참

NOT EXISTS = 서브쿼리에 해당하지 않는 값

서브쿼리를 사용할 수 있는 6가지 주요 위치

위치	예시	설명
① SELECT절	SELECT (SELECT MAX(price) FROM 상품) AS 최고가	컬럼처럼 사용
② FROM절 (인라인 뷰)	FROM (SELECT * FROM 고객 WHERE 지역='서울') AS 서울고객	가상의 테이블처럼 사용
③ WHERE절	WHERE id IN (SELECT 고객ID FROM VIP고객)	조건으로 사용
④ HAVING절	HAVING SUM(금액) > (SELECT AVG(총합) FROM 주문요약)	그룹 조건 필터
⑤ JOIN절	JOIN (SELECT * FROM 주문 WHERE 상태='완료') AS 완료주문 ON ...	조인 대상 서브쿼리
⑥ WITH절 (공통 테이블 식, CTE)	WITH 최근주문 AS (SELECT ... ) SELECT * FROM 최근주문	재사용 가능한 서브쿼리

• DISTINCT → 출력 결과에서 중복을 잠시 제거해서 보여주는 것
• UNIQUE → 아예 중복된 값을 넣지 못하게 하는 데이터베이스의 규칙
• 시험에서 “조회 결과에서 중복 제거”라고 하면 → 정답은 DISTINCT

연산자	의미	중복 제거
UNION	합집합	O
INTERSECT	교집합	O
EXCEPT	A - B (A에만 있는 값)	O
MINUS	A - B (Oracle)	O

ORDER BY 기본은 오름차순임.

계층형 쿼리란?

Oracle DB  부모-자식 관계를 가진 데이터를 트리 구조로 조회하는 SQL

SELECT LEVEL, 사원명, 사번, 상사사번
FROM EMP
START WITH 상사사번 IS NULL
CONNECT BY PRIOR 사번 = 상사사번;

START WITH	시작 조건 (루트 노드 지정)	어디서부터 시작할지
CONNECT BY	부모-자식 관계 정의	계층 구조 만들기
PRIOR	관계의 방향 지정	→ 이게 순방향/역방향을 결정
LEVEL	계층의 깊이 표시	1부터 시작하는 트리 레벨

• CONNECT BY PRIOR 자식 =  부모 ==  부모 → 자식 (순방향)

CUBE란?

GROUP BY의 확장 기능으로, 모든 가능한 조합의 소계 + 전체 합계까지 포함한 집계 결과를 만들어주는 기능입니다.
모든 컬럼에 모든 경우의 다차원 집계를 내줌.

ROLLUP

ROLLUP함수는 소그룹간의 합계를 계산하는 함수임. GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 구함.

SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP (region, product);

ROLLUP 함수 사용

체크 제약 조건은 특정 조건을 만족해야 한다

• CHECK 제약 조건은 해당 컬럼의 값이 조건식을 만족해야만 삽입됨

CHECK (나이 >= 0 AND 나이 <= 130)

ROLE은 SQL 명령어이다	X	ROLE은 객체이지 명령어가 아님
ROLE은 권한의 집합체를 의미한다	O
ROLE은 GRANT로 생성된다	X	생성은 CREATE ROLE
ROLE은 REVOKE로 삭제된다	X	삭제는 DROP ROLE

NATURAL JOIN이란?

두 테이블 간에 같은 이름을 가진 컬럼을 자동으로 찾아
그 컬럼을 기준으로 조인하는 SQL 문법입니다.

ON DELETE RESTRICT - 외래키 연결된 행 삭제 못 하게 막는 옵션
ON DELETE CASCADE - 외래키 연결된 행 같이 삭제 되는 옵션

트랜잭션/고립성

Dirty Read: 커밋되지 않은 데이터를 읽음
Non-Repeatable Read: 같은 쿼리를 두 번 했는데 값이 바뀜
Phantom Read: 같은 조건인데 행 수가 늘어남

연산자

• Oracle: || (문자열 연결 연산자), CONCAT() 함수.
• MySQL: CONCAT() 함수, CONCAT_WS() 함수 (구분자 포함).

날짜/시간 함수

SYSDATE	Oracle 등	O 포함	X 비표준
GETDATE()	SQL Server	O 포함	X 비표준
NOW()	MySQL 등	O 포함	X 비표준
CURRENT_DATE	모든 DB 지원	X 시간 없음	O 표준 SQL

 

항목	TRUNCATE	DELETE
삭제 대상	전체 데이터만 삭제	조건 지정 가능 (WHERE절 사용 가능)
WHERE 절	X 사용 불가	O 사용 가능
롤백 가능 여부	X (일반적으로 롤백 불가)*	O 트랜잭션 안에서 롤백 가능
속도	O 빠름 (테이블 초기화 수준)	X 느림 (한 행씩 삭제)
로그 기록	최소 로그 (DDL처럼 처리)	모든 행에 대해 로그 기록
트리거 작동	X 트리거 작동안 함	O 트리거 작동
제약조건	외래키 제약 있으면 실행 불가	조건에 따라 외래키 무시하고 일부 삭제 가능
AUTO_INCREMENT	O 초기화됨 (MySQL 기준)	X 초기화 안됨

ROUND('34124.1',-2)

• 자릿수가 양수: 소수점 아래에서 반올림
  예: ROUND(123.456, 2) → 123.46
• 자릿수가 0: 정수로 반올림
  예: ROUND(123.456, 0) → 123
• 자릿수가 음수: 정수 부분의 왼쪽 자리에서 반올림
  예: ROUND(1234, -2) → 1200

REGEXP_LIKE

이메일 형식이 맞는 행만 추출하는 SQL문

SELECT *
FROM users
WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z]+\.[a-z]{2,}$');

(전화번호 검증)

SELECT *
FROM contacts
WHERE REGEXP_LIKE(phone_number, '^010-[0-9]{4}-[0-9]{4}$');

PIVOT

행을 열로 바꿈.

SELECT *
FROM sales
PIVOT (
  SUM(price) FOR month IN ([1], [2], [3])
) AS p;

UNPIVOT

열을 행으로 바꿈.

SELECT *
FROM monthly_sales
UNPIVOT (
  price FOR month IN (Jan, Feb, Mar)
);

UNION(DISTINCT) vs UNION ALL

union - 중복 제거
union all - 중복 포함

연관 서브쿼리

SELECT 사원명
FROM 사원 A
WHERE 급여 > (
  SELECT AVG(급여)
  FROM 사원
  WHERE 부서번호 = A.부서번호
);

서브쿼리 안에서 메인 쿼리의 컬럼(A.부서번호)을 사용하고 있음
=> 서브쿼리가 메인 쿼리의 한 행 한 행을 기준으로 달라짐.

일반 서브쿼리

SELECT *
FROM 사원
WHERE 부서번호 IN (SELECT 부서번호 FROM 부서 WHERE 지역 = '서울');

 

EXISTS는 존재 확인용 if 조건문이고,

"해당 행을 포함한다"는 건 WHERE절 조건이 참이라서 해당 행이 선택된 것일 뿐이에요.

EXISTS (서브쿼리)

• 이건 "서브쿼리가 결과를 한 줄이라도 반환하면 TRUE, 아니면 FALSE"예요.
• 즉, 존재하면 참(True), 존재하지 않으면 거짓(False)

=> 조건절로만 사용됩니다.

인라인뷰(Inline View) - 마치 하나의 테이블처럼 사용

인라인 뷰(Inline View) = 동적 뷰(Dynamin View)
단일 행 서브쿼리 = 스칼라 서브쿼리(Scalar)

---

GROUPTING SETS 예시 테이블

지역	상품	매출
서울	A	100
서울	B	150
부산	A	120
부산	B	180

 

GROUPING SETS (지역, 상품)

→ 지역 단독으로 집계 + 상품 단독으로 집계 (두 번 집계)

SELECT 지역, 상품, SUM(매출) FROM 매출표 GROUP BY GROUPING SETS (지역, 상품);

결과

지역상품매출

서울	NULL	250
부산	NULL	300
NULL	A	220
NULL	B	330

 

→ 지역 기준 총합, 상품 기준 총합이 별도로 집계

GROUPING SETS ((지역, 상품))

→ 지역 + 상품 조합으로만 집계 (그룹 하나만)

SELECT 지역, 상품, SUM(매출) FROM 매출표 GROUP BY GROUPING SETS ((지역, 상품));

결과

지역상품매출

서울	A	100
서울	B	150
부산	A	120
부산	B	180

 

→ GROUP BY 지역, 상품과 같은 효과

SELECT 지역, 상품, SUM(매출)
FROM 매출표
GROUP BY GROUPING SETS (지역);

 

결과

지역	상품	매출
서울	NULL	250
부산	NULL	300

 

연관 관계 = 멤버변수
의존 관계 = 오퍼레이션의 파라미터

관계 표기법 - 관계성, 관계차수, 선택성(선택사양)

발생 시점에 따른 엔터티 분류

기초 엔터티	업무 수행에 기본이 되는 데이터 (변하지 않음) 예: 사원, 고객, 상품
중심 엔터티	업무가 발생할 때마다 생기는 데이터 (이벤트 중심) 예: 주문, 계약
행위 엔터티	중심 엔터티가 발생한 결과 (성과, 이력) 예: 매출, 정산, 거래이력

next_day : 첫번째로 오는 요일

SELECT NEXT_DAY(ADD_MONTHS (sysdate,6),‘월요일’) FROM dual;

옵티마이저란? - 내비게이션

SQL 문을 실행하기 전에:

1. 여러 실행 경로(조인 순서, 인덱스 사용 여부 등)를 고려해서
2. 가장 비용이 적게 드는 실행 계획(Execution Plan)을 선택하는 엔진

서브쿼리 사용 X = GROUP BY, ORDER BY

NESTED SUBQUERY (중첩 서브쿼리)

❝서브쿼리가 한 번만 실행되고, 그 결과를 메인쿼리가 참조❞

특징

• 메인쿼리와 독립적 (서브쿼리는 메인쿼리의 값을 사용하지 않음)
• 먼저 실행된 후, 메인쿼리 조건에 사용됨

예시

SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

CORRELATED SUBQUERY (상관 서브쿼리)

❝서브쿼리가 **메인쿼리의 값을 사용하며, 메인쿼리의 각 행마다 반복 실행❞

특징

• 메인쿼리와 의존 관계 있음
• 메인쿼리의 각 행마다 서브쿼리가 다시 실행됨

예시

SELECT e1.ename, e1.sal FROM emp e1 WHERE sal > ( SELECT AVG(sal) FROM emp e2 WHERE e2.deptno = e1.deptno ); 

데이터 모델링의 세 가지 관점
1. 데이터 관점 : 업무가 어떤 데이터와 관련이 있는지 또는 데이터 간의 관계는 무엇인지에 대해서 모델링 하는 방법(What, Data)
2. 프로세스 관점 : 업무가 실제로 하고 있는 일은 무엇인지 또는 무엇을 해야 하는지를 모델링 하는 방법(How, Process)
3. 데이터와 프로세스의 상관 관점 : 업무가 처리하는 일의 방법에 따라 데이터는 어떻게 영향을 받고 있는지 모델링하는 방법(Interaction)

TO_CHARD는 '201501'이라면 1월 달 내내를 말함.
TO_DATE는 '201501'이라면 2015010100000을 말함. = 기본값으로 넣어버림. 특정 초까지를 말하게 되어버림.

GROUP BY 표현식 유의사항

SELECT 부서, 급여 FROM 지역별매출 GROUP BY 부서; #문제됨

GROUP BY로 묶지 않은 칼럼은 SELECT 절에서 그냥 쓰지 못함!
→ 반드시 집계 함수로 감싸야함.

SELECT 부서, SUM(급여) FROM 지역별매출 GROUP BY 부서; #가능!

위의 쿼리는 문제가 없음.

USING 조건절이란?

두 테이블 간 같은 이름을 가진 컬럼을 기준으로 JOIN할 때 쓰는 간결한 문법

COUNT - NULL

COUNT(*) → 전체 행 수 (NULL 포함)
COUNT(컬럼) → NULL 제외
COUNT(CASE ...) → NULL이 결과이면 제외함