SQL 자격검정 실전문제 # 문제집 요점정리_과목 2_SQL 기본

 

SQL 명령어 종류

• TCL에는 COMMIT, ROLLBACK, SAVEPOINT가 있다. 일부는 TCL을 DCL로 분류하기도 한다.
  (ROLLBACK은 COMMIT되지 않은 모든 TRANSACTION에 대해 적용된다.)
• DML은 비절차적 데이터 조작어라고 불린다.
  또한 호스트 프로그램 속에 삽입되어 사용되는 명령어기 때문에 데이터 부속어(Data Sub Language)라고도 한다.

 

절차/비절차적 데이터 조작어

• 비절차적 데이터 조작어(DML)
  - 사용자가 무슨 데이터를 원하는지만을 명세.
• 절차적 데이터 조작어
  - 어떻게 데이터를 접근해야하는지 명세.
  - PL/SQL(오라클), T-SQL(SQL Server)등이 있다.

 

CONSTRAINT 예제

CREATE TABLE TEAM ( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR2(8) NOT NULL, STADIUM_ID CHAR(3) NOT NULL, ADDRESS VARCHAR2(80), OWNER VARCHAR2(10), CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) );

CREATE TABLE TEAM ( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR2(8) NOT NULL, STADIUM_ID CHAR(3) NOT NULL, ADDRESS VARCHAR2(80), OWNER VARCHAR2(10), CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) );

 or

Oracle

CREATE TABLE TEAM ( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR2(8) NOT NULL, STADIUM_ID CHAR(3) NOT NULL, ADDRESS VARCHAR2(80), OWNER VARCHAR2(10)); ALTER TABLE PRODUCT ADD CONSTRAINT PRIMARY KEY PRODUCT_PK ON (PROD_ID);

 

SQL Server

43p 7

• 여러 컬럼을 동시에 수정하는 구문을 지원하지 않는다.
• 괄호를 사용하지 않는다.
• DDL문장 수행 후 자동으로 COMMIT 수행하지 않음.
• NULL값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우 가장 위쪽에 위치.
• ''이 입력되면 NULL로 저장된다.
  또한 이를 찾으려면 컬럼명 = ''로 찾아야 한다.

Oracle

• DDL문장 수행 후 자동으로 COMMIT 수행.
• NULL값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우 가장 마지막에 위치.
• ''이 입력되면 NULL로 저장된다.
  또한 이를 찾으려면 IS NULL명령어로 찾아야 한다.

 

제약조건의 종류

• PRIMARY KEY(기본키)
  - UNIQUE
  - NOT NULL
• UNIQUE KEY(고유키)
  - UNIQUE
  - NULL 가능
• NOT NULL
• CHECK
  - 데이터 무결성을 유지하기 위해 특정 컬럼에 설정하는 제약
• FOREIGN KEY(왜래키)
  - 테이블간의 관계를 정의하기 위해 PK를 다른 테이블의 왜래키가 참조하도록 생성한다.
  - NULL 가능
  - 한 테이블에 여러 FK 존재 가능
  - 참조 무결성 제약을 받을 수 있다.
    (2개의 관계 변수간의 일관성)

 

 

컬럼 삭제하기

ALTER TABLE 테이블 명 
DROP 컬럼명;

 

인덱스 생성하기

CREATE INDEX 인덱스 이름 
ON 테이블명 (인덱스 생성하고싶은 컬럼명)​;

 

테이블명 바꾸기

RENAME 테이블명
TO 바뀐 테이블 명;

 

 

참조동작(Referential Action)

Delete

• Cascade
  - Master 삭제 시 Child 같이 삭제
• Set Null
  - Master 삭제 시 Child 해당 필드 Null
• Set Default
  - Master 삭제 시 Child 해당 필드 Dafult
• Restrict
  - Child에 PK 없는 경우만 Master 삭제 허용
• No Action
  - 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

Insert

• Automatic
  - Master에 PK가 없는 경우 PK를 생성 후 Child 입력
• Set Null
  - Master 테이블에 PK가 없는 경우 Child 외부키를 Null값으로 처리
• Set Default
  - Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
• Dependent
  - Master에 PK가 존재할 때만 Child입력 허용
• No Action
  - 참조무결성을 위반하는 입력 액션을 취하지 않음

DROP	TRUNCATE	DELETE
DDL	DDL, 일부 DML	DML
Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
Auto Commit	Auto Commit	사용자 Commit
사용했던 스토리지 모두 릴리즈 (즉, 디스크 사용량 초기화 가능)	사용했던 스토리지 중 최초 테이블 생성 시 할당된 스토리지 외에 모두 릴리즈 (즉, 디스크 사용량 초기화 가능)	데이터를 모두 지워도 사용했던 스토리지는 릴리즈되지 않음 (즉, 디스크 사용량 초기화 불가능)
테이블 구조 및 정의도 완전히 삭제	데이터만 삭제	데이터만 삭제

• TRUNCATE은 UNDO를 위한 데이터를 생성하지 않기 때문에 DELETE보다 빠르다.
  (TRUNCATE은 Auto Commit)

 

 

트랜잭션 특성

• 원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(all or nothing)
• 일관성 : 트랜잭션이 실행되기 전 데이터베이스에 이상이 없다면 실행 후 에도 이상이 없어야 한다.
• 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.
• 지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.

 

트랜잭션의 격리성이 낮은 경우 발생할 수 있는 문제점

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

 

트랜잭션 종료를 위한 명령어

• COMMIT
  데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영
• ROLLBACK
  데이터에 대한 변경사항을 모두 폐기하고 변경 전의 상태로 되돌리는 명령어.

 

논리 연산자 처리 우선순위

1. ( )
2. NOT
3. AND
4. OR 

 

(IS) NULL (↔ (IS) NOT NULL)

• 조건에 맞는 데이터가 없을 때의 공집합과도 다르다.
• NOT NULL 또는 PK로 지정되지 않은 모든 데이터 유형은 NULL을 포함할 수도 있다.
• '아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미.
  0 또는 공백과 다르다. 0은 숫자 공백은 문자이다.
• Null과의 모든 비교(IS NULL제외)는 알 수 없음(Unknown)을 반환한다.
• NULL비교는 IS NULL, IS NOT NULL로만 비교가 가능하다.
• Null값이 포함된 사칙연산의 결과는 Null이다.
• 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.
• NULL값의 대상이 숫자 유형 데이터인 경우는 주로 0으로,
  문제 유형 데이터인 경우에는 블랭크 보단 'x'와 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.
• Null값과의 비교연산은 FALSE를 리턴한다.
  특정값 보다 크다 작다로 표현할 수 없기 때문.
• Oracle : IS NULL
  SQL : ''

 

 

64p 48

COALESCE 함수

첫 번째로 NULL이 아닌 값을 반환.

 

 

내장 함수(BUILT-IN FUNCTION)

함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다. 

 

• 단일행 함수
  - 단일행 값이 입력됨.
  - 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 
  - SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용이 가능하다.
• 다중행 함수
  - 여러 행의 값이 입력됨.
  - 여러 레코드의 값들을 입력 인수로 사용하는 것이다.
  - 다중행 함수는 집계함수, 그룹함수, 윈도우 함수로 다시 구분된다.

 

단일행 NULL관련 함수의 종류

 

집계함수의 종류

COUNT(*)를 제외한 나머지 집계함수는 모두 NULL값 제외

 

 

GROUP BY절과 HAVING절의 특성

• GROUP BY절을 통해 소그룹별 기준을 정한 후 SELECT절에 집계함수를 사용.
• 집계 함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행.
• GROUP BY 절에서는 SELECT절과 달리 ALIAS명 사용 불가능.
• 집계함수는 WHERE 절에 올 수 없다.
  (집계함수를 사용할 수 있는 GROUP BY절보다 WHERE절이 먼저 시행되기 때문.)
• WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킴.
• HAVING절은 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
• GROUP BY절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING절은 일반적으로 GROUP BY절 뒤에 위치한다.

 

ORDER BY절 특징

• 기본적 정렬 순서는 오름차순(ASC)다.
• 숫자형 데이터 타입은 오름차순으로 정렬했을 경우 가장 작은 값 부터 출력된다.
• 날짜형 데이터 타입은 오름차순으로 정렬할 경우 날짜 값이 가장 빠른 값이 먼저 출력된다.
  예를 들어 2021년 1월달 데이터가 2021년 11월 데이터 보다 먼저 출력된다.
• GROUP BY절을 사용하는 경우 ORDER BY절에 집계함수를 사용할 수도 있다.
• ORDER BY 절에 컬럼명 대신 ALIAS명이나 컬럼 순서를 나타내는 정수를 혼용하여 사용할 수 있다.

 

SELECT절 실행순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

 

• FROM
  대상 테이블 참조
• WHERE
  대상 데이터 아닌 것 제거.
• GROUP BY
  행들을 소그룹화
• HAVING
  그룹핑된 값의 조건에 맞는 것만을 출력.
• SELECT
  데이터 값을 출력 및 계산.
• ORDER BY
  데이터를 정렬한다.

 

 

WITH TIES

TOP N질문에서 N에 해당하는 값이 동일한 경우 함께 출력되도록 하는 옵션.

ORDER BY절과 함께 사용한다.

SELECT TOP(3) WITH TIES 팀명, 승리건수
FROM 팀별성적
ORDER BY 승리건수 DESC;

 

JOIN

• N-1 : 여러 테이블로 부터 원하는 데이터를 조회하기 위해서 필요한 최소 JOIN조건 갯수.
• 일반적으로 JOIN은 PK와 FK값의 연관성에 의해 성립된다.
  하지만 어떤 경우에는 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.
• DBMS옵티마이져는 FROM 절에 나열된 테이블이 아무리 많아도 항상 단 2개의 테이블씩 짝을 지어 JOIN을 수행한다.
• EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다.
• EQUI JOIN은 '=' 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우에는 모두 NON EQUI JOIN이다.
• 대부분 NON EQUI JOIN을 수행할 수 있지만 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.