2024 SQLD 시험 준비 - 개념 정리 6편 <관계형 데이터베이스, SELECT, FROM>

SQLD 시험준비 (벼락치기) 개념정리 6번째 글

2절인 SQL 기본 및 활용 내용 첫 글. 지난 글들은 아래 참고.

2024 SQLD 시험 준비 - 개념 정리 1편 <데이터 모델의 이해>

2024 SQLD 시험 준비 - 개념 정리 2편 <엔터티, 속성, 관계>

2024 SQLD 시험 준비 - 개념 정리 3편 <식별자>

2024 SQLD 시험 준비 - 개념 정리 4편 <성능 데이터 모델링, 정규화, 반정규화>

2024 SQLD 시험 준비 - 개념 정리 5편 <관계와 조인, 트랜잭션, Null>

관계형 데이터베이스 개요

데이터베이스 (Database) & DBMS (Database Management System)

• 데이터베이스 (Database) : 데이터의 집합, 데이터를 일정한 형태로 저장해 놓은 것, 
• DBMS (Database Management System) : 데이터를 효과적으로 관리하기 위한 시스템 (ORACLE, MYSQL 등)

관계형 데이터베이스 구성 요소

• 계정: 데이터 접근 제한을 위한 업무 / 시스템별 계정 존재
• 테이블 : DBMS의 DB안에 데이터 저장되는 형식
• 스키마 : 테이블 구성 및 정보에 대한 기본적인 구조 정의

참고:
- 도메인: 각 컬럼(속성)이 갖는 범위
- 릴레이션 : 테이블 간의 관계
- 튜플 : 하나의 행
- 키 : 식별자

테이블 (Table)

• 행 (row) 및 열 (column)을 갖는 2차원 구조로 구성
• 데이터를 입력 및 저장하는 최소 단위
• 하나의 테이블은 반드시 하나의 유저(계정) 소유여야 함
• 테이블간 관계는 1:1 / 1:N / N:N 관계를 가질 수 있음
• 테이블명은 중복될 수 없음, 다만 소유자가 다른 경우 같은 이름 생성 가능
• 행 단위로 데이터 입력, 삭제됨. 다만 수정의 경우 값 단위 수정 가능

 

데이터 무결성 (Integrity)

• 데이터의 정확성과 일관성을 유지하고, 데이터 결손과 부정합이 없음을 보증
• 데이터 무결성 유지는 데이터베이스 관리시스템에 중요한 기능
• 데이터 베이스 내에 지정된 값과 현실의 비즈니스 모델 값이 일치하는 정확성

 

데이터 무결성 종류

개체 무결성	테이블 기본키를 구성하는 컬럼(속성)은 Null 일수 없음 테이블 기본키를 구성하는 컬럼(속성)은 중복값을 가질 수 없음
참조 무결성	외래키 값은 Null 일수 있음 외래키 값은 참조 테이블의 기본키 값과 동일해야 함
도메인 무결성	속성 값이 정해진 도메인 내에 속한 값이어야 함
NULL 무결성	특정 속성에 대해 Null을 허용하지 않는 특징
고유 무결성	특정 속성에 대해 값이 중복되지 않는 특징
키 무결성	하나의 릴레이션 (관계)에 적어도 하나의 키가 존재해야 함 테이블이 서로 관계를 가질 경우 반드시 하나 이상의 조인키를 가짐

 

ERD (Entity Relationship Diagram)

• 테이블 간 서로의 상관관계를 그림으로 표현
• 구성 요소: 엔티티 (Entity) / 관계 (Relation) / 속성(Attribute)

 

SQL (Structured Query Language)

• 관계형 데이터베이스에서 데이터 조회 및 조작, DMBS 시스템 관리 기능을 명령하는 언어
• 데이터 정의 (DDL) 데이터 조작 (DML) 데이터 제어 언어 (DCL) 구분
• 대 소문자 구분하지 않음

 

SQL 종류

기능에 따라 구분

데이터 정의 (DDL) Data Definition Language	CREATE, ALTER, DROP, TRUNCATE - roll back 할 수 없음 (되돌릴 수 없음) - Auto commit  - Truncate (테이블의 구조는 두고 데이터를 지우는 것, 되돌릴 수 없기때문에 주의)
데이터 조작 (DML) Data Manipulation Language	INSERT, DELETE, UPDATE, MERGE
데이터 제어 언어 (DCL) Data Control Language	GRANT, REVOKE - 데이터 권한을 부여 갱신 회수 등 할 수 있음
TCL Transaction Control Language	COMMIT, ROLLBACK
DQL Data Query Language	SELECT

*SELECT 의 경우 SQL 종류 중 속하지 않아서 DQL이 등장함

 

SELECT 구조

• SELECT 문장을 사용하여 불러올 컬럼명, 연산 결과를 작성하는 절
• 6개의 절로 구성
• 각 절의 순서대로 작성 (GROUP BY ↔ HAVING 은 바뀔 수도 있지만 보통 사용 X)
• SELECT 문의 구성 순서와 파싱 순서(DBSM에서 해석하는 순서)는 다름
• 실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT * 또는 컬럼명 또는 표현식
FROM 테이블명 또는 뷰명
WHERE 조회 조건
GROUP BY 그룹핑 컬럼명
HAVING 그룹핑 필터링 조건
ORDER BY 정렬 컬럼명

 

SELECT 절

• *를 사용하여 전체 컬럼명 불러오거나, 원하는 컬럼을 콤마(,)로 나열하여 작성 → 작성 순서대로 출력
• 표현식 사용하여 원본과 다른 데이터 추출 가능 (예: 컬럼명 * 1.1 등으로 수식 작성)
• 불러올 컬럼에 별칭 (Alias) 지정 가능
  • AS로 컬럼명 입력
  • 한글 사용 가능
  • SELECT 문보다 늦게 수행되는 ORDER BY 절에서 컬럼 별칭 사용 가능 (그 외 사용 시 에러)
  • 이미 존재하는 예약어는 별칭 사용 불가 (예: count decode avg 등)
  • 공백, 특수문자 (_제외), 대소문자 출력 구분 필요시 쌍 따옴표 활용 ("")

 

FROM 절

• 데이터를 불러올 테이블명 또는 뷰명
• 테이블 여러 개 가능 (콤마, 로 구분) → 조인 조건 없이 테이블명만 나열 시 카티시안 곱 발생 주의
• 테이블 별칭 선언 가능 (AS 사용하지 않음)
• ORACLE에서는 FROM 절 생략 불가, 의미상 불필요할 경우 DUAL 테이블 선언 (23c 버전부터 가능)
• MSSQL에서는 FROM 절 생략 가능 (예: 오늘 날짜 조회 시)

뷰 (View)
- 테이블과 동일하게 데이터를 조회할 수 있는 객체
- 테이블처럼 실제 데이터가 저장된 것이 아님 → SELECT 문 결과에 이름을 붙여 그 이름으로 조회가 가능하게 한 것

 

 

함수

• input value에 맞는 output value를 출력, 둘의 관계를 정의한 객체
• From 절을 제외한 모든 절에 사용 가능

 

함수의 기능

• 데이터 계산을 수행
• 개별 데이터 항목을 수정
• 표시할 날짜 및 숫자 형식을 지정
• 열 데이터 유형 (data type) 변환

 

함수의 종류

• 단일행 함수 : input ↔ output 관계가 1:1
• 복수행 함수 : 여러 건의 데이터를 동시에 입력 → 하나의 요약값 리턴 (=그룹함수 또는 집계함수)

 

문자형 함수

• 문자열 결합, 추출, 삭제 등 수행
• 단일행 함수 형태
• Output 역시 대부분 문자값 (LENGH, INSTR 제외)

함수명	함수기능	예시	출력	설명
LOWER(대상)	소문자로 변환	LOWER('ABC')	abc
UPPER(대상)	대문자로 변환	UPPER('abc')	ABC
SUBSTR(대상,m,n)	문자열 중 m위치의 n개 문자열 추출	SUBSTR('ABCDE',2,3)	BCD
INSTR(대상,찾을문자열)	대상에서 찾을 문자열 위치 추출	INSTR('A&B&C&','&',3,2)	6	3번째 글자부터 시작해서 2번째로 나온 & 위치
LTRIM(대상,삭제문자열)	특정 문자열을 왼쪽에서 삭제	LTRIM('AABABAA','A')	BABAA	삭제문자열 입력하지 않을 경우 공백 삭제
RTIRM(대상,삭제문자열)	특정 문자열을 오른쪽에서 삭제	RTRIM('AABABAA','A')	AABAB	삭제문자열 입력하지 않을 경우 공백 삭제
TRIM(대상)	특정 문자열을 양쪽에서 삭제	TRIM('AABABAA','A')	BAB	Oracle Trim은 공백삭제만 가능
LPAD(대상,n,문자열)	왼쪽에 문자열을 추가하여 총 n 길의 리턴	LPAD('ABC',6,'*')	***ABC
RPAD(대상,n,문자열)	오른쪽에 문자열을 추가하여 총 n 길의 리턴	RPAD('ABC',6,'*')	ABC***
CONCAT(대상1, 대상2)	문자열 결합	CONCAT('A','B')	AB	두개 인수만 가능
LENGTH(대상)	문자열 길이	LENGTH('ABCDEFG')	7
REPLACE(대상,찾을문자열,바꿀문자열)	문자열 치환 및 삭제	REPLACE('ABBBB','AB','ac')	acBBB	바꿀문자열 생략 또는 빈칸으로 입력 시 찾을문자열 삭제 기능
TRANSLATE(대상,찾을문자열,바꿀문자열)	글자를 1:1로 치환	TRANSLATE('ABBA','AB','ab')	abba	매칭 글자끼리 치환 (A→a, B→b)

 

SQL-Server
- SUBSTR → SUBSTRING
- LENGTH → LEN
- INSTR → CHARINDEX

 

숫자형 함수

• 숫자 값을 반환
• 단일행 함수 형태
• ORACLE & SQL-server 함수 거의 동일함

함수명	함수기능	예시	출력	설명
ABS(숫자)	절대값 반환	ABS(-7.5)	7.5
ROUND(숫자,자리수)	소수점 자리 반올림	ROUND(123.456,2) ROUND(123.456,-2)	123.46 100	음수일 경우 정수자리
TRUNC(숫자,자리수)	소수점 자리 버림	TRUNC(123.456,2)	123.45
SIGN(숫자)	양수 (1) 음수 (-1) 0 (0) 반환	TRUNC(123.456,-2)	100
FLOOR(숫자)	작거나 같은 최대 정수값 리턴	FLOOR(3.5)	3
CEIL(숫자)	크거나 같은 최대 정수값 리턴	CEIL(3.5)	4
MOD(숫자1, 숫자2)	숫자 1 / 숫자 2 한 값의 나머지 리턴	MOD(7,2)	1
POWER(m,n)	m의 n 거듭제곱값 리턴	POWER(2,4)	16
SQRT(숫자)	루트값 리턴	SQRT(16)	4

 

날짜형 함수

• 날짜 연산 관련 함수
• ORACLE & SQL-server 함수 거의 다름

함수명	함수기능	예시	출력	설명
SYSDATE	현재 날짜 시간 리턴	SYSDATE	2024/03/08 11:23:34	출력형식에 따라 날짜만 리턴될 수 있음
CURRENT_DATE	현재 날짜 리턴	CURRENT_DATE	2024/03/08	출력형식에 따라 날짜/시간이 리턴될 수 있음
CURRENT_TIMESTAMP	현재 타임스탬프 리턴	CURRENT_TIMESTAMP	2024/03/08 11:23:34 +09:00
ADD_MONTHS(날짜,n)	날짜에서 n개월 후 날짜 리턴	ADD_MONTHS(SYSDATE,3)	2024/06/08 11:23:34	n이 음수인 경우 n개월 전 과거 날짜 리턴
LAST_DAY(날짜)	주워진 월의 마지막 날짜 리턴	LAST_DAY(SYSDATE)	2024/03/31 11:23:34
LAST_DAY(날짜,n)	주워진 날짜 이후 지정된요일의 첫번째 날짜 리턴	LAST_DAY(SYSDATE,1)	2024/03/11 11:23:34	1: 일요일, 2:월요일...
ROUND(날짜, 자리수)	날짜 반올림	ROUND(SYSDATE, 'MONTH')	2024/03/01 00:00
TRUNC(날짜, 자리수)	날짜 버림	TRUNC(SYSDATE, 'MONTH')	2024/03/01 00:00

 

SQL-server
- SYSDATE → GETDATE
- ADD_MONTHS → DATEADD (모든 단위 날짜 연산 가능)
- MONTHS_BETWEEN → DATEDIFF (두 날짜 사이의 연,  월, 일 추출)

 

변환 함수

• 값이 가지고 있는 데이터 타입을 변환
• 문자 → 숫자, 숫자 → 문자, 날짜 → 문자 등

함수명	함수기능	예시	출력	설명
TO_NUMBER(대상)	숫자 타입으로 변경	TO_NUMBER('100')	100	문자 100 → 숫자 100
TO_CHAR(대상,포맷)	날짜 포맷 변경 숫자 포맷 변경	TO_CHAR(SYSDATE,'MM-DD-YYYY') TO_CHAR(9000,'9,999')	03-08-2024 9,000	날짜 포맷 (문자 리턴) 숫자 포맷 (문자 리턴)
TO_DATE(문자,포맷)	문자를 날짜로 변경	TO_DATE('2024/01/01','YYYY/MM/DD')	2024/01/01	문자 → 날짜
CAST(대상 AS 데이터타입)	대상 데이터타입 변경	CAST('100' AS int)	100	문자 100 → 숫자 100

 

SQL-server
- TO_NUMBER, TO_DATE, TO_CHAR → CONVERT
- 단순 변환 시 주로 CAST 사용

 

그룹 함수

• 다중행 함수
• 여러 값의 input → 하나의 요약 값으로 리턴
• GROUP BY와 함께 자주 사용됨
• ORACLE & SQL-server 함수 거의 동일함

함수명	함수기능	설명
COUNT()	행의 수 리턴
SUM()	총 합계 리턴
AVG()	평균값 리턴
MIN()	최소값 리턴
MAX()	최대값 리턴
VARIANCE()	분산 리턴
STDDEV()	표준편차 리턴

 

SQL-server
- VARIANCE → VAR
- STDDEV → STDEV

 

일반 함수

• 그 외 널 치환 함수 등

함수명	함수기능	설명
DECODE(대상,값1,리턴1,값2,..)	대상이 값1일 경우 리턴1, 값2일경우 리턴 2 ... 그 외 리턴값 리턴	대소비교 치환 불가 오라클만 존재
NVL(대상,치환값)	대상이 NULL 일경우 치환값으로 리턴
NVL2(대상,치환값1,치환값2)	대상이 NULL 일경우 치환값 1로 리턴 대상이 NULL 아닐 경우 치환값 2로 리턴
COALESCE(대상1,대상2,...그외리턴)	대상 1이 NULL 이면 대상2 대상 2가 NULL 이면 대상3  ... 모두가 NULL 인경우 그외 리턴값	그외 리턴값 생략 시 NULL 리턴
CASE	조건별 치환 및 연산

 

 

예시: DECODE

SELECT
	DEPARTMENT_NO,
	TITLE, 
	DECODE (DEPARTMENT_NO, 10,  #DEPARTMENT_NO 값이 10일경우 아래 한번 더 DECODE
    	DECODE (TITLE,'MANAGER','A','B'),'C') AS CATEGORY ) #10일 경우 TITLE이 'MANAGER'이냐에 따라 A, B로 리턴하도록
FROM TABLE;

 

 

DEPARTMENT_NO	TITLE	CATEGORY	참고
10	MANAGER	A	DEPARTMENT_NO 값이 10일때 TITLE에 따라 별도 DECODE
10	ASSISTANT	B
20	MANAGER	C	DEPARTMENT_NO 값이 10이 아니므로 C 리턴
20	ANALYST	C
30	MANAGER	C

 

예시: NVL, NVL2

SELECT
	CALCULATION,
    NVL(CALCULATION,0), #CALCULATION 값이 NULL 일 경우 0으로 리턴
    NVL2(CALCULATION,CALCULATION*2,1000) #CALCULATION 값이 NULL이 아닐 경우 기존 값에 x2, NULL 일 경우 1000으로 리턴
FROM TABLE;

 

CALCULATION	NVL(CALCULATION,0)	NVL2(CALCULATION,CALCULATION*2,1000)
	0	1000
400	400	800
700	700	1400
	0	1000
500	500	1000

 

예시: CASE

다음과 같이 WHEN 사이에 어떤 대상이 있을 경우 생략되어 조건값이 설정되기도 함

--1
CASE DEPARTMENT
	WHEN 10 THEN 'A'
    WHEN 20 THEN 'B'
    WHEN 30 THEN 'C'
    ELSE 'ETC'
END AS NAME1

--2
CASE
	WHEN DEPARTMENT = 10 THEN 'A' 
	WHEN DEPARTMENT = 20 THEN 'B' 
	WHEN DEPARTMENT = 30 THEN 'C'
	ELSE 'ETC'
END AS NAME2