4. 서브 쿼리
서브쿼리(Subquery): 하나의 SQL문안에 포함되어 있는 또 다른 SQL문
서브 쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없다.
서브쿼리를 괄호로 감싸서 사용한다. 단일행 또는 복수행 비교연산자와 함께 사용가능하다. ORDER BY를 사용하지 못한다
서브쿼리의 종류 분류
단일행 서브쿼리
단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브 쿼리의 결과 건수가 반드시 1건 이하여야 함
다중행 서브쿼리
서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용
@@ 예)
SQL>>
“선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하라!”
SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수')
ORDER BY TEAM_NAME;동명이인 있을 수 있음.
다중 칼럼 서브쿼리
서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것
연관서브쿼리
서브쿼리내에 메인쿼리칼럼이 사용된 서브 쿼리
EXISTS는 항상 연관서브쿼리로 사용
@@ SELECT 절에 서브쿼리 사용하기 - 스칼라 서브쿼리(Scalar Subquery)
한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리
@@ FROM 절에서 서브 쿼리 사용하기 - 인라인뷰(Inline View)
일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)
인라인뷰에서는 ORDER BY절을 사용할 수 있다. 인라인뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부데이터를 추출하는 것을 TOP-N 쿼리라고 한다
Oracle에서는 ROWNUM이라는 연산자를 통해 결과 중 일부데이터만 추출 가능
@@ 뷰 view
테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 없고 단지 뷰 정의(View Definition)만 있음.
5. 그룹 함수
데이터 분석 개요
SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의:
-
AGGREGATE FUNCTION : COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함
-
GROUP FUNCTION : 그룹함수
소그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목들 간 다차원적인 소계를 계산 할 수 있는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수 -
WINDOW FUNCTION
ROLLUP 함수
소그룹 간의 소계를 계산
ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행결과도 바뀌게 되므로 인수의 순서에도 주의
cube 함수 @@
결합 가능한 모든 값에 대하여 다차원 집계를 생성
grouping sets 함수 @@
원하는 부분의 소계만 손쉽게 추출
인수는 평등한 관계이므로 인수의 순서 바뀌어도 결과는 같다
6. 윈도우 함수
행과 행간의 관계를 쉽게 정의하기 위해 만든 함수
2가지 종류(BETWEEN 사용 타입, BETWEEN 미사용 타입)
OVER문구가 키워드로 필수 포함
SQL>>
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;
그룹 내 순위함수
Rank 함수
DENSE_RANK 함수 @@
동일한 순위를 하나의 건수로 취급, 중간 순위를 안 비움
ROW_NUMBER 함수 @@
동일한 값이라도 고유한 순위를 부여한다는 점이 RANK,DENSE_RANK와 다르다.
예) SQL>>
“사원 데이터에서 급여가 높은 순서와 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;일반 집계 함수
SUM 함수
MAX 함수
MIN 함수
AVG 함수
COUNT 함수
그룹 내 행 순서 함수
-
FIRST_VALUE 함수: (server지원 X)
파티션별 윈도우에서 가장 먼저 나온 값 -
LAST_VALUE 함수: (server지원 X)
파티션별 윈도우에서 가장 나중에 나온 값 -
★ LAG 함수: (server지원 X)
“현재 읽혀진 데이터의 이전 값을 알아내는 함수”
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져오기
- ★ LEAD 함수: (server지원 X)
“이후 값을 알아내는 함수”
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져오기
그룹 내 비율함수
-
RATIO_TO_REPORT 함수 (server지원 X)
파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 -
PERCENT_RANK 함수 (server지원 X)
파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율 -
CUME_DIST 함수 (server지원 X)
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 -
NTILE 함수
전체 건수를 ARGUMENT 값으로 N 등분한 결과
7. DCL
DATA CONTROL LANGUAGE
유저를 생성하고 권한을 제어
-
Oracle
유저를 통해 데이터베이스에 접속을 하는 형태. 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받기 -
SQL Server
인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. 더 나아가 특정 유저는 특정 데이터베이스 내의 특정 스키마에 대해 권한을 부여받을 수 있다
유저와 권한
유저 생성과 시스템 권한 부여: 롤(ROLE)을 이용하여 간편하고 쉽게 권한을 부여
OBJECT에 대한 권한 부여: 오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미
Role을 이용한 권한 부여
유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공 - 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지를 관리하는 번거로움 줄이기 위해
데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후ROLE을 다른 ROLE이나 유저에게 부여
ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여
8. 절차형 SQL
절차형 sql 개요 @@
절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다
DBMS 벤더별로
PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공
PL/SQL
Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어
T-SQL
T-SQL 특징: T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, T-SQL은 엄격히 말하면, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것
Trigger
특정한 테이블에 INSERT, UPDATE DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
데이터베이스에서 자동적으로 수행
Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.
데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용
프로시저와 트리거의 차이
프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만,
데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.
