[SQLD] 개념정리 2과목 - SQL 활용

표준조인

1. INNER JOIN (내부 조인): INNER JOIN은 두 개 이상의 테이블 간에 일치하는 행만 반환합니다. 즉, 조인 조건을 만족하는 행들만 결과에 포함됩니다. INNER JOIN은 가장 일반적으로 사용되는 조인 유형 중 하나입니다.
2. LEFT JOIN (왼쪽 조인): LEFT JOIN은 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서 일치하는 행이 있으면 해당 행을 결합합니다. 오른쪽 테이블에 일치하는 행이 없으면 NULL 값을 가집니다.
3. RIGHT JOIN (오른쪽 조인): RIGHT JOIN은 LEFT JOIN과 반대로 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에서 일치하는 행이 있으면 해당 행을 결합합니다. 왼쪽 테이블에 일치하는 행이 없으면 NULL 값을 가집니다.
4. FULL JOIN (전체 조인): FULL JOIN은 왼쪽과 오른쪽 테이블의 모든 행을 반환하고, 양쪽 테이블에서 일치하는 행이 없으면 NULL 값을 가집니다.

집합 연산자

1. UNION: UNION 연산자는 두 개 이상의 SELECT 문의 결과를 결합하여 중복된 행을 제거한 후 하나의 결과 집합으로 반환 → 결과 집합은 모든 SELECT 문의 결과를 포함하지만 중복 행은 한 번만 포함되는 것
2. SELECT column1 FROM table1 UNION SELECT column2 FROM table2;
3. UNION ALL: UNION ALL은 UNION과 유사하지만 중복된 행을 제거하지 않고 모든 결과를 포함하는 것 따라서 UNION ALL은 중복을 허용하는 합집합 연산을 수행한다.
4. SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2;
5. INTERSECT: INTERSECT 연산자는 두 개의 SELECT 문의 결과에서 공통된 행만을 반환 즉, 교집합을 구하여 반환한다. INTERSECT 연산자는 각 SELECT 문의 결과 집합에서 중복된 행을 제거함.
6. SELECT column1 FROM table1 INTERSECT SELECT column2 FROM table2;
7. EXCEPT 또는 MINUS: EXCEPT 연산자는 첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과를 제외한 결과를 반환하는 것 = 즉, 차집합을 구하여 반환한다
8. SELECT column1 FROM table1 EXCEPT SELECT column2 FROM table2;

계층형 질의

: SQL에서 계층적인 데이터를 다루거나 표현하는데 사용한다

• CONNECT BY절 : 부모-자식 관계를 정의하고 트리 구조를 탐색한다
• PRIOR 연산자 : 부모-자식 관계 표현, 현재 행의 부모 행을 가리킨다
• LEVEL 을 사용한 계층 수준 제어 : 현재 행이 트리 구조에서 어느 수준에 위치하는지 나타내는 열
• START WITH 절 : 계층형 질의의 시작저믈 지정하여, 일반적으로 트리의 루트 노드를 지정하는데 사용

주로 조직도, 부서 구조, 트리 구조와 같이 부모-자식 관계를 갖는 데이터를 쿼리하고 표현하기 위해 활용

SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

//계층형 질의를 통해 복잡한 데이터 구조를 쉽게 분석하고 표현할 수 있으며, 
//조직 구조나 트리 구조와 같은 데이터를 효과적으로 다룰 수 있다.

서브쿼리

서브쿼리 : 다른 쿼리 내에 포함된 쿼리 외부 쿼리의 조건이나 결과에 의존하여 실행되며, 주로 WHERE, HAVING, FROM등의 절에서 사용된다.

• 단일 행 서브쿼리 : 결과가 단일 행을 반환, 주로 비교 연산자와 사용

SELECT EMPLOYEE_NAME 
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

• 다중 행 서브쿼리 : 여러 행을 반환하는 경우 사용, 주로 IN, ANY, ALL등과 함께 사용

SELECT EMPLOYEE_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID 
FROM DEPARTMENTS WHERE LOCATION_ID = 1700);

• 스칼라 서브쿼리 : 단일 값(스칼라 값)을 반환하는 경우 사용, 주로 SELECT 절이나 비교 연산자의 오른쪽 피연산자로 사용

SELECT EMPLOYEE_NAME, (SELECT MAX(SALART) FROM EMPLOYEES)
AS MAX_SALARY EMPLOYEES;

• WHERE 절 서브쿼리 : 주로 외부 쿼리의 결과를 필터링하는데 사용된다.
• FROM 절 서브쿼리 : 서브쿼리가 하나의 테이블처럼 사용되며, 서브쿼리의 결과를 임시 테이블로 사용할 수 있다.
• SELECT 절 서브쿼리 : 서브쿼리의 결과가 스칼라 값이면 SELECT 절에 사용될 수 있다.
• HAVING 절 서브쿼리 : 그룹화된 결과를 필터링하는데 사용

그룹함수

그룹함수 : 특정 그룹의 데이터를 집계하고 분석하는데 사용, 주로 GROUP BY절과 함께 사용되며, 집계된 결과는 각 그룹의 단일 값으로 반환된다.

• COUNT : 그룹 내 행의 수
• SUM : 그룹 내 특정 열의 합계
• AVG : 그룹 내 특정 열의 평균
• MAX : 그룹 내 특정 열의 최댓값
• MIN : 그룹 내 특정 열의 최솟값
• 단일 그룹 함수 사용 : 단일 그룹 함수를 사용하여 전체 그룹의 집계 결과를 얻는다
• GROUP BY 절 : 그룹 함수와 함께 사용되며, 특정 열을 기준으로 데이터를 그룹화한다.
• HAVING 절 : 그룹 함수를 필터링하는데, GROUP BY 절 이후에 작성된다.

윈도우 함수

윈도우 함수

: SQL에서 특정 윈도우(창) 내에서 데이터를 분석하고 집계하는데 사용되는 함수 → 특정 윈도우의 데이터를 분석하는데 사용

⇒ 윈도우 함수는 SELECT 문의 SELECT 절 또는 ORDER BY 절에 사용 ⇒ PARTITON BY 절을 사용하여 데이터를 그룹화하고, ORDER BY 절을 사용하여 행의 정렬 순서를 지정

→ 윈도우 함수를 통해 일반적인 집계 함수와는 다른 유연한 분석이 가능하며, 복잡한 데이터 분석 작업을 수행할 수 있다.

• 윈도우 스펙 (Window Specification) : 윈도우 함수를 적용할 데이터의 범위를 정의
• 오더링 (Ordering) : 윈도우 함수가 적용될 때 행의 정렬 순서를 지정 → 일반적으로 PARTITION BY 절과 ORDER BY 절을 함께 사용하여 윈도우를 정의
• 프레임 (Frame) : 윈도우 내 함수가 실행될 행의 범위를 결정 → 현재 행을 중심으로 이전 행, 다음 행 등을 포함 가능

윈도우 함수 종류

• ROW_NUMVER() : 각 행에 순서 지정
• RANK() : 각 행에 대해 순위 지정, 동일한 값의 경우 동일한 순위 부여
• DENSE_RANK() : RANK()와 비슷하지만 동일한 값에 대해 중복 순위를 부여하지 않음
• NTILE(n) : 결과 집합을 n개의 동일한 크기의 그룹으로 분할함
• LAG() / LEAD() : 현재 행의 이전/다음 행의 값을 가져옴
• SUM(), AVG(), MIN(), MAX()

SELECT EMPLOYEE_ID, SALARY, SUM(SALARY)
OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE)
AS DEPT_TOTAL_SALARY FROM EMPLOYEES;

//employees 테이블에서 각 부서별로 hire_date를 기준으로 정렬한 후, 
// 해당 부서의 누적 급여를 계산하는 SQL  

 

절차형 SQL

절차형 SQL

: SQL 언어의 확장으로, 프로그래밍 언어와 유사한 제어 구조와 변수 지원을 제공하여 복잡한 비즈니스 로직을 구현

→ 저장 프로시저, 함수, 트리거 등을 작성하는데 활용하는 SQL

[구성요소]

• 변수 (Variables) : 값을 저장하고 처리하는 데 사용되는 이름이 지정된 메모리 위치
• 조건문 (Conditional Statements) : 조건에 따라 프로그램 흐름 제어_IF, ELSE, CASE 등
• 반복문 (Looping Statements) : WHILE, FOR 등, 코드블록 반복 실행
• 커서 (Cursor) : 쿼리 결과 집합을 한 행씩 처리하기 위한 데이터 포인터
• 예외처리 (Exception Handling) : 실행 중 발생할 수 있는 예외 처리 기능 _TRY…CATCH 등

✔️ [절차형 SQL의 장점]

• 재사용성 : 반복적인 코드를 줄이고 유지보수를 높일 수 있음
• 성능향상 : 일괄 처리 작업이 필요한 경우 데이터 처리의 성능을 향상시킬 수 있음
• 보안 : 사용자가 직접 쿼리를 실행하는 것보다 저장 프로시저를 사용해 DB 보안을 강화할 수 있음
• 저장 프로시저 : 데이터베이스 내 미리 컴파일 되어 저장된 SQL 코드 블록 → 이를 통해 파라미터를 받아들이고 실행되며, 결과를 반환할 수도 있음
• 함수 : 값을 반환하는 절차형 SQL 객체, SELECT문이나 다른 함수의 일부로 사용 가능 ⇒ 사용자 정의 함수 / 시스템 함수로 나뉨
• 트리거 : 특정 이벤트 발생 시 자동으로 실행되는 절차형 SQL → 주로 INSERT, UPDATE, DELETE와 같은 데이터 변경 이벤트에 대해 실행

//저장 프로시저
CREATE PROCEDURE SP_GETEMPLOYEE
@EMPLOYEEID INT 
AS BEGIN
	SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = @EMPLOYEEID;
END;

//함수
CREATE FUNCTION FN_GETTOTALSALES (@STARTDATE DATE, @ENDDATE DATE)
RETURNS DECIMAL(10,2)
AS 
BEGIN
	DECLARE @TOTALSALES DECIMAL(10,2);
	SELECT @TOTALSALES = SUM(SALESAMOUNT) FROM SALES WHERE SALEDATE BETWEEN @STARTDATE AND @ENDDATE;
	RETURN @TOTALSALES;
END;

//트리거
//->employees 테이블의 행이 업데이트 될 때마다 실행된다
//새로 업데이트된 행은 inserted 테이블에 저장되며, 이를 사용하여 변경 내용을 로그 테이블에 기록한다 (변경된 사원의 ID, 수행된 작업, 로깅된 날짜를 로그테이블에 저장)
CREATE TRIGGER TRG_AFTERUPDATEEMPLOYEE
ON EMPLOYEES
AFTER UPDATE
AS
BEGIN
	INSERT INTO EMPLOYEELOG (EMPLOYEEID, ACTION, LOGDATE)
	SELECT 
			EMPLOYEEID,
			'EMPLOYEE UPDATED', 
			GETDATE()
	FROM
			INSERTED;
END;