2주차 - SQL

1. SQL에 대해서 설명해주세요. SQL은 C언어와 같은 프로그래밍 언어와 비교해서 어떤 차이가 있나요?

SQL(Structured Query Language, 구조적 쿼리 언어)은 RDBMS에 접근해서 데이터를 조작하는 프로그래밍 언어입니다. 왜냐하면 사전 정의된 어휘 세트와 문법 규칙을 가지고 있기 때문입니다. SQL과 C언어는 문제 해결 범위에서 차이가 있습니다. SQL은 DSL(Domain-Specific Lanuage, 도메인 특정 언어)로 특정 도메인(데이터베이스)의 문제를 해결하기 위해 만들어졌습니다. C언어와 같은 프로그래밍 언어는 범용적인 문제를 해결하기 위해 만들어진 GPL(General Purpose Language, 범용 목적 언어)입니다.

모범 예상 답변

펼치기

SQL은 관계형 데이터베이스를 관리하고 조작하기 위한 구조화된 질의 언어입니다. SQL은 기능에 따라 데이터정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL) 등으로 나누어집니다.

2. 개발자가 작성한 SQL이 어떤 과정을 통해 실행되는지 설명해주세요.

DB마다 세부적인 과정은 다를 수 있습니다. Oracle 공식 문서를 참고했을 때 SQL은 총 4단계로 처리됩니다.

구문 분석(Parsing)
1. 구문 검사(Syntax Check)
2. 의미 검사(Semantic Check)
3. 공유 풀 검사(Shared Pool Check)
최적화(Optimization)
행 소스 생성(Row Source Generation)
실행(Execution)

구문 분석(Parsing) 단계에서는 쿼리의 유효성을 검사합니다. 이 과정에서 SQL 규칙을 위반하거나(ex. FORM 등 오타), 부적절한 의미(ex. 존재하지 않는 테이블 접근)의 쿼리는 실패합니다. 또한 공유 풀 검사를 통해 리소스를 많이 사용하는 명령문 처리 단계의 생략 여부를 결정합니다. DB는 해싱으로 모든 SQL에 대한 해시 값을 생성합니다. 해시 값이 일치하는 경우 기존 코드를 재사용해서 최적화 및 행 소스 생성 단계를 건너뜁니다. 일치하지 않는 경우 하드 구문 분석이 일어나고 새로운 실행 가능 버전을 구축합니다. 최적화(Optimization) 단계에서는 다양한 실행 계획을 생성하며 그 과정에서 쿼리 실행 계획을 최적화합니다. 이 단계에서 DB는 모든 고유한 DML 문에 대해 적어도 한 번의 하드 구문 분석을 수행합니다. DDL은 최적화하지 않으며 예외적으로 DML 요소가 포함된 경우에만 최적화합니다. 행 소스 생성(Row Source Generation) 단계에서는 최적 실행 계획에 따라 반복 실행 계획을 생성합니다. 이 과정에서 행 소스 생성기가 row source tree를 생성합니다. 실행(Execution) 단계에서는 row source tree를 실행합니다.

모범 예상 답변

펼치기

MySQL 기준으로 설명 드리겠습니다. 1. 사용자가 작성한 SQL을 데이터베이스로 보냅니다. 2. MySQL 쿼리파서는 SQL 문장을 토큰으로 쪼개서 트리를 만듭니다.이 트리를 Parse Tree라고 하는데 이를 통해 쿼리를 실행합니다.이 과정에서 문법 오류도 체크합니다. 3. 다음으로 전처리기가 Parse Tree을 기반으로 SQL 문장구조에 문제가 없는지 체크합니다.또한 SQL에 포함된 테이블, 컬럼 이름이 유효한지 접근권한이 있는지 체크합니다. 4. 다음으로 옵티마이저가 SQL 실행을 최적화하기 위해 실행 계획을 수립합니다. 5. 마지막으로 쿼리 실행엔진이 수립된 실행 계획대로 스토리지 엔진을 호출해서 쿼리를 수행하고 결과를 사용자에게 응답합니다.

3. DML은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

DML(Data Manipulation Language)은 데이터베이스에 저장된 정보를 조작하는 명령어를 말합니다.

SELECT
INSERT
UPDATE
DELETE

모범 예상 답변

펼치기

DML은 데이터 조작어로 테이블에서 데이터를 조회, 삽입, 수정, 삭제하는데 사용됩니다. 주요 구문으로는 SELECT, INSERT, UPDATE, DELETE 등이 있습니다.

4. DDL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

DDL(Data Definition Language, 데이터 정의 언어)은 데이터 구조를 정의하는 명령어를 말합니다.

CREATE
ALTER
DROP
RENAME

모범 예상 답변

펼치기

DDL은 데이터 정의어로 테이블 생성, 수정, 삭제 등을 하는데 사용됩니다. 주요 구문으로는 CREATE, ALTER, DROP 등이 있습니다.

5. DCL은 무엇인가요? 어떤 구문이 있는지도 설명해주세요.

DCL(Data Control Language, 데이터 제어 언어)은 사용자의 액세스 권한을 관리하는 명령어를 말합니다.

GRANT
DENY
REVOKE

모범 예상 답변

펼치기

DCL은 데이터 제어어로 데이터에 대한 접근 권한을 관리하는데 사용됩니다. 주요 구문으로는 GRANT, REVOKE, COMMIT, ROLLBACK, LOCK 등이 있습니다.

6. 참조 무결성에 대해서 설명해주세요.

IC(Integrity Constraint, 무결성 제약조건)는 데이터의 품질을 유지하고 보호하기 위한 제약 조건입니다. 참조 무결성이란 외래키로 연결된 두 테이블의 속성에 대한 일관성을 보장하는 것을 말합니다. 예를 들어 직원 테이블과 부서 테이블이 있고 직원 테이블의 부서 번호 속성이 부서 테이블을 참조하는 외래키라고 가정해 보겠습니다. 영업팀에서 근무하는 직원이 있다면 연쇄 삭제(CASCADE)가 허용되지 않은 경우 영업팀이라는 부서만 삭제할 수 없습니다. 참조 무결성을 해치기 때문입니다.

7. CASCADE 설정에 대해서 설명해주세요.

CASCADE는 상위 테이블의 변경 사항을 연관된 하위 테이블에도 적용함으로써 참조 무결성을 보장하는 옵션입니다. 예를 들어 직원 테이블과 부서 테이블이 있고 직원 테이블의 부서 번호 속성이 부서 테이블을 참조하는 외래키라고 가정해 보겠습니다. 연쇄 삭제(CASCADE)를 허용한 경우 영업팀을 삭제하면 영업팀에서 근무하던 직원 튜플도 삭제됩니다.

모범 예상 답변

펼치기

CASCADE는 참조 무결성을 유지하기 위해 사용되는 설정입니다. 예를들어 설명 드리겠습니다. 주문, 고객 테이블이 있고 주문이 고객 테이블의 기본키를 참조하는 외래키를 갖는다고 했을때, 주문 테이블에 ON DELETE CASCADE 설정을 하면 고객 테이블의 투플이 삭제 되었을때 연관된 주문 테이블의 투플도 삭제되게 됩니다. 또 주문 테이블에 ON UPDATE CASCADE 설정을 하면 고객 테이블의 투플의 기본키가 수정되었을때 연관된 주문 테이블의 투플의 외래키도 수정되게 됩니다. (꼬리질문) 실무에서는 보통 CASCADE 설정을 하지 않는데 이유가 뭐라고 생각하세요?

8. VIEW에 대해서 설명해주세요.

뷰(View)는 일종의 가상 테이블로 행과 열을 가지고 있습니다. DB에서는 하나 이상의 테이블에서 필드를 선택해서 뷰를 만들 수 있습니다. 뷰를 사용하면 데이터의 복잡성을 숨기고 액세스를 제한하며 다양한 사용자를 위한 보기를 생성할 수 있습니다.

모범 예상 답변

펼치기

VIEW는 여러 테이블들을 합쳐서 만든 가상의 테이블입니다. VIEW는 여러 테이블들이 합쳐져 하나의 테이블로 정의된 것이기 때문에 SQL 작성을 간단히 할 수 있습니다. 또한 원본 테이블을 노출시키지 않게 함으로 보안성을 제공할 수 있습니다.

9. SELECT 절의 처리 순서에 대해서 설명해주세요.

SELECT 절의 실행 순서는 다음과 같습니다.

FROM -> JOIN -> WHERE -> GROUP BY -> DISTINCT -> HAVING -> SELECT -> ORDER BY -> LIMIT

모범 예상 답변

펼치기

MySQL 기준으로 설명 드리겠습니다. 1. 먼저 FROM 절에서 타겟 테이블이 정해 집니다. JOIN이 있다면 적용됩니다. 2. WHERE 절이 적용되어 투플이 필터링 됩니다. 3. GROUP BY에 지정한 컬럼을 기준으로 투플이 그룹화 됩니다. 4. DISTINCT가 적용되어 중복된 투플이 필터링 됩니다. 5. HAVING이 적용되어 GROUP BY로 그룹화된 투플에 대해 추가적으로 필터링 됩니다. 6. ORDER BY가 적용되어 투플이 정렬됩니다. 7. LIMIT가 적용되어 명시된 개수만큼 투플이 반환됩니다.

10. SELECT ~ FOR UPDATE 구문에 대해서 설명해주세요.

FOR UPDATE 구문은 쿼리가 속한 전체 트랜잭션이 완료될 때까지 SELECt 쿼리가 반환할 행을 잠금(Lock)하는 명령입니다. 해당 행에 액세스를 시도하는 다른 트랜잭션은 시간 기반 큐에 배치되어 대기합니다. 이는 여러 트랜잭션이 동일한 행을 읽으려고 시도할 때 발생할 수 있는 동시성 문제를 방지할 수 있어 유용합니다.

모범 예상 답변

펼치기

SELECT ~ FROM UPDATE 구문은 투플들을 조회하면서 락을 겁니다. 그렇기에 다른 트랜잭션이 접근하여 해당 투플들을 수정할 수 없습니다.

11. GROUP BY절에 대해서 설명해주세요.

GROUP BY 명령어는 속성(컬럼)에 따라 데이터를 집계합니다. 예를 들어 지역 따라 GROUP BY를 걸면 전체 튜플이 서울, 경기 등 지역으로 나뉘어 집계됩니다.

모범 예상 답변

펼치기

GROUP BY 절을 사용해서 특정 속성을 기준으로 데이터를 그룹화 할 수 있습니다. 주로 집계 함수와 함께 사용됩니다. 예를들어 고객 ID 컬럼과 가격 컬럼이 있는 주문 테이블에서, 고객 ID 기준으로 GROUP BY를 하고 SELECT 절에서, 집계함수 SUM을 사용하면 고객별 총 주문 가격을 조회할 수 있습니다.

12. ORDER BY절에 대해서 설명해주세요.

GROUP BY 명령어는 속성(컬럼)에 따라 데이터를 정렬합니다. 내림차순(DESC), 오름차순(ASC) 등 다양한 옵션을 지정할 수 있습니다.

모범 예상 답변

펼치기

ORDER BY 절을 사용해서 특정 속성을 기준으로 조회 결과를 정렬할 수 있습니다. 기본적으로 오름차순으로 정렬되며, DESC 키워드를 사용하면 내림차순으로 정렬할 수 있습니다.

13. INNER JOIN과 OUTER JOIN의 차이점에 대해서 설명해주세요.

INNER JOIN은 교집합(A∩B), OUTER JOIN은 합집합(A∪B)입니다. INNER JOIN은 두 테이블 모두 지정한 열의 데이터가 있어야 하지만, OUTER JOIN은 한 개의 테이블에만 데이터가 있어도 결과가 나옵니다.

모범 예상 답변

펼치기

INNER JOIN을 통해 두 테이블을 결합할 수 있습니다. ON 절에는 테이블 결합 조건을 지정할 수 있습니다. 예를들어 테이블 A, B에 대하여 INNER JOIN을 하면 ON 절 기준으로 두 테이블의 교집합 결과를 가져옵니다.

14. LEFT OUTER JOIN, RIGHT OUTER JOIN에 대해서 설명해주세요.

LEFT OUTER JOIN은 왼쪽 테이블의 모든 값이 출력되고 RIGHT OUTER JOIN은 오른쪽 테이블의 모든 값이 출력됩니다.

모범 예상 답변

펼치기

A, B 테이블이 있을때 LEFT OUTER JOIN을 하면 A 테이블의 모든 결과와 A,B 교집합된 결과를 가져옵니다. A 테이블의 결과중 B 테이블과 일치하는 결과가 없다면 NULL 값으로 채워집니다. RIGHT OUTER JOIN은 LEFT OUTER JOIN과 반대 방향으로 적용됩니다.

15. CROSS JOIN에 대해서도 설명해주세요.

CROSS JOIN은 A와 B의 모든 값을 각각 합칩니다. 예를 들어 A 테이블의 행의 개수가 3이고 B 테이블의 행의 개수가 4이면 CROSS JOIN 결과 전체 행의 개수는 12입니다.

모범 예상 답변

펼치기

CROSS JOIN을 하면 두 테이블의 모든 가능한 조합 결과를 생성합니다. 두 테이블의 각 레코드 수를 곱한 개수 만큼 결과가 나옵니다. CROSS JOIN은 카테시안 곱이라고도 부릅니다.

16. 서브쿼리에 대해서 설명해주세요.

서브 쿼리는 SQL문 안에 포함된 다른 SQL문을 말합니다.

SELECT * FROM CUSTOMERS WHERE AGE = (SELECT MIN(AGE)FROM CUSTOMERS);

모범 예상 답변

펼치기

서브쿼리는 SQL안에 포함된 또 다른 SQL입니다. 서브쿼리는 WHERE, SELECT, FROM절에 적용될 수 있습니다.

17. DROP, TRUNCATE, DELETE에 각각에 대해 설명해주세요. 어떤 차이가 있나요?

DROP

TRUNCATE

DELETE

특징

데이터베이스 혹은 테이블 제거

테이블의 내용 초기화(지우기)

테이블의 행 제거

롤백

메모리 공간

할당 해제

할당 유지

수행 속도

중간

빠름

느림

무결성 제약

제거

제거되지 않음

유지

모범 예상 답변

펼치기

TRUNCATE는 DDL로 테이블의 모든 데이터를 제거합니다. 수행시 DELETE 처럼 행마다 로그를 남지지 않기 때문에 빠르고 ROLLBACK이 불가능합니다. AUTO_INCREMENT 값이 초기화 되기도합니다. DELETE는 DML로 테이블의 투플을 삭제합니다. 각 행마다 로그를 남겨서 TRUNCATE 보다 느립니다. ROLLBACK을 할 수 있으며 WHERE과 함께 사용하여 특정 투플만 삭제할 수 있습니다. DROP은 테이블과 테이블에 포함된 데이터, 인덱스, 트리거 등 테이블과 관련된 모든 정보를 제거합니다.

18. DISTINCT에 대해서 설명해주세요. 사용해본 경험도 설명해주세요.

DISTINCT 명령어는 가져올 데이터의 중복을 허용하지 않습니다. 데이터 분석을 할 때 전체 데이터에서 공백과 중복을 제외한 고유값을 확인하고 싶을 때 사용했습니다.

SELECT DISTINCT NAME FROM CUSTOMERS;

모범 예상 답변

펼치기

DISTINCT를 통해 SELECT 문에서 조회된 중복된 결과를 필터링 할 수 있습니다. 예를 들어 주문 테이블에서 회원 정보를 중복되지 않게 조회할때 DISTINCT를 사용할 수 있습니다.

19. SQL Injection 공격이 무엇인지 어떻게 공격을 예방할 수 있는지 설명해주세요.

SQL Injection(SQL 주입)은 프로그램의 보안 상 허점을 이용해서 악의적인 SQL문을 실행시켜 DB을 조작하는 공격 방법을 말합니다.

$username = $_POST["username"];
$password = $_POST["password"];

$mysqli->query("SELECT * FROM users WHERE username='{$username}' AND password='{$password}'");

예를 들어 위 코드에서 공격자가 유저 네임에 admin, 패스워드에 'password' OR 1=1 --'를 입력하면 쿼리문은 아래와 같이 변경됩니다.

SELECT * FROM users WHERE username='admin' and password='password' OR 1=1 --'

1=1은 항상 참이기 때문에 공격자는 비밀번호를 입력하지 않고 로그인할 수 있습니다. SQL Injection을 방지하기 위해서 사용자 입력을 쿼리문과 분리할 수 있습니다. 사용자 입력의 유효성을 검사하고 검증된 변수만 쿼리문으로 넘기면 SQL Injection을 예방할 수 있습니다. 또한 데이터베이스에서 제공하는 prepared statement를 활용하면 방어할 수 있습니다. prepared statement는 데이터베이스가 SQL 코드를 사전에 컴파일하고 결과를 데이터와 분리하여 저장하는 기능입니다. prepared statement는 다음과 같이 실행됩니다.

준비(prepare): 애플리케이션이 명령문 템플릿을 생성해서 DBMS로 전송
- INSERT INTO products (name, price) VALUES (?, ?);
컴파일(compile) DBMS는 문장 템플릿을 컴파일(파싱, 최적화, 번역) 후 실행하지 않고 결과를 저장
실행: 프로그램은 매개 변수에 대한 값을 바인딩하고 DBMS는 명령문 실행
- INSERT INTO products (name, price) VALUES ("bike", "10900");

모범 예상 답변

펼치기

SQL Injection 공격은 악의적인 사용자가 SQL 구문을 클라이언트단 입력폼에 주입하여 서버의 데이터베이스를 공격하는 것입니다. 실행할 SQL 구문을 미리 Prepared Statement로 정해두고, 사용자가 입력한 값은 파라미터로만 넣어주는 방식을 사용하면, SQL Injection을 통해 비정상적인 쿼리 수행을 방지할 수 있습니다.

20. 알고 있는 SQL 안티 패턴이 있다면 설명해주세요.

안티패턴

전체 조회 SELECT * FROM

문제

테이블 구조가 변경되거나 컬럼이 추가되면 예상치 못한 결과 발생 가능

해결

필요한 컬럼만 명시적으로 조회

안티패턴

문자열 연결로 동적 쿼리 생성 "SELECT * FROM USERS WHERE NAME = '" + NAME + "'"

문제

SQL 인젝션 공격에 취약

해결

파라미터화 된 쿼리나 프로시저 사용

안티패턴

부적절한 데이터 타입 사용 order_date VARCHAR(10)

문제

데이터의 정확성과 효율성 저하

해결

데이터의 성격과 범위에 맞는 데이터 타입 사용

모범 예상 답변

펼치기

SELECT 쿼리에서 LIKE를 사용할때 와일드카드를 앞뒤로 넣는 방식은 (LIKE %one%)는 테이블 full scan이 되기 때문에 성능이 떨어지기 때문에 사용을 지양해야합니다. 앞에 붙은 와일드 카드로 인해 정렬 기준이 없어져서 인덱스가 사용 되지 않기 때문입니다.

21. 페이지네이션을 구현한다고 했을때 쿼리를 어떻게 작성해야 할까요?

게시글을 10개씩 끊어 첫 번째 페이지를 내림차순으로 보여준다고 가정할 경우 아래와 같이 작성할 수 있습니다.

SELECT TITLE, CONTENT, AUTHOR FROM POSTS ORDERBY DESC LIMIT 10 OFFSET 0;

모범 예상 답변

펼치기

LIMIT와 OFFSET을 사용하여 페이지네이션 쿼리를 작성할 수 있습니다. 페이지 번호는 OFFSET을 통해, 페이지에 포함된 행 개수는 LMIT를 통해 표 예를들어 주문 테이블 대상 SELECT 쿼리에서 LMIT가 10이고 OFFSET이 10이라면, 11번째 투플부터 20번째 투플까지만 조회할 수 있습니다. 이렇게 하면 10번 페이지에서 10개 행을 보여주는 쿼리가 됩니다.

참고 자료

Previous1주차 - 기본 개념 Next3주차 - 인덱스

Last updated 11 months ago