[SQLD 학습 정리] SQLD 2과목 - SQL 기본 및 활용

도비·2025년 2월 24일

DBMS SQLD 개발 학습정리

SQLD

목록 보기

2/5

유튜브 강의 정리본 입니다.

2과목 1단원, 관계형 데이터베이스 개요

관계형 데이터베이스 : 하나의 테이블에 있었던걸 분리시킨 후 이들간의 관계를 만든다

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

계정 : 데이터의 접근 제한을 위해 여러 업무별/시스템별 계정이 존대
테이블 : DBMS의 DB 내 데이터가 저장되는 형식
스키마 : 테이블의 구성, 정보 등의 구조를 정의

테이블

행(row)과 열(Column)을 갖는 2차원 주소
데이터를 입력하여 저장하는 최소 단위

특징

하나의 테이블은 반드시 하나의 계정의 소유여야 함 (여러명이 조회하는건 상관 없음)
- 테이블간 관계는 일대일(1:1) / 일대다(1:N) / 다대다(N:N)의 관계를 가짐
테이블명은 중복될 수 없지만, 소유자가 다른 경우 같은 이름으로 생성 가능
행 단위로 데이터가 입력 및 삭제, 값 단위로 데이터가 수정

SQL

관계형 데이터베이스에서 데이터 조회 및 조작, DBMS 관리 기능을 명령하는 언어
DDL(정의어), DML(조작어), DCL(제어어)로 구분
SQL 문법은 대/소문자를 구분하지 않음 (하지만 현업에서는 표준이 있음)

특징

데이터의 분류, 정렬, 탐색 속도가 빠름
신뢰성이 높음, 무결성 보장
스키마를 수정하는게 어려움
DB의 부하를 분석하기 어려움 (복잡한 시스템이다 보니)

데이터의 무결성(Integrity)

의미 : 정확성 및 일관성을 가짐, 데이터의 결손과 부정합이 없음
종류

개체 무결성 : 기본 키는 NULL이나 중복값을 가질 수 없다.
참조 무결성 : 외래키 값은 NULL이거나 참조 테이블의 기본키 값과 동일해야 한다.
- 기본키가 아닌 다른 값은 참조할 수 없다.
도메인 무결성 : 주어진 속성 값이 정의된 도메인 범위 안에 있어야 한다.
NULL 무결성 : 특성 속성(ex. Nullable이 FALSE인 속성)에 대해 NULL을 허용하지 않음
고유 무결성 : 특정 속성(ex. Unique가 TRUE인 속성)에 대해 중복되는 값을 허용하지 않음
키 무결성 : 릴레이션(관계)에는 적어도 하나의 키(Join 키)가 존재해야 함

SQL의 종류

DDL (Data Definition Language): 구조 자체를 변경하는 명령어
- Types: CREATE, ALTER, DROP, TRUNCATE
- 되돌릴 수 없음 (Auto Commit - 자동 확정)
- TRUNCATE가 데이터를 다 날리는건데 DDL인 이유? Auto Commit.
DML (Data Manipulation Language): 데이터를 조작하는 명령어
- Types: INSERT, DELETE, UPDATE, MERGE
DCL (Data Control Language): GRANT, REVOKE(권한 회수)
TCL (Transaction Control Language): 트랜잭션을 제어하는 명령어
- Types: COMMIT. REVOKE
DQL (Data Query Language): 쿼리어
- Types: SELECT

2과목 2단원, SELECT

SELECT 문 구조

SELECT * | 컬럼명 | 표현식 -- 조회하고 싶은 대상
	FROM 테이블명 또는 뷰명 -- 데이터의 출처
	WHERE 조회 조건 -- 행을 걸러내는 조건
	GROUP BY 그룹필터링컬럼 -- 그룹 연산이 필요할 때
	HAVING 그룹핑 필터링 조건 -- GROUP BY를 수행한 결과에 조건을 추가하고 싶을 때
	ORDER BY 정렬컬럼명 -- 마지막으로 정렬

6개의 절로 구성, 각 절의 순서대로 작성해야 함
파싱(문법적 해석) 순서
- FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
- 데이터가 있어야 하니까 FROM 먼저, 그 다음 WHERE, GROUP으로 묶고, 그 후에 결과에 넣을 속성 정하고, 마지막으로 정렬

SELECT 절

조회할 컬럼명/연산결과를 정의
표시할 대상에 Alias(별칭, 출력할 다른 이름) 지정 가능 (대소문자 구분 X)
- SELECT 절 이후에 파싱하는 ORDER BY에서만 사용 가능
- 한글 사용 가능, 예약어(avg, count, decode, SELECT, FROM ...)는 사용 불가
다음의 경우 별칭에 반드시 쌍따옴표로 전달
- 1. 공백을 포함하는 경우
- 언더바 (_)를 제외한특수문자를 포함하는 경우
- 별칭의 대소문자 그대로 전달할 경우
- AS는 생략 가능

FROM 절

가장 먼저 실행되는 절, 데이터의 출처를 밝히는 역할
테이블명 대신 뷰명을 전달해도 됨
- 뷰 : 데이터를 조회할 수 있지만, 물리적으로 저장되어있지 않는 객체
테이블 여러개 전달 가능, 조인 조건 없이 테이블명만 나열 시 카타시안 곱 발생
별칭을 사용할 수 있음, ORACLE에서는 AS 사용 불가, SQL Server는 사용/생략 가능
ORACLE에서는 의미상 필요하지 않더라도 생략할 수 없음
- 필요없는 경우 DUAL이라는 Dummy Table을 선언해야 함
- SQL Server는 가능 (오늘 날짜 조회 등)
- ORACLE 23c 버전부터는 생략 가능 (하지만, 자격증에서 물어보지는 않을 듯)
ex) SELECT 24 * 123 FROM DUAL;
테이블 별칭을 선언한 이후에는 테이블명이 아닌 별칭으로 사용해야 함.

2과목 3단원, 함수

input value에 대해 out value를 출력해주는 객체
from절을 제외한 모든 절에서 사용 가능

함수의 기능

계산을 수행, 데이터의 항목을 수정

입력값의 수에 따른 함수의 종류

단일행 함수 : input과 output의 관계가 1:1
복수행 함수 (그룹함수 / 집계함수) : 여러 건의 데이터를 입력받아 하나의 요약값을 반환

입 출력값의 타입에 따른 함수 분류

문자형 함수

문자열 결함, 추출, 삭제, 치환 ...
단일행 함수 형태
output도 대부분 문자값 (cf. LENGTH, INSTR)

LOWER(대상) / UPPER(대상) : 대소문자 치환 (~= Inicap <- Camel 식으로 변환)
SUBSTR(대상, m, n) : m 위치에서 n개의 문자열 추출
- ex) SUBSTR('ABCDE', 2, 3) => BCD
INSTR(대상, 찾을문자열, m, n) : 찾은 문자열 반환 (m번째부터, n번째 발견된 문자열)
- INSTR('A#B#C#', '#', 3, 2) => 6
- m의 값이 음수가 되면 스캔 방향도 바뀜
LTRIM(대상, 삭제문자열) / RTRIM(대상, 삭제문자열) : 특정 문자열을 왼쪽에서 삭제
- ~= TRIM(대상) : 양쪽에서 삭제 (ORACLE에서는 공백만 가능)
LPAD(대상, n, 문자열) / RPAD(대상, n, 문자열) : 대상의 왼쪽 / 오른쪽에 문자열을 삽입해서 총 n만큼의 길이를 반환함
- ex) RPAD('ABC', 5, '#') => ABC##
CONCAT(대상1, 대상2) : 문자열 결함
LENGTH(대상) : 문자열 길이 반환
- LENGTHB(대상) : 문자열의 바이트 수 반환
REPLACE(대상, 찾을문자열, 바꿀문자열) : 문자열 치환
- REPLCAE('ABBA', 'AB', 'ab') => abBA
TRANSLATE(대상, 찾을문자열, 바꿀문자열) : 글자(char)를 1대1로 치환
- 바꿀문자열을 생략할 수 없음, 빈 문자열 전달시 전체가 Null을 반환
- TRANSLATE('ABBA', 'AB', 'ab') => abba
cf. ORACLE vs SQL Server
SUBSTR vs SUBSTRING
LENGTH vs LEN
INSTR vs CHARINDEX

숫자형 함수

ABS(숫자) : 절댓값
ROUND(숫자, 자리수) : 반올림
TRUNC(숫자, 자리수) : 버림
SIGN(숫자) : 양수면 1, 음수면 -1, 0이면 0 반환
FLOOR(숫자) : 작거나 같은 최대 정수 = 내림
CEIL(숫자) : 크거나 같은 최대 정수 = 올림
MOD(숫자1, 숫자2) : 숫자1 % 숫자2
POWER(m, n) : m의 n 제곱
SQRT(숫자) : 루트값

날짜형 함수

DBMS마다 다르기에 출제율이 낮을 것이라 예상됨

SYSDATE : 현재 날짜와 시간 리턴
CURRENT_DATE : 현재 날짜
CURRENT_TIMESTAMP : 현재 타임스탬프
ADD_MONTHS(날짜, n) : 날짜에서 n개월 후
MONTH_BETWEEN(날짜1, 날짜2) : 날짜1과 날짜2 사이의 개월수
LAST_DAY(날짜) : 주어진 월의 마지막 날짜 리턴
NEXT_DAY(날짜, n) : 오는 요일의 날짜를 리턴
- n은 숫자지만 1 : 일요일, 2 : 월요일 , ... , 7 : 토요일
ROUND(날짜, 자리수) : 날자 반올림
- ex) ROUND(Date("2025/02.20 14:31:00"), 'MONTH') => 2025-03-01 0:00 <- 맞나...?
- 기준은 무조건 15, 16부터 올림
TRUNC(날짜, 자리수) : 날짜 버림
월과 관련된 함수가 많은 이유 : 날짜에 + - 숫자를 하면 일 수가 바뀜, 그렇기에 월과 관련된 기능들을 두는 것
cf. ORACLE vs SQL Server
SYSDATE vs GETDATE
ADD_MONTHS vs DATEADD
MONTH_BETWEEN vs DATEDIFF

변환 함수

TO_NUMBER(문자)
TO_CHAR(대상, 포멧) : 대상은 숫자 / 날짜, 결과는 문자
- ex) TO_CHAR(9000, '9,999') => 9,000, TO_CHAR(9000, '09999') => 09000
- 9,999 : 천 단위 구분자 찍어줌 / 0~ : 부족한 자리수를 0으로 채움
TO_DATE(문자, 포멧) : 포멧 형식에 맞게 읽어 날짜로 리턴
- ex) TODATE('2024/01/01', 'YYYY/MM/DD')
FORMAT(날짜, 포멧) : 날짜의 포멧 변경
- SQL Server의 함수, FORMAT(GETDATE(), 'YYYY') => 2025
CAST(대상 AS 데이터 타입) : 타입 캐스팅
- CAST('100' AS int)

그룹 함수

COUNT, SUM, AVG, MIN, MAX, VARIANCE(분산), STDDEV(표준편차)
- NULL은 무시하고 연산
ORACLE vs SQL Server
VARIANCE vs VAR
STDDEV vs STDEV

일반 함수

DECODE(대상, 값1, 리턴1, 값2, 리턴2 ... 그 외 리턴) : 값과 리턴을 매핑 <- switch네
- DECODE(DEPTNO, 10, A, B) <- DEPTNO의 값이 10이면 A, 아니면(디폴트) B
- 디폴트가 없을 경우 Null 반환
MVL(대상, 치환값) : Null 치환
MVL2(대상, 치환값1, 치환값2) : Null일때와 Null이 아닐 때 모두 치환
- Null이 아니면 치환값 1, Null이면 치환값 2
NVL2(COMM, COMM*1.1, 0) => COMM1*1 or 0
COALESCE(대상1, 대상2, ... , 그 외 리턴) : 널이 아닌 값을 리턴하는 함수
- 대상 1이 Null이 아니면 1을 반환, Null이면 대상2로 ...
ISNULL(대상, 치환값) : SQL Server의 치환 함수
NULLIF(대상1, 대상2) : 두 값이 같으면 Null, 다르면 대상 1 반환
CASE문 : 조건별 치환 및 연산 수행 (대소 비교 가능)

SELECT SCORE, GRADE
	CASE WHEN SCORE < 80 THEN 'C',
		 WHEN SCORE < 90 THEN 'B',
						 ELSE 'A'
		END AS GRADE
FROM EMP;

SCORE 테이블에서 80보다 작으면 C를, 90보다 작으면 B를, 보다 크면 A를 GRADE 속성값으로
만약 동등 비교고 대상이 중복되면 SCORE을 제외해도 됨

SELECT AREA_CODE, AREA_NAME
	CASE AREA_CODE WHEN 031 THEN 'Gyeonggi',
				   WHEN 054 THEN 'Gyeongbuk',
				   WHEN 051 THEN 'Busan',
						    ELSE 'DK'
		END AS AREA_NAME
FROM EMP;

2과목 4단원, WHERE 절

개념

조건에 맞는 데이터만 조회하고싶을 경우, 행을 선택
여러 조건을 동시에 전달할 수 있음 (AND와 OR로 조건 연결)
Null 조회 시 IS NULL / IS NOT NULL 연산자를 사용해야 함

연산자

=, !=,<> , >, >=, <, <=
BETWEEN a AND b
IN(a, b, c)
LIKE : 패턴을 가진 조건 검색 (정규표현식과 유사해 보임)
IS NULL / IS NOT NULL
A AND B, A OR B, NOT A

주의 사항

문자나 날짜 상수 표현 시 반드시 홑따옴표 사용

예시

SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE NOT ENAME = 'SMITH' -- ENAME != SMITH가 더 낫겠지만...
	AND SAL >= 1500;

-- "%": 모든, "_": 한 자리
SELECT * FROM EMP
	WHERE ENAME LIKE 'S%'; -- 이름이 S로 시작하는 사원

SELECT * FROM EMP
	WHERE ENAME LIKE 'S_M%'; -- S로 시작하고 세 번째 문자가 M인 사원

2과목 5단원, GROUP BY 절과 HAVING 절

GROUP BY 개념

특정 조건에 따라 그룹으로 분리하여 계산할 때 쓰임
나열할 때는 ,를 사용
제외할 대상이 있다면 미리 WHERE 절에서 제외하는게 효율적
- WHERE -> GROUP BY 순서로 실행됨
GROUP BY로 얻은 값을 WHERE로 필터링할 수 없음 (WHERE이 먼저기에)
- 따라서 GROUP BY 뒤에 HAVING절로 필터링함

SELECT JOB, AVG(SAL) AS AVG_SAL -- 직업별 평균 구하기
	FROM EMP
	GROUP BY JOB;

SELECT DEPTNO, AVG(SAL) AS AVG_SAL -- 평균 급여가 3000 이상인 부서만
	FROM EMP
	GROUP BY DEPTNO
	HAVING AVG(SAL) >= 3000;

2과목 6단원, ORDER BY 절

개념

정렬할 때 사용하는 절
SELECT 문의 마지막에 배치되며 마지막에 수행됨
- SELECT절 뒤에있는 유일한 절이기에 유일하게 Column 별칭을 사용할 수 있음
1차 정렬, 2차 정렬 가능
- ASC와 DESC가 있음, 디폴트는 ASC (생략 가능)
- 2차 정렬 : 첫 번째 조건의 값이 같은 경우 두 번째 조건으로 정렬
숫자도 들어갈 수 있음 (숫자는 SELECT에 열거되어있는 순서의 대상을 의미)

2차 정렬 예시

SELECT EMPNO, ENAME, JOB, SAL, COMM -- JOB 순으로 정렬되지만 같을경우 COMM 순으로
	FROM EMP
	ORDER BY JOB ASC, COMM DESC;

NULL의 정렬

ORACLE 디폴트는 맨 마지막, SQL Server는 맨 먼저 정렬
- ORACLE의 경우 NULL LAST / NULL FIRST로 제어 가능

2과목 7단원, 조인

여러 테이블을 사용하거나 참조할 경우, 관계 규칙을 연결해서 함께 조회
FROM에 조회할 테이블을 (ORACLE 기준 : ,로 이어) 나열 (ANSI 기준 : OUTER JOIN시 순서 상관 있음) <- ORACLE <-> ANSI 변환 문제 기출
N개의 Table이 Join 되어야 하면 최소 N-1개의 Join 조건이 필요, 부족하면 불필요한 데이터의 조합이 출력될 수 있음 (카타시안 곱)
사원 테이블

종류

조건 형태

EQUI JOIN : 동등 조건 (가장 많은 JOIN 형태)

SELECT T1.C, T2,C
	FROM T1, T2
	WHERE T1.C = T2.C

NOT EQUI JOIN : 동등 조건이 아닐 경우 (크다/작다, IN ... )

SELECT E.NAME, E.SAL, S.GRADE
	FROM EMP E, SAL.GRADE S
	WHERE E.SAL BETWEEN S.LLOSAL AND S.HISAL;

조인 결과

INNER JOIN : 조건에 성립하는 데이터만 출력
OUTER JOIN : 성립하지 않는 데이터도 출력
- 기준 테이블의 위치에 따라 LEFT/RIGHT/FULL로 나뉨 <- ANSI 표준에서 테이블 순서가 중요한 이유
NATURAL JOIN : 두 테이블에 같은 이름으로 자연 연결
CROSS JOIN : 조건 생략 시 발생 가능한 모든 행을 출력 (카타시안 곱 발생)
SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인
- 앞에서 다뤘었음

2과목 8단원, 표준 조인

정의

ANSI 표준으로 작성되는 INNER JOIN, CROSS JOIN, NATURAL JOIN, OUTER JOIN
- ORACLE에서는 이러한 조인 방식을 쓰지 않음

INNER JOIN

INNER 생략 가능
반드시 조건절을 USING / ON 으로 작성해야 함

ON 절

조건을 명시 (ORACLE과 달리 WHERE과 ON이 명확히 구분됨)
괄호 생략 가능
이름이 같은 컬럼이 있다면 출처를 밝혀야 함

SELECT TABLE1.COMUMN1, TABLE2.COLUMN2
	FROM TABLE1 INNER JOIN TABLE2
	ON (TABLE1.JOIN_COLUMN1 = TABLE2.JOIN_COLUMN2);

USING 절

조인할 컬럼명이 같을 경우
괄호 필수
테이블 출처는 불필요

SELECT EMP.ENAME, DEPT.DNAME
	FROM EMP JOIN DEPT
	USING(DEPTNO);

NATURAL JOIN

조건을 명시하지 않아도 JOIN 됨
두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI JOIN
- USING, ON, WHERE로 조건 정의 불가
- NULL은 연결되지 않음

SELECT T1.ENAME, T2.DNAME
	FROM EMP NATURAL JOIN DEPT;

CROSS JOIN

JOIN 조건이 없을 경우, 생성 가능한 모든 데이터들의 조합 (카타시안곱)

SELECT T1.C1, T2.C2
	FROM T1 CROSS JOIN T2;

OUTER JOIN

조인조건이 성립되지 않아도 출력을 원할 때, 무엇을 기준으로 JOIN할 것인지에 따라.
OUTER 생략 가능 (LEFT OUTER JOIN -> LEFT JOIN)

LEFT OUTER JOIN

왼쪽 테이블의 데이터는 생략되지 않도록 함. (JOIN 조건이 성립하지 않아도, NULL로 채움 => 무조건 있음)

-- ORACLE 표준
SELECT *
	FROM STUDENT S, PROFESSOR P
	WHERE S.PROFNO = P.RROFNO(+) -- (+)가 LEFT OUTER JOIN을 의미
	AND S.GRADE IN (1, 4);

-- ANSI 표준
SELECT S.STUDNO, S.NAME AS 학생명, S.GRADE, S.PROFNO, 
		P.PROFNO, P.NAME AS 교수명
	FROM STUDENT S LEFT OUTER JOIN PROFESSOR P -- 테이블 순서가 중요하겠죠? 이거 기준으로 왼쪽 / 오른쪽이 될테니
	ON S.PROFNO = P.PROFNO
	WHERE S.GRADE IN (1,4);

RIGHT OUTER JOIN

마찬가지

FULL

양 테이블 모두 생략되는 컬럼은 없음
ORACLE은 이 결과를 지원하지 않음
- 다만 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION으로 구현할 수 있음

-- ORACLE 표준
SELECT *
	FROM STUDENT S, PROFESSOR P
	WHERE S.PROFNO = P.PROFNO(+)
	UNION
SELECT *
	FROM STUDENT S, PROFESSOR P
	WHERE S.PROFNO(+) = P.PROFNO;

-- ANSI 표준
SELECT S.STUDNO, S.NAME AS 학생명, S.GRADE, S.PROFNO, 
		P.PROFNO, P.NAME AS 교수명
	FROM STUDENT S FULL OUTER JOIN PROFESSOR P
	ON S.PROFNO = P.PROFNO;

도비

문과 였던 것...

이전 포스트

[SQLD 학습 정리] SQLD 1과목 - 데이터 모델링의 이해

다음 포스트