Kwangmin Kim - Data Governance Study

1 SQL (Structured Query Language)

SQL은 영문명을 풀이하면 구조를 갖는 질의 언어라는 의미인데 DBMS (Database Management System)를 다루는 언어이다.
SQL(Structured Query Language)
- Structure = DDL(Data Definition Language) + DML (Data Manipulation Language) + DCL(Data Control Language) + TCL (Transaction Control Language)
  - 4가지 모두 명령어들의 집합체 (SQL 함수의 집합체) 4가지의 다른 명령어 집합이 있음
SQL은 단순하게 질의만을 수행하는 것이 아니라 데이터베이스의 모든 작업을 통제하는 비절차적(Non-procedural) 언어
- 다시 말해, 비절차적 언어는 사용자가 “무엇을” 원하는지만 명시하고, “어떻게” 그것을 수행할지는 시스템에 맡기는 프로그래밍 방식이다.
- SQL의 비절차적 특성
  - 사용자는 원하는 결과만 명시 (예: 어떤 데이터를 조회하고 싶은지).
  - 데이터베이스 시스템이 그 결과를 얻기 위한 최적의 방법을 결정
- 절차적 언어와의 차이
  - 절차적 언어: 모든 단계를 순서대로 명시해야 함
  - 비절차적 언어: 원하는 결과만 명시하면 됨
- 예시: SQL 쿼리 markdown SELECT * FROM Customers WHERE City = 'London';
  - 사용자: London에 있는 모든 고객 정보를 원한다고만 명시
  - 시스템: 어떤 인덱스를 사용할지, 어떤 순서로 데이터를 읽을지 등을 결정
- 장점
  - 사용 편의성: 상세한 기술 지식 없이도 사용 가능
  - 최적화: 시스템이 최적의 실행 계획을 선택
  - 높은 생산성: 적은 코드로 복잡한 작업 수행 가능
- 특징
  - 데이터베이스 작업의 효율성 증대
  - 사용자는 데이터 자체에 집중 가능
  - 시스템의 내부 변경에도 쿼리는 그대로 사용 가능
독립 실행형 / 내장형
- 독립 실행형 : SQL 인터페이스를 이용하여 SQL 쿼리를 직접 DBMS에 입력
- 내장형
  - C, C++, Java 등의 프로그래밍 언어에 내장됨
  - Host Language + Data sublanguage로 구성됨

1.1 DDL (데이터 정의어)

스키마에 대한 명령어 : 스키마를 기술하기 위해 사용되며, 주로 DB 설계자가 사용
예시 (DBMS마다 문법이 조금씩 다를 수 있다.)

    -- 테이블 생성
    CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        FirstName VARCHAR(50),
        LastName VARCHAR(50),
        Department VARCHAR(50)
    );

    -- 테이블 구조 변경
    ALTER TABLE Employees ADD Salary DECIMAL(10, 2);

    -- 테이블 삭제
    DROP TABLE Employees;

    -- 테이블 이름 변경
    RENAME TABLE Employees TO Staff;

    -- Oracle에서의 다른 문법
    ALTER TABLE Employees RENAME TO Staff;

    -- 열 이름 변경 (DBMS에 따라 문법이 다를 수 있음)
    ALTER TABLE Staff RENAME COLUMN EmployeeID TO StaffID;

    -- MySQL에서 데이터베이스 이름 변경
    RENAME DATABASE old_db TO new_db;

    -- TRUNCATE는 테이블의 모든 데이터를 빠르게 삭제하는 데 사용
    -- 테이블 구조는 유지됩
    
    -- Employees 테이블의 모든 데이터 삭제
    TRUNCATE TABLE Employees;

    -- 특정 파티션 truncate (Oracle)
    TRUNCATE TABLE Sales PARTITION (sales_q1);

    -- 여러 테이블 동시에 truncate (일부 DBMS에서 지원)
    TRUNCATE TABLE Table1, Table2, Table3;

TRUNCATE의 특징
- DELETE보다 빠르게 실행됨.
- 일반적으로 롤백할 수 없음
- 자동으로 COMMIT 됨
- 테이블의 AUTO_INCREMENT 값을 리셋 (DBMS에 따라 다를 수 있음).
주의사항
- RENAME과 TRUNCATE는 DDL 명령어이므로 자동 커밋되며, 일반적으로 롤백할 수 없다.
- 데이터베이스 시스템에 따라 문법이 조금씩 다를 수 있으므로, 사용 중인 DBMS의 문서를 참조하는 것이 좋다.
- 이러한 명령어들은 데이터베이스 구조나 데이터에 큰 영향을 미치므로 신중하게 사용해야 한다.

1.2 DML (데이터 조작어)

주로 instance를 다루는 명령어
- 데이터의 조회 (Retrieval), 삽입 (Insertion) , 삭제 (Deletion), 갱신(Update)이
예시

    -- 데이터 삽입
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
    VALUES (1, 'John', 'Doe', 'IT');

    -- 데이터 조회
    SELECT * FROM Employees WHERE Department = 'IT';

    -- 데이터 갱신
    UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 1;

    -- 데이터 삭제
    DELETE FROM Employees WHERE EmployeeID = 1;

1.3 DCL (데이터 제어어)

사용자 권한 관리: 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들을 말함
예시

-- 사용자에게 권한 부여
GRANT SELECT, INSERT ON Employees TO user1;

-- 사용자로부터 권한 회수
REVOKE INSERT ON Employees FROM user1;

1.4 TCL (트랜젝션 제어어)

트랜잭션 관리: 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말함.
TCL은 특정 상황에서 데이터의 일관성과 무결성을 유지하기 위해 사용
- 복잡한 작업 수행 시
  - 여러 관련 테이블을 업데이트하는 경우
  - 예: 은행 계좌 간 송금 (한 계좌에서 출금, 다른 계좌에 입금)
- 대량의 데이터 처리 시
  - 많은 양의 데이터를 삽입, 수정, 삭제하는 경우
  - 중간에 오류가 발생하면 부분적 업데이트를 방지하기 위해
- 데이터 일관성이 중요한 경우:
  - 여러 단계의 작업이 모두 성공해야 의미가 있는 경우
  - 예: 주문 처리 (재고 확인, 주문 생성, 결제 처리)
- 오류 복구가 필요한 경우
  - 작업 중 오류 발생 시 이전 상태로 롤백해야 할 때
- 테스트 및 개발 환경
  - 데이터 변경 사항을 테스트하고 필요 시 롤백할 때
- 동시성 제어
  - 여러 사용자가 동시에 데이터를 수정할 때 일관성 유지를 위해
- 배치 처리
  - 대규모 배치 작업에서 전체 작업의 성공 또는 실패를 관리할 때
예시

    -- 트랜잭션 시작 (일부 DBMS에서는 명시적으로 시작할 필요가 없을 수 있다)
    BEGIN TRANSACTION;

    -- 데이터 삽입
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
    VALUES (1, 'John', 'Doe', 50000);

    -- 첫 번째 저장점 생성
    SAVEPOINT sp1;

    -- 데이터 수정
    UPDATE Employees SET Salary = 55000 WHERE EmployeeID = 1;

    -- 두 번째 저장점 생성
    SAVEPOINT sp2;

    -- 데이터 삭제
    DELETE FROM Employees WHERE EmployeeID = 1;

    -- sp2 저장점까지 롤백 (DELETE 작업만 취소)
    ROLLBACK TO SAVEPOINT sp2;

    -- 여기서 COMMIT하면 INSERT와 UPDATE 작업이 저장
    COMMIT;

    -- 새로운 트랜잭션 시작
    BEGIN TRANSACTION;

    -- 다른 데이터 수정
    UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

    -- 전체 트랜잭션 롤백 (이 UPDATE 작업 취소)
    ROLLBACK;

1.5 쿼리(질의) 응용 예시

테이블 생성

-- example: the Student table
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
-- example: the Course table
CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

관계 (relationship) 생성

-- 수강 테이블 (학생과 과목의 M:N 관계를 표현)
CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

학적 관리

Course Table
- attribute: course_name, credit_hours, department
course_name credit_hours department

Introduction to Computer Science 3 Computer Science

Calculus I 4 Mathematics

World History 3 History

Organic Chemistry 4 Chemistry
Student Table
- attribute: name, student_number
name student_number

John Smith 20240001

Emma Johnson 20240002

Michael Lee 20240003

Sophia Chen 20240004

course_name	credit_hours	department
Introduction to Computer Science	3	Computer Science
Calculus I	4	Mathematics
World History	3	History
Organic Chemistry	4	Chemistry

name	student_number
John Smith	20240001
Emma Johnson	20240002
Michael Lee	20240003
Sophia Chen	20240004

Section Table

attribute: section_ideintifier, course_number, semester, year, instructor

section_identifier	course_number	semester	year	instructor
CS101-1	CS101	Fall	2023	Dr. Alan Turing
MATH201-2	MATH201	Spring	2023	Dr. Katherine Johnson
HIST100-3	HIST100	Fall	2023	Prof. Howard Zinn
CHEM302-1	CHEM302	Spring	2023	Dr. Marie Curie

Grade Report Table
- attribute: student_number, section_identifier, grade
student_number section_identifier grade

20240001 CS101-1 A

20240002 MATH201-2 B+

20240003 HIST100-3 A-

20240004 CHEM302-1 B

student_number	section_identifier	grade
20240001	CS101-1	A
20240002	MATH201-2	B+
20240003	HIST100-3	A-
20240004	CHEM302-1	B

calculus 1 course의 section을 하나라도 수강한 학생을 찾으시오

질의의 정보를 각 table을 통해 추적이 가능해야 한다.
결과적으로 course table >> section table >> grade_report table >> student table 순으로 추적함 (join의 원리)

SELECT DISTINCT s.student_number, s.name
    FROM Course c
    JOIN Section sec ON c.course_name = sec.course_number
    JOIN Grade_Report gr ON sec.section_identifier = gr.section_identifier
    JOIN Student s ON gr.student_number = s.student_number
    WHERE c.course_name = 'Calculus I';

MATH201-2, student 20240001의 grade를 B로 수정하시오

UPDATE Grade_Report
SET grade = 'B'
WHERE section_identifier = 'MATH201-2'
    AND student_number = '20240001';

위 처럼 table의 추적이 가능하게 하려면 DB 설계를 잘해야한다
- 설계: table의 정의를 잘 내려야 하고 table들간 관계를 잘 설정 해야한다.