SQL 기본 실전문제(2) - TCL , WHERE 조건절, 함수, GROUP BY, HAVING 절, ORDER BY 절, 조인(JOIN)

데이터베이스 트랜잭션의 특성에 대해 서술하시오

데이터베이스의 트랜잭션은 다음과 같은 특성이 있습니다.

 

- 원자성 : 트랜잭션에서 정의된 연산은 모두 성공적으로 실행되지 않으면 전혀 실행되지 않은 상태로 남아있어야합니다.

- 일관성 : 트랜잭션 실행 전에 데이터베이스에 문제가 없다면 실행 후에도 데이터베이스에 문제가 없어야 합니다.

- 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아서는 안됩니다.

- 지속성 : 트랜잭션이 성공하면 변경된 내용은 영구적으로 저장돼야합니다.

 

낮은 트랜젝션 격리성으로 인해 발생하는 데이터베이스 이상현상에 대해 서술하시오.

 

낮은 격리성으로 인해 발생하는 대표적인 이상현상은 다음과 같습니다.

 

- Dirty Read : 커밋되지 않은 데이터를 다른 트랜잭션에서 읽을 수 있을때 발생합니다. 다른 트랜잭션에서 읽은 커밋되지 않은 데이터가 롤백 됐을때 발생합니다. 오라클은 다중 버전 읽기 일관성 모델을 채택함으로써 Lock을 사용하지 않고도 Dirty Read를 피해 일관성 있는 데이터 읽기가 가능합니다.

 

- Non-Repeatable Read : 하나의 트랜잭션에서 같은 쿼리를 두 번 수행할때, 그사이에 다른 트랜잭션이 쿼리와 연관 데이터를 수정하여 같은 쿼리의 결과가 달라지는 현상입니다. select시 for update를 통해 트랜잭션을 격리하여 이 현상을 방지할 수 있습니다.

 

- Phantom Read : 하나의 트랜잭션에서 같은 쿼리를 두 번 수행했을때, 그 사이 발생한 다른 트랜잭션에 의해 없던 레코드가 발생하는 현상을 말합니다.

 

ORACLE과 SQL SERVER의 DDL AUTO COMMIT 정책에 대해 서술하시오.

 

ORACLE은 DDL 실행 후, AUTO COMMIT 하고 SQL SERVER의 경우 AUTO COMMIT 하지 않습니다.

 

ORACLE의 트랜잭션 설졍 방법에 대해 서술하시오.

 

BEGIN TRANSACTION 구문으로 트랜잭션을 시작합니다. COMMIT TRANSACTION, ROLLBACK TRANSACTION 구문을 만나면 트랜잭션이 종료됩니다. ROLLBACK 구문을 만나면 BEGIN TRANSACTION 수에 상관없이 BEGIN TRANSACTION 지점까지 ROLL BACK 됩니다.

 

단, SAVEPOINT 를 지정하면 트랜젝션에 포함된 전체 작업을 되돌리는 것이 아니라 SAVEPOINT 까지 되둘리 수 있습니다. SAVEPOINT의 지정은 오라클의 경우 다음과 같습니다.

 

SAVEPOINT sp1

..
ROLLBACK TO sp1

 

SQL SERVER의 경우 아래와 같습니다.

 

SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR1;

 

조건절 연산자의 우선순위에 대해 서술하시오

 

1. 괄호로 묶은 연산자
2. 부정연산자(NOT)
3. 비교 연산자와 SQL 비교연산자(BETWEEN A AND B, IN, LIKE, IS, NULL)
4. AND
5. OR

 

ORACLE의 NULL 연산 정책에 대해 서술하시오.

 

NULL과의 연산은 항상 NULL 값을 리턴합니다. NULL 값과의 비교연산은 항상 FALSE를 리턴합니다.

 

밴더별 NULL 허용 칼럼의 공백문차열 처리방식에 대해 서술하시오.

 

ORACLE의 경우 NULL 허용칼럼에 공백문자열이 입력되면 NULL로 입력되고 SQL SERVER의 경우 빈문자열이 입력됩니다.
SQL SERVER의 경우 IS NULL 연산자로 해당 칼럼을 검색할 수 없고 ORACLE 의 경우 검색할 수 있습니다.

 

ORACLE 내장함수에 대해서 서술하시오

 

내장함수는 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 나눌 수 있습니다. 두 종류의 함수 모두 단일행 결과를 리턴합니다. 다중행 함수는 집계함수, 그룹함수, 윈도우함수로 구분됩니다. 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용이 가능합니다.

 

단일형 문자형 함수의 종류에 대해 서술하시오

 

- LOWER : 전체 문자열 소문자로
- UPPER : 전체 문자열 대문자로
- ASCCII : 문자열, 숫자를 ASCII 코드로 변환
- CHAR : ASCII 코드나 숫자를 문자열로 변환
- CONCAT : 문자열 1과 문자열 2를 연결합니다.
- SUBSTR(문자열, m[,n]) : 문자열중 m 위치의 문자열부터 n 개의 문자를 반환합니다. n이 없으면 마지막자리 문자열까지 반환합니다.
- LENGTH : 문자열의 길이를 반환
- LTRIM(문자열, [, 지정문자]) : 첫 문자부터 탐색해 지정문자가 있으면 삭제한다. 지정문자가 없으면 공백이 default
- RTRIM : 문자열의 마지막 문자부터 탐삭하여 지정문자가 있으면 삭제합니다. 지정문자가 없으면 공백이 default
- TRIM([leading|trailing\both] 지정문자 from 문자열) : 문자열에서 머리말 꼬리말 또는 양쪽에 있는 지정문자를 제거합니다.

 

날짜형 함수의 종류에 대해 서술하시오

 

- SYSDATE(ORACLE)/GETDATE(SQL_SERVER) : 시스템 날짜를 추출합니다.
- EXTRACT/DATEPART : 날짜유형의 칼럼에서 특정요소를 추출합니다.

 

-- 날짜요소 : YEAR/MONTH/DAY/HOUR/MINUTE/SECOND
select extract('날짜요소' from 컬럼X) as 별칭
from 테이블A;

 

- TO_NUMBER(TO_CHAR(D, 'YYYY|MM|DD')) : 날짜형 속성값을 문자열로 변환해 이를 다시 숫자로 변환합니다.

 

SEARCHED_CASE_EXPRESSION /SIMPLE_CASE_EXPRESSION에 대해 서술하시오.

 

-- SEARCHED_CASE_EXPRESSION
SELECT LOC,
CASE WHEN
  LOC = 'NEW YORK' THEN 'EAST'
  LOC = 'LA' THEN 'WEST'
  ELSE 'ETC'
 END
FROM DEPT;

-- SIMPLE_CASE_EXPRESSION
SELECT LOC,
CASE LOC
    WHEN 'NEW YORK' THEN 'EAST'
      WHEN 'LA' THEN 'WEST'
    ELSE 'ETC'
END
FROM DEPT;

 

ORACLE NULL의 특징에 대해 서술하시오.

 

널 값은 아직 정의되지 않은 값으로 0 또는 공백과는 다릅니다. 0은 숫자이고 공백은 하나의 문자입니다. 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 설정되지 않은 모든 데이터 유형은 NULL을 포함할 수 있습니다.

 

null을 포함하는 연산의 결과는 항상 NULL 입니다. 결과가 NULL이 아닌 다른 값을 얻고자할때 NVL/ISNULL 함수를 사용합니다. 체크 대상이 숫자타입인 경우 주로 0으로, 문자타입인경우 'X'와 같이 의미없는 문자로 치환합니다.

 

단일 행 NULL 관련 함수에 대해 서술하시오.

 

- NVL(EXP1, EXP2)(ORACLE) ISNULL(EXP1,EXP2)(SQL_SERVER) : 표현식1의 결과값이 NULL이면 표현식2의 값을 출력합니다. 두 표현식의 데이터 타입이 동일해야합니다.

- NULLIF(EXP1, EXP2) : exp1과 Exp2가 일치하면 NULL, 일치하지 않으면 exp1을 반환합니다.

- COALESCE(EXP1,EXP2,...) : 임의 개수의 표현식에서 NULL이 아닌 최초의 표현식을 반환합니다. 모든 표현식이 NULL이면 NULL을 반환합니다

 

GROUP BY절과 HAVING 절에 대해 서술하시오.

 

형식은 다음과 같습니다.

 

SELECT [DISTINCT] COLUMN_NAME [ALIAS]
FROM TABLE_NAME
[WHERE CONDITION]
[GROUP BY COLUNM OR EXP]
[HAVING GROUP BY CONDITION]

 

GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 소그룹별 집계합니다. 집계함수의 통계정보에는 NULL을 가진 행이 포함되지 않습니다. GROUP BY 절에는 ALIAS를 사용할 수 없습니다. WHERE 절이 GROUP BY 절보다 먼저 실행되기 때문에 집계 함수는

WHERE 절에 올 수 없습니다.

 

WHERE 절을 통해 집계에 포함되지 않을 행들을 미리 제거합니다. HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수있습니다. HAVING 절에는 GROUP BY에 사용된 칼럼을 집계함수없이 사용할 수 있습니다.

GROUP BY 절에 의해 소그룹별로 만들어진 집계 데이터 중 HAVING 절에서 제한조건을 두어 조건을 만족하는 내용만 출력합니다.

중첩된 집계함수의 경우 최종결과가 1건이 될 수 밖에 없기때문에 GROUP BY 절에 기술된 칼럼을 SELECT 절에 사용할 수 없습니다.

 

SELECT C.광고매체명, B.광고명, A.광고시작일자
FROM 공고게시 A, 광고 B, 광고메체 C, (
    SELECT 광고매체ID, MIN(광고시작일자) AS 광고시작일자
    FROM 광고게시
    GROUP BY 광고매체ID
) D
WHERE A.광고시작일자 = D.광고시작일자
AND A.광고메체ID = D.광고매체ID
AND A.광고ID = B.광고ID
AND A.광고매체ID = C.광고매체ID
ORDER BY C.광고매체명

 

ORACLE ORDER BY 절의 특성에 대해 서술하시오.

 

오라클은 행기반 DATABASE이기 때문에 데이터를 엑세스할 때 행 전체 칼럼을 메모리에 로드합니다. 이와 같은 특성으로 SELECT 절에 기술되지 않은 칼럼을 SELECT 절 이후에 실행되는 ORDER BY 절에 사용할 수 있습니다. 단, FROM 절의 서브쿼리에서 사용되지 않은 칼럼을 상위 쿼리에서 사용할 수는 없습니다.

 

SELECT LOC, AMOUNT
FROM (
    SELECT LOC, AMOUNT
    FROM AMOUNTPERLOC
)
ORDER BY YEAR DESC

 

GROUP BY 절에 사용되지 않은 칼럼은 집계함수 없이는 SELECT 절과 ORDER BY 절에 사용될 수 없습니다.

기본적인 정렬순서는 오름차순(ASC)입니다. 숫자형 데이터 타입은 오름차순으로 정렬할 경우 가장 작은 값 부터 출력됩니다. 날짜형 데이터 타입은 오름차순으로 정렬할 경우 날짜 값이 가장 빠른 값이 먼저 출력 됩니다.

 

ORACLE은 NULL 값을 가장 큰값으로 간주합니다. 오름차순으로 정렬해쓸 경우 가장 마지막에, 내림차순으로 정렬했을 경우 가장 먼저 위치합니다. 반면 SQL SERVER에서는 가장 작은 값으로 간주하여 오름차순으로 정렬하면 가장 머넞, 내림차순으로 정렬하면 가장 마지막에 위치합니다.

 

GROUP BY 절을 사용하는 경우 ORDER BY 절에 집계 함수를 사용할 수 있습니다. ORDER BY 절에서 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용이 가능합니다. 이를 혼용할 수도 있습니다.

 

SELECT 문장의 실행 순서에 대해 서술하시오.

 

발췌 대상 테이블(FROM)을 참조합니다. - 발췌 대상 데이터가 아닌것은 제외합니다(where). - 행을 소그룹화 합니다 (GROUP BY) - 그룸핑된 값의 조건에

맞는 것만을 출력합니다.(HAVING) - 데이터 값을 출력합니다.(select) - 데이터를 정렬합니다.(ORDER BY)

 

SQL SERVER의 TOP(N) WITH TIES COLUMN 함수에 대해 서술하시오.

 

ORDER BY 순서에 따라 N 개의 레코드를 출력합니다. 값이 같으면 함께 출력합니다. WITH TIES 옵션을 ORDER BY 절과 함께 사용해야 합니다.

 

EQUI 조인과 ANSI EQUI JOIN에 대해 서술하시오.

 

-- EQUI JOIN
SELECT T1.COLUMN1, T2.COLUMN2
FROM T1, T2
WHERE T1.COLUMN1 = T2.COLUMN1

-- ANSI EQUI JOIN
SELECT T1.COLUMN1, T2.COLUMN2
FROM T1 INNER JOIN T2
ON T1.COLUMN1 = T2.COLUMN1

 

JOIN에 대해 서술하시오.

 

두 개 이상의 테이블을 연결하여 데이터를 출력하는 것을 JOIN이라 합니다. 일반적으로 연관 테이블은 PK나 FK에 의해 JOIN이 성립됩니다. 하지만 어떤 경우에는 이러한 PK, FK 관계가 없이도 논리적인 값들의 연관만으로 JOIN 성립이 가능합니다. EQUI 조인은 = 연산자에 의해서만 수행되며 비교 연산자를 사용하는 것은 모두 NON-EQUI jOIN입니다.