SQL 자격검정 실전문제 # 문제집 요점정리_과목 2_SQL 활용

 

순수 관계 연산자

• SELECT 연산
  - WHERE 절로 구현
• PROJECT 연산
  - SELECT 절로 구현
• (NATURAL) JOIN 연산
  - 다양한 JOIN 기능으로 구현
• DIVIDE 연산
  - 현재 사용되지 않는다.

 

Cartesian Product

카티시안 곱

• 두 엔터티를 JOIN 할 때 적절한 JOIN조건이 없을 때 발생.
• CROSS JOIN을 사용하자.
• 82p 70

 

FROM 절 JOIN 형태

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.

• INNER JOIN
  - 기존 WHERE 절의 검색 조건과 JOIN 조건을 그대로 사용할 수 있으며 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있게 되었다.
• NATURAL JOIN
  - INNER JOIN의 하위 개념
  - 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에  EQUI JOIN을 수행한다.
  - JOIN칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
• USING 조건절
• ON 조건절
• CROSS JOIN
• OUTER JOIN

 

INNER JOIN

JOIN 조건에서 동일한 값이 있는 행만 반환

FROM 절 JOIN 조건

• FROM 절에서 정의
• USING, ON 조건절을 필수적으로 사용해야 함.

 

CROSS JOIN

테이블간 JOIN조건이 없는 경우 생길 수 있는 모든 데이터의 조합.

결과는 양쪽 집합의 M*N건의 데이터 조합이 발생한다.

 

 

OUTER JOIN

JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용.
주키와 왜래키는 영향을 미치지 않는다.
ON은 JOIN할 대상을 결정하여 해당하지 않는 데이터는 지워지지만 기준 테이블은 항상 모두 표시된다.
결과건에 대한 필터링은 WHERE절에서 수행된다.
Oracle에서는 OUTER JOIN을 (+)기호를 사용하여 나타내기도 한다.(87p 77)

 

• OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

 

LEFT OUTER JOIN

• 조인 수행시 좌측 테이블에서 데이터를 먼저 읽은 후, 우측 테이블에서 JOIN 대상을 읽어 온다.
  예) 테이블 A와 B가 있을 때 B의 JOIN칼럼에서 같은 값이 있을 때 해당 데이터를 가져오고 B의 JOIN칼럼에서 같은 값이 없는 경우엔 B에서 가져오는 칼럼들은 NULL값으로 채운다.
• 즉, 좌측 테이블이 기준이 됨.

 

FULL OUTER JOIN

• 좌, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성.
  예) 테이블 A와 B가 있을 때 RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일.
• RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

 

집합 연산자의 종류(SET OPERATOR)

• UNION : 합집합
• INTERSECT : 교집합
• MINUS/EXCEPT : 차집합

 

94p 87번

계층형 쿼리 문제풀이 영상

 

계층형 질의(Hierarchical Query)

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위한 방법.
• 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
• 예) 사원 테이블에서는 사원들 사이에 상위 사원(관리자)고 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다.

 

Oracle 계층형 질의

• START WITH절
  - 계층 구조의 시작점을 지정하는 구문이다.
  - 즉, 루트 데이터 지정
• ORDER SIBLINGS BY 절
  - 형제 노드 사이에서 정렬을 지정하는 구문이다.
  
• PRIOR
  - CONNECT BY절에 사용되며 현재 읽은 칼럼을 지정한다.
  - PRIOR 키워드는 CONNECT BY절 이외에 SELECT, WHERE절에서도 사용할 수 있다.
  - PRIOR 자식 = 부모
  ☞ 계층구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 순방향 전개를 한다.
  - PRIOR 부모 = 자식
  ☞ 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 역방향 전개를 한다.
  
• 루트 노드의 LEVEL값은 1이다.

 

SQL Server 계층형 질의

• CTE(Common Table Expression)을 재귀 호출함으로써 계층 구조를 전개.
• 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행.

 

Oracle 계층형 질의

WHERE절은 모든 전개를 진행한 후 필터 조건으로 조건을 만족하는 데이터만을 추출하는데 활용된다.

 

Self Join

• 동일 테이블 사이의 조인을 말한다.
• FROM절에 동일 테이블이 두 번 이상 나타난다.
• 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(ALIAS)를 사용해야 한다.
• 하나의 테이블에서 두 개의 칼럼이 연관 관계를 가지고 있는 경우에 사용한다.

 

 

서브쿼리

• 괄호로 감싸서 사용한다.
• 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.
  - 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 한다.
  - 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
• 연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고있는 형태의 서브쿼리다.
  즉, 비연관 서브쿼리가 메인 쿼리에 값을 제공하기 위한 목적.
• 서브쿼리에서는 ORDER BY를 사용하지 못한다.
  ORDER BY절은 SELECT절에서 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.
• 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인 쿼리의 조건과 비교되는데,
  SQL Server에서는 현재 지원하지 않는 기능이다.

 

서브쿼리가 SQL문에서 사용이 가능한 곳

SELECT 절, FROM 절, WHERE 절, HAVING 절, ORDER BY 절, INSERT문의 VALUES 절, UPDATE문의 SET 절

 

인라인 뷰(Inline View)

FROM절에서 사용되는 서브쿼리

• 서브쿼리 결과가 마치 실행 시에 동적으로 생성된 테이블인 것 처럼 사용할 수 있다.
• SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터 베이스에 해당 정보가 저장되지 않는다.
• 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것 처럼 사용할 수 있다.

 

뷰(View)

• 뷰는 단지 정의만을 가지고 있으며 실행 시점에 질의를 재작성하여 수행한다.
• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

 

뷰 사용의 장점

• 독립성
  - 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
• 편리성
  - 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
  - 또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.
• 보안성
  - 숨기고 싶은 정보가 존재한다면 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다.

 

ROLLUP

나열된 컬럼에 대해 계층 구조로 집계를 출력하는 함수

주어진 컬럼의 순서에 따라 다른 결과를 추출하게 되는 그룹함수.

 

• CUBE, ROLLUP, GROUPING SETS 모두 일반 그룹 함수로 동일한 결과를 추출해낼 수 있다. 
• CUBE, ROLLUP, GROUPING SETS 함수들에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP대상 컬럼의 값은 NULL을 반환한다.
• CUBE그룹함수는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해 집계를 수행하므로 다른 그룹 함수에 비해 시스템에 대한 부하가 크다.

 

윈도우 함수

• Partition과 Group By구문은 의미적으로 유사하다.
• Partition구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.
• 윈도우 함수는 결과에 대한 함수처리기 때문에 결과 건수는 줄어들지 않는다.
• 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

 

GRANT

ROLE을 DBMS USER에게 부여하는 명렁어

REVOKE

ROLE을 회수하는 명령어

 

 

PL/SQL

• 변수와 상수 등을 사용하여 일반 SQL문장을 실행할 때 WHERE절의 조건 등으로 대입할 수 있다.
• Procedure, User Defined Function, Trigger객체를 PL/SQL로 작성할 수 있다.
• Procedure내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL문장을 SQL실행기가 처리한다.
• PL/SQL로 작성된 Procedure, User Defined Function은 작성자의 기준으로 트랜젝션을 분할할 수 있으며 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트렌젝션과는 별도로 PRAGMA AUTONOMOUS_TRAINSACTION을 선언하여 자율 트랜젝션 처리를 할 수 있다.
• PL/SQL에서는 동적 SQL또는 DDL문장을 실행할 때 EXECUTE IMMEDIATE 를 사용해야 한다.
• Block구조로 되어있어 각 기능별로 모듈화가 가능하다.
• IF, LOOP등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• 응용 프로그램의 성능을 향상시킨다.
• 여러 SQL문장을 Block으로 묶고 한 번에 Block전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

 

절차형 SQL모듈

• 저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합을 의미.
• 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할을 한다.

 

Trigger

• 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다.
• 데이터의 무결성과 일관성을 위해 사용자 점의 함수를 사용한다.
• 데이터베이스에 의해 자동으로 호출되고 수행된다.
• Procedure와 달리 Commit 및 Rollback과 같은 TCL을 사용할 수 없다.
• 데이터베이스에 로그인하는 작업도 정의할 수 있다.