71. DCL (Data Control Language)
DCL (Data Control Language)은 데이터베이스 관리 시스템에서 데이터 액세스 및 보안을 관리하는 데 사용되는 SQL. 주로 `GRANT`와 `REVOKE` 두 가지 주요 명령어로 구성된다.
1) GRANT
특정 데이터베이스 객체(테이블, 뷰, 프로시저 등)에 대한 특정 권한을 사용자나 역할(role)에게 부여하는 데 사용. 다른 사용자가 데이터베이스 객체에 대한 다양한 작업을 수행할 수 있도록 허용할 수 있다.
GRANT [권한] ON [객체] TO [사용자 또는 역할];
예를 들어, 다음 SQL 명령어는 `employees` 테이블에 대한 `SELECT` 권한을 `user1` 사용자에게 부여
GRANT SELECT ON employees TO user1;
2) REVOKE
이전에 부여된 권한을 취소하고 사용자나 역할로부터 특정 데이터베이스 객체에 대한 권한을 회수하는 데 사용
REVOKE [권한] ON [객체] FROM [사용자 또는 역할];
예를 들어, `user1` 사용자로부터 `employees` 테이블에 대한 `SELECT` 권한을 회수하려면 다음과 같이 `REVOKE` 명령어를 사용할 수 있다.
REVOKE SELECT ON employees FROM user1;
이렇게 함으로써 데이터베이스 관리자는 사용자 또는 역할에 대한 권한을 동적으로 조정하여 데이터의 보안과 무결성을 유지할 수 있다.
72. TCL (Transaction Control Language)
데이터베이스 관리 시스템에서 트랜잭션의 처리를 관리하는 데 사용되는 SQL
주로 `COMMIT`과 `ROLLBACK` 두 가지 주요 명령어로 구성된다.
1) COMMIT
하나의 트랜잭션을 성공적으로 완료하고, 데이터베이스에서 변경된 내용을 영구적으로 저장하는 데 사용
트랜잭션의 모든 변경 내용이 데이터베이스에 적용되며, 데이터베이스는 이제 해당 트랜잭션의 상태를 영구적으로 유지
COMMIT;
2) ROLLBACK
트랜잭션을 취소하고, 트랜잭션을 시작하기 전 상태로 되돌리는 데 사용. 트랜잭션 도중에 발생한 오류나 문제를 해결하고 이전 상태로 데이터베이스를 되돌릴 수 있다.
ROLLBACK;
TCL 명령어들은 데이터베이스의 일관성과 무결성을 유지하기 위해 중요하다. 트랜잭션은 여러 개의 SQL 문을 하나의 논리적인 작업 단위로 묶은 것이며, 이러한 트랜잭션의 성공적인 완료 또는 롤백은 데이터베이스 시스템의 안정성을 보장하는 데 중요한 역할을 한다.
73. NVL(Null Value)
데이터베이스 쿼리에서 사용되는 함수로, 주어진 컬럼이 `NULL`인지를 확인하고, `NULL`이면 대체할 값을 지정할 수 있다. 이 함수는 데이터베이스의 데이터 처리에서 `NULL` 값을 처리할 때 유용하게 사용된다.
NVL(컬럼1, 값)
위의 구문에서 `컬럼1`은 검사할 컬럼을 나타내고, 만약 `컬럼1`이 `NULL`이 아니면 `컬럼1`의 값을 반환하고, `컬럼1`이 `NULL`이면 지정한 `값`을 반환한다.
아래의 쿼리는 `employees` 테이블에서 `salary` 컬럼이 `NULL`인 경우 해당 행의 값으로 0을 반환한다.
SELECT NVL(salary, 0) FROM employees;
다른 데이터베이스 시스템에서는 `NVL` 함수와 유사한 기능을 하는 함수가 존재할 수 있습니다. 예를 들어, Oracle에서는 `NVL` 함수를 사용할 수 있고, MySQL에서는 `IFNULL` 함수 또는 `COALESCE` 함수를 사용할 수 있다. 이러한 함수들은 데이터베이스 시스템에 따라 다르지만 기본적인 목적은 `NULL` 값을 대체하는 것이다.
74. NVL2
주어진 컬럼이 `NULL`인지 여부에 따라 두 가지 다른 값을 반환하는 데이터베이스 함수
NVL2(컬럼1, a, b)
- 컬럼1 : 검사할 컬럼
- a : 컬럼1 이 NULL이 아닐 때 반환할 값
- b : 컬럼1 이 NULL일 때 반환할 값
`NVL2` 함수는 `컬럼1`이 `NULL`이 아니라면 `a`를 반환하고, `컬럼1`이 `NULL`이면 `b`를 반환한다.
예를 들어, 아래 쿼리는 `employees` 테이블에서 `salary` 컬럼이 `NULL`인 경우 "Not Specified"를, `salary` 컬럼이 `NULL`이 아닌 경우 실제 `salary` 값을 반환한다.
SELECT NVL2(salary, salary, 'Not Specified') FROM employees;75. DECODE
조건에 따라 값을 비교하고 특정 조건에 해당하는 값을 반환
주로 Oracle 데이터베이스에서 사용되며, 다른 데이터베이스 시스템에서는 비슷한 기능을 수행하는 함수가 존재할 수 있다.
DECODE(expression, search_value1, result1, search_value2, result2, ..., default_result)
- expression : 비교할 값 또는 표현식
- search_value1, search_value2, ... : 비교할 값
- result1, result2, ... : expression이 각각 search_value1, search_value2, ... 와 일치할 때 반환할 값
- default_result : 어떤 search_value 와도 일치하지 않을 때 반환할 기본 값
`DECODE` 함수는 `expression`이 `search_value`와 일치하는 경우 해당 `result` 값을 반환하고, 일치하는 값이 없을 때 `default_result` 값을 반환한다.
예를 들어, `employees` 테이블에서 직책(`job_title`)이 "Manager"인 경우에는 1, "Analyst"인 경우에는 2, 그 외의 경우에는 3을 반환하고, 만약 `job_title`이 `NULL`이면 0을 반환하는 SQL 쿼리는 다음과 같이 작성할 수 있다.
SELECT DECODE(job_title, 'Manager', 1, 'Analyst', 2, 3, 0) AS job_code
FROM employees;
76. Subquery
하나의 SQL 문 안에 포함된 다른 SQL 문
주로 다른 쿼리의 결과를 평가하고 그 결과를 바탕으로 외부 쿼리의 조건에 따라 동적으로 데이터를 추출하거나 처리하는 데 사용
1) 스칼라 서브쿼리(Scalar Subquery) : 서브쿼리가 하나의 값을 반환하는 경우
SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
2) 테이블 서브쿼리(Table Subquery) : 서브쿼리가 여러 행을 반환하는 경우. 이런 종류의 서브쿼리는 주로 IN, NOT IN, EXISTS, NOT EXISTS와 같은 비교 연산자와 함께 사용된다.
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE product_id = '123');
서브쿼리는 복잡한 데이터 추출 및 조건부 처리를 가능하게 하며, 주로 SELECT, FROM, WHERE, HAVING 절에서 사용된다. 서브쿼리를 잘 활용하면 더 효과적이고 정확한 데이터 검색 및 조작이 가능해진다.
77. EQUI JOIN
두 테이블 간의 조인 연산에서, 두 테이블의 특정 컬럼 값들이 정확하게 일치하는 경우에 해당하는 조인을 수행하는 것. 주로 등호(=) 연산자를 사용하여 조인 조건을 지정
SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;
위의 쿼리에서, `table1`과 `table2`는 각각 조인할 테이블을 나타내며, `column_name`은 두 테이블 간에 정확하게 일치해야 하는 컬럼의 이름을 나타낸다. 두 테이블의 `column_name` 값이 정확하게 일치하는 행만을 결과로 반환한다.
예를 들어, `employees` 테이블과 `departments` 테이블이 각각 `department_id`라는 컬럼을 가지고 있을 때, 이 두 테이블을 `department_id` 값이 일치하는 행들로 조인할 수 있다.
SELECT *
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;
78. NON-EQUI JOIN
두 테이블 간의 조인 연산에서, 두 테이블의 컬럼 값들이 정확히 일치하지 않고 범위나 조건 등을 사용하여 일치하는 행을 찾아 조인하는 것. 등호(=)가 아닌 비교 연산자나 범위 지정 연산자(예: BETWEEN, >, < 등)를 사용하여 조인 조건을 정의
SELECT *
FROM table1
JOIN table2
ON table1.column_name operator table2.column_name;
위의 쿼리에서 `table1`과 `table2`는 조인할 두 테이블을 나타내며, `column_name`은 비교 조건을 적용할 컬럼의 이름을 나타낸다. `operator`는 비교 조건을 나타내며, 이 연산자를 사용하여 두 컬럼 간의 관계를 정의한다.
예를 들어, `employees` 테이블과 `salaries` 테이블이 각각 `employee_id`와 `salary_amount`라는 컬럼을 가지고 있을 때, `salary_amount`가 일정 범위 내에 있는 행들을 찾기 위해 BETWEEN 연산자를 사용한 비등가 조인을 수행할 수 있다.
SELECT *
FROM employees
JOIN salaries
ON employees.employee_id = salaries.employee_id
WHERE salaries.salary_amount BETWEEN 50000 AND 80000;
위의 쿼리는 `employees` 테이블과 `salaries` 테이블을 `employee_id` 값이 일치하고, `salary_amount`가 50000과 80000 사이에 있는 행들로 조인하고, 조인된 결과를 반환한다. 이런 방식으로 NON-EQUI JOIN은 두 테이블 간의 정확한 일치가 아닌 조건에 따라 일치하는 행들을 찾아 조인을 수행한다.
79. Outer JOIN
두 개의 테이블을 조인할 때, 조인 조건을 만족하지 않는 행들을 포함하여 하나의 테이블의 모든 행을 결과에 포함시키는 조인 유형. Inner Join과 달리 조인 조건을 만족하지 않는 경우에도 결과에 포함시킬 수 있다.
1) Left Outer Join : 왼쪽 테이블의 모든 행을 결과에 포함시키고, 오른쪽 테이블의 조건에 맞는 행들을 포함. 조인 조건을 만족하지 않는 오른쪽 테이블의 행은 NULL 값으로 표시
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
2) Right Outer Join : 오른쪽 테이블의 모든 행을 결과에 포함시키고, 왼쪽 테이블의 조건에 맞는 행들을 포함. 조인 조건을 만족하지 않는 왼쪽 테이블의 행은 NULL 값으로 표시
SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
또한, 일부 데이터베이스 시스템에서는 Full Outer Join 이라는 개념도 지원한다. Full Outer Join은 양쪽 테이블의 모든 행을 결과에 포함시키고, 조인 조건을 만족하지 않는 행은 NULL 값으로 표시된다. 하지만, 모든 데이터베이스 시스템이 Full Outer Join을 지원하지 않을 수 있으므로 사용 전에 문서를 참조해야 한다.
Outer Join은 두 테이블 간의 관계를 유지하면서 조인 결과에 누락된 정보를 포함시키기 위해 사용된다. 이를 통해 데이터의 빠진 부분을 식별하고 데이터 분석 또는 보고서 작성에 유용하게 활용될 수 있다.
80. Self JOIN
하나의 테이블에서 여러 열을 참조하여 테이블 내에서 관계를 찾는 조인 유형. 하나의 테이블에서 여러 행 간의 관계를 파악할 때 사용. 특히 테이블 내에 부모-자식 또는 상위-하위 관계가 있는 경우에 자주 사용
예를 들어, 조직 구조를 저장하는 `employees` 테이블에 각 직원의 `employee_id`와 `manager_id`라는 컬럼이 있고, `manager_id`는 해당 직원의 상사의 `employee_id`를 참조할 때 셀프 조인을 사용해 상사와 부하직원 사이의 관계를 찾을 수 있다.
SELECT *
FROM employees AS e1
JOIN employees AS e2
ON e1.manager_id = e2.employee_id;
위의 쿼리에서, `employees` 테이블을 두 번 사용하며, 첫 번째 인스턴스(`e1`)는 부하직원을 나타내고 두 번째 인스턴스(`e2`)는 해당 부하직원의 상사를 나타낸다. `e1.manager_id`가 `e2.employee_id`와 일치하는 경우, 셀프 조인은 상사와 부하직원 사이의 관계를 찾아낸다.
셀프 조인은 계층 구조를 가진 데이터를 관리하거나, 특정 레코드와 그 레코드의 하위 레코드들 간의 관계를 찾을 때 유용하게 사용된다. 이를 통해 조직도, 계층 구조의 트리, 계층 구조의 카테고리 등을 다루는 데에 활용된다.