[Database] SQLD 자격검정 실전문제 개념 정리 2편(p40-p49)

이번 포스팅은 [Database] SQLD 자격검정 실전문제 개념 정리 1편에 이어 2편을 다루고자 합니다.

DUAL 테이블의 특성

사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블입니다.

SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블입니다.

DUMMY라는 문자열 유형의 컬럼에 X 라는 값이 들어 있는 행을 1건 포함하고 있습니다.

단일행 함수의 종류

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환한다.	LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/ LEN, LTRIM, RTRIM, TRIM, ASCII,
숫자형 함수	숫자를 입력하면 숫자 값을 반환한다.	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL.CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산한다.	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, 'YYYY'|'MM'|'DD)) |YEAR|MONTH|DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.	TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

집계 함수의 종류

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

CASE, DECODE 함수

CASE 함수는 조건에 따라 다른 값을 반환할 때 사용됩니다.
CASE 함수에서 ELSE 절을 생략하면 조건을 만족하지 않는 경우 NULL 값을 반환합니다.

구문 형식

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE resultN
END

예제 코드

SELECT 
    employee_id,
    salary,
    CASE 
        WHEN salary < 3000 THEN 'Low'
        WHEN salary >= 3000 AND salary < 7000 THEN 'Medium'
        ELSE 'High'
    END AS salary_grade
FROM employees;

DECODE 함수 또한 특정 값에 따라 다른 값을 반환합니다. 주로 Oracle DBMS에서 사용됩니다.

구문 형식

DECODE(expression, search1, result1, search2, result2, ..., default)

예제 코드

SELECT 
    employee_id,
    DECODE(department_id, 
           10, 'Accounting', 
           20, 'Research', 
           30, 'Sales', 
           'Other') AS department_name
FROM employees;

NVL/ISNULL, NULLIF, COALESCE

NVL은 Oracle에서 NULL 값을 대체할 때 사용되고, ISNULL은 SQL Server에서 사용됩니다.

구문 형식

NVL(expression, replacement)
ISNULL(expression, replacement)

예제 코드

SELECT 
    employee_id,
    NVL(commission_pct, 0) AS commission
FROM employees;

SELECT 
    employee_id,
    ISNULL(commission_pct, 0) AS commission
FROM employees;

NULLIF 함수는 두 개의 표현식이 같으면 NULL을 반환하고, 다르면 첫 번째 표현식을 반환합니다.

구문 형식

NULLIF(expression1, expression2)

예제 코드

SELECT 
    employee_id,
    NULLIF(salary, 0) AS salary
FROM employees;

COALESCE 함수는 주어진 표현식 목록 중에서 첫 번째로 NULL이 아닌 값을 반환합니다.

구문 형식

COALESCE(expression1, expression2, ..., expressionN)

예제 코드

SELECT 
    employee_id,
    COALESCE(commission_pct, bonus_pct, 0) AS effective_commission
FROM employees;

오라클 날짜 연산

오라클에서 날짜의 연산은 숫자의 연산과 같습니다.

예를 들어, 특정 날짜에 1을 더하면 하루를 더한 결과와 같고 (1/24/60)을 더하면 1분을 더한 것과 같습니다.

SELECT * FROM 테이블1, 테이블2, 테이블3 구조

해당 구문은 암시적 조인(implicit join)이라고도 불리는 데카르트 곱(Cartesian Product)을 수행합니다. 이 구문은 세 개의 테이블의 모든 가능한 조합을 반환합니다. 즉, 각 테이블의 모든 행이 다른 테이블의 모든 행과 결합되는 방식입니다.

SELECT DNAME, LOC AREA, DEPTNO FROM DEPT ORDER BY 1, AREA, 3 DESC 정렬 순서

SELECT DNAME, LOC AS AREA, DEPTNO FROM DEPT ORDER BY 1, AREA, 3 DESC

해당 쿼리는 DEPT 테이블에서 DNAME, LOC (별칭 AREA), DEPTNO를 선택한 후 특정 순서로 정렬합니다.

ORDER BY 1: 첫 번째 선택된 열인 DNAME을 기준으로 오름차순 정렬합니다.
ORDER BY AREA: LOC 열의 별칭인 AREA를 기준으로 오름차순 정렬합니다.
ORDER BY 3 DESC: 세 번째 선택된 열인 DEPTNO를 기준으로 내림차순 정렬합니다.

Like 연산자

SQL에서 LIKE 연산자는 문자열 패턴 매칭에 사용됩니다.

LIKE 연산자는 %와 _ 두 개의 와일드카드를 사용합니다.

%는 0개 이상의 임의의 문자를 나타냅니다. 즉, %는 해당 위치에 어떤 문자나 문자열이 와도 매칭됩니다.

_는 단일 임의의 문자 하나를 나타냅니다. 즉, _는 해당 위치에 정확히 한 개의 문자가 와야 합니다.

SUBSTR/SUBSTRING 함수

SUBSTR/SUBSTRING 함수는 주어진 문자열에서 지정된 위치부터 시작하여 특정 길이만큼의 부분 문자열을 반환합니다. 인덱스는 1부터 시작입니다.

SUBSTR 함수는 Oracle, MySQL 등에서 사용되며, SUBSTRING 함수는 SQL Server, PostgreSQL 등에서 사용됩니다.

구문 형식

SUBSTR(string, start_position, [length])

예제 코드

SELECT SUBSTR('Hello, World!', 8, 5) AS result FROM dual;
-- 결과: 'World'

SELECT SUBSTR('Hello, World!', -6, 5) AS result FROM dual;
-- 결과: 'World' (음수 시작 위치는 끝에서부터 계산)

Round 함수

ROUND 함수는 숫자 값을 지정된 소수점 자리로 반올림하는 데 사용됩니다.

ROUND 함수는 소수점 이하 자릿수를 지정할 수 있고, 지정하지 않으면 기본적으로 0을 사용하여 정수로 반올림합니다.

구문 형식

ROUND(number, decimal_places)
-- ROUND(반올림할 숫자, 반올림할 소수점 자리)

예제 코드

SELECT ROUND(123.456, 2) AS result FROM dual;
-- 결과: 123.46

SELECT ROUND(123.456) AS result FROM dual;
-- 결과: 123

SELECT ROUND(123.456, -1) AS result FROM dual;
-- 결과: 120

SAVEPOINT, ROLLBACK

SAVEPOINT는 트랜잭션 내에서 특정 지점을 저장하는 데 사용됩니다. 이를 통해 트랜잭션을 부분적으로 롤백할 수 있습니다. 즉, 트랜잭션 내의 특정 시점으로 되돌릴 수 있습니다.

ROLLBACK은 트랜잭션을 취소하고, 트랜잭션이 시작된 시점 또는 특정 SAVEPOINT로 되돌리는 데 사용됩니다.

예제 코드

-- 트랜잭션 시작
BEGIN;

-- 계좌 1에서 100을 빼기
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- SAVEPOINT 설정
SAVEPOINT sp1;

-- 계좌 2에서 200을 빼기
UPDATE accounts SET balance = balance - 200 WHERE account_id = 2;

-- 계좌 1에서 50을 더하기 (이 작업이 실패한다고 가정)
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;

-- SAVEPOINT 설정
SAVEPOINT sp2;

-- 잘못된 작업으로 트랜잭션 롤백
ROLLBACK TO SAVEPOINT sp1;

-- 트랜잭션 커밋
COMMIT;

-- 결국 sp1 이후의 트랜잭션들은 롤백 처리되고 sp1전까지의 내용들이 커밋됩니다.

Group By 절

Group By 절은 데이터들을 작은 그룹으로 분류해 소그룹에 대한 항목별 통계 정보를 얻을 때 사용합니다.

NULL 데이터도 집계에 포함하므로 컬럼의 값에 NULL이 있는 행도 결과로 출력됩니다.

ORDER BY(CASE WHEN ID = 999 THEN 0 ELSE ID END)

특정 ID 값(예: 999)을 다른 값들보다 우선하여 정렬하기 위한 것입니다. ID가 999인 경우 0으로 변환되어 정렬 시 맨 앞에 위치하게 되고, 나머지 ID 값들은 원래 값대로 정렬됩니다.

실제로 ID 값이 변하는 것은 아니고, 정렬을 위해 임시로 값이 변경되어 처리되는 것입니다.