SQLD 2과목 SQL 기본 및 활용

이세령·2025년 11월 5일
SQLDdb
0

기록

목록 보기
9/9

관계형 DB

계정 + 테이블 + 스키마

테이블

  • 하나의 테이블은 반드시 하나의 유저 소유여야 한다
  • 소유자가 다를경우 같은 이름의 table 생성 가능하다
  • 행 단위로 데이터가 입력, 삭제 된다
  • 수정은 cell 단위로 가능하다

관계형 DB 특징

  • 신뢰성이 높고 무결성을 보장한다
  • 분류, 정렬, 탐색이 빠르다
  • 스키마 수정이 어렵다
  • DB의 부하를 분석하는게 어렵다(성능 판별)

무결성

데이터의 정확성과 일관성을 유지하고 결손과 부정합이 없음을 보장

개체 무결성

기본키 => NULL, 중복이 아니다

참조 무결성

외래키 => NULL 허용, 기본키 값

도메인 무결성

정해진 도메인에 속한다

NULL 무결성

NULL을 허용하지 않는다

고유 무결성

중복을 허용하지 않는다

키 무결성

1관계 당 1키

SQL의 종류

DDL (Data Definition Language)

CREATE, ALTER, DROP, TRUNCATE

DML (Data Manipulation Language)

INSERT, DELETE, UPDATE, MERGE

DCL (Data Contrl Language)

GRANT, REVOKE

TCL (Transaction Control Language)

COMMIT, ROLLBACK

DQL (Data Query Language)

SELECT

SQL조회

SELECT * | 컬럼명 | 표현식
FROM 테이블명 or 뷰명
WHERE 조회 조건
GROUP BY 그룹핑컬럼명
HAVING 그룹핑 필터링 조건
ORDER BY 정렬컬럼명
  • 해석순서
    FROM > WHERE > GRUOP BY > HAVING > SELECT > ORDER BY

SELECT

  • alias(별칭) 사용 가능
  • ORDER BY절에서만 사용가능
  • (" ") 큰따음표를 사용하는 경우
    • 공백
    • 특수문자(_제외)
    • 대소문자 그대로 표현

FROM

  • alias(별칭) 사용 가능
    • ORACLE 에서 AS 생략 불가, SQL Server는 사용/생략 가능

함수

문자함수

LOWER(문자열)

문자열을 소문자로 변경

UPPER(문자열)

문자열을 대문자로 변경

SUBSTR(문자열, 위치1, 위치2)

문자열의 m위치에서 n개의 문자열 추출

INSTR(문자열, 찾을문자열, m, n)

문자열에서 찾을문자열 위치 반환
m 위치에서 시작하여 n번째 발견된 문자열 위치

LTRIM(문자열, 삭제 문자열)

문자열 중 삭제문자열을 왼쪽에서 삭제

RTRIM(문자열, 삭제 문자열)

문자열 중 삭제문자열을 오른쪽에서 삭제

TRIM(문자열)

문자열 중 특정 문자열을 양옆에서 삭제

LPAD(문자열, n, 추가문자열)

문자열 왼쪽에 추가문자열을 추가하여 총 n의 길이를 반환

RPAD(문자열, n, 추가문자열)

문자열 오른쪽에 추가문자열을 추가하여 총 n의 길이를 반환

CONCAT(문자열1, 문자열2)

문자열1 + 문자열2

LENGTH(문자열)

문자열 길이 반환

REPLACE(문자열, 찾을문자열, 바꿀문자열)

문자열에서 찾을문자열을 바꿀문자열로 치환

TRANSLATE(문자열, 찾을문자열, 바꿀문자열)

글자를 1대 1로 치환

숫자함수

ABS(숫자)

절대값 반환

ROUND(숫자, 자리수)

자리수 이전에서 반올림

TRUNC(숫자, 자리수)

자리수 이전에서 버림

SIGN(숫자)

숫자가 양수면 1, 음수면 -1, 0이면 0 반환

FLOOR(숫자)

작거나 같은 최대 정수 반환

CEIL(숫자)

크거나 같은 최소 정수 반환

MOD(숫자1,숫자2)

숫자1 나누기 숫자2, 하여 나머지 반환

POWER(m,n)

m의 n 제곱

SQRT(숫자)

루트값

기타함수(중요)

DECODE(대상, 값1, 리턴1, 값2, 리턴2...., 그외리턴)

대상이 값1이면 리턴1, 값2 이면 리턴 2 .... 그외 그외리턴

NVL(대상, 치환값)

대상이 NULL이면 치환값 반환 아니라면 대상 반환

NVL2(대상, 치환값1,치환값2)

대상이 NULL이면 치환값2, 아니라면 치환값1 반환

COALESCE(대상1, 대상2, ...,그외)

대상들 중 NULL이 아닌 값 출력
모두 NULL이면 그외 출력

INSNULL(대상, 치환값)

대상이 NULL이면 치환값 반환

NULLIF(대상1, 대상2)

대상1 == 대상2 이면 NULL 반환
다르면 대상1 반환

CASE문

SELECT VAL1, 
		CASE VAL1 WHEN 5 THEN '값1'
        			WHEN 10 TEHN '값2'
                    		ELSE '기타'
         END AS VAL2

그외 SQL문

LIKE 연산자

S% // S로 시작하는 문자열
%S // S로 끝나는 문자열
%S% // 중간에 S가 들어가는 문자열
_S% // _(1자리), S가 두번째인 문자열

GROUP BY

  • 그룹에 대한 조건은 WHERE 절에서 사용 불가
    ex) sum, count, min
  • GROUP BY ~ HAVING (조건)
  • GROUP BY절에 명시되지 않은 컬럼은 전달 불가
SELECT dept, name, AVG(salary)
FROM emp
GROUP BY dept;

=> 오류 발생, name이 GROUP BY에 없고 집계함수도 아니기 때문에, dept 그룹에 직원이 여러명인데 어느 name을 출력할 지 결정할 수 없다.

HAVING

  • 그룹 함수 결과를 조건으로 사용
  • 내부 연산 순서가 SELECT 보다 먼저이기에 SELECT에서 선언된 Alias(별칭) 사용불가

ORDER BY

  • 실행 순서가 가장 마지막
  • 행의 순서를 변경하고 싶을 때 사용
  • 복합정렬
    • 먼저 정렬한 값의 동일한 결과가 있을 경우, 추가적으로 정렬가능
  • 별칭 정렬가능
  • ORACLE은 NULL을 마지막에 배치하고 SQL Server는 처음에 배치

JOIN

여러 테이블의 데이터를 사용하여 동시 출력하거나 참조할 경우 사용

  • FROM 절에 JOIN할 테이블 나열
  • 같은 열 이름이 있을 경우, Alias(별칭) 사용

INNER JOIN

조건이 성립하는 행만 반환

  • 두 테이블 간의 공통된 행만 조회
SELECT 학생.이름, 수강.강의코드
FROM 학생
INNER JOIN 수강 ON 학생.학번 = 수강.학번;

=> 학번을 기준으로 조인하며, 수강 내역이 없는 학생은 제외

OUTER JOIN

한쪽 테이블에는 데이터가 있지만, 다른 테이블에는 없는 경우에도 결과를 반환

LEFT OUTER JOIN

왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에 일치하는 데이터가 없을 경우 NULL을 반환

RIGHT OUTER JOIN

오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에 일치하는 데이터가 없을 경우 NULL을 반환

FULL OUTER JOIN

왼쪽과 오른쪽 테이블의 모든 데이터를 반환

  • 조인 조건이 맞지 않더라도 한쪽 테이블의 데이터는 유지되고, 다른 테이블에서 일치하는 값이 없으면 NULL로 채워진다

CROSS JOIN

모든 경우의 행의 조합

SELF JOIN

같은 테이블을 2번 이상 참조하여 연결

SELECT A.이름 AS 직원, B.이름 AS 관리자
FROM 직원 A
LEFT JOIN 직원 B ON A.관리자ID = B.직원ID;

EQUI JOIN

두 테이블 간 칼럼값이 서로 정확히 일치하는 경우 사용

  • 가장 많이 사용
  • NULL은 equl에 반응하지 않는다.
SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
 FROM   테이블1, 테이블2 
 WHERE  테이블1.칼럼명1 = 테이블2.칼럼명2;

NON-EQUI JOIN

두 테이블 간 칼럼값이 서로 정확하게 일치하지 않는 경우 사용

  • BETWEEN, >, <= 등 연산자들을 이용하여 JOIN 수행
SELECT 테이블1.칼럼명, 테이블2.칼럼명
FROM   테이블1, 테이블2 
WHERE  테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

NULL

  • 산술연산의 결과는 항상 NULL 이다.
  • 집계함수는 NULL을 무시한다.

    AVG(COL1) -> NULL, 30, 50 이면 80/2 로 계산한다.
    SUM(COL1+COL2+COL3) -> 행별로 계산한다.

NULL 비교

  • 비교 연산 결과는 모두 FALSE
  • INNER JOIN/NATURAL JOIN 조인 컬럼에 NULL 포함한 행은 생략

NULL 집합

  • COUNT 결과는 0
  • COUNT를 제외한 집계함수 결과는 NULL(SUM, AVG...)
  • 공집합의 집계함수 결과도 위와 동일하다.

NULL과 GROUP BY

  • NULL 그룹도 GROUP BY 결과로 출력된다.
  • HAVING 결과에 만족하는 그룹이 없는 경우, 공집합이 반환된다.

SQL활용

서브쿼리

쿼리 안의 쿼리

  • SELECT 문에서는 SELECT, FROM, WHERE, HAVING 절에서 사용 가능
    • GROUP BY, ORDER BY는 사용불가

연관 서브쿼리

메인 쿼리의 각 행을 참조하면서 실행되는 서브쿼리

  • 각 행마다 한번씩 실행되므로 비효율 주의
  • 주로 EXISTS 연산자 사용
SELECT 이름
FROM 학생 S
WHERE EXISTS (
    SELECT 1
    FROM 수강 C
    WHERE C.학번 = S.학번 AND C.강의코드 = 'CS101'
);

학생 테이블의 각 행을 참조 -> 수강 테이블에서 학번이 CS101 강의를 수강했는지 확인 -> 존재할 경우 해당 학생의 이름을 반환

비연관 서브쿼리

메인 쿼리 내 컬럼이 존재하지 않는 서브쿼리

  • 서브쿼리가 먼저 실행 후, 결과가 메인 쿼리에 제공
  • 주로 WHERE, FROM, SELECT 절에서 사용
SELECT 이름, 점수
FROM 학생
WHERE 점수 > (SELECT AVG(점수) FROM 학생);

서브쿼리가 먼저 실행되어 평균 점수를 계산 -> 해당 평균 점수보다 높은 학생만을 메인쿼리가 조회

다중 행 서브쿼리

IN

서브쿼리 결과와 값이 같은 모든 행 출력

WHERE COL1 IN (SELECT ~)

ALL

서브쿼리 결과 중, 최댓값보다 큰 값을 갖는 모든 행 출력

WHERE COL1 > ALL (SELECT ~)

서브쿼리 결과 중, 최솟값보다 작은 값을 갖는 모든 행 출력

WHERE COL1 < ALL (SELECT ~)

ANY

서브쿼리 결과 중, 최솟값보다 큰 값을 갖는 모든 행 출력

WHERE COL1 > ANY (SELECT ~)

서브쿼리 결과 중, 최댓값보다 작은 값을 갖는 모든 행 출력

WHERE COL1 < ANY (SELECT ~)

윈도우 함수

데이터의 특정 범위(윈도우) 내에서 계산

RANK()

데이터를 특정 기준으로 정렬

  • 동일한 값에 동일한 순위 부여
  • 다음 순위 건너뜀
  SELECT 이름, 점수,
         RANK() OVER (ORDER BY 점수 DESC) AS 순위
  FROM 학생;

DENSE_RANK()

  • 동일한 순위 부여 후, 다음 순위를 건너뛰지 않음
  SELECT 이름, 점수,
         DENSE_RANK() OVER (ORDER BY 점수 DESC) AS 순위
  FROM 학생;

ROW_NUMBER()

  • 고유한 순번 부여
  SELECT 이름, 점수,
         ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS 행번호
  FROM 학생;

윈도우 프레임 지정

CURRENT ROW

현재 행을 기준으로 윈도우 프레임 설정

  SELECT 이름, 점수,
         SUM(점수) OVER (ORDER BY 점수 DESC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적점수
  FROM 학생;

=> 현재 행까지의 점수 누적 합산

ROWS BETWEEN vs RANGE BETWEEN

ROWS BETWEEN

물리적인 행 개수 기준으로 인도우 설정

  • 고정된 개수(이전 N행, 이후 N행)로 프레임 설정

RANGE BETWEEN

ORDER BY 기준값이 동일한 행 포함(논리적 범위)

  • 특정 값이 같은 행을 포함하여 프레임 설정
키워드설명
UNBOUNDED PRECEDING해당 파티션의 첫 번째 행부터 현재 행까지
n PRECEDING현재 행을 포함하여 이전 n개 행까지
CURRENT ROW현재 행만 포함
n FOLLOWING현재 행을 포함하여 이후 n개 행까지
UNBOUNDED FOLLOWING현재 행부터 파티션의 마지막 행까지

GROUPING 함수

ROLLUP 혹은 CUBE 연산에서 생성된 소계 및 합계 행을 식별하기 위해 사용

  SELECT 부서번호,
         직책,
         GROUPING(부서번호) AS 부서_그룹핑,
         GROUPING(직책) AS 직책_그룹핑,
         SUM(급여) AS 총급여
  FROM 직원
  GROUP BY ROLLUP(부서번호, 직책);

ROLLUP()

ROLLUP(a,b) = GROUPING SETS((a,b),(a),())

CUBE()

인자로 들어간 칼럼에 대해 모든 경우의 수로 부분합계
CUBE(a,b) = GROUPING SETS((a,b), (a), (b), ())

PARTITION BY 문법

  • 윈도우 함수에서 데이터를 그룹화하는 역할
  • 특정 열을 기준으로 데이터를 그룹화
SELECT 열이름,
       윈도우함수() OVER (PARTITION BY 그룹기준 ORDER BY 정렬기준)
FROM 테이블명;

예제

PARTITION BY를 사용하여 그룹 내에서 순위 계산

sql
복사편집
SELECT 이름, 부서, 급여,
       RANK() OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 부서별순위
FROM 직원;
설명
  • PARTITION BY 부서를 사용하여 부서별로 그룹을 나눔
  • 그룹 내에서 ORDER BY 급여 DESC를 기준으로 부서별 순위 계산

결과 예시

이름부서급여부서별순위
김철수영업부60001
이민호영업부52002
박영희마케팅부55001
정하나마케팅부50002

PARTITION BY를 사용한 집계 함수 활용

sql
복사편집
SELECT 이름, 부서, 급여,
       SUM(급여) OVER (PARTITION BY 부서) AS 부서별급여총합,
       AVG(급여) OVER (PARTITION BY 부서) AS 부서별평균급여
FROM 직원;
설명
  • 부서별로 SUM(급여), AVG(급여) 값을 계산하여 각 행에 표시
  • GROUP BY와 달리 개별 행을 유지하면서 부서별 급여 합계 및 평균을 구함

결과 예시

이름부서급여부서별급여총합부서별평균급여
김철수영업부6000112005600
이민호영업부5200112005600
박영희마케팅부5500105005250
정하나마케팅부5000105005250

PARTITION BY와 ROWS BETWEEN을 활용한 누적 합계

sql
복사편집
SELECT 이름, 부서, 급여,
       SUM(급여) OVER (PARTITION BY 부서 ORDER BY 급여 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 부서별누적급여
FROM 직원;
설명
  • PARTITION BY 부서 → 부서별로 그룹을 나눔
  • ORDER BY 급여 DESC → 급여가 높은 순서로 정렬
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → 현재 행까지의 누적 합계 계산

결과 예시

이름부서급여부서별누적급여
김철수영업부60006000
이민호영업부520011200
박영희마케팅부55005500
정하나마케팅부500010500

PARTITION BY와 LAG, LEAD 함수 활용

sql
복사편집
SELECT 이름, 부서, 급여,
       LAG(급여) OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 이전급여,
       LEAD(급여) OVER (PARTITION BY 부서 ORDER BY 급여 DESC) AS 다음급여
FROM 직원;
설명
  • LAG(급여) OVER (...) → 이전 행의 급여 가져오기
  • LEAD(급여) OVER (...) → 다음 행의 급여 가져오기
  • PARTITION BY 부서 → 부서별로 그룹을 나눔
  • ORDER BY 급여 DESC → 급여가 높은 순서로 정렬

결과 예시

이름부서급여이전급여다음급여
김철수영업부6000NULL5200
이민호영업부52006000NULL
박영희마케팅부5500NULL5000
정하나마케팅부50005500NULL

TOP N

상위 N개의 데이터 조회

SELECT TOP 3 이름, 점수
FROM 학생
ORDER BY 점수 DESC;

TOP N WITH TIES

점수가 높은 상위 N명 + 동일한 점수를 가진 추가 학생들까지 반환

SELECT TOP 3 WITH TIES 이름, 점수
FROM 학생
ORDER BY 점수 DESC;
profile
이세령
https://github.com/Hediar?tab=repositories
이전 포스트

SQLD 1과목 데이터 모델링의 이해

0개의 댓글