Post

Sqld 자격증 정리

Posted
By backendVirtuoso
45 min read
Sqld 자격증 정리

데이터 모델링의 이해

모델링이 갖추야 할 조건

  • 현실 세계를 반영해야 한다.
  • 단순화하여 표현해야 한다.
  • 관리하고자 하는 데이터를 모델로 설계한다.

모델링의 특징

  • 추상화(Abstraction) : 현실 세계를 일정한 형식으로 표현하는 것이다. 즉, 아이디어나 개념을 간략하게 표현하는 과정
  • 단순화(Simplification) : 복잡한 현실 세계를 정해진 표기법으로 단순하고 쉽게 표현한다는 의미이다.
  • 명확화(Clarity) : 불분명함을 제거하고 명확하게 해석할 수 있도록 기술.

모델링의 세 가지 관점

  • 데이터 관점
    • 데이터 위주의 모델링이라고 할 수 있다. 어떤 데이터들이 업무와 얽혀있는지, 그리고 그 데이터간에는 어떤 관계가 있는지에 대해서 모델링하는 방법
  • 프로세스 관점
    • 프로세스 위주의 모델링이라고 할 수 있다. 이 업무가 실제로 처리하고 있는 일은 무엇인지 또는 앞으로 처리해야 하는 일은 무엇인지를 모델링하는 방법
  • 데이터와 프로세스의 상관 관점
    • 데이터와 프로세스의 관계를 위주로 한 모델링이라고 할 수 있다. 프로세스의 흐름에 따라 데이터가 어떤 영향을 받는지를 모델링하는 방법

데이터 모델링 시 유의사항

  • 중복
    • 같은 데이터가 여러 엔터티에 중복으로 저장되는 형상을 지양해야 한다.
  • 비유연성
    • 데이터 모델의 설계에 따라 애플리케이션의 사소한 변경에도 데이터 모델이 수시로 변경되어야 하는 상황이 생길 수 있다. 이런 상황은 시스템을 유지보수하는 데에 어려움을 가중시키므로 데이터 모델과 프로세스를 분리하여 유연성을 높이는 것이 바람직하다.
  • 비일관성
    • 데이터의 중복이 없는 경우에도 비일관성이 발생할 수 있다. 개발자가 다른 데이터와의 연관성을 고려하지 않고 일부 데이터만 변경할 수 있기 때문이다. 이런 위험을 예방하기 위해 데이터 모델링을 할 때 데이터 간의 연관 관계에 대해 명확하게 정의해야 한다.

모델링의 세 가지 단계

  • 개념적 데이터 모델링
    • 전사(회사 전체 차원의)적 데이터 모델링 수행 시 행해지며 추상화 레벨이 가장 높음 모델링이다. 이 단계에서는 업무 중심적이고 표괄적인 수준의 모델링이 진행된다.
  • 논리적 데이터 모델링
    • 재사용성이 가장 높은 모델링으로 데이터베이스 모델에 대한 Key, 속성, 관계 등을 모두 표현하는 단계
  • 물리적 데이터 모델링
    • 실제 데이터베이스로 구현할 수 있도록 성능이나 가용성 등의 물리적인 성격을 고려하여 모델을 표현하는 단계

3단계 스키마 구조

  • 외부 스키마

    사용자의 관점 : Multiple User’s View 단계로 각 사용자가 보는 데이터베이스의 스키마를 정의한다.

  • 개념 스키마

    통합된 관점 : Community View of DB 단계로 모든 사용자가 보는 데이터베이스의 스키마를 통합하여 전체 데이터베이스를 나타내는 것이다. 데이터베이스에 저장되는 데이터들을 표현하고 데이터들 간의 관계를 나타낸다.

  • 내부 스키마

    물리적인 관점 : Physical Representation 단계로 물리적인 저장 구조를 나타낸다. 실질적인 데이터의 저장 구조나 컬럼 정의, 인덱스 등이 포함된다.

3단계 스키마 구조가 보장하는 독립성

  • 논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마는 영향받지 않는다.
  • 물리적 독립성 : 내부 스키마가 변경되어도 외부/개념 스키마는 영향받지 않는다.

ERD 작성순서

  1. 엔터티를 도출하고 그린다.
  2. 엔터티를 적절하게 배치한다
  3. 엔터티 간의 관계를 설정한다.
  4. 관계명을 기입한다.
  5. 관계의 참여도를 기입한다.
  6. 관계의 필수/선택 여부를 기입한다.

엔터티의 특징

  • 업무에서 쓰이는 정보여야 함
  • 유니크함을 보장할 수 있는 식별자가 있어야 함
  • 2개 이상의 인스턴스를 가지고 있어야 함
  • 반드시 속성을 가지고 있어야 함
  • 다른 엔터티와 1개 이상의 관계를 가지고 있어야 함

엔터티의 분류

  1. 유형 vs 무형
    • 유형 엔터티 : 물리적인 형태 존재, 안정적, 지속적
    • 개념 엔터티 : 물리적인 형태 없음, 개념적
    • 사건 엔터티 : 행위를 함으로써 발생, 빈번함. 통계 자료로 이용 가능
  2. 발생시점
    • 기본 엔터티
      • 업무에 원래 존재하는 정보
      • 독립적으로 생성되며, 자식 엔터티를 가질 수 있음
    • 중심 엔터티
      • 기본 엔터티로부터 파생되고, 행위 엔터티 생성
      • 업무에 있어서 중심적인 역할을 하며 데이터의 양이 ㅁ낳이 발생
    • 행위 엔터티
      • 2개 이상의 엔터티로부터 파생
      • 데이터가 자주 변경되거나 증가할 수 잇음

엔터티의 이름을 정할 때 주의할 점

  • 업무에서 실제로 쓰이는 용어 사용
  • 한글은 약어를 사용하지 않고 영문은 대문자로 표기
  • 단수 명사로 표현하고 띄어쓰기는 하지 않음
  • 다른 엔터티와 의미상으로 중복될 수 없음
  • 해당 엔터티가 갖고 있는 데이터가 무엇인지 명확하게 표현

속성

  • 의미상 더 이상 쪼개지지 않는 레이어야 하고 프로세스에 필요한 항목이어야 한다.

엔터티, 인스턴스, 속성, 속성값의 관계

  1. 한 개의 엔터티는 두 개 이상의 인스턴스를 갖는다.
  2. 한 개의 인스턴스는 두 개 이상의 속성을 갖는다.
  3. 한 개의 속성은 하나의 속성값을 갖는다.

특성에 따른 분류

  • 기본속성 : 업무 프로세스 분석을 통해 바로 정의가 가능한 속성
  • 설계속성 : 업무에 존재하지는 않지만 설계하다 보니 필요하다고 판단되어 도출해낸 속성
  • 파생속성 : 다른 속성의 속성값을 계산하거나 특정한 규칙으로 변형하여 생성한 속성.

구성에 따른 분류

  • PK속성 : 엔터티의 인스턴스들을 식별할 수 있는 속성
  • FK속성 : 다른 엔터티의 속성에서 가져온 속성
  • 일반속성 : PK, FK를 제외한 나머지 속성

도메인

  • 속성이 가질 수 있는 속성값의 범위

관계

  • 존재 관계 : 존재 자체로 연관성이 있는 관계를 의미한다.
  • 행위 관계 : 특정한 행위를 함으로써 연관성이 생기는 관계를 의미한다.

표기법

  • 관계명 : 관계의 이름
  • 관계차수 : 관계에 참여하는 수
  • 관계선택사양 : 필수인지 선택인지의 여부

주식별자

  • 유일성 : 각 인스턴스에 유니크함을 부여하여 식별이 가능하도록 한다.
  • 최소성 : 유일성을 보장하는 최소 개수의 속성이어야 한다.
  • 불변성 : 속성값이 되도록 변하지 않아야 한다.
  • 존재성 : 속성값이 NULL일 수 없다.

분류

  1. 대표성 여부
    • 주식별자
      • 유일성, 최소상, 불변성, 존재성을 가진 대표 식별자
      • 다른 엔터티와 참조 관계로 연결
    • 보조 식별자
      • 인스턴스를 식별할 수는 있지만 대표 식별자가 아님
      • 다른 엔터티와 참조 관계로 연결되지 않음
  2. 스스로 생성되었는지 여부
    • 내부식별자 : 엔터티 내부에서 스스로 생성된 식별자
    • 외부식별자 : 다른 엔터티에서 온 식별자, 다른 엔터티와의 연결고리 역할
  3. 단일 속성의 여부
    • 단일식별자 : 하나의 속성을 구성된 식별자
    • 복합식별자 : 두 개 이상의 속성으로 구성된 식별자
  4. 대체 여부
    • 원조식별자 : 업무 프로세스에 존재하는 식별자, 가공되지 않는 원래의 식별자(본질식별자)
    • 대리식별자 : 주식별자의 속성이 두 개 이상인 경우 그 속성들을 하나로 묶어서 사용하는 식별자(인조식별자)

식별자 관계

  • 부모 엔터티의 식별자가 자식 엔터티의 주식별자가 되는 관계
  • 반드시 존재해야 하므로(존재성) 부모 엔터티가 있어야 생성 가능하며 단일식별자인지 복합식별자인지에 따라 1:1, 1:M이거나 결정된다.

비식별자 관계

  • 부모 엔터티의 식별자가 자식 엔터티의 주식별자가 아닌 일반 속성이 되는 관계
  • 일반 속성의 속성값은 NULL이 될 수 있으므로 부모 엔터티가 없는 자식 엔터티 생성이 가능하고 마찬가지의 이유로 자식 엔터티가 존재하는상태에서 부모 엔터티가 삭제될 수도 있다.

데이터 모델과 SQL

정규화

  • 제1정규형 : 모든 속성은 반드시 하나의 값만 가져야 한다.
  • 제2정규형 : 엔터티의 모든 일반속성은 반드시 모든 주식별자에 종속되여야 한다
  • 제3정규형 : 주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없다.

트랜잭션

  • 데이터를 조작하기 위한 하나의 논리적인 작업 단위

NULL의 특성

  • 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다
  • 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
  • 널 값을 포함하는 연산의 경우 결괏값도 널 값이다.
  • 결괏값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

SQL 기본

테이블에 대한 설명

  • 데이터 모델에서 엔터티에 해당한다.
  • 데이터를 저장하기 위해 사용된다.
  • 데이터베이스는 일반적으로 여러 개의 테이블로 구성된다.
  • 데이터 모델에서 인스턴스에 해당하는 로우와 속성에 해당하는 컬럼으로 이루어진다.

산술연산자 우선순위

  • ( ) , *, /, +, -, % (SQL Serever)

SQL 문장들의 종류

명령어의 종류명령어설명
데이터 조작어 DMLSELECT데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다.
 INSERT, UPDATE, DELETE데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어. 예를 들어 데이터를 테이블의 새로운 행에 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어를 DML이라고 부른다.
데이터 정의어 DDLCREATE, ALTER, DROP, RENAME테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 부른다
데이터 제어어 DCLGRANT, REVOKE데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다.
트랜잭션 제어어 TCLCOMMIT, ROLLBACK논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

TCL

논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위별로 제어하는 명령어인 COMMIT, ROLLBACK, SAVEPOINT 등이 여기에 해당하며, 일부에서는 DCL로 분류하기도 한다.

SELECT 문에 대한 설명

  • WHERE 절은 필수가 아니므로 생략 가능
  • DISTINCT 옵션을 통해 중복된 데이터가 있을 경우 1건으로 처리해 출력할 수 있다.
  • 오라클 SQL은 FROM 절 생략 불가능
  • SELECT List에 서브쿼리가 사용될 수 있다.

아래에 대한 설명으로 적절한 것은?

CREATE TABLE 서비스
(
    서비스번호 VARCHAR2(10) PRIMARY KEY,
    서비스명 VARCHAR2(100) NULL,
    개시일자 DATE NOT NULL
)
[SQL]
ㄱ. SELECT * FROM 서비스 WHERE 서비스번호 = 1;
ㄴ. INSERT INTO 서비스 VALUES(’999’, ‘’, ‘2015-11-11’);
ㄷ. SELECT * FROM 서비스 WHERE 서비스명 = ‘’;
ㄹ. SELECT * FROM 서비스 WHERE 서비스명 IS NULL;
  1. 서비스번호 칼럼의 레코드 중 모든 레코드가 ‘001’과 같은 숫자 형식으로 입력되어 있다면 ㄱ은 오류 없이 실행된다.
  2. 오라클에서 ㄴ과 같이 데이터를 입력하였을 때, 서비스명 칼럼에 NULL 데이터가 입력된다.
  3. 오라클에서 ㄴ과 같이 데이터를 입력하고 ㄹ과 같이 조회하였을 때, 데이터는 조회된다.
  4. SQL Server에서 ㄴ과 같이 데이터를 입력하고 ㄹ과 같이 조회하였을 때, 데이터는 조회되지 않는다. ㄷ과 같이 조회해야 한다.

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 절 뒤에 위치한다.

다음 (가)와 같은 동일한 결과 출력

(가)
SELECT LOC,
    CASE WHEN LOC = ‘NEW YORK’ THEN ‘EAST’
        ELSE ‘ETC’
    END as AREA
FROM DEPT;

(나)
SELECT LOC,
    CASE LOC WHEN ‘NEW YORK’ THEN ‘EAST’ ELSE ‘ETC’ END as AREA
FROM DEPT;

NULL 관련 함수

  • NVL(표현식1, 표현식2) : 식1 의 값이 이면 식 출력 2. 공집합을 바꿔주진 않음
  • ISNULL(표현식1, 표현식2) : 식1이 NULL이 아니면 식1 출력, NULL이면 식2 출력
  • NULLIF(표현식1, 표현식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력
  • COALESCE(표현식1, 표현식2, ….) : NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환

집계 함수의 종류

집계 함수사용 목적
COUNT(*)NULL 값을 포함한 행의 수를 출력한다
COUNT(표현식)표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력
SUM([DISTINCT | ALL] 표현식)표현식의 NULL 값을 제외한 합계를 출력
AVG([DISTINCT | ALL] 표현식)표현식의 NULL 값을 제외한 평균를 출력
MAX([DISTINCT | ALL] 표현식)최대값을 출력(문자, 날짜 데이터 타입도 사용가능)
MIN([DISTINCT | ALL] 표현식)최소값을 출력(문자, 날짜 데이터 타입도 사용가능)
STDDEV([DISTINCT | ALL] 표현식)표준 편차를 출력
VARIAN([DISTINCT | ALL] 표현식)분산을 출력
기타 통계 함수벤더별로 다양한 통계석을 제공한다.

단일행 문자형 함수의 종류

문자형 함수사용 목적
LOWER(문자열)문자열의 알파벳 문자를 소문자로
UPPER(문자열)문자열의 알파벳 문자를 대문자로
ASCII(문자)문자나 숫자를 ASCII 코드 번호로
CHR/CHAR(ASCII번호)ASCII 코드 번호를 문자나 숫자로
CONCAT(문자열1, 문자열2)오라클, My SQL에서 유효한 함수이며 문자열1과 문자열2를 연결한다.
합성 연산자 ‘||’나 ‘+’와 동일하다
SUBSTR/SUBSTRING(문자열, m[, n ])문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다.
n이 생략되면 마지막 문자까지이다.
LENGTH/LEN(문자열)문자열의 개수를 숫자값으로 돌려준다
LTRIM(문자열 [, 지정문자])문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트),
SQL Server에서는 LTRIM 함수에 지정 문자를 사용할 수 없다. 공백만 제거
RTRIM(문자열 [, 지정문자])문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트),
SQL Server에서는 RTRIM 함수에 지정 문자를 사용할 수 없다. 공백만 제거
TRIM([leading|trailing|both] 지정문자 FROM 문자열)문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다. (leading | trailing | both가 생략되면 both가 디폴트).
SQL Server에서는 TRIM 함수에 지정문자를 사용할 수 없다. 공백만 제거

SELECT 문장 실행 순서

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

  1. 발췌 대상 테이블을 참조한다.
  2. 발취 대상 데이터가 아닌 것은 제거한다.
  3. 행들을 소그룹한다.
  4. 그루핑된 값의 조건에 맞는 것만을 출력한다
  5. 데이터 값을 출력/계산한다.
  6. 데이터를 정렬한다.

오라클 계층형 질의에 대한 설명

  • START WITH 절은 계층 구조의 시작점을 지정하는 구문
  • ORDER SIBILINGS BY 절은 형제 노드 사이에서 정렬을 지정하는 구문
  • 순방향전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 말한다.

서브쿼리를 사용할 때 주의사항

  • 서브쿼리를 괄호로 감싸서 사용한다
  • 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관없다.
  • 서브쿼리에서는 ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다.

뷰 사용의 장점

  • 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨.
  • 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL을 자주 사용할 때 부를 이용하면 편리하게 사용할 수 있다.
  • 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성하여 사용자에게 정보를 감출 수 있다.

윈도우 함수에 대한 설명

  • PARTITION BY 절과 GROUP BY 절은 의미적으로 유사하다.
  • PARTITION BY 절이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.
  • 윈도우 함수 처리로 결과 건수가 줄어 들지 않는다.
  • 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

RANK, DENSE_RANK, ROW_NUMBER

  • RANK : ORDER BY를 포함한 QUERY문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순서를 부여한다.
  • DENSE_RANK : RANk 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 다른 점.
  • ROW_NUMBER : 다른 RANK와 달리 동일한 값이라도 고유한 순위를 부여한다.

제약조건의 종류

  • PRIMARY KEY (기본키)
  • UNIQUE KEY (고유키)
  • NOT NULL
  • CHECK
  • FOREIGN KEY (외래키)

테이블 생성의 주의사항

  • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
  • 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
  • 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
  • 테이블 이름을 지정하고 각 칼럼들은 괄호 “()”로 묶어 지정한다.
  • 각 컬럼들은 콤마 “,”로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 “;”으로 끝난다.
  • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다 (데이터 표준화 관점)
  • 칼럼 뒤에 데이터 유형은 곡 지정되어야 한다.
  • 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 잇다.
  • 번데에서 사전에 정의한 예약어 (Reseved word)는 쓸 수 없다.
  • A-Z, a-z, 0-9, _, $, # 문자만 허용한다.

외래키에 대한 설명

  • 테이블 생성 시 설정할 수 있다.
  • 외래키 값은 NULL을 가질 수 있다.
  • 한 테이블에 하나 이상 생성할 수 있다.
  • 외래키 값은 참조 무결성 제약을 받을 수 있다.

트랜잭션의 특성

  • 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다
  • 일관성 : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 도니다.
  • 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
  • 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 테이터베이스의 내용은 영구적으로 저장된다.

DELETE, TRUNCATE, DROP 명령에에 대해 비고한 설명

  • DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기상태로 만든다
  • TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제 시 DELETE 보다 빠르다.
  • DROP과 TRUNCATE는 Atu commit되고, DELETE는 사용자 Commit으로 수행된다.

BEGIN TRANSACTION

  • BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 트랜잭션을 시작하고 COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는 ROLLBACK TARNSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다.
  • ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

아래의 SQL의 실행 결과는?

SELECT TO_CHAR(TO_DATE(’2023.01.10 10’, ‘YYYY.MM.DD HH24’) + 1/24/(60/10), ‘YYYY.MM.DD HH24:MI:SS’) 
FROM DUAL;

답: 2023.01.10 10:10:00

실행 결과가 다른 하나

  • 4번 DEPT를 역순으로 정렬함.
    1. SELECT DNAME, LOC, DEPTNO FROM DEPT ORDER BY DNAME, LOC, 3 DESC;
    2. SELECT DNAME, LOC AREA, DEPTNO FROM DEPT ORDER BY DNAME, AREA, DEPTNO DESC;
    3. SELECT DNAME, LOC AREA, DEPTNO FROM DEPT ORDER BY 1, AREA, 3 DESC;
    4. SELECT DNAME DEPT, LOC AREA, DEPTNO FROM DEPT ORDER BY DEPT DESC, LOC, 3 DESC;

아래 SQL을 순서대로 실행했을 때 최종적으로 반영되는 SQL을 모두 고른 것은?

  • 4번 정답
(가) INSERT INTO emp(empno, ename, deptno) VALUES (999, ‘Smith’, 10);
     SAVEPOINT a;
(나) DELETE emp WHERE empno = 202;
     SAVEPOINT b;
(다) UPDATE emp SET ename = ‘Clark’;
     ROLLBACK TO SAVEPOINT a;
(라) INSERT INTO emp(empno, ename, deptno) VALUES (300, ‘Thomas’, 30);
     SAVEPOINT c;
(마) DELETE emp WHERE deptno = 20;
     COMMIT;
  1. 가, 나, 다, 라, 마
  2. 가, 나 다, 라
  3. 나, 다, 라, 마
  4. 가, 라, 마

오류가 발생하는 SQL은?

  • 3번
    1. SELECT 지역, SUM(매출금액) AS 매출금액 FROM 지역별매출 GROUP BY 지역 ORDER BY 매출금액 DESC;
    2. SELECT 지역, 매출금액 FROM 지역별매출 ORDER BY 년 ASC;
    3. SELECT 지역, SUM(매출금액) AS 매출금액 FROM 지역별매출 GROUP BY 지역 ORDER BY 년 DESC;
    4. SELECT 지역, SUM(매출금액) AS 매출금액 FROM 지역별매출 GROUP BY 지역 HAVING SUM(매출금액) > 1000 ORDER BY COUNT(*) ASC;

아래의 SQL의 실행 결과는?

SELECT TO_CHAR(TO_DATE(’2019.02.25’, ‘YYYY.MM.DD’) + 1/24/(60/30), ‘YYYY.MM.DD HH24:MI:SS’) 
FROM DUAL;

답: 2019.02.25 01:00:00

실행 결과가 NULL인 SQL은?

  • 2번(동일하면 NULL 출력)
    1. SELECT COLAESCE(NULL, ‘A’) FROM DUAL;
    2. SELECT NULLIF(’A’, ‘A’) FORM DUAL;
    3. SELECT NVL(’A’, NULL) FROM DUAL;
    4. SELECT NVL(NULL, 0) + 10 FROM DUAL;

아래에서 JOIN에 대한 설명으로 가장 적절한 것은? (가)~(마)

(가) 일반적으로 조인은 PK와 FK값의 연관성에 의해 성립된다.
(나) DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 항상 2개 정도씩 묶어서 조인을 처리한다.
(다) EQUI JOIN은 조인에 관여하는 테이블 간의 칼럼 값들이 정확하게 일치하는 경우에 사용되는 방법
(라) EQUI JOIN은 ‘=’ 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우에는 모두 NON EQUI JOIN이다.
(마) 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.

실행 결과가 다른 하나는?

  • 1번 USING에는 접미사를 붙이지 않는다.
  1. SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME FROM TEAM T INNER JOIN STADIUM S USING (T.STADIUM_ID = S.STADIUM_ID);
  2. SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME FROM TEAM INNER JOIN STADIUM ON (TEAM.STADIUM_ID = STADIUM.STADIUM_ID);
  3. SELECT T.REGION_NAME, T.TEAM_NAME, T.STADUIM_ID, S.STADIUM_NAME FROM TEAM T, STADIUM S WHERE T.STADIUM_ID = S.STADIUM_ID;
  4. SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADUIUM_ID, STADIUM.STADIUM_NAME, FROM TEAM, STADIUM WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID;

아래 두 SQL이 같은 결과를 출력

[SQL(1)]
SELECT ENAME, DNAME
FROM EMP, DEPT
ORDER BY ENAME;

[SQL(2)]
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

아래 세 SQL이 같은 결과를 출력

[SQL(1)]
SELECT [A.ID](http://A.ID), B.ID
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON A.ID = B.ID

[SQL(2)]
SELECT A.ID, B.ID
FROM TBL1 A LEFT OUTER JOIN TBL2 B
ON A.ID = B.ID
UNION
SELECT A.ID, B.ID
FROM TBL1 A RIGHT OUTER JOIN TBL2 B
ON A.ID = B.ID

[SQL(3)]
SELECT A.ID, B.ID
FROM TBL1 A, TBL2 B
WHERE A.ID = B.ID
UNION ALL
SELECT A.ID, NULL
FROM TBL1 A
WHERE NOT EXISTS (SELECT 1 FROM TBL2 B WHERE A.ID = B.ID)
UNION ALL
SELECT NULL, B.ID
FROM TBL2 B
WHERE NOT EXISTS (SELECT 1 FROM TBL1 A WHERE B.ID = A.ID) |

DEPT와 EMP를 조인하되 사원이 없는 부서 정보도 같이 출력

SELECT E.ENAME, D.DEPTNO, D.DNAME 
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO = E.DEPTNO;

다중 칼럼(Multi Row) 서브쿼리

  • 서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

집합 연산자에 대한 설명

  1. UNION 연산자는 합집합 결과에서 중복된 행을 하나의 행으로 만든다.
  2. UNION ALL 연산자는 집합 간의 결과가 중복되지 않는 경우, UNION과 결과가 동일하다.
  3. UNION 연산자를 사용한 SQL은 각각의 집합에 GROUP BY절을 사용할 수 있다.
  4. UNION 연산자를 사용한 SQL은 ORDER BY절을 가장 마지막에 한 번만 사용할 수 있다.

계층형 질의문에 대한 설명

  1. SQL Server에서의 계층형 질의문은 CTE를 재귀 호출함으로써 계층 구조를 전개한다.
  2. SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
  3. 오라클의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는 데 활용한다.
  4. 오라클의 계층형 질의문에서 PRIOR 키워드는 SELECT, WHER, CONNECT BY 절에만 사용할 수 있으며 ‘PRIOR 자식 = 부모’ 형태로 사용하면 순방향 전개로 수행된다.

계층형 질의

  • START WITH : 계층 구조 전개의 시작 위치 지정 CONNECT BY : 다음에 전개될 자식 데이터 지정 PRIOR : CONNECT BY , 절에 사용되며 현재 읽은 칼럼을 지정한다 자식 . PRIOR = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터 부모 자식 방향으로 ( -> ) 전개하는 순방향 전개를 한다. 반대는 역방향 전개 NOCYCLE : 동일한 데이터가 전개되지 않음 ORDER SIBLINGS BY : 형제 노드간의 정렬 수행 WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다 필터링 .( )

서브쿼리에 대한 설명

서브쿼리는 단일 행(Single Row) 또는 복수 행(Multi Row) 비교 연산자와 함께 사용헐 수 있다.
서브쿼리는 SELECT 절, FROM 절, HAVING, ORDER BY 절 등에서 사용이 가능하다.
서브쿼리의 결과가 복수 행 결과를 반한하는 경우네는 IN, ANY, ALL 등의 복수행 비교 연산자와 함께 사용 할 수 있다.
연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리이다.
다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미하며 오라클 DBMS에서 사용할 수 있다. SQL Server은 지원안함.

예제로 보는 DDL 명령어

 Oracle DatabaseSQL Server
테이블 구조 확인DESCRIBE PLAYER;exec sp_help 'dbo.PLAYER' go
테이블 복사CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;SELECT * INTO TEAM_TEMP FROM TEAM;
컬럼 생성ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));ALTER TABLE PLAYER ADD ADDRESS VARCHAR2(80);
컬럼 삭제ALTER TABLE PLAYER DROP COLUMN ADDRESS;ALTER TABLE PLAYER DROP COLUMN ADDRESS;
컬럼 수정ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL;ALTER TABLE TEAM_TEMP ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY; 
컬럼명 변경ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN';
제약조건 생성ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);
제약조건 삭제ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;
테이블 TRUNCATETRUNCATE TABLE TEAM;TRUNCATE TABLE TEAM;
테이블 삭제DROP TABLE TEAM;DROP TABLE TEAM;
This post is licensed under CC BY 4.0 by the author.