2-2. SQL 활용

SQL 활용

(1) 표준 조인

STANDAR SQL

• 일반 집합 연산자를 현재의 SQL과 비교하면,
  1. UNION 연산은 UNION 기능으로,
  2. INTERSECTION 연산은 INTERSECT 기능으로,
  3. DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로,
  4. PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.

E.F.CODD 일반 집합 연산자

• 순수 관계 연산자를 현재의 SQL과 비교하면,
  1. SELECT 연산은 WHERE 절로 구현되었다.
  2. PROJECT 연산은 SELECT 절로 구현되었다.
  3. (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.
  4. DIVIDE 연산은 현재 사용되지 않는다.

E.F.CODD 순수 관계 연산자

 

FROM절의 JOIN 형태

• ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.
• INNER JOIN / NATURAL JOIN / USING 조건절 / ON 조건절 / CROSS JOIN / OUTER JOIN

 

INNER JOIN

• JOIN 조건에서 동일한 값이 있는 행만 반환한다.
• DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다.
• USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

SELECT * FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
 
-- JOIN문에 추가 조건 및 정렬이 가능하다.
SELECT * FROM EMP INNER JOIN DEPT
  ON EMP.DEPTNO = DEPT.DEPTNO
 AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;

NATURAL JOIN

• 두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI(=)JOIN을 수행한다.
• 추가로 USING 조건절, ON 조건절, WHERE절에서 JOIN 조건을 정의할 수 없다.
• JOIN에 사용된 컬럼들은 같은 데이터 유형이어야 한다.
• ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.

-- 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력하시오.
SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;

 

USING 조건절

• 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 =JOIN을 할 수 있다.
• SQL Server에서는 지원하지 않는다.
• JOIN 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

/* 
ON 조건절 예제
팀과 스타디움 테이블을 팀ID로 JOIN하여 팀이름, 팀ID, 스타디움 이름을 찾아본다.
STADIUM에는 팀ID가 HOMETEAM_ID라는 칼럼으로 표시되어 있다. 
*/
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME FROM TEAM
JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID ORDER BY TEAM_ID;

 

CROSS JOIN

CARTESIAN PRODUCT / JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합

-- 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.
SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT ORDER BY ENAME;

OUTER JOIN 설명

1.  LEFT OUTER JOIN
   조인 수행 시 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 우측 테이블에서 JOIN 대상 데이터를 읽어온다.
   

   /* STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다.
   STADIUM과 TEAM을 JOIN 하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다. */
   SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME
   FROM STADIUM LEFT OUTER JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
   ORDER BY HOMETEAM_ID;

2. RIGHT OUTER JOIN
   LEFT OUTER JOIN와 반대로 우측 테이블이 기준이 되어 결과 생성
   
   
3. FULL OUTER JOIN
   합집합 개념으로 LEFT와 RIGHT를 모두 읽어온다.
   

   UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20; --(먼저 업데이트)
   SELECT * FROM DEPT_TEMP; --(업데이트한 모두를 읽어온다)

 

---

(2) 집합 연산자

• 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나
• 집합연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어준다.
• SELECT절의 칼럼 수가 동일하고 동일 위치에 존재하는 칼럼의 데이터타입이 상호 호환 가능해야 한다.

 

집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다.
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 만든다.
EXCEPT(MINUS)	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합으로 중복된 행은 하나의 행으로 만든다.

 

---

(3) 계층형 질의와 셀프 조인

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. (예: 조직, 사원, 메뉴 등)

 

CONNECT BY 키워드

키워드	설명
LEVEL	검색 항목의 깊이를 의미한다. 즉, 계층구조에서 가장 상위 레벨이 1이 된다.
CONNECT_BY_ROOT	계층구조에서 가장 최상위 값을 표시한다.
CONNECT_BY_ISLEAF	계층구조에서 가장 최하위 값을 표시한다.
SYS_CONNECT_BY_PATH	계층구조의 전체 전개 경로를 표시한다.
NOCYCLE	순환구조가 발생지점까지만 전개된다.
CONNECT_BY_ISCYCLE	순환구조 발생 지점을 표시한다.

 

셀프 조인

동일 테이블 사이의 조인, 반드시 테이블 별칭(Alias)을 사용해야 한다.

 

---

(4) 서브 쿼리

• 서브쿼리(Subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
• 서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다.
• 서브쿼리를 괄호로 감싸서 사용한다. 단일행 또는 복수행 비교연산자와 함께 사용가능하다. ORDER BY를 사용하지 못한다

 

서브쿼리 종류(반환 행)

서브쿼리 종류	설명
단일 행 서브쿼리 (Single row subquery)	- 서브쿼리를 실행하면 그 결과는 반드시 한 행만 조회된다. - 비교 연산자인 =, <, <=, >, >=, <>를 사용한다.
다중 행 서브쿼리 (Multi row subquery)	- 서브쿼리를 실행하면 그 결과는 여러 개의 행이 조회된다. - 다중 행 비교 연산자인 IN, ANY, ALL, EXISTS를 사용한다.

 

다중 행 비교 연산자

다중 행 연산	설명
IN (서브쿼리)	Main query의 비교조건이 Subquery의 결과 중 하나만 동일하면 참이 된다. (OR조건)
ALL (서브쿼리)	- Main query와 Subquery의 결과가 모두 동일하면 참이 된다. - < ALL : 최솟값을 반환한다. - > ALL : 최댓값을 반환한다.
ANY (서브쿼리)	- Main query의 비교조건이 Subquery의 결과 중 하나 이상 동일하면 참이 된다. - < ANY : 하나라도 크게 되면 참이 된다. - > ANY : 하나라도 작게 되면 참이 된다.
EXISTS (서브쿼리)	Main query와 Subquery의 결과가 하나라도 존재하면 참이 된다.

 

스칼라 서브쿼리

반드시 한 행과 한 컬럼만 반환하는 서브쿼리이다.

---

뷰(VIEW)

• 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다.
• 뷰의 장점 : 독립성 / 편리성 / 보안성

 

---

(5) 그룹 함수

• ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의하고 있다.
• AGGREGATE FUNCTION, GROUP FUNCTION, WINDOW FUNCTION

 

ROLLUP 함수

• ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
• 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀜. 가능한 Subtotal만 생성된다.

/* 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행 */
SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB;

 

CUBE 함수

결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.

/* 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행 */
SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs'
ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE (DNAME, JOB) ;

 

GROUPING SETS 함수

• GROUPING SETS 함수는 GROUP BY에 나오는 컬럼의 순서와 관계없이 다양한 소계를 만들 수 있다.
• GROUPING SETS 함수는 GROUP BY에 나오는 컬럼의 순서와 관계없이 개별적으로 모두 처리한다.

 

---

(6) 윈도우 함수

• 윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다.
• 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

SELECT WINDOW_FUNCTION(ARGUMENTS)
    OVER (PARTITION BY 컬럼
        ORDER BY WINDOWING절)
FROM 테이블명;

 

윈도우 함수 구조

구조	설명
ARGUMENTS(인수)	윈도우 함수에 따라서 0~N개의 인수를 설정한다.
PARTITION BY	전체 집합을 기준에 의해 소그룹으로 나눈다.
ORDER BY	어떤 항목에 대해서 정렬한다.
WINDOWING	- 행 기준의 범위를 정한다. - ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위이다.

 

WINDOWING

구조	설명
ROWS	부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다.
RANGE	논리적인 주소에 의해 행 집합을 지정한다.
BETWEEN~AND	윈도우의 시작과 끝의 위치를 지정한다.
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행임을 의미한다.
UNBOUNDED FOLLOWING	윈도우 마지막 위치가 마지막 행임을 의미한다.
CURRENT ROW	윈도우 시작 위치가 현재 행임을 의미한다.

 

순위 함수(RANK Function)

• 윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.
• 순위 함수는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다.

순위 함수	설명
RANK	- 특정항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다.
DENSE_RANK	동일한 순위를 하나의 건수로 계산한다.
ROW_NUMBER	동일한 순위에 대해서 고유의 순위를 부여한다.

• 순위 함수 예시

/* 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다. */
SELECT JOB, ENAME, SAL, RANK( )
OVER (ORDER BY SAL DESC) ALL_RANK, RANK( )
OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

 

집계 함수(RANK Function)

SUM / AVG / COUNT (행의 수) / MAX와 MIN

 

행 순서 관련 함수

행 순서	설명
FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다.
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구한다. - MAX 함수를 사용해서 같은 결과를 구할 수 있다.
LAG	이전 행을 가지고 온다.
LEAD	- 윈도우에서 특정 위치의 행을 가지고 온다. - 기본값은 1이다.

 

비율 관련 함수

비율 함수	설명
CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상에 위치를 0~1사이의 값을 가진다.
PERCENT_RANK	파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다.
NTILE	파티션별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 조회한다.
RATIO_TO_REPORT	파티션 내에 전체 SUM(컬럼)에 대한 행 별 컬럼값의 백분율을 소수점까지 조회한다.

 

---

(7) 절차형 SQL

• 일반적인 개발 언어처럼 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 절차형 SQL을 제공 [ Oracle : PL/SQL, SQL Server : T-SQL, DB2 : SQL/PL ]
• 절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.
• 절차형 SQL을 이용하여 Procedure, User Defined Function, Trigger 저장 모듈 생성 가능.

 

PL/SQL 개요

Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.

 

T-SQL 개요

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, T-SQL은 엄격히 말하면, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것

 

Procedure의 생성과 활용

 

User Defined Function의 생성과 활용

절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다.

 

Trigger의 생성과 활용

• “특정한 테이블에 INSERT, UPDATE DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램”
• 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다. Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.
• Trigger는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.
• Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.

 

프로시저와 트리거의 차이

프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만,
데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.

프로시저	트리거
CREATE Procedure 문법 사용	CREATE Trigger 문법 사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨