[SQLD] 2024년 신유형 노랭이 문제 풀이 해설 모음 - 과목 2. SQL 기본 및 활용

제 1장 SQL 기본

---

데이터 정의어(DDL, Data Definition Language) : CREATE, DROP, ALTER, RENAME

데이터 조작어(DML, Data Modification Language) : SELECT, INSERT, UPDATE, DELETE

데이터 제어어(DCL, Data Control Language) : GRANT, REVOKE

트랜잭션 제어어(TCL, Transaction Contorl Language) : COMMIT, ROLLBACK

 

WHERE절에는 집계 함수를 사용할 수 없다.

 

NULL은 SUM 연산 대상에서 제외된다.

 

오라클에서 ''로 입력하면 NULL로 입력된다.

 

GROUP BY절, HAVING 절의 특성

집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.

GROUP BY 절에서는 SELECT 절과는 달리 ALIAS명을 사용할 수 없다.

집계 함수는 WHERE절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)

 

 

 

REPLACE('문자열','특정 문자열'[,'변환할 문자열 defualt='']) : 문자열 내의 모든 특정 문자열을 다른 문자열로 변환시켜준다.

REPLACE(C1, CHT(10)) -> 줄바꿈(CHT(10)) 제거

 

CASE WHEN = 'NEW YORK' THEN 'EAST'
ELSE 'ETC'
END

CASE LOC WHEN 'NEW YORK' THEN 'EAST'
ELSE 'ETC'
END

DECODE (LOC, 'NEW WORK', 'EAST', 'ETC')

 

NULL 관련 함수

오라클 / SQL

• NVL(표현식 1, 표현식 2) 함수 / ISNULL(표현식 1, 표현식 2) : 표현식 1의 결괏값이 NULL이면 표현식 2의 값을 출력한다. 
• NULLIF(표현식 1, 표현식 2) : 표현식 1이 표현식 2와 같으면 NULL을, 같지 않으면 표현식 1을 리턴한다.
• COALESCE(표현식 1, 표현식 2, ... )  : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.

문자형 함수

SUBSTR/SUBSTRING(문자열, m[, n]) : 문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생각되면 마지막 문자까지이다.

 

GROUP BY절은 NULL 데이터도 집계에 포함한다.

 

SELECT 문장 실행 순서

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

여러 테이블로부터 원하는 데이터를 조회하기 위해서는 전체 테이블 개수에서 최소 N-1개만큼의 JOIN 조건이 필요하다.

DBMS 옵티마이저는 FROM절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 join을 수행한다.

 

순수 관계 연산자

SELECT, JOIN, DIVIDE, PROJECT

 

 

제 2장 SQL 활용

---

 

그룹 함수

GROUPING SET

CUBE

ROLLUP

 

SET OPERATOR

• UNION : 합집합 (중복 X)
• UNION ALL : 합집합 (중복 O)
• INTERSECT : 교집합
• MINUS/EXCEPT : 차집합

집합 연산자를 사용하나 SQL의 OERDER BY 절은 최종 결과를 적용, 마지막 줄에 1번만 사용 가능 

 

계층형 질의

START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문. 루트 데이터 지정 (액세스). 시작 데이터는 결과 내역에 포함됨

CONNECT BY PRIOR 

- PRIOR 키워드는 SELECT, WHERE, CONNECT BY 모두 사용 가능

ORDER SIBILINGS BY : 형제 노드 (동일 LEVEL) 사이에서 정렬 수행 

루트 노드의 LEVEL 값 = 1 

 

서브쿼리

• 단일 행(Single Row) 또는 복수 행(Multi Row) 비교 연산자와 함께 사용할 수 있음
  • 단일 행 : =, <, <=, >, >=, <> (다중 행 서브쿼리의 비교 연산자로 사용 불가)
  • 복수 행 : IN, ALL, ANY (단일 행 서브쿼리의 비교 연산자로 사용 가능)
• SELECT, FROM, HAVING, ORDER BY 절 등에서 사용이 가능
• 연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리
• 비연관 서브쿼리 : 메인 쿼리에 값을 제공하기 위한 목적으로 사용됨
• 다중 칼럼 서브쿼리 : 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미(only 오라클)

 

윈도우 함수 

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

DENSE_RANK : 순위를 구하는 함수, 동일한 순위를 하나의 등수로 취급, 그 다음에 나오는 행에는 순위를 건너뛰지 않고 이어서 순위를 부여 (ex. 1, 2, 2, 3, 3, 3, ...)

RANK : 파티션 내의 ORDER BY에 의한 순위를 구하는 함수, 동일한 순위에 대해서는 동일한 순위 부여, 그 다음 나오는 행에는 같은 순위가 나왔던 숫자만큼 건너 뛰고 순위를 부여 (ex. 1, 1, 3, 4, 4, 4, 7, 8 ...)

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

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

LAG : 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있다. 

LEAD : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다. 

뷰

인라인 뷰 = 동적 뷰 

 

뷰 사용의 장점

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

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

 

제 3장 관리 구문

ALTER TABLE PROD ADD PRIMARY KEY PRODUCT_PK ON (PROD_ID)

-> ALTER TABLE PROD ADD PRIMARY KEY CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID)

 

트랜잭션

• 원자성
• 고립성 : 어떤 트랜잭션이 수행 중일 때 다른 트랜잭션의 영향을 받아 잘못된 결과를 도출하는 경우가 발생하는 것을 방지하는 것 
• 일관성
• 영속성

테이블명 : 객체를 의미할 수 있는 적절한 단수형 권고

벤더에서 사전에 정의한 예약어 사용 못함

 

불필요한 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

Delete(/Modify) Action 

• Cascade : Master 삭제 시 Child 같이 삭제
• Set Null : Master 삭제 시 Child 해당 필드 Null
• SetDefault : Master 삭제 시 Child 해당 필드 Default 값으로 설정
• Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

DATE 타입에 숫자값은 입력하지 못하고 에러를 발생시킨다.

 

오라클 : DDL 수행 후 auto commit (O)

SQL : DDL 수행 후 auto commit (X)

 

TOP(N) WITH TIES COL1, COL2 : N에 해당하는 값이 동일한 경우 함께 출력되는 옵션. ORDER BY와 함께 사용