[노랑이] 오답 | 과목 2 | SQL 기본 및 활용 51-98

 

51. 다중 컬럼(Multi Column) 서브쿼리

: 서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

 

53. SET OPERATOR

- 합집합 : UNION

- 교집합 : INTERSECTION

- 차집합 : MINUS / EXCEPT

 

54. 집합 연산자

집합(SET) 연산자를 사용한 SQL의 ORDER BY 절은 최종 결과를 정렬하며, 가장 마지막 줄에 한번만 사용할 수 있다.

 

61. 오라클 계층형 질의에서 루트 노드의 LEVEL 값은 1이다.

+ START WITH 절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다. (엑세스)

+ ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

 

63. 계층형 질의문

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

+ SQL Server에서의 계층형 질의문은 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.

+ SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.

+ 오라클 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE 절에서도 사용할 수 있다.

+ PRIOR 자식 = 부모 : 부모 → 자식 : 순방향

+ PRIOR 부모 = 자식 : 자식 → 부모 : 역방향

 

65. Natural Join

Natural Join을 사용하면 공통된 속성을 찾아 같은 값을 갖는 항목끼리 결합시켜준다.

만약 같은 값을 갖는 항목이 있다면 해당 항목은 제외한다.

 

68. 서브쿼리

- 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multi Row) 비교 연산자와 함께 사용할 수 있다.

- 서브쿼리는 SELECT 절, FROM 절, HAVING 절, ORDER BY 절 등에서 사용이 가능하다.

- 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ALL, ANY 등의 복수 행 비교 연산자와 사용하여야 한다.

- 연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리이다.

- 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 비교되는데, SQL Server에서는 현재 지원하지 않는 기능이다.

 

69. 함수 정리

- PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수이다.

- DENSE_RANK : 순위를 구하는 함수로 동일한 순위를 하나의 등수로 취급한다.

- CUME_DIST : 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구하는 함수이다.

- RANK : 파티션 내의 ORDER BY에 의한 순위를 구하는 함수로, 동일한 순위에 대해서는 동일한 순위를 부여한다.

- RATIO_TO_REPORT : 파티션 내의 주어진 칼럼 값의 합계에 대한 행별 백분율을 소수점으로 구하는 함수이다.

- NTILE : 파티션별 전체 건수를 주어진 인자로 N 등분한 결과를 반환한다.

 

72. 서브쿼리

- 단일 행 서브쿼리의 비교 연산자로는 =, <, <=, >, >=, <>가 되어야 한다. IN, ALL 등의 비교 연산자는 다중 행 서브쿼리의 비교연산자이다.

- 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교 연산자로 사용할 수 없지만, 반대의 경우는 가능하다.

- 비 연관 서브쿼리가 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용된다.

- 메인쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인쿼리로 제공될 수도 있으므로 실행 순서는 상황에 따라 달라진다.

 

75. 뷰 View 의 장점

- 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

- 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다.

- 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 빼고 생성하여 사용자에게 정보를 감출 수 있다.

 

79. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관없다.

# 예시
SELECT EMPNO, SAL
FROM EMP
WHERE SAL >= (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

# -> '단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.' 오류 발생한다.

 

84. 윈도우 함수 Window function

- PARTITION BY 절과 GROUP BY 절은 의미적으로 유사하다.

- PARTITION BY 절이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.

- 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.

- 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

 

90. LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수이며, SQL Server에서는 지원하지 않는 함수이다.

 

91. REVOKE 문을 사용하여 권한을 취소하면 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 모두 연쇄적으로 취소된다.