[SQLD] 2. SQL 기본 및 활용

2. SQL 기본 및 활용 (40문제)
(1) SQL 기본
(2) SQL 활용
(3) SQL 최적화 기본원리

---

(1) SQL 기본

 

• SQL 문장 (MDDT)

1. 데이터 조작어 (DML) - Manipulation (SUID)
- SELECT : 데이터 조회, 검색하는 명령어 (a.k.a RETRIEVE)
- INSERT, UPDATE, DELETE : 데이터에 변형을 가하는 명령어 (생성, 삭제, 수정 등)

비절차적 데이터 조작어 : 무슨 데이터를 원하는 지만을 명세함.
절차적 데이터 조작어 : 어떻게 데이터를 접근해야하는지 명세함 (PL/SQL: 오라클, T-SQL : SQL Server)

2. 데이터 정의어 (DDL) - Definition (CARD)
- CREATE, ALTER, DROP, RENAME
테이블 같은 데이터 구조 정의하는 명령어 (구조 생성, 변경, 삭제, 이름 변경 등)

3. 데이터 제어어 (DCL) - Control (GR)
- GRANT, REVOKE
데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어

- GRANT : 권한을 DBMS USER에게 부여하기 위해서 사용하는 명령어
GRANT SELECT, UPDATE ON A.TB_A TO B ( B에게 A.TB_A 권한 부여 )
- REVOKE : 권한을 회수하기 위해서 사용하는 명령어

4. 트랜잭션 제어어 (TCL) - Transaction ( DCL로 분류하기도 함 ) (CR)
- COMMIT, ROLLBACK
논리적인 작업 단위 묶어 DML로 조작된 결과를 작업단위(트랜잭션)별로 제어하는 명령어

• DDL 문법

CREATE TABLE PRODUCT
( PROD_ID VARCHAR2(10) NOT NULL
, PROD_NM VARCHAR2(10) NOT NULL
, REGR_NO NUMBER(10) NULL);

ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID);

CREATE INDEX IDX_P ON PRODUCT (REGR_NO);

ALTER TABLE _ ADD CONSTRAINT A PRIMARY KEY ()

CREATE TABLE PRODUCT
( PROD_ID VARCHAR2(10) NOT NULL
, PROD_NM VARCHAR2(10) NOT NULL
, REGR_NO NUMBER(10) NULL
, CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID) );

CONSTRAINT A PRIMARY KEY ()

CREATE TABLE PRODUCT
( PRODUCT_PK VARCHAR2(10) PRIMARY KEY
, PROD_ID VARCHAR2(4) DEFAULT '0000' NOT NULL
, PROD_NM VARCHAR2(10) NOT NULL
, REGR_NO NUMBER(10) NULL );

CREATE INDEX IDX_P ON PRODUCT (REGR_NO);

 

• 데이터 컬럼 정의 변경

- SQL Server

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명1 VARCHAR2(30) NOT NULL;
ALTER TABLE 테이블명 ALTER COLUMN 컬럼명2 DATE NOT NULL;

 

• 불필요한 컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

 

• 테이블 이름 변경 (ANSI, Oracle)

RENAME 과거 테이블명 TO 변경 테이블명;

 

• 참조 무결성 규정

( DELETE ) - (R)
- CASCADE : Master 삭제 시에 Child 같이 삭제
- SET NULL : Master 삭제 시에 Child 해당 필드 NULL
- SET DEFAULT : Master 삭제 시에 Child 해당 필드 Default 값으로 설정
- RESTRICT : Child 테이블에 PK 없는 경우 Master 삭제 허용
- NO ACTION : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

( INSERT ) - (D)
- AUTOMATIC : Master 에 PK가 없는 경우 Master PK 를 생성 후 Child 입력
- SET NULL : Master 에 PK가 없는 경우 Child 외부 키를 NULL 값으로 처리
- SET DEFAULT : Master 에 PK가 없는 경우 Child 외부 키를 지정된 기본값으로 입력
- DEPENDENT : Master 에 PK가 존재할 때만 Child 입력
- NO ACTION : 참조 무결성을 위반하는 입력 액션을 취하지 않음

• 제약조건

- PRIMARY KEY : UNIQUE, NOT NULL 특징 가짐
- UNIQUE KEY : 중복되는 값이 없으며, NULL 입력 가능
- NOT NULL
- CHECK : 데이터 무결성을 유지하기 위해 특정 컬럼에 설정하는 제약
- FOREIGN KEY : NULL 입력 가능, 여러 개 존재 가능, 참조 무결성 제약 받을 수 있다.

• 테이블명과 컬럼명의 명명

- 반드시 문자로 시작
- A-Z, a-z, 0-9, _, $, # 허용

• 테이블 삭제

- TRUNCATE TABLE ♥️
행 제거, 디스크 사용량 초기화, 스키마 정의 유지, 로그를 남기지 않는 삭제, ROLLBACK 불가, AUTO COMMIT
- DROP TABLE
행 제거, 디스크 사용량 초기화, 스키마 정의 삭제, 로그를 남기지 않는 삭제, ROLLBACK 불가, AUTO COMMIT
- DELETE FROM ♥️
데이터 모두 삭제, 로그를 남기는 삭제, 디스크 사용량 초기화 X ( ※ DELETE TABLE FROM 아님 ), 커밋 이전 ROLLBACK 가능, 사용자 COMMIT

• 중복 제거

SELECT DISTINCT 거주지, 근무지
FROM 고객지역;


SELECT 거주지, 근무지
FROM 고객지역
GROUP BY 거주지, 근무지;

 

• 트랜잭션 (Transaction)

데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작
- BEGIN TRANSACTION = BEGIN TRAN : 트랜잭션 시작

• 커밋 (Commit)

트랜잭션의 종료를 위한 대표적 명령어, 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영
- COMMIT TRANSACTION = COMMIT

• 롤백 (Rollback)

트랜잭션의 종료를 위한 대표적 명령어, 커밋되지 않은 상위의 모든 트랜젝션을 롤백함, 데이터에 대한 변경사항을 모두 폐기하고 변경 전의 상태로 되돌림
- ROLLBACK TRANSACTION = ROLLBACK : 최초의 BEGIN TRANSACTION 시점까지 모두 롤백 수행

• 저장점 (SAVEPOINT)

저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체작업 롤백이 아니라 SAVEPOINT까지 트랜잭션 일부만 롤백함
- SAVE TRANSACTION

• 트랜잭션의 4가지 특성

 

• 데이터베이스 트랜잭션에 대한 격리성이 낮은 경우 문제점

- Dirty Read
다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
- Non-Repeatable Read
한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제해 두 쿼리 결과가 다르게 나타나는 현상
- Phantom Read
한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

• Oracle과 SQL Server Commit

- Oracle
DDL 문장 수행 후 자동으로 commit 수행, 문장 수행 후 내부적으로 트랜젝션 종료. ROLLBACK해도 돌아오지않음
- SQL Server
DDL 문장 수행 후 자동으로 commit 수행 안함

• DDL

- VARCHAR2
'001' 숫자 형식으로 입력 가능하지만 'AA1'과 같은 것이 있으면 에러 발생
( Oracle : '' 입력시에 NULL로 입력, 이후 WHERE a = '' 이면 조회 불가, ISNULL )
( SQL Server : '' 입력시에 ''로 입력, 이후 WHERE a = '' 이면 조회 가능 )

• 단일 행 함수 VS. 다중 행 함수

함수의 입력 행 수에 따라 단일행 함수와 다중행 함수로 구분됨

- 단일 행 함수
추출되는 각 행마다 단일 값 반환
SELECT, WHERE, ORDER BY, UPDATE 의 SET 절에 사용 가능
데이터 타입 변경 가능
중첩해서 사용 가능

- 다중 행 함수
여러 개의 행이 입력, 단일 값 반환
그룹 함수가 다중 행 함수 ( SUM, AVG, MAX, MIN, COUNT )

• 단일 행 문자열 함수

- CONCAT(문자열1, 문자열2)
문자열1과 문자열2 연결

[연결연산자]
- Oracle : '||' , SQL server : '+' 와 동일
A : 1,2 / B : a,a
A||B : 1a, 2a

• 내장 함수

- CHR(10) : 줄 바꿈
- REPLACE( C1, CHR(10) ) == REPLACE( C1, CHR(10), '' ) : 줄 바꿈을 공백문자로 변경

• DATE 타입

- DATE + NUMBER : 일 수 (SYSDATE + 1 : 1일)
- DATE - NUMBER
- DATE + NUMBER/24 : 시간
(SYSDATE + 1/24 : 1시간)
(SYSDATE + 1/24/6 : 10분)
(SYSDATE + 1/24/60 : 1분)
(TRUNCATE(SYSDATE)+23/24 : 매일 밤 11시)
- DATE1 - DATE2

• NULL 함수

- NULLIF(MGR, 7689) : MGR이 7689와 같으면 NULL 표시, 같지 않으면 MGR
- ISNULL('COL2','X') : COL2가 NULL이면 X 출력, 오라클의 NVL()과 동일 (=NULL 아님)
- NVL(COL, 식1) : COL이 NULL이면 식1
- NVL2(COL, 식1, 식2) : COL이 NULL이 아니면 식1, NULL이면 식2
- COALESCE(식1, 식2) : NULL이 아닌 최초의 표현식을 나타냄
- NULL이 포함되어있는 연산(+-/*)의 결과는 NULL
- AVG, COUNT(표현식), SUM 식에서는 NULL 제외
- COUNT(*) : NULL 포함한 행의 수 출력

• 부정 비교 연산자

- !=, ^=, <> : 같지 않다.
- NOT 컬럼명 = : ~와 같지 않다
- NOT 컬럼명 > : ~보다 크지 않다

• SEARCHED_CASE_EXPRESSION, SIMPLE_CASE_EXPRESSION

CASE WHEN LOC = 'NEW YORK' THEN 'EAST' (SEARCHED)
(==) CASE LOC WHEN 'NEW YORK' THEN 'EAST' (SIMPLE)

• 중첩된 그룹함수는 결과가 1건

 

• ORDER BY, GROUP BY

ORDER BY, GROUP BY 절을 사용 시에는 GROUP BY 표현식이나 그룹함수 값만 사용 가능

ORDER BY
- 기본적으로 오름차순
- Oracle : NULL을 가장 큰 값으로 간주해 가장 마지막에 나옴
- SQL Server : NULL을 가장 작은 값으로 간주해 가장 먼저 나옴
- 컬럼명, Alias, 컬럼 순서 정수 등 혼용해 사용 가능

• SELECT 문장 실행 순서

(1) FROM (2) WHERE (3) GROUP BY (4) HAVING (5) SELECT (6) ORDER BY

• TOP(N)

WITH TIES 와 ORDER BY 를 함께 사용하면 N에 해당되는 값이 동일한 경우 함께 출력되도록 함

SELECT TOP(3) WITH TIES 컬럼명
FROM EMP
ORDER BY 컬럼명 DESC;

 

• JOIN

- DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 임의로 2개 정도씩 묶어서 JOIN 처리
- EQUI Join
Join에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용됨
= 연산자에 의해 수행되며 그 이외의 연산자는 Non EQUI Join
대부분 Non EQUI Join 수행가능하지만 불가능한 경우도 존재
LIKE JOIN (?)

---

(2) SQL 활용

 

• 순수 관계 연산자

SELECT(행 선택), PROJECT(컬럼 선택), JOIN, DIVIDE

• JOIN

- JOIN시에 적절한 조건이 없으면 카타시안 곱 발생
- JOIN시에 USING을 사용할 때, ALIAS나 테이블 이름과 같은 접두사 붙이지 않는다. (USING 컬럼명)
- CROSS JOIN ♥️
테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 의미
양쪽 집합의 M*N 건의 데이터 조합 발생
- JOIN 시 ON 절에서 같은 값이 없는 경우에는 NULL로 모두 채움

• Oracle (+) : 대상의 OUTER JOIN ♥️

 

• 집합 연산자

- UNION : 여러 개의 SQL 문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦
- UNION ALL : 여러 개의 SQL 문의 결과에 대한 합집합으로 결과에서 중복된 행들은 그대로 결과로 표시
- INTERSECT : 여러 개의 SQL 문의 결과에 대한 교집합, 중복된 행은 하나의 행으로 만듦
- EXCEPT : 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합으로 중복된 행은 하나의 행으로 만듦
(Oracle : MINUS)

- ORDER BY 1, 2 : 첫번째 컬럼, 두번째 컬럼 기준 오름차순 정렬

• 계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용
(계층형 데이터 : 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터)

- START WITH : 계층 구조 전개의 시작 위치를 지정함 (루트 노드, LEVEL 값 1)
- level 값이 큰 쪽에서 작은 쪽으로 전개함
- CONNECT BY : 다음에 전개될 자식 데이터를 지정
- ORDER SIBLINGS BY : 형제 Node 사이의 정렬 ♥️
- WHERE : 모든 전개를 진행한 이후 필터 조건으로 조건을 만족하는 데이터만을 추출하는데 활용

- PRIOR : CONNECT BY, SELECT, WHERE 절에서 사용 가능, CONNECT BY에서 현재 읽은 컬럼 지정
- PRIOR 자식=부모 : 부모 -> 자식 (순방향)
- PRIOR 부모=자식 : 자식 -> 부모 (역방향)

- SQL Server 계층형 질의
CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개
앵커 멤버를 실행해 기본 결과 집합을 만들고, 이후 재귀 멤버를 지속적으로 실행

• SELF JOIN

동일 테이블 사이의 조인으로 FROM절에 동일 테이블이 두 번 이상 나타난다.
동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일해 식별을 위해 반드시 Alias 사용해야함
하나의 테이블에서 두 개의 컬럼이 연관관계를 가지고 있는 경우에 사용함

SELECT A.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별 매출 B ON (A.일자 >= B.일자)
GROUP BY A.일자
ORDER BY A.일자;

 

• 서브쿼리

SELECT절, FROM절, HAVING절, ORDER BY절 등에서 사용 가능 (GROUP BY는 불가)
연관서브쿼리 : 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리로 메인쿼리에서 값을 제공받음

- 단일 행 서브쿼리 = 스칼라 서브쿼리
결과가 항상 1건 이하인 서브쿼리, 단일 행 비교연산자와 함께 사용됨 (=,<,>,<> 등)
단일 행 서브쿼리 비교연산자는 다중 행 서브쿼리 비교연산자로 사용할 수 없음

- 다중 행 서브쿼리
결과가 여러 건인 서브쿼리, 다중 행 비교연산자와 함께 사용됨 (IN, ALL, ANY, SOME, EXISTS)
다중 행 서브쿼리 비교연산자는 단일 행 서브쿼리 비교 연산자로도 사용할 수 있음

- 다중 컬럼 서브쿼리 (Oracle에서만 지원,SQL Server 지원하지 않음)
서브쿼리의 실행 결과로 여러 컬럼 반환, 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교,
서브쿼리와 메인 쿼리에서 비교하고자 하는 컬럼 개수와 위차가 동일해야함

• COUNT(0) = 1

 

• 뷰

단지 정의만을 가지고 있으며 실행 시점에 질의를 재작성해 수행
실제 데이터를 저장하고 있는 뷰를 생성하는 DBMS도 존재

- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
- 편리성 : 복잡한 형태의 SQL 문을 자주 사용할 때 뷰를 이용하면 편리하게 사용가능
- 보안성 : 숨기고 싶은 정보가 있다면 뷰를 생성할 때 해당 컬럼을 빼고 생성해 사용자에게 정보를 감출 수 있음

- 인라인 뷰 (Inline View)
FROM 절에 사용되는 서브쿼리
SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰(Dynamic View)라 데이터베이스에 해당 정보가 저장되지 않음

• 일반 그룹 함수

집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환

1. ROLLUP(A,B) ♥️
계층 구조를 가진 SUB TOTAL을 생성하는 함수로 나열된 컬럼의 순서가 변경되면 수행 결과도 변경됨
(1) (A와 , B를 GROUPBY COUNT한 결과) : (A,B)별 집계
(2) (A만을 GROUPBY COUNT한 결과, NULL) : A별 집계
(3) (NULL, NULL) 전체 COUNT : 전체 집계

2. CUBE(A,B)
- 결합 가능한 모든 값에 대해 다차원 집계를 생성함
- Grouping Columns이 가질 수 있는 모든 경우에 대해 Subtotal을 생성해야 하는 경우에 사용함
- ROLLUP에 비해 시스템에 많은 부담을 주니 사용에 주의해야함
- ORDER BY 로 결과에 대한 정렬 가능

3. GROUPING SETS
- 표시된 인수들에 대한 개별 집계를 구함 : ROLLUP의 첫 결과 (A,B)별 집계
- 표시된 인수들 간에는 계층 구조인 ROLLUP과 다르게 평등한 관계
- 인수의 순서가 바뀌어도 결과는 동일함
- ORDER BY 로 결과에 대한 정렬 가능

(+) ROLLUP(A,B)와 동일하게
GROUPING SETS( (A,B), A, () )

(+) CUBE(A,B)와 동일하게
GROUPING SETS( A, B, (A,B), () )

• GROUPING(A)

A가 NULL이면 1, NULL 아니면 0

• 윈도우 함수(Analytic Function)

- 결과에 대한 함수처리로 결과 건수는 줄지 않는다 ( 줄어드는 것은 GROUP 함수 )
- Partition과 Group by 구문은 의미적으로 유사
- Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일
- 윈도우 함수 적용범위는 Partition을 넘을 수 없음

• RANK

ORDER BY를 포함한 쿼리문에서 특정 컬럼에 대한 순위값을 매기는 함수로, 동일한 값에 대해서는 동일한 순위를 부여
RANK() OVER (ORDER BY A DESC)
(1,1,3,4,4)

• DENSE_RANK

RANK 함수와 흡사하나 동일한 순위를 하나의 건수로 취급
RANK() OVER
(1,1,2,3,3)

• ROW_NUMBER 🤦🏻‍♀️

ROW_NUMBER는 고유한 순위를 부여함, 일련번호
ROW_NUMBER() OVER

• RANGE BETWEEN 10000 PRECENDING AND 10000 FOLLOWING

-10000 ~ +10000 사이의 범위

• LAG / LEAD

LAG(A) : 바로 이전의 값, 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음
LEAD(A) : 바로 이후 값을 가져오는 함수이나 SQL Server에서는 불가한 기능

• ROLE

- 시스템 및 오브젝트 권한을 모아둔 것
- DBMS 관리자가 사용자별로 권한 관리 하는 부담을 줄이기 위해 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에 중개 역할을 수행하는 ROLE 제공

• PL/SQL

- 블록 구조로 각 기능별로 모듈화 가능
- 변수, 상수 등을 선언해 SQL 문장 간 값을 교환
- IF, LOOP 등 절차형 언어를 사용해 절차적 프로그램이 가능하도록 함
- DBMS 정의 에러나 사용자 정의 에러를 정의해 사용할 수 있음
- Oracle에 내장되어있어 Oracle, PL/SQL 지원하는 어떤 서버로도 프로그램을 옮길 수 있음
- 응용 프로그램의 성능을 향상시킴
- 여러 SQL문장을 블록으로 묶고 한 번에 블록 전부를 서버로 보내 통신량을 줄일 수 있음
- Procedure, User Defined Function(UDF), Trigger 객체 작성
- 트랜잭션 분할 가능하며 각 트랜잭션 별로 호출 가능
- 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야 함

• 저장 모듈 (PL/SQL, LP/SQL, T-SQL)

Oracle : Procedure, User Defined Function, Trigger
SQL 문장을 데이터베이스 서버에 저장해 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 SQL 컴포넌트 프로그램
독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

- 저장형 프로시저(Procedure)
SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미
CREATE PROCEDURE 명령 사용해 생성
TCL 가능
EXECUTE 명령어로 실행

- 사용자 정의 함수(User Defined Function)
단독 실행보다는 다른 SQL 문을 통해 호출되고 결과를 리턴하는 SQL 보조역할
CREATE FUNCTION 명령 사용해 생성 -> 한번 최대 데이터 리턴 1개, 없으면 NULL 리턴

- 트리거(Trigger)
INSERT, UPDATE, DELETE 같은 DML 문이 수행될 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
CREATE TRIGGER 명령 사용해 생성
데이터 무결성과 일관성 유지
COMMIT, ROLLBACK TCL 실행 불가
데이터베이스 로그인 작업 정의 가능

---

 

(3) SQL 최적화 기본 원리

 

---

 

---

(+) 출처

[한국데이터산업진흥원] SQL 자격검정 실전문제