수테크📱⌚️👀

***  일반 + 신유형

 

oracle에서 빈문자열은 null로 저장 / sql server에서는 빈문자열 그대로 저장되어 

4번은 null이 아니자나... 그래서 조회가 안됩니다...

 

논리 연산자 우선순위 : NOT > AND > OR

 

null 은 포함하지 않고 계산하면

 

1. (20 + 0) / 2 = 10

2. (20 + null (x)) / 1  = 20

3. (20 + null (x)) / 1  = 20

 

NVL(대상데이터 , NULL일때 데이터)

값이 null 인 경우, 2번째 지정값을 출력하고 그렇지 않으면 대상데이터를 출력한다.

 

SELECT TOP(3) WITH TIES 팀명, 승리건수   // TOP(3) 만 적는다면 그냥 3개만 출력
	FROM 팀별 성적
    ORDER BY 승리건수 DESC;

 

=> 3번째 행과 동일한 값을 가진 추가 행이 있다면 이 행들도 포함(SQL Server에서 사용)

 

SELECT 팀명. 승리건수
	FROM 팀별성적
    WHERE ROWNM <= 3
    ORDER BY 승리건수 DESC;

 

=> 3번째 행과 동일한 값을 가지 추가행이 있더라도 포함 x (Oracle에서 사용)

=> Oracle에서서 TOP(3) WITH TIES와 유사한 기능을 구현하려면 DENSE_RANK()의 윈도우 함수를 사용할 수 있다

 

 

RANK()와 DENSE_RANK()의 차이....

 

EmployeeIDNameSalary

1	Alice	5000
2	Bob	4000
3	Carol	4000
4	Dave	3000
5	Eve	2000

RANK() 사용 예시

SELECT *
FROM (
    SELECT e.*, RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees e
)
WHERE rnk <= 3;

 

EmployeeIDNameSalaryrnk

EmployeeID	Name	Salary	rank
1	Alice	5000	1
2	Bob	4000	2
3	Carol	4000	2
4	Dave	3000	4

 

RANK() 함수는 Salary가 같은 Bob과 Carol에게 동일한 2순위를 부여하고, 다음 순위인 3을 건너뛰고 4를 Dave에게 부여

DENSE_RANK() 사용 예시

SELECT *
FROM (
    SELECT e.*, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees e
)
WHERE rnk <= 3;

 

EmployeeIDNameSalaryrnk

1	Alice	5000	1
2	Bob	4000	2
3	Carol	4000	2
4	Dave	3000	3

DENSE_RANK() 함수는 Salary가 같은 Bob과 Carol에게 동일한 2순위를 부여하고, 다음 순위를 3으로 부여하여 연속성을 유지합니다.

 

• RANK(): 동일한 값을 가진 행들이 있으면 순위에 공백이 생깁니다.
• DENSE_RANK(): 동일한 값을 가진 행들이 있더라도 순위에 공백이 생기지 않고 연속적으로 부여됩니다.

 

oracle은 수정후에 create하면 자동으로 저장된다

    

헷갈리기 쉬운 문제...

ROWNUM 을 정렬 전에 쓰면 아무 의미 없다....

정답은 3번

 

' \d+', 'xxx', 1, 2 => 문자를 xxx로 바꾸는데 처음부터 시작해서 2번째 위치꺼를 REPLACE 바꿔라

 

 

*** 노랭이 모르는거

 

TCL :  논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업 단위(Transaction) 별로 제어하는 명령어인 Commit, Rollback, Savepoint 등이 여기에 해당하며, 일부에서는 DCL로 분류하기도 한다

 

UNIQUE는 NULL 입력이 가능하다!!! PK는 불가능함 ㅋㅋㅋ

 

 

****  노랭이 정리

 

데이터 모델링

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

2. 논리적 데이터 모델링 : 시스템으로 구축하고자 하는 업무에 대해 Key 속성, 관계 등을 정확하게 표현, 재사용성이 높음

3. 물리적 데이터 모델링 : 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계 

 

 

데이터 모델링을 하는 주요한 이유

1. 업무정보를 구성하는 기초가 되는 정보들에 대해 일정한 표기법에 의해 표현함으로써 정보시스템 구축의 대상이 되는 업무 내용을 정확하게 분석하기 위해서

2. 분석된 모델을 가지고 실제 데이터베이스를 생성하여 개발 및 데이터 관리에 사용하기 위해서

 

=> 데이터 모델링 이라는 것은 단지 데이터베이스만을 구축하기 위한 용도로 쓰이는 것이 아니라 데이터모델링 자체로서 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가짐

 

데이터 모델링 유의점 : 중복, 비유연성, 비일관성

 

데이터베이스 스키마 구조 3단계

- 외부 스키마 : 사용자가 보는 데이터베이스의 부분적 뷰를 정의

- 개념 스키마 : 통합 관점의 스키마 구조를 표현한 것으로 데이터 모델링은 통합관점의 뷰를 가지고 있는 개념 스키마를 만들어가는 과정임.

- 내부 스키마 : 데이터의 물리적 저장 구조를 정의

 

ERD 작성 순서

1. 엔터티를 그린다

2. 엔터티를 적절하게 배치한다

3. 엔터티 간 관계를 설정한다

4. 관계명을 기술한다

5, 관계의 참여도를 기술한다

6. 관계의 필수여부를 기술한다

 

엔터티의 특징

1.반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야 한다

2. 유일한 식별자에 의해 식별이 가능해야 한다

3. 영속적으로 존재하는 (두개 이상의)인스턴스의 집합이어야 한다

4. 엔터티는 업무 프로세스에 의해 이용되어야 한다

5. 엔터티는 반드시 속성이 있어야 한다

6. 엔터티는 다른 엔터티와 최소 한 개 이상의 관계야 있어야 한다 

 

기본엔터티(키엔터티) 

- 원래 존재하는 정보로서 다른 엔터티와의 관계에 의해 생성되지 않고 독립적으로 생성이 가능하고 자신은 타 엔터티의 부모의 역할을 하게 된다

- 다른 엔터티로부터 주식별자를 상속받지 않고 자신의 고유한 주식별자를 가지게 된다

- 예를 들어 사원, 부서, 고객...

-  vs) 중심엔터티(메인엔터티) : 데이터 모델에서 다른 여러 엔터티와 관계를 맺는 중심적인 역할을 하는 엔터티로 여러 엔터티와의 관계를 맺고 관계를 통해 데이터를 연결한다

 

속성(Attribute) 

: 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

=> 업무상 관리가 가능한 최소의 의미 단위(엔터티의 한 분야)

 

속성의 특성에 따른 분류

- 기본속성 :  엔터티가 본질적으로 가지고 있는 속성('고객' 엔터티의  '이름', '주소', '전화번호')

- 설계 속성 : 데이터베이스 설계를 위해 추가된 속성('주문' 엔터티의 '주문번호', '고객ID')

- 파생 속성 : 다른 속성 값에서 계산되거나 유도되는 속성, 조회할 때 빠른 성능을 내도록 함('주문' 엔터티의 총금액)

 

 속성의 도메인

:  각 속성은 가질 수 있는 값의 범위가 있는데 이를 속성의 도메인이라하며, 엔터티 내에서 속성에 대한 데이터타입과 크기 그리고 제약사항을 저장하는 것

 

두개의 엔터티 사이에 체크해야 할 사항

1. 두 개의  엔터티 사이에 관심있는 연관규칙이 존재하는가?

2. 두 개읜 엔터티 사이에 정보의 조합이 발생하는가?

3. 업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?

4. 업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?

 

주식별자를 저장할 때 고려해야 할 사항

- 주식별자에 의해 엔터티내에 모든 인스턴스들이 유일하게 구분되어야 한다(유일성)

- 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다(최소성)

- 지정된 주식별자의 값은 자주 변하지 않는 것이어야 한다(불변성)

- 주식별자가 지정이 되면 반드시 값이 들어와야 한다(존재성)                                                                                                                                                                                                                                                                              

식별자의 종류

- 엔터티 내에서 대표성을 가지는가에 따라 주식별자와 보조식별자로 구분

- 엔터티 내에서 스스로 생성되었는지 여부에 따라 내부식별자와 외부식별자로 구분

- 단일 속성으로 식별이 되는가에 따라 단일식별자와 복합 식별자로 구분

- 원래 업무적으로 의미가 있던 식별자 속성을 대체하여 일련번호와 같이 새롭게 만든 식별자를 구분하기 위해 본질식별자와 인조식별자로 구분

 

성능 데이터 모델링 수행 절차에 대한 설명..

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

2. 데이터베이스 용량산정을 수행

3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악

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

5. 이력모델의 조정, PK/FK 조정, 슈퍼 타입/서브타입 조정 등을 수행

6. 성능관점에서 데이터모델을 검증                                                                                                                                         

 

 

 

데이터 모델링의 이해

• 데이터 모델링 개요
• 엔터티
• 속성
• 관계 with ERD
• 식별자
• 성능 데이터 모델링
• 정규화
• 반정규화
• 기타 말문제 개념 정리

 

1) 데이터 모델링의 특징

• 추상화 : 현실 세계를 일정한 형식에 맞춰 표현하기 위함
• 정확화 : 누구나 이해할 수 있도록 현상을 기술
• 단순화 : 복잡한 현실을 이해하기 쉽게 제한된 언어와 표기법으로 단순하게 표현하기 위함
  >>> 현실세계를 단순하게 추상화하여 정확하게 공유하는 작업이 데이터 모델링!!
• 정보시스템을 구축하기 위한 업무 분석 기법
• 현실세계의 데이터에 대해 약속된 표기법으로 표현
• 데이터베이스를 구축하기 위한 분석과 설계를 위한 과정

 

데이터 모델링의 유의사항

• 중복성 :  중복을 최소화해야 함(같은 정보를 여러번 저장하는 것은 지양)
• 비유연성 : 데이터의 정의 <=> 데이터 사용 프로세스, 프로그램 <=> 테이블 간의 연계성을 줄여서, 
           데이터 혹은 프로세스의 변화가 데이터베이스에 중대한 변화를 초래하지 않도록 유연하게 관리해야 함
• 비일관성 : '약속된 표기법'을 준용하는 등 업무를 명확하게 정의해서 정보 관리의 일관성을 최대화 해야함

 

데이터 모델링의 단계

• 개념적 데이터 모델링 : 추상화 수준이 높음, 전사적 모델링 시 많이 함, 포괄적 수준의 모델링
• 논리적 데이터 모델링 : Key, 속성, 관계 등을 정확히 기술하는 모델링
• 물리적 데이터 모델링 : 실제로 데이터 베이스에 이식 가능하도록 저장, 성능 등 물리적인 특성을 고려한 모델링

 

데이터베이스 스키마 구조 3단계

• 외부스키마 : [사용자 관점] 응용프로그램을 사용하는 개별 사용자 관점으로 데이터베이스를 정의하는 구조
• 개념스키마 : [통합 관점] 모든 사용자의 관점을 통합한 조직 전체의 관점의 통합적 데이터 베이스 구조
• 내부스키마 : [개발자 관점] 어떤 데이터를 어떻게 저장할 것인지 데이터베이스의 물리적 저장 구조

 

데이터의 구성

 

엔터티(Entity) 중요!!!

• 해당 업무에서 필요하고 관리해야하는 정보여야 함
• 유일한 식별자에 의해 식별이 가능해야 함 (ex 회원 테이블 상 고객번호)
• 하나의 엔터티는 반드시 2개 이상의 인스턴스를 가져야 함
• 하나의 엔터티는 반드시 속성이 있어야 함 (2개 이상)
• 하나의 엔터티는 반드시 1개 이상의 관계가 있어야 함 (통계성, 코드성 엔터티 제외)

 

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

• 기본엔터티 : 독립적 생성되는 엔터티
• 중심엔터티 : 기본 엔터티로부터 발생하고 행위 엔터티를 생성함
• 행위엔터티 : 기본, 중심 엔터티 등 2개 이상의 엔터티로부터 발생함

 

- 물리적 형태에 따른 엔터티 분류

• 유형엔터티 : 물리적 형태가 있는 엔터티
• 개념엔터티 : 물리적 형태는 없지만 관리해야하는 개념적 정보
• 사전엔터티 : 비즈니스 프로세스 중 생기는 엔터티

 

 

속성(Column)

• 인스턴스에서 관리하고자 하는 의미가 더 이상 부리되지 않는 최소의 데이터 단위
• 1개의 속성은 1개의 속성값을 가진다
• 기본속성 / 설계속성 / 파생속성
  - 설계 속성 : 코드성 컬럼, 일련번호 등 규칙화를 위한 속성
  - 파생 속성 : 계산된 값 (테이블 내에 적을수록 좋음)
  - 기본 속성 : 업무상 바로 정의가 가능한 속성(이름, 계좌번호, 주문일자..)

 

EX) 은행은 고객에게 지급되는 월단위 '이자'를 관리하고자 한다. 이때 이자는 어떤 속성일까? 파생속성

ex) 그렇다면, 예금 분류에 따라 관리되는 '이자율'은 어떤 속상일까? 기본속성

 

• 각 속성이 가질 수 있는 값의 범위를 속성의 '도메일'이라고 함 (데이터 타입, 크기, 제약사항 등 지정)
  ex) 은행계좌 라는 엔터티가 있을 때, 계좌명이라는 속성은 길이가 20자리 이내의 문자열로 정의

 

관계 with ERD(Entity-Relationship Model)

• 하나의 엔터티는 1개 이상의 관계를 가져야 함(통계성, 코드성 엔터티 제외)
• 관계의 구분 : 행위에 의한 관계, 존재에 의한 관계
  >> ERD 에서는 두 관계를 구분없이 동일한 표기법으로 표현함
  (단, 클래스다이어그램에서는 구분하여 연관관계와 의존관계로 실선과 점선의 표기법으로 다르게 표현함)

 

* 두 개의 엔터티 사이에서 관계를 도출할 때 체크해야 하는 사항

• 관심있는 연관규칙 여부
• 정보의 조합 여부
• 업무기술서, 장표에 관계연결을 가능하게 하는 동사 여부
• 업무기술서, 장표에 관계연결에 대한 규칙 서술 여부

 

* ERD 작성순서

1) 엔터티 도출 & 그리기

2) 엔터티 배치(가장 중요한 엔터티를 좌측 상단에 배치, 추가 발생하는 엔터티는 우측과 하단에 배치)

 

3) 엔터티 간의 관계를 설정

4) 엔터티 간의 관계명 기술

 

 

5) 관계의 참여도 기술(1:1, 1:N, M:N)

6) 관계의 필수여부 기술(필수적 관계 : I, 선택적 관계 : O)

 

 

식별자 구분

 

대표성 여부

• 주식별자 : 유일성, 최소성, 대표성을 만족하는 식별자이며 타 엔터티와 참조 관계로 연결될 수 있다.(Primary Key)
• 보조식별자 : 유일성, 최소성은 만족하지만 대표성을 갖지 못하는 식별자이다.

속성의 수

• 단일 식별자
• 복합 식별자 : 2갸 이상의 속성을 구성된 식별자

 

 

스스로 생성 여부

• 내부 식별자 : 엔터티 내에서 생성된 식별자
• 외부 식별자 : 타 엔터티와의 관계를 통해 받아오는 식별자(Foreign Key)

대체 여부

• 본질 식별자 : 업무에 의해 만들어지는 식별자
• 인조 식별자 : 복잡한 원조 식별자 대신에 인위적으로 만들어진 식별자(ex. 일련번호)

 

주식별자

• 유일성 : 주식별자에 의해 엔터티 속 모든 인스턴스들이 유일하게 구분될 수 있어야 함
• 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
• 불변성 : 지정된 주식별자의 값은 자주 변하지 않는 것이어야 함
• 존재성 : 주식별자가 지정되면 반드시 값이 있어야 함(NULL)

 

2) 데이터 모델과 성능

 

성능 데이터 모델링

• 데이터베이스의 성능 향상을 위해 분석/설계 단계부터 성능과 관련된 사항이 데이터 모델리에 반영되도록 하는 것
• 데이터의 증가가 빠를수록 성능 개선 비용이 증가함
• 설계단계부터 고려할 셩우 비용 최소화가 가능함

 

성능 데이터 모델링 순서

1. 데이터 모델링 시 정규화를 정확히 수행함
2. 데이터베이스 용량을 산정함
3. 데이터베이스에 발생하는 트랜잭션 유형을 파악함
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행함
5. 이력모델, PK/FK 조정, 슈퍼타입/서브타입 조정을 수행함

 

정규화란?

• 데이터를 분해하는 과정
• 목적 : 데이터의 중복을 최소화하고, 유연성을 가지며, 변경을 최소화하고, 데이터가 일관될 수 있도록 하는 방법
• 제 1~5 정규화가 있지만, 주로 1~3 정규화까지 수행함

 

* 제1정규화

• 기본키를 설정하는 과정
• 속성의 원자성과 엔터티의 유일성을 확보함
• 모든 속성이 식별자에 종속되어야 함
  X가 변하면 Y가 변한다(T는 X에 함수적으로 종속됨)
  식별자가 변하면 속성이 변함
• 속성값의 중복 여부 체크! (단일 -> 복합 순)

 

* 제2정규화

• 기본키가 2개 이상으로 구성될 경우 수행함
• 모든 속성(지역명, 점주명, 메뉴명, 출시일, 가격)이 식별자에 종속해야하며, 아니면 분해해야 함
• 부분함수 종속성을 분해함
• 주식별자 중 일부로부터 독립하여 1:M 의 관계로 재설계

 

* 제 3정규화

• 기본키 외에 컬럼간 종속성을 제거함
• 이행 함수 종속성 제거

 

반정규화

• 정규화를 했을 때 성능이 더욱 악화될 경우, 데이터의 중복을 허용하고 조인을 줄여서 성능을 향상시키는 방법

 

반정규화 기법(컬럼 & 테이블)

* 컬럼 추가 - 중복 컬럼 추가, 파생컬럼 추가(ex. 계산된 컬럼), 이력테이블 컬럼 추가, PK에 의한 칼럼 추가 등

ex) 중복 컬럼 추가
      A 테이블에 있는 a, b 정보를 조회할 때, C 테이블의 c 정보와 D 테이블의 d정보를 늘 함께 조회한다면?

     >>> 과도한 조인을 줄이기 위해 A 테이블에 c, d 컬럼을 추가하여 조회 성능을 향상시킨다.

 

ex) 파생컬럼 추가

      여러 테이블을 JOIN해서 얻을 수 있는 정보 값이 자주 사용된다면?

      >>> 미리 계산된 컬럼을 추가해둔다. ex) SUM(단기)

 

 

* 테이블 병밥 / 테이블 분할 / 테이블 추가

• 테이블 병합 : 1:1관계 테이블 병합, 1:M 관계 테이블 병합, 슈퍼/서브타입 테이블 병합
  * 슈퍼 타입(회웜) <=> 서브 타입(개인회원, 법인회원)
• 테이블 분할
  * 수직분할 : [a, b, c, d] 컬럼 -> [a,b] [c,d] 로 분리하여 보관
  * 수평분할 : 하나의 테이블에 있는 값을 기준으로 테이블 분할(Partition 기법)
  ex. 메일 쌒이는 고객별 위치 정보를 한 테이블에 넣어두면 엑세스 범위가 넓음 -> 연도별로 쪼개서 저장
• 테이블 추가 : 중복테이블, 통계테이블, 이력테이블, 부분테이블

 

기타 성능관련 말문제 개념 정리

 

1. 한 테이블에 너무 많은 칼럼이 존재하며 로우체이님이 발생하는 등 조회성능 저하가 발생한다면
=> 트랜잭션이 접근하는 컬럼 유형을 분석해 테이블을 분리하면, 디스크 I/O가 줄어서 성능이 향상될 수 있다.

2. WHERE문을 통해 조회를 할 때 자주 사용되는 PK 순으로 순서를 변경하고 인덱스를 생성하는 것이 성능에 유리할 수 있다
ex) WHERE 지역명 = '서울' AND 출시일 BETWEEN '20220101' AND '20220331'
-> (1) 상수값으로 조회하는 PK(2) 범위로 조회하는 PK 순
-> 앞쪽에 위치한 속성의 값이 가급적 '=', 최소한 범위를 나타내는 'BETWEEN', '<' '>'가 들어와야 인덱스를 사용할 수 있다

3. 트랜잭션의 통합 / 개별 처리 케이스
- 데이터 전체를 대상으로 일괄 처리하는데 테이블이 분리되어 개별로 관리하면 오히려 연산량을 늘릴 수 있고
- 데이터를 개별로 처리하는데 테이블은 하나로 통합되어 관리하면 불필요하게 많은 데이터의 집적으로 성능이 저하될 수 있음
-> 트랜잭션을 통합하여 처리하면 통합해두는 것이 성능에 좋고, 개별로 처리하면 개별로 유지하는 것이 좋다

 

 

 

1) JOIN : 테이블 간의 결합 / 집합과 유사함!

*교집합 & 합집합 

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• (FULL) OUTER JOIN

 

* 합집합

- UNION (ALL)

 

* 차집합

- MINUS (#orable) = EXCEPT (#SQL Server)

 

* 결합되는 대상간의 일치 정도

- EQUI 조인 <> non-EQUI 조인

 

* 조건구 없는 CROSS JOIN(상호 결합)

 

INNER JOIN

 

INNER JOIN 표현 방식 2가지

1번
SELECT A.*, B.연령
FROM GENDER A INNER JOIN AGE B
         ON A.회원코드 = B.회원코드
WHERE A.성별 = 'F';

2번.
SELECT A.*, B.연령
FROM GENDER A, AGE B
WHERE A.회원코드 = B.회원코드
           AND A.성별 = 'F';

 

 

LEFT (OUTER) JOIN

 

 

LEFT (OUTER) JOIN 표현방식 2가지

1번
SELECT A.*, B.연령
FROM GENDER A LEFT JOIN AGE B
        ON A.회원코드 = B.회원코드;

2번
SELECT A.*, B.연령
FROM GENDER A, AGE B
WHERE A.회원코드 = B.회원코드(+);

 

 

RIGHT (OUTER) JOIN

 

 

RIGHT (OUTER) JOIN 표현방식 2가지

1번
SELECT B.회원코드, 성별, 연령
FROM GENDER A RIGHT JOIN AGE B
          ON A.회원코드 = B.회원코드;

2번
SELECT B.회원코드, 성별, 연령
FROM GENDER A, AGE B
WHERE A.회원코드(+) = B.회원코드;

 

 

(FULL) OUTER JOIN

 

(FULL) OUTTER JOIN  표현방식 2가지

1번.
SELECT A.*, B.연령
FROM GENDER A FULL OUTER JOIN AGE B
         ON A.회원코드 = B.회원코드;

2번.
SELECT A.*, B.연령
FROM GENDER A , AGE B
WHERE A.회원코드(+) = B.회원코드(+);
# Oracle9 버전 이후부터는 양쪽의 (+) 가능

 

 

예제1)

 

 => 정답!!

 

SELECT *

FROM T1 [ LEFT OUTER JOIN ] T2

ON T1.c2 = T2.c2;

 

 

UNION, UNION ALL - 두 플랫폼 기업이 합병할 떄!

 

예제2)

=> 정답!!

A. JOIN

B. UNION

 

 

예제3)

 

예제4)

 

MINUS - A 회사에만 있는 고객 정보를 추리려고 한다!!!

EQUI JOIN, non-EQUI JOIN

 

CROSS JOIN - A 테이블의 모든 행과 B 테이블의 모든 행을 조인(A 레코드 수 * B 레코드 수)

 

 

2) 계층형 조회 & 그룹 함수

 

계층형 조회 : 트리형태의 데이터에 대해 조회를 수행하는 것

 

>> 자식 노드가 없는 데이터 : LEAF 노드

 

예제 1)

=> 정답!!  12

 

계층형 조회 문제는...

조회를 통해 어떻게 레코드가 재배치 되는지 파악하는 것이 중요!!

1. 시작점 잡기

START WITH : 계층 구조가 시작되는 지점(ROOT NODE)을 알려줌, 여기에 적힌 조건에 맞는 레코드가 ROOT(부모), 현 문제에서는 첫번째 레코드가 트리 구조의 ROOT NODE

 

 

2. 재배치 기준

CONNECT BY, PRIOR : 다음 레코드가 어떤 것이 올지 알려줌
- CONNECT BY PRIOR a = b
- CONNECT BY a = PRIOR b
모든 레코드에 대하여 관계성(계층 구조)를 파악하여 테이블을 재배치하기 위함

1) a컬럼과 b컬럼이 동일한 레코드를 간에 계층화가 발생(a=b)
2) 앞선(PRIOR) 레코드의 a컬럼과 동일한 b컿험을 가진 레코드들이 뒤에 온다
CONNECT BY PRIOR a = b : 앞선 레코드의 a = b이다

현 문제에서는 
1) col1과 col2가 동일한 레코드를 간에 계층화가 발생한다
2) ㄱ의 col1과 동일한 col2를 가진 레코드가 다음 순서에 온다

 

 

그럼 ㄴ, ㄷ 중에 뭐가 먼저 올지 어떻게 암?? =? ORDER SIBLINGS BY 가 해결해줌!!

 ORDER SIBLINGS BY col3 
: col3 기준, 오름차순으로 배치 순서 결정
>> col3 : [ㄴ] 12 < [ㄷ] 13 

 

 

예제 2)

 

GROUP 함수

GROUP BY 성별, 연령	1. 성별 * 연령 별 소계
GROUP BY GROUPING SETS( 성별, 연령)	1. 성별 별 소계 2. 연령 별 소계 * 괄호 묶은 집합별 집계 가능
GROUP BY ROLLUP(성별, 연령)	1. 성별 * 연령 별 소계 2. 성별 별 소계 3. 전체 집계
GROUP BY CUBE(성별, 연령)	1. 성별 * 연령 별 소계 2. 성별 별 소계 3. 연령 별 소계 4. 전체 집계 * 그룹화될 수 있는 모든 경우에 대해 생성

 

ex) QUERY 사례

SELECT 성별, 연령, SUM(결제금액)

FROM 결제

GROUP BY GROUPING SETS(성별, 연령대);

 

외우기!!!

• Grouping Sets (set1, set2) :그룹핑 세트 세트(UNION ALL과 같은 결과)
• Rollup(앞, 뒤) : 롤업 압뒤
• CUBE(모든경우) : 큐브

 

예제 3)

 

=> 정답!! 2번

CUBE는 가능한 모든 조합의 경우

 

예제 4)

=> 정답!! 1, 2번

1번 => ROLLUP에 해당하는 설명

2번 => 집계 대상 컬럼 값은 NULL이 아니라 기준이 되는 값으로 설정해야 한다

 

 

3) TOP(N) 쿼리

: 테이블에서 N 개의 행만 가져오고 싶다!

 

1. ORACLE 기준 : ROWNUM 슈도 칼럼
 => 컬럼과 비슷한 성격의 Pseudo Column 으로서, SQL 처리결과 집합의 각 행에 대해 임시적으로 부여되는 일련번호

SELECT *
FROM C_INFO
WHERE ROWNUM <= 100;

** (오류) C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
SELECT *
FROM C_INFO
WHERE ROWNUM <= 100
ORDER BY AGE;
=> C_INFO에서 무작위로 추출된 100개의 레코드를 AGE 순으로 정렬한 것과 같다

** (정답) C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
=> 인라인 뷰에서 먼저 정렬하기!
SELECT *
FROM (SELECT *
            FROM C_INFO
            ORDER BY AGE)
WHERE ROWNUM <= 100;

 

 

2. SQL SERVER 기준 : TOP 절

** C_INFO 테이블에서 나이가 어른 100명을 조회하려면?
SELECT TOP(100)
              NAME, AGE
FROM C_INFO
ORDER BY AGE;

** WITH TIES
=> TOP N의 마지막 행과 동일한 값이 있는 경우 추가 행이 출력됨
SELECT TOP(2) WITH TIES
               NAME, AGE
FROM C_INFO
ORDER BY AGE;

=> C_INFO 테이블에서 AGE가 낮은 2명을 오름차순으로 출력하는데, 같은 AGE인 고객이 잇으면 같이 출력한다
=> ORDER BY가 지정된 경우에만 사용 가능

 

 

3. ROW LIMITING 절(ANSI 표준 SQL)

** C_INFO 테이블에서 나이가 어린 100명을 조회하려면?
SELECT NAME, AGE
FROM C_INFO
ORDER BY AGE FETCH FIRST 100 ROWS ONLY;

* FETCH : 반환할 것 지정해줌
* ONLY : 지정한 만큼(with ties 마지막행 동순위 포함)

- ORACLE 
SELECT *
FROM (SELECT *
             FROM C_INFO
             ORDER BY AGE )
WHERE ROWNUM <= 100;

- SQL SERVER
SELECT TOP(100)
               NAME, AGE
FROM C_INFO
ORDER BY AGE;

**상위 100개 행을 건너뛴 이후의 전체 행 반환하려면?
SELECT NAME, AGE FROM C_INFO ORDER BY AGE OFFSET 100 ROWS;

* OFFSET : 건너뛸 것 지정해줌

 

 

3. 윈도우 함수 & 파티션

 

WINDOW 함수

: 레코드(행) 사이의 관계를 쉽게 정의하기 위한 함수

매장 코드	지역명	메뉴명	판매량	판매순위
101	서울	연어스시	500	1
101	서울	참치스시	400	2
101	서울	와규	300	3
102	부산	연어스시	600	1
102	부산	튀김	300	2

 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITON BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING] ) FROM 테이블명;

- WINDOW_FUNCTION : 윈도우 함수
- ARGUMENTS : 인수(컬럼명 등 함수의 작업이 이뤄지는 대상)
- PARTITION BY : 테이블의 레코드들을 쪼개는 기준
- ORDER BY : 쪼개진 레코드들 내에서 혹은 전체 테이블에서 레코드들을 어떤 기준으로 정렬할지
- WINDOWING : 함수의 연산 대상이 되는 레코드의 범위를 정함

 

* WINDOW_FUNCTION 종류

- 그룹 내 집계 함수 : COUNT, SUM, MIN, MAX, AVG등

- 그룹 내 순위(RANK) 함수

- 그룹 내 비율 관련 함수

- 그룹 내 행 순서 함수

 

 

WINDOW 함수 - 그룹 내 RANK 함수

RANK	- 동일한 순위에 대해 동일한 순위를 부여한다 - 동일한 순위를 하나의 건수로 계산하지 않는다 (1 / 2, 2 / 4)
DENSE_RANK	- 동일한 순위에 대해 동일한 순위를 부여한다 - 동일한 순위는 하나의 건수로 계산한다 (1 / 2, 2 / 3)
ROW_NUMBER	- 동일한 순위에 대해 고유한 순위를 부여한다 (1 / 2 / 3 / 4 )

**RANK와 DENSE_RANK 차이 비교!!

 

예시1)

 

=> 정답!! 

 

A : DENSE_RANK

B : RANK

C : ROW_NUMBER

 

예시2) 

 

=> 정답!!  1번 (여타 SQL문과 동일하게 ORDER BY를 사용하여 오름차순, 내림차순 출력이 가능 / 순위로 자동으로 정렬되는 것은 아님)

 

예시 3)

 

=> 정답!!

 

SELECT 매장 코드, 지역명, 메뉴명, 판매량

                , DENSE_RANK () OVER (PARTITION BY 지역명 ORDER BY 판매량 DESC) AS

            판매순위

FROM 판매

 

 

 Window 함수 - 그룹 내 비율 함수

PERCENT_RANK	- 값이 아닌, 순서를 대상으로, 파티션 내에서의 순서별 백분율을 조회함
NTILE(n)	- 파티션별로 전체 건수를 n등분한 값을 반환한다. n=4이면 4등분한 것 내에서 몇인지 조회
CUME_DIST	- 파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대해 누적 백분율을 조회 - 누적 분포 상에 0~1 값을 가짐

 

 

WIndow 함수 - 그룹 내 행 순서 함수

FIRST_VALUE	- 파티션 내에서 가장 처음 나오는 값 반환 - MIN과 동일한 결과
LAST_VALUE	- 파티션 내에서 가장 마지막에 나오는 값 반환 - MAX와 동일한 결과
LAG(컬럼명, 레코드 위치 차이값)	- 이전 행을 가져온다
LEAD(컬럼명, 레코드 위치 차이값,  null일 경우 대체값)	- 다음(특정 위치의) 행을 가져온다 - default는 1이다

 

예제4)

 

 

=> 정답 (ㄱ : E,  ㄴ : E)

 

 

Window 함수 중 WINDOWING

SELECT WINDOW _FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 컬럼명] [ORDER BY 컬럼명] [WINDOWING] ) FROM 테이블명; 

 

- WINDOWING : 함수의 연산 대상이 되는 레코드의 범위를 정함

 

RANGE	ex ) RANGE BETWEEN 100 AND 200 범위를 지정해줄 때 사용
BETWEEN a AND b	a부터 b까지 윈도우가 적용됨
UNBOUNDED PRECEDING	윈도우의 시작 위치 = 첫 번째 행
UNBOUNDED FOLLOWING	윈도우의 마지막 위치 = 마지막 행
CURRENT ROW	윈도우 시작 위치가 현재 행

 

예제5)

 

=> 정답!! UNBOUNDED PRECEDING

 

 

테이블 파티션

 

- 대용량의 테이블을 여러 개의 데이터 파일에 분리하여 저장하는 것

물리적으로 분리된 데이터 파일에 저장되어 입력 / 수정 / 삭제 / 조회 성능이 향상되고, 독립적 관리가 가능

조회의 범위를 줄이는 효과가 있어 성능이 향상됨

** 막 엄청 중요하진 않음;;

 

RANGE PARTITION	값의 범위를 기준으로 파티션을 나눠 저장하는 방법 ex) 매출액에 50000 이상인 레코드와 미만인 레코드를 별도로 저장
LIST PARTITION	특정 값을 기준으로 분할 ex) 특정 값이 100일 때와 250일 때 각각 다른 데이터파일에 저장
HASH PARTITION	데이터베이스 관리 시스템이 자체적으로 해시 함수를 사용해 분할하고 관리하는 방식

 

 

SQL의 정의

 

SQL은 데이터베이스를 직접적으로 엑세스 할 수 있는 언어로,

데이터를 정의하고 => DDL

조작하며 => DML

조작한 결과를 적용하거나 취소할 수 있고 => TCL

접근 권한을 제어하는 처리들로 구성된다 => DCL

 

 

1) DCL : 데이터베이스 사용자에게 권한을 부여 / 회수하는 언어

 

GRANT :권한을 줌

REVOKE : 권한을 회수

**출제 빈도가 낮음...

 

 

WITH GRANT의 경우 : 엄마가 회수 되면 자식도 회수된다!!(A -> B, B -> C, B가 퇴사 하여 권한을 회수당하면 C도 같이 회수하는 경우)

WITH ADMIN의 경우 : 엄마가 회수 당하더라도 자식은 영향을 받지 않음

 

 

예시 문제1)

 

=> 정답!! GRANT SELECT, UPDATE ON hoho_qualification.data(테이블 명) TO hoho(유저, 권한을 부여 받는 사람);

 

GRANT 권한(SELECT, UPDATE등등...) ON 테이블명 TO 유저명;

 

 

예시 문제 2) 

 

데이터베이스 상에서 많은 사용자들에게 개별적으로 권한을 부여하고 관리하는 어려움을 해소하고자 다양한 권한을 하나의 그룹으로 묶어서 관리할 수 있도록 하는 논리적인 권한의 그룹(명령어)을 칭하는 말은?

 

=> 정답!!  ROLE

 

* 다양한 권한을 다양한 유저를 대상으로 관리하기 위한 명령어이다
* 여러 사용자에게 동일한 ROLE 부여 가능하다
* ROLE의 생성은 [ CREATE ROLE ] 권한을 가진 유저가 할 수 있다

 

 

2) DDL : 데이터를 보관하고 관리하기 위한 객체의 구조를 정의하기 위한 언어

 

 

1. CREATE : 데이터 베이스 상 테이블 구조 생성

CREATE TABLE C_INFO(테이블 명) {
    컬럼명(속성명)  /  데이터의 유형  /  크기
    이름                       varchar2(10),
    생년                       number(4) default 9999,
    phone                    varchar2(15) not null,
    첫방문일               date,
    고객 번호              varchar2(10) primary key
);

=> C_INFO라는 이름을 가진 테이블을 만든다

 

* 컬럼명(속성명) : 영문, 한글, 숫자 모두 가능(시작만 문자로) ex) h10(o), 10h(x)

* 데이터 유형(data type)

• char : 고정길이 문자열(할당된 길이만큼 문자 채움)
• varchar2 : 가변 길이 문자열
• number :  숫자형
• date : 날짜형

* 제약 조건(constraint)

• default : 기본값 지정
• not null : null 입력 불가
• primary key : 기본키 지정(PK는 not null, PK는 unique 한 값, 테이블 내 중복 없음)
• foreign key : 외래키 지정

 

예시 문제1)

 

 

=> 정답!! 서로 같다.

 

하지만 SELECT count(가격) FROM MENU의 경우 다르다!!!!!!!!!!

- 가격 컬럼은 NOT NULL 제약조건이 붙지도 않았고, primary key도 아니다

- 실제로 삽입된 튜플 중 하나는 가격 값이 Null이다

- count(*)는 4가 출력되고. count(가격)은 3이 출력된다.

 

* NULL에 대해서 😋

1. NULL은 모르는 값을 상징하고, 값이 없음(부재)를 의미함
2. NULL과의 사칙연산(+, -, /, *), NULL 과의 비교연산(=, <, > 등) 결과는 늘 NULL(알 수 없음)이다
3. 해당 컬럼의 값이 NULL 인지 확인하려면 '=' 가 아닌 'IS' 혹은 'IS NOT'를 사용한다
4. NULL은 숫자 0이나 공백문자(' ') 와 동일하지 않음(얘네는 값이 있음, NULL은 아예 값이 없는 것입니다.)

 

 

2. ALTER : 테이블과 컬럼에 대해 이름 및 속성 변경, 추가/삭제 등 구조 수정을 위해 사용!

 

테이블 명 변경 : ALTER TABLE MENU RENAME (TO) ho_MENU;
컬럼 명 변경 :    ALTER  TABLE MENU RENAME COLUMN phone TO 전화번호(수정 결과);

!! : 수정할게요 / 테이블에서 / 이름 다시 / 어떤 컬럼 / 이걸로

컬럼 속성 변경 : ALTER TABLE_MENU MODIFY (이름 varchar(20) not null);
컬럼 추가 : ALTER TABLE_MENU ADD( 거주 지역 varchar2(10));
컬럼 삭제  : ALTEr TABLE_MENU DROP COLUMN 이름;
제약 조건 추가 / 삭제 : ALTER TABLE_MENU ADD CONSTRAINT 제약조건명 제약조건(대상);
                                                                          DROP CONSTRAINT 제약조건명;

 

 

 

++) MODIFY와 ADD CONSTRAINT 비교

 

제약조건 추가 / 삭제  : ALTER TABLE MENU ADD CONSTRAINT 제약조건명 제약 조건 (대상);

컬럼 속성 변경 : ALTER TABLE MENU MODIFY ( 이름 varchar(20) not null );

 

대표 제약조건 5가지(NOT NULL, UNIQUE, Primary Key, Foreign Key, CHECK)를 수정을 통해 부여할 경우
5가지 중 NOT NULL만 MODIFY를 사용하고, 그 밖의 제약조건들은 모두 ADD를 사용

 

 

• NOT NULL : 해당 컬럼의 값에는 NULL이 포함될 수 없음(값은 모두 정의되어 있어야 함)
• UNIQUE : 해당 컬럼의 내에서는 값이 중복될 수 없음 ex) ALTER TABLE 테이블명 ADD UNIQUE (컬럼명);
• PRIMARY KEY : NOt NULL + UNIQUE 
  ex) ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);
        ALTER TABLE 테이블명 ADD CONSTRAINT PK_RIDING PRIMARY KET (컬럼명1, 컬럼명2);
• FOREIGN KEY : 다른 테이블의 값을 참조하여 값을 가져오라는 제약
  ex) ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 부모테이블명 (컬럼명);
• CHECK : 특정 조건을 충족하는 값만을 컬럼에 넣어줘야 함
  ex) ALTER TABLE RIDING ADD CONSTRAINT RD_CHECK CHECK (ride_date > '2021-12-10');

 

예시 문제1)

 

=> 정답!! ALTER TABLE RIDING MODIFY ( phone varchar(15) NOT NULL );

 ** 대표 제약조건 5가지 중 NOT NULL만 MODIFY 를 사용

 

 

3. DROP : 테이블 및 컬럼 삭제 

 

컬럼 삭제 : ALTER TABLE MENU DROP COLUMN 이름;
테이블 삭제 : DROP TABLE MENU;

**유의
DROP TABLE MENU CASCADE CONSTRAINT;
* 해당 테이블의 데이터를 외래키(FK)로 참조한 제약사항도 모두 삭제
* Oracle에만 있는 옵션, SQL Server에는 존재하지 않음
* FK 제약조건과 참조테이블 먼저 삭제하고, 해당 테이블을 삭제

 

 

DROP VS TRUNCATE 비교하기

 

1) DROP(테이블 삭제) : DROP TABLE MENU;

• 테이블 관련한 모든 것이 삭제된다. 구조도, 데이터도!
• 테이블 정의를 완전 삭제함
• 테이블이 사용했던 모든 저장공간이 Release

 

2) TRUNCATE(테이블 초기화) : TRUNCATE TABLE MENU;

• 테이블 속 데이터만 삭제되고 구조는 살아있다
• 테이블을 초기 상태로 만든다
• 테이블 최초 형성 시 사용했던 저장공간만 남기고 Release

 

예시 문제1)

 

=> 정답!!!

A : DROP TABLE F_INFO;

B : TRUNCATE TABLE F_INFO;

 

 

3) DML : 정의된 데이터베이스에 레코드를 입력하거나, 수정, 삭제 및 조회(SELECT) 하기 위한 명령어다.

 

예시 문제1)

 

 

=> 정답!!  3, 4
3 : not null 값이 메뉴명에 대한 insert가 이뤄지지 않으므로 오류 발생

4 : 컬럼명 지정이 이뤄지지 않은 상태에서는 전체 컬럼에 값을 넣어줘야 함 => VALUES의 값이 4개가 와야 한다!! ('102', '와규', 30000, )

 

* 숫자는 varchar2 와 char에 입력 가능

* DELETE (FROM) MENU : FROM 생략하고 사용 가능합니다

 

 

예시 문제2)

 

 

=> 정답!!  2

 

DELETE

* WHERE 조건문을 통해 원하는 데이터만 지울 수 있다

* 삭제한 것은 되돌릴 수 있다.(DB에 반영되기 전까지는)

* 데이터는 지워지지만 테이블 용량은 줄어들지 않는다

 

 

4) TCL : 트랜잭션을 제어하기 위한 언어

 

* 트랜잭션(Transaction)이란..?

•  데이터베이스의 상태를 변화시키기 위해서 수행하는 작업의 단위
• 고립성, 원자성, 지속성, 일관성의 특징을 가짐

1. 고립성 :  트랜잭션이 실행되는 동안 다른 트랜잭션에 영향을 받아 잘못된 결과를 만들어선 안된다
2. 원자성 : 트랜잭션에서 정의된 연산은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 있어야 한다
3. 지속성 : 트랜잭션이 성공적으로 완료되면 해당 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다
4. 일관성 : 데이터베이스 내용에 잘못된 점이 없다면 트랜잭션 수행 후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

 

 

예시 문제1)

 

=> 정답!! 250 (ROLLBACK 할떄는 가장 최신의 SavePoint로 돌아갑니다!!)

 

 

예시 문제2)

 

=> 정답!!  10, 50, 30

 

 

COMMIT과 ROLLBACK 효과

 

• 데이터 무결성을 보장할 수 있다
• 영구적인 변경 전 데이터에 대한 변동사항을 확인할 수 있다
• 논리적 연관성 있는 작업을 그룹화하여 처리할 수 있다

**데이터 무결성 이란?

: 데이터베이스에 저장된 데이터의 정확성, 일관성을 유지하고 보증하는 것

- 정확성 : 중복이나 누락이 없는 상태(같은 데이터가 여러번 저장 x)

- 일관성 : 원인과 결과의 의미가 연속적으로 보장되어 변하지 않는 상태

 

** 무결성 제약조건 이란?

: 데이터베이스의 정확성, 일관성(무결성)을 보장하기 위해 저장, 삭제, 수정 등을 제약하기 위한 조건

1. 개체 무결성(기본키 제약) 
   - 테이블은 기본키를 지정하고 그에 따른 무결성을 지켜야 한다.
   - 기본키에는 NULL 값이 올 수 없음
   - 기본키는 테이블 내에 오직 하나의 값만 존재해야 한다.
   (동일한 기본키를 가진 레코드가 테이블 내에 여러개일 수 없다.)
2. 참조 무결성(외래키 제약)
   - 외래키 값은 NULL이거나 참조하는 릴레이션의 기본키 값과 동일해야 함
   ([결제]에서 [고객]의 회원번호를 참조하면, 있는 회원번호만 가져와야 함)
3. 도메인 무결성 : 속성값은 속성의 도메인 속한 값(초등학교 학년 1 ~ 6)
4. 고유 무결성 : UNIQUE 조건이 주어진 컬럼의 속성값은 서로 달라야 함
5. NULL 무결성 : NOT NULL로 제한하면 NULL이 와선 안됨

 

5) DML_ SELECT(데이터 조회)  

SELECT 조회 대상(컬럼명)
FROM 테이블 명
WHERE 조건문
GROUP BY 집계기준 컬럼명
HAVING grouping된 후 상태 기반의 조건문
ORDER BY 컬럼명

 

예시 문제1)

 

 

=> 정답!!  3

 

 

문자형 함수

 

TRIM 함수

- RTRIM (문자열, 제거 대상)

- LTRIM (문자열, 제거 대상)

- TRIM (제거 대상 FROM 문자열)

 

++) 쿼리문

SELECT RTRIM ('XXSQLXX', 'X')

         .TRIM ( 'X' FROM 'XXSQLXX')

FROM DUAL;

 

REPLACE 함수

- TRIM (제거 대상 FROM 문자열)

- REPLACE (문자열, 제거 문자열, [대체 문자열])

 

++) 쿼리문

SELECT TRIM ('X' FROM 'XXSQLXX')

           .REPLACE('XXSQLXX', 'X')

           .REPLACE('XXSQLXX', 'X', 'Y')

FROM DUAL;

 

 

예시 문제2)

 

=> 정답!! SELECT 회원코드, RTRIM(연령대, '대'), UPPER(이름) FROM C_INFO;

 

 

숫자형 함수

 

 

 

예시 문제3)

 

=> 정답!! TO_CHAR(SYSDATE -1, 'YYYYMMDD')

 

*DUAL

- 듀얼 테이블은 오라클에 존재하는 기본 테이블로, 하나의 열로만 이뤄져있음

- 오늘 날짜를 구하거나 간단한 계산을 하는 등에 사용하기 적합

 

** SYSDATE

- SQL을 작업하는 시점의 날짜와 시각을 알려주며, -1을 할 경우 전날의 날짜값이 출력된다

- 날짜를 조회한다고 하였으며 TO_CHAR을 통해 형변환을 하여 날짜만 남긴다.

 

 

DECODE & CASE WHEN

 

* DECODE(= IF문)

- DECODE (값1, 값2, 참일 때 출력 값, 거짓일 때 출력 값)

ex) .DECODE(col1, 'KATE', '본인', '다른사람')

 

* CASE WHEN(= 여러 케이스를 포함하는 IF 문)

 

- 단일 조건 : CASE WHEN 조건 THEN 조건이 참일 때 결과 ELSE 거짓일 떄 결과 END

- 다중 조건 : 

 

CASE WHEN 조건문1 THEN 결과값1

           WHEN 조건문2 THEN 결과값2

                                  ....

           WHEN 조건문n THEN 결과값n

ELSE 결과값 n+1

END

 

ex) CASE WHEN col1 < 10 THEN '한자리 수' (CASE col1 WHEN 도 가능)

                 WHEN col1 BETWEEN 10 AND 99 THEN '두자리 수'

ELSE '세자리 수'

END

*단, col1에는 정수형으로 1000미만의 숫자만 들어가 있음을 가정함

 

 

WHERE 조건문

 

• 조건을 지정해서 테이블에서 조건에 맞는 레코드(행)만 출력하기 위한 구문
• 비교연산자, 논리연산자 등 '연산자'를 통해 조건을 부여함
• '조건'을 지정해주는 것이므로 true or false의 결과가 나옴(조건을 충족하는 true인 레코드들만 출력)

CASE WHEN col1 < 10 THEN '한자리수'
        WHEN col1 BETWEEN 10 AND 99
        THEN '두자리 수'
        ELSE '세자리 수'
END _
;

SELECT *
FROM C_INFO
WHERE NAME = '김호호' AND PHONE LIKE %1234'

 

 

연산자 종류

비교 연산자	=, >, >=, <, <= - 부정 비교 연산자 : !=, ^=, <> : 같지 않은 것을 조회 WHERE col1 <> 1
논리 연산자	AND, OR, NOT - AND : 조건을 모두 만족해야 함 - OR : 조건을 하나라도 만족하면 참 - NOT : 참이면 거짓으로, 거짓이면 참으로 바꿈 WHERE NOT col1 = 1 (WHERE col1 <> 1과 같은 의미) WHERE NOT col1 > 10(WHERE col1 <= 10과 같은 의미)
BETWEEN a AND b	a와 B 사이에 값이 있는지 판단 WHERE col1 BETWEEN 10 AND 99 - WHERE col1 >= 10 AND col1 <= 99와 동일함

 

 

연산자 종류 - NULL 판단

IS NULL	NULL인지 판단, NULL일 경우 TRUE
IS NOT NULL	NULL이 아닐 경우 TRUE

 

NULL은 =, <>으로 비교될 수 없고, IS, IS NOT으로만 비교된다

 

WHERE A.col IS NULL (0)
WHERE A.col IS NOT NULL (0)
WHERE A.col = NULL (X)
WHERE A.col <> NULL (X)

 

예시 문제4)

=> 정답!! NVL or ISNULL or IFNULL(단, 표현식 두개(생년, 9999)의 데이터 타입이 같아야 함)

 

 

연산자 종류 - IN

IN(x, y, z....)	x, y, z 등으로 구성된 목록 내 값 중 어느 하나라도 일치하면 된다.(or 조건)
NOT IN(x, y, z....)	x, y, z 등으로 구성된 목록 내 값 중 어느 하나라도 일치하면 안된다.(and 조건)

 

WHERE A.COL IN (1, 2, 3)을 만족하려면...
A.col = 1이 참이거나(or)
A.col = 2이 참이거나(or)
A.col = 3이 참이어야 한다  => OR 조건

WHERE A,COL NOT IN (1, 2, 3)을 만족하려면...
A.col <> 1 이 참이고 (and)
A.col <> 2 이 참이고 (and)
A.col <> 3 이 참이어야 한다  => AND 조건

ex) 
SELECT * FROM C_INFO WHERE AGE IN (20, 30, 40);
>> 나이가 20살이거나 30살이거나 40살인 고객의 회원정보만 출력

 

**) NULL이 IN() 혹은 NOT IN() 안에 포함된다면??

 

• IN(1, 2, NULL) >> IN은 OR 조건이므로 1또는 2를 만족하면 TRUE 가능
• NOT IN(1, 2, NULL) >> NOT IN은 AND 조건이므로 항상 FALSE인 NULL이 포함되어 있으므로 항상 FALSE 를 출력

 

문자열 조건문 연산자

 

A LIKE B	A에 대하여 B와 유사한 문자열을 찾아줌
%	문자 0개 이상이 존재한다는 의미
_	문자 한개

 

 

1. %는 문자가 해당 위치에 0개 이상이 존재한다는 의미

ex) WHERE name LIKE '%k%' 일 경우 조건에 해당하는 name은 k라는 단어를 포함만 하면 됨

• key : k 앞에 문자 0개 (True)
• chuck : k 뒤에 문자 0개 (True)
• bike : k 앞뒤에 문자 존재 (True)
• k : k 앞뒤에 문자 0개 (True)

2, k가 양끝에 있는 경우를 찾으려면

>> LIKE 'k%k' (양끝 문자열은 고정이고, 중간은 0~n개의 문자가 존재가능)

 

3. 특정한 위치와 문자 개수를 정의하고 싶으면

>> LIKE 'k_y'

 

예시 문제5)

 

 

=> 정답!! LIKE '%ki%'

 

💪 추가 사례

- 해당 자리에 문자가 0개 이상 존재한다는 의미
ex) 'ki%' : ki로 시작하는 문자
>> kind, kite

ex) '%ce' : 'ce'로 끝나는 문자
>> choice, chance

실무) '202309%' : '202309'로 시작하는 문자열(ex. 2023년 9월)

- 문자 한개
ex) WHERE 단어 LIKE 'ki__' : ki뒤에 두 자리가 더 있는 row 출력
>> kind, kite

ex) WHERE 단어 LIKE '__ce' : ce로 끝나는 6자리 문자
>> choice, chance

 

 

Subquery 

 

: 하나의 SQL 문에 포함되어있는 또 다른 SQL 문을 의미함

SELECT COUNT(*)
FROM 결제
WHERE 회원번호 NOT IN (SELECT 회원번호 FROM 회원)

• SELECT절, FROM절, WHERE절, HAVING절, ORDER BY절 등에서 사용 가능
• 괄호로 감싸서 사용해야 함, 세미콜론 사용하지 않음
• 서브쿼리는 단일행 또는 복수행 비교연산자와 함께 사용 가능
• 서브쿼리도 AS로 별칭 지정이 가능 (FROM 절에 사용되면 별칭 지정 필수)

 

위치에 따른 서브쿼리 명칭

• 스칼라 서브쿼리 : SELECT 절에서 사용하는 서브쿼리로, 결과는 하나의 행이어야만 한다
  SELECT *,  (SELECT 부서명 FROM 부서 WHERE 부서,부서번호 = 직원.부서번호) AS 소속부서 FROM 직원;
• 인라인뷰 : FROM 절 내에 쓰여진 서브쿼리
  SELECT * FROM (SELECT * FROM C_INFO WHERE name LIKE '%a%') AS T;

 

다중행 연산자

비교 연산자	=, >, >=, <, <=, !=, ^=, <> SELECT 날짜, 매출총액, 결제건수 FROM 매출 WHERE 매출총액 >= (SELECT AVG(매출 총액) FROM 매출 WHERE SUBSTR(날짜, 1.6) = '202309');
다중행 연산자	- IN : 메인 쿼리의 비교대상이 서브쿼리 결과 중에서 하나라도 일치하면 참 - EXISTS : 메인 쿼리의 비교대상이 서브쿼리 결과 중에서 만족하는 값이 하나라도 존재하면 참 => 비교연산자랑 같이 사용되지 않음 - ALL : 메인쿼리의 비교대상이 서브쿼리의 결과와 모든 값이 일치하면 참 - ANY, SOME : 메인쿼리의 비교대상이 서브쿼리의 결과 중 하나 이상이 일치하면 참 => =, >, < 등 비교연산자랑 같이 사용 (ex. col1 = ALL(a, b, c) a, b, c모든 값을 가지는 col1반환)

 

예시 문제6)

 

 

=> 정답!!

SELECT 이름
FROM 사장후보
WHERE 입사년도 < ALL (SELECT 입사년도 FROM 임원)

 

예시 문제7)

 

다음 보기 중 서브쿼리와 다중행 연산자에 대한 설명으로 옳은 것은?

1. 서브쿼리에서는 정렬을 수행하는 ORDER BY를 사용할 수 있다. (X)

>> 사용할 수 없다

2. 여러 행을 반환하는 서브쿼리는 단일 행 연산자를 사용해야 한다 (X)

>> 다중 행 연산자를 사용해야 한다

3. 메인 쿼리에서 서브쿼리의 컬럼을 자유롭게 사용할 수 있다 (X)

>> 메인 쿼리에서는 SELECT 되지 않은 서브쿼리의 컬럼을 사용할 수 없음

>> 서브 쿼리 내부에서는 메인 쿼리의 컬럼을 사용할 수 있음

4. EXIST가 반환하는 결과값은 True 혹은 False 이다 (0)

>> 결과가 하나라도 존재하면 참

ex. WHERE EXISTS(SELECT 1 FROM WHERE 거주지 = '서울')

 

 

WITH절 : 서브쿼리를 WITH절로 만들어서 사용해보기

SELECT * FROM (SELECT * FROM C_INFO WHERE name LIKE '%a%') AS T;

WITH T AS (
    SELECT * FROM C_INFO WHERE name LIKE '%a%')
,     T2 AS (SELECT _ FROM T)
SELECT *
FROM T;

* WITH 테이블명 AS (쿼리문) SELECT _ FROM 테이블명
* 임시테이블(중간결과 저장용 등 임시적으로 사용하는 테이블)이나 뷰(VIEW)처럼 사용 가능함
* 쿼리문 내에서 반복해서 동일한 테이블에 접근하는 경우 WITH 절로 반복 쿼리문을 줄여 테이블 엑세스를 줄이고 쿼리 성능을 높일 수 있음

 

 

View 테이블이란?

• 일종의 가상테이블로서 실제 데이터가 하드웨어에 저장되는 것은 아니다
• 실제 데이터를 가지고 있지 않고 SQL만 저장한다(Table은 실질적 데이터를 가진다)
  => 즉, 데이터를 보여주기만 하는 테이블
• 테이블 구조가 변경되더라도 독립적으로 존재한다

 

VIEW 테이블을 사용하는 이유

• 사용상의 편의를 위해 사용
  => 복잡한 쿼리문이 있으면 매번 쿼리문을 작성하기 보다는 해당 쿼리문을 View 테이블로 만들어두고 관리하고 조회하는 것이 편하므로
• 보안관리를 위해 사용
• 임시적인 작업을 위해 사용\

 

예시 문제8)

 

=> 정답!!

WITH T AS (
SELECT *
FROM C_INFO
WHERE NAME LIKE '%a%'
)
SELECT count (*) FROM T ;

 

 

집계함수

COUNT(*), COUNT(exp)	count(*) : NULL 포함, count(exp) : NULL 제외
SUM( [DISTINCT | ALL] exp )	합계
AVG( [DISTINCT | ALL] exp )	평균
MAX( [DISTINCT | ALL] exp )	최대값
MIN( [DISTINCT | ALL] exp )	최소값
STDDEV( [DISTINCT | ALL] exp )	표준편차
VARIAN( [DISTINCT | ALL] exp )	분산

 

**) NULL값에 대한 연산의 결과는 모두 NULL이며 통계적 집계함수를 연산할 때, NULL은 제외하고 계산한다

ex) NULL / 10 = NULL

 

 

예시 문제9) 

C_INFO에서 평균 연령이 30대인 성별과 해당 성별의 평균 연령을 출력하는 SQL을 작성하시오.

=> 정답!!

SELECT 성별, AVG(연령)

FROM  C_INFO

GROUP BY 성별

HAVING AVG(연령) >= 30 AND AVG(연령) < 40;

 

 

예시 문제10) 

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY 

 

반정규화

 

반정규화 개념 

- 시스템의 성능 향상, 개발 및 운영의 편의성 등을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정으로 의도적으로 정규화 원칙을 위배하는 행위

 

반정규화의 대상 분석

- 디스크 I/O 량이 많아 성능 저하

- 경로가 너무 멀어 조인으로 성능 저하

- 컬럼을 계산하여 읽을 떄 성능 저하

 

반정규화의 특징

- 반정규화를 수행하면 시스템의 성능이 향상되고 관리 효율성을 증가시키지만 데이터의 일관성 및 정합성이 저하될 수 있다

- 과도한 반정규화는 오히려 성능을 저하시킨다.

- 반정규화를 위해서는 사전에 데이터의 일관성과 무결성을 우선으로 할지, 데이터베이스의 성능과 단순화를 우선으로 할지를 결정해야 한다

- 반정규화 방법에는 테이블 통합, 테이블 분할, 중복 테이블 추가, 중복 속성 추가 등이 있다

 

테이블 통합

- 두개의 테이블에서 발생하는 프로세스가 동일하게 자주 처리되는 경우, 두개의 테이블을 이용하여 항상 조회를 수행하는 경우 테이블 통합을 고려한다.

 

 

속성

속성의 의미

- 업무에서 필요로 하는 인스턴스에서 더 이상 분리되지 않는 최소의 데이터 단위

 

속성의 특징

- 한 개의 속성은 반드시 한 개의 속성값만을 가진다

- 한 개의 엔티티는 최소 2개 이상의 속성을 가진다

- 한개의 엔티티는 최소 1개 이상의 다른 엔티티와의 관계를 가진다

- 한개의 엔티티는 최소 2개 이상의 인스턴스 집합이어야 한다

 

속성의 종류(특성에 따른)

- 기본 속성 : 업무상 필요한 데이터에 대해 정의 

- 설계 속성 : 업무를 규칙화하기 위해 새로 만들거나 변형한 속성

- 파생 속성 : 다른 속성의 영향을 받아 발생, 주로 계산하는 값들이 이에 해당(계산을 통해 구한다 -> 파생, 파생 => 최소주문 일자, 주문 금액, 총주문금액)

 

 

ex) 은행에 적금을 들고자 합니다. 그래서 우리는 원금을 통장에 입금했습니다. 이자율은 5%라서 연 이자는 원금의 * 0.05를 기대하면 되겠습니다.

 

기본속성 : 원금, 이자율

파생 속성 : 이자

설계 속성 : 예금 분류

 

인스턴스

: 데이터 베이스에 저장된 데이터 내용의 전체 집합을 의미(하나하나가 아님)

 

 

비식별자 관계 

: 부모 엔티티로부터 속성을 받았지만 자식 엔티티의 주식별자로 사용핮 않고 일반적인 속성으로만 사용하는 경우

 

ex)

1. 자식 엔티티에서 받은 속성이 반드시 필수가 아니어도 무방하기 때문에 부모 없는 자식이 생성될 수 있는 경우

2. 엔티티별로 데이터의 생명주기를 다르게 관리할 경우

3. 여러 개의 엔티티가 하나의 엔티티로 통합되어 표현되었는데 각각의 엔티티가 별도의 관계를 가질 때의 경우

4. 자식 엔티티에 주식별자로 사용하여도 되지만 자식엔티티에서 별도의 주식별자를 생성하는 것이 더 유리하다고 판단 되는 경우

 

 

데이터 모델링

 

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

- 논리적 데이터 모델링 : 시스템으로 구축하고자 하는 업무에 대해 Key 속성, 관계 등을 정확하게 표현, 재사용성이 높음

- 물리적 데이터 모델링 : 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계

 

 

정규형

 

제 1정규형 

: 모든 속성이 도메인이 원자 값으로만 구성되어 있으면 제 1정규형에 속한다

제 2정규형

: 제 1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2정규형에 속한다.

제 3정규형

: 제 2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3정규형에 속한다

=> 속성 간의 함수 종속성이 발생하지 않는 경우

 

 

 

++ 단답형 + 주관식

 

도메인 : 속성이 가질 수 있는 값의 범위

 

유일성 : 하나의 키로 특정 행을 바로 찾아낼 수 있는 고유한 데이터 속성

 

뷰(VIEW) : 다른 테이블에서 파생된 테이브로, 물리적 데이터가 저장되는 것이 아닌 노리적으로 존재하는 것