자격증공부/SQLD

[2과목] SQL 활용

지야소이 2023. 9. 1. 13:33

😸 표준 조인

- ANSI/ISO 표준 SQL에서 규정한 조인 기법

- 내부 조인(Inner Join), 외부 조인(Outer Join), 크로스 조인(Cross Join), 자연 조인(Natural Join)이나 표준 조인 조건절인

  ON, USING을 통해 테이블 간 JOIN 조건을 FROM 절에서 명시적 정의

 

  • NATURAL JOIN

- 두 테이블 간에 동일한 이름, 타입을 가진 컬럼을 조인

- 조인에 이용하는 컬럼은 명시하지 않아도 자동으로 조인

- 동일한 이름이나 데이터 타입이 다르면 에러 발생

  ex) SELECT STU_NO, STU_NAME FROM STUDENT NATURAL JOIN ENROL WHERE SUB_NO IN(101, 102);

       -> 과목번호가 101 또는 102를 수강하는 학생의 학번과 이름을 출력 

 

  • USING JOIN

- 조인에 사용될 컬럼을 지정하고, 조인 컬럼은 괄호로 묶어서 기술

- NATURAL절과 USING절은 함께 사용할 수 없음

  ex) SELECT STU_NO, STU_NAME FROM STUDENT JOIN ENROL USING(STU_NO) WHERE SUB_NO IN(101,102);

       -> 과목번호가 101 또는 102를 수강하는 학생의 학번과 이름을 출력

 

  • CROSS JOIN

- 공통 분모가 없이 서로 합쳐지는 조인 (TABLE 통합)

 ex) SELECT STUDENT.STU_NO, ENROL.SUB_NO FROM STUDENT CROSS JOIN ENROL;

# Inner Join
SELECT 컬럼명 FROM 테이블1 INNER JOIN 테이블2 ON 조건절;

# Outer Join
SELECT 컬럼명 FROM 테이블 (LEFT, RIGHT) OUTER JOIN 테이블2 ON 조건절;

# Full Outer Join
SELECT 컬럼명 FROM 테이블 FULL OUTER JOIN 테이블2 ON 조건절;

# Cross Join 
SELECT 컬럼명 FROM 테이블1 CROSS JOIN 테이블2;

# Natural Join
SELECT 컬럼명 FROM 테이블1 NATURAL JOIN 테이블2;

# ON 조건절
SELECT 컬럼명 FROM 테이블1 JOIN 테이블2 ON 조건절;

# USING 조건절
SELECT 컬럼명 FROM 테이블1 JOIN 테이블2 USING 조건절;

 

 

👉 관계 대수

  •  집합 연산자 
  자동 정렬 속 도 특 징
UNION (합집합) O 느림 중복 제거 O
UNION ALL (합집합) X 빠름 중복 제거 X
INTERSECT (교집합) O 느림 교집합
MINUS or EXCEPT (차집합) O 느림 차집합

 

👉 계층형 질의

  • 계층형 질의 문법
SELECT 컬럼명 FROM 테이블명 WHERE 조건 START WITH 조건 
			 CONNECT BY [NOCYCLE] 조건 [ORDER SLBLINGS BY 컬럼명];

- 순방향 계층형 질의 

ex) SELECT EMP_ID, EMP_NAME, MGR_ID, LEVEL, CONNECT BY_ISLEAF AS ISLEAF,           

     SYS_CONNECT_BY_PATH(EMP_NAME, '/') AS EMP_PATH, CONNECT_BY_ROOT(EMP_NAME) AS ROOT

     FROM EMP START WITH MGR_ID IS NULL 

     CONNECT BY PRIOR EMP_ID = MGR_ID;

 

- 역방향 계층형 질의

ex) SELECT EMP_ID, EMP_NAME, MGR_ID, LEVEL, CONNECT BY_ISEAF AS ISLEAF,

      SYS_CONNECT_BY_PATH(EMP_NAME, '/') AS EMP_PATH, CONNECT_BY_ROOT(EMP_NAME) AS ROOT

      FROM EMP START WITH EMP_ID = '105' 

      CONNECT BY EMP_ID = PRIOR MGR_ID;

 

- 셀프 조인

 : 한 테이블 내에서 두 컬럼 이상 내부적인 연관관계가 있을 경우 이용

ex) SELECT A.EMP_ID, A.EMP_NAME, A.MGR_ID, B.EMP_NAME AS MGR_NAME

      FROM EMP A, EMP B WHERE A.MGR_ID = B.EMP_ID;

 

👉 서브쿼리

- SELECT : 스칼라서브쿼리

- FROM : 인라인뷰(메인쿼리의 컬럼 사용 가능)

- WHERE : 중첩 서브 쿼리

- GROUP BY : 사용불가

- HAVING : 중첩 서브 쿼리

- ORDER BY : 스칼라서브쿼리

- IN : 서브쿼리출력값들 or 조건

- ANY/ SOME : 서브쿼리출력값들 중 가장 작거나 큰 값과 비교

- EXISTS : 서브쿼리 내 SELECT절엔 뭐가 와도 상관없음. ROW가 있으면 TRUE, 없으면 FALSE

 

- 단일행 서브 쿼리 

ex)  SELECT EMP_ID, EMP_NAME, DEPT_ID FROM EMP

       WHERE DEPT_ID = (SELECT DEPT_ID FROM EMP WHERE EMP_NAME = '홍길동');

 

- 다중행 서브 쿼리

ex) SELECT EMP_ID, EMP_NAME, DEPT_ID FROM EMP

      WHERE EMP_ID IN (SELECT MGR_ID FROM DEPT);

 

- ALL 연산자 

ex) SELECT EMP_ID, EMP_NAME, SALARY, JOB_TITLE FROM EMP

      WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE JOB_TITLE = '과장');

 

- ANY 연산자

ex) SELECT EMP_ID, EMP_NAME, SALARY, JOB_TITLE FROM EMP

      WHERE SALARY > ANY (SELECT SALARY FROM EMP WHERE JOB_TITLE =' 과장');

 

- EXISTS 연산자

ex) SELECT A.EMP_ID, A.EMP_NAME, A.SALARY FROM A

      WHERE EXISTS (SELECT 1 FROM B WHERE A.SALARY = B.SALARY AND B.EMP_NAME = "홍길동");

 

- 다중 컬럼 서브 쿼리

ex) SELECT EMP_ID, EMP_NAME, DEPT_ID, MGR_ID FROM EMP

      WHERE (DEPT_ID, MGR_ID) IN (SELECT DEPT_ID, MGR_ID FROM EMP WHERE EMP_NAME = "홍길동");

 

- 연관 서브 쿼리

ex) SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY FROM EMP A

      WHERE SALARY > (SELECT AVG(SALARY) FROM EMP B WHERE A.DEPT_ID = B.DEPT_ID);

 

- 스칼라 서브 쿼리

ex) SELECT A.EMP_NAME, A.SALARY, (SELECT AVG(SALARY) FROM EMP WHERE DEPT_ID = A.DEPT_ID)

      AS AVG_SALARY FROM EMP A;

 

- 인라인 뷰

 : 동적뷰라고 한다

ex) SELECT B.DEPT_NAME, A.AVG_SALARY FROM (SELECT DEPT_ID, AVG(SALARY) AS AVG_SALARY 

      FROM EMP GROUP BY DEPT_ID) A, DEPT B WHERE A.DEPT_ID = B.DEPT_ID;

 

- 뷰 생성

ex) CREATE VIEW VW_EMP AS SELECT B.DEPT_NAME, A.EMP_NAME, A SALARY FROM EMP A, DEPT B

      WHERE A.DEPT_ID = B.DEPT_ID;

 

- 뷰 조회

ex) SELECT DEPT_NAME, EMP_NAME, SALARY FROM VW_EMP;

 

 

👉 그룹 함수

- 전체 행을 하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

 

1) ROLLUP 함수

- 지정된 컬럼의 소계 및 총계를 구하기 위해 사용하는 그룹 함수

- 지정 컬럼 수보다 하나 더 큰 레벨만큼의 중간 집계 값이 생성

SELECT 컬럼명, 집계함수 FROM 테이블명 [WHERE...] 
GROUP BY [컬럼명] ROLLUP (그룹화할 컬럼) [HAVING...] [ORDER BY...];

ex) SELECT GENDER, AGE, SUM(PAY_AMOUNT) AS PAY_AMOUNT FROM PAY 

     GROUP BY ROLLUP (GENDER, AGE);

 

2) CUBE 함수

- 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수

- 내부적으로 대상 컬럼의 순서를 변경하여 또 한번의 쿼리를 수행

- 다른 그룹 함수보다 성능상 불리함

- 기준 컬럼이 1개일 때 ROLLUP과 같음

SELECT 컬럼명, 집계함수 FROM 테이블명 [WHERE...]
GROUP BY [컬럼명] CUBE (그룹화할 컬럼) [HAVING...] [ORDER BY...];

ex) SELECT GENDER, AGE, SUM(PAY_AMOUNT) AS PAY_AMOUNT FROM PAY

      GROUP BY CUBE (GENDER, AGE);

 

 

3) GROUPING SETS 함수

- 집계 대상 컬럼에 대한 소계를 구할 수 있는 그룹 함수

- ROLLUP과 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있음

SELECT 컬럼명, 집계함수 FROM 테이블 [WHERE...]
GROUP BY [컬럼] GROUPING SETS (그룹화할 컬럼) [HAVING...] [ORDER BY...];

ex) SELECT GENDER, AGE, SUM(PAY_AMOUNT) AS PAY_AMOUNT FROM PAY

      GROUP BY GROUPING SETS (GENDER, (GENDER, AGE)):

 

👉 계층형 SQL

- 한 테이블 안에 계층적인 데이터 구조를 가진 테이블에서 쉽게 데이터를 출력하기 위한 SQL 문법

# 순방향
SELECT 컬럼명 FROM 테이블 WHERE 조건절
START WITH 시작조건 CONNECT BY [NOCYCLE] PRIOR (부모->자식)
ORDER [SIBLINGS] BY 정렬조건

👉 윈도우 함수

- 데이터베이스에서 컬럼과 컬럼 간의 연산이 아닌 행과 행간의 비교 또는 연산 처리하는 함수

- 메인 쿼리 뿐아니라 서브 쿼리에서도 사용 가능하나, 함수 자체에서 중첩으로 사용할 수 없다

 

1) WINDOWING 

ex) SELECT DEPT_NAME, EMP_NAME. SALARY, RANK() OVER(PARTITION BY DEPT_NAME ORDER BY SALARY          DESC) AS "RANK", DENSE_RANK() OVER(PARTITION BY DEPT_NAME ORDER BY SALARY DESC) AS     

      "DENSE_RANK", ROW_NUMBER() OVER(PARTITION BY SALARY DESC) AS "ROW_NUMBER" FROM EMP;

 

2) 순위 함수

- 전체 또는 그룹 내에서 순위를 계산하는 함수로 ORDER BY를 필수로 사용

- RANK : 동일 값에 대해 동일 순위를 부여하고 중간 순위를 비워둔다. ex) 1 1 3 4 5

- DENSE_RANK: 동일 순위를 부여하되 중간 순위를 비우지 않는다. ex) 1 1 2 3 4

- ROW_NUMBER : 동일 값에 대해서도 유일한 순위를 부여한다.  ex) 1 2 3 4 5 

- RANK, DENSE_RANK, ROW_NUMBER

- 순위 추출을 위해서는 OVER절에 PARTITION BY 적용

 

3) 집계 함수

- 파티션 별 윈도우 합, 최댓값, 최솟값, 평균값, 건수를 계산하는 함수

- SUM, MAX, MIN, AVG, COUNT

 

4) 순서 함수

- 파티션별 윈도우에서 첫 번째 값, 마지막 값, 이전 행의 값, 이후 행의 값을 반환하는 함수

- FRIST_VALUE, LAST_VALUE, LAG, LEAD

 

5) 비율 함수

- 파티션 내 백분율을 계산하거나 비율에 따라 n 등분을 하는 함수

- RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE

 

👉  ROWNUM, ROWID, WITH문

  • ROWNUM

- Oracle에서 출력 rows를 설정하는 기능

- Mysql에서 top() 기능으로 대체하여 사용 가능

SELECT *
FROM SALARY
WHERE ROWNUM < 3

SELECT TOP(2) *
FROM SALARY

 

  • ROWID

- Oracle에서 row가 생성될 때 부여되는 row의 고유 id

- ROWID로 조건을 입력할 수 있음

- 데이터 객체번호, 상대적파일번호, 블록번호, 행번호의 조합

ex) SELECT ROWID, * FROM SALARY

 

  • WITH

- 임시적인 VIEW 테이블로 특정 SQL 블록을 재사용하기 위해 사용

- SQL 실행 속도에 유리하게 작용하며 여러 번 사용될수록 유리

- 하나의 SQL문에서 여러 개의 WITH 사용 가능

WITH SQL1 AS (SELECT * FROM SALARY WHERE ROWNUM < 3),
SQL2 AS (SELECT * FROM SALARY WHERE ROWNUM < 2)
SELECT * FROM SQL1 UNION ALL
SELECT * FROM SQL2

-> SQL1에서 2줄, SQL2에서 1줄을 UNION ALL하여 총 3줄이 출력

 

  • ROLE

- 권한과 유저 사이에 존재하는 개념으로 다양한 권한을 역할에 따라 유저에게 부여할 때 사용

- 특정 ROLE은 여러 권한을 받아 놓고 특정 유저에게 여러 권한을 가진 ROLE을 부여함으로 권한 관리 가능

ex) CREATE ROLE MY_ROLE GRANT CREATE SESSION, CREATE TABLE TO MY_ROLE

     GRANT MY_ROLE TO MY_USER;

 

👉 Row Chaining vs Row migration

구분 Row Chaining Row Migration
정의 하나의 Row를 하나의 블록에 저장할 수 없어서
여러 블록에 걸쳐서 저장하는 현상		 UPDATE로 인하여 늘어나는 공간을 저장할 공간이
없어서 다른 블록으로 Row를 옮기는 현상
특성 Initial Row Piece(행 조작)와 Row Pointer로
블록 내에 저장됨		 기존 블록에는 Migration되는 데이터의 row reader와
블록 주소값을 갖게 되고, 새로운 블록에는
Migration되는 데이터가 저장됨
문제점 Row의 정보를 검색하기 위해 하나 이상의 데이터 블록을 Scan 해야 하기 때문에 성능이 감소 Migration된 Row를 읽기 전에 기존 블록에서 헤더를 통해 Migration된 Row를 읽기 때문에 성능이 감소
해결책 블록의 크기를 크게 만든다 - PCTFREE를 크게 설정
- 객체를 Export하고 삭제한 후 import
- 객체를 Migration 하고 Truncate

 

 

👉  절차형 SQL

  • PL / SQL의 특징

- Block 구조로 각 기능별로 모듈화 가능

- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환

- IF, LOOP등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능

- Oracle에 내장되어 있어 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

- PL/SQL은 응용 프로그램의 성능을 향상 시킴

- PL/SQL은 여러 SQL 문장을 Block으로 묶고 한번에 서버로 보낼 수 있어 통신량 줄일 수 있음

 

1) Procedure 

- 필요한 로직을 처리하기만 하고 결과값을 반환하지 않는 프로그램

- 테이블에서 데이터를 조회하고 변경하는 일련의 작업을 처리할 때 프로시저를 사용

ex) CREATE PROCEDURE DELETE_EMP(V_EMP_ID NUMBER) IS BEGIN 

      DELETE FROM EMP WHERE EMP_ID = V_EMP_ID; END;

 

2) Function

- 매개변수를 통해 입력받은 값을 처리하고 결과값을 반환하는 프로그램

- DBMS에서 제공하는 내장함수 외에도 사용자가 직접 작성 가능

- 저장형 함수는 단독적으로 실행되기 보다 다른 SQL문을 통해서 호출되고 그 결과를 반환하는 SQL의 보조적 역할

ex) CREATE FUNCTION GET_SALARY (V_EMP_ID NUMBER) RETURN NUMBER IS V_SALARY NUMBER(12);

      BEGIN SELECT SALARY INTO V_SALARY FROM EMP WHERE EMP_ID = V_EMP_ID;

      RETURN V_SALSARY; END;

 

3) Trigger

- 데이터의 무결성과 일관성을 위해 사용자 정의 함수인 트리거를 사용

- 데이터 입력, 수정, 삭제 등의 INSERT, UPDATE, DELETE문이 수행될 때 자동으로 실행되는 프로그램

- 주로 데이터 무결성 유지 및 로그 메시지 출력을 위해 사용

- 트리거 내 COMMIT, ROLLBACK 등 트랜잭션 제어어 사용 시 컴파일 에러 발생

- 트리거에서 에러 발생 시 트리거 이후 작업은 반영되지 않음

- 트리거는 데이터베이스 로그인 작업에서도 정의 가능

ex) CREATE TRIGGER T_EMP BERORE INSERT ON EMP FOR EACH ROW 

      BEGIN IF (:NEW.DEPT_ID IS NULL) THEN RAISE_APPLICATION_ERROR (-20502, '부서코드 입력 필수;): END IF;        END;

 

PROCEDURE TRIGGER
EXECUTE 명령어로 실행 자동 실행 (이벤트 발생 시)
CREATE PROCEDURE CREATE TRIGGER
COMMIT, ROLLBACK 가능 COMMIT, ROLLBACK 불가능

👉 실행 계획

- OPERATION

SELECT STATEMENT
	SORT GROUP BY
    	HASH JOIN OUTER
        	TABLE ACCESS BY INDEX ROWID
            	INDEX RANGE SCAN
            TABLE ACCESS BY INDEX ROWID
            	INDEX SKOP SCAN