[SQLD] 2과목 2장 - SQL 활용(Q.92~127)

---

📕 2과목 - SQL 기본 및 활용

---

📌 2장 - SQL 활용

---

 

 

 

📍  SELF JOIN ?

• 동일 테이블 사이의 조인
• FROM 절에 동일 테이블이 두 번 이상 나타남.
• 동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭을 사용해야함.
• SELECT A.col1, B.col1 from self_join_test A, self_join_test B WHERE A.col1 = B.col1;

 

 

📍  서브쿼리 ?

• 알 수 없는 값을 조건값으로 이용하기 위해 사용.
• 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있음.
• SELECT, FROM, HAVING, ORDER BY 등에서 사용 가능
•  
• +) 2023.03.11 추가.
  • 단일행 : SELECT * FROM EMP WHERE A =    ( SELECT F FROM DEPT WHERE F = 5 );   
  • 다중행 : SELECT * FROM EMP WHERE A IN    ( SELECT F FROM DEPT WHERE F = 5 OR F = 6 );   
•  
• 서브쿼리의 결과가 복수행 결과를 반환하는 경우,
  • IN, ALL, ANY 등의 복수행 비교 연산자와 사용해야함.
• 서브쿼리의 결과가 단일행 결과를 반환하는 경우
  • =, <, >, <> 등등의 단일행 비교 연산자와 사용해야함.
• 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리임.
  • +) 2023.03.11 추가.
  • SELECT A
    FROM EMP 
    WHERE A IN ( SELECT F 
                            FROM DEPT 
                            WHERE  EMP.A = DEPT.F  );
• 다중 컬럼 서브쿼리
  • 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인 쿼리의 조건과 비교됨.
  • SQL Server 에서는 지원하지 않음.
    • +) 2023.03.11 추가.
    • SELECT A
      FROM EMP
      WHERE ( EMP.A, EMP.B ) IN  ( SELECT DEPT.D, DEPT.F
                                                         FROM DEPT
                                                         WHERE EMP.A = DEPT.F );

 

 

📍  NOT EXISTS, NOT IN, LEFT OUTER JOIN ?

• 현재 부양하는 가족이 없는 사원들의 이름을 구하라.
•  
• SELECT 이름 FROM 사원 WHERE
•   
• 1. NOT EXISTS SELECT 이름 FROM 사원 WHERE NOT EXISTS ( SELECT * FROM 가족 WHERE 사번 = 부양사번 )
• 2. NOT IN SELECT 이름 FROM 사원 WHERE 사번 NOT IN ( SELECT 부양사번 FROM 가족 )
• 3. LEFT OUTER JOIN SELECT 이름 FROM 사번 LEFT OUTER JOIN 가족 ON ( 사번 = 부양사번 ) WHERE 부양사번 IS NULL

 

 

 

 

 

📍  99번 ?

• 단일행 서브쿼리 비교연산자
  • <,>, =, <> 등등
• 단일행 서브쿼리의 비교연산자는 다중행 서브쿼리의 비교연산자로 사용 불가.
• 다중행 서브쿼리 비교 연산자는 단힐행 서브쿼리의 비교연산자로는 사용 가능.
  • SELECT * FROM 과자 WHERE 제조사 IN (오리온, 농심);
• 비연관 서브쿼리는 주로 메인 쿼리에 값을 제공하기 위한 목적으로 사용함.
• +) 2023.03.12 추가.
  • 연관 서브쿼리는 메인 쿼리에 값을 제공 받음.
• 메인 쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인 쿼리로 제공될 수도 있음.
  • 실행 순서는 상황에 따라 달라짐. 

 

 

 

 

+) 2023.03.12 추가.
📍  100번 ?

• SELECT 절에 있는 서브쿼리를 스칼라 서브쿼리 라고도 함.
  • 스칼라 서브쿼리는 JOIN으로 동일한 결과를 추출할 수 있음.
  • SELECT  *
    FROM ( SELECT *
                  FROM 사원
                  WHERE 입사년도 = '2014' ) A, 부양가족 B
    WHERE A.사원번호 = B.사원번호;  
• FROM 절에 있는 서브쿼리를 Inline View, 동적 뷰 (Dynamic View)  라고도 함.

 

 

 

 

 

📍  뷰? 

• 단지 정의만 가지고 있음.
• 실행 시점에 질의를 재작성해서 수행해야 함.
• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS 도 있음.
• 뷰 생성 스크립트
  • CREATE VIEW V_TBL 
  • AS
  • SELECT * FROM TBL WHERE C1 IS NULL;
• 생성한 뷰 스크립트로 SELECT 해보면
  • SELECT * FROM V_TBL WHERE C2 = A;
  •  
  • 일단 뷰에는 C1이 NULL 인 것만 보이고, 
  • 그 다음에 V_TBL 에서 SELECT 하게 되는 거니까 
  • 결과로는 V_TBL 로 나온 결과에서 C2 가 A 인 값들이 나옴.

 

📍  뷰의 장점 ? 

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

 

 

📍 GROUPING , ROLLUP ( 105~ 111 )

 

 

 

 

 

 

📍  윈도우 함수 ?

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

 

 

 

 

 

📍  RANK ?

• 1번 ( 1, A ) |  2번 ( 2, B ) |  3번 ( 3, B ) |  4번 ( 4, C )
• RANK WINDOW FUNTION == RANK() OVER
  • 동일 값에 대해 동일 순위 부여, 중간 순위를 비움.
  • 1, 2, 2, 4
• DENSE_RANK
  • 동일 순위, 중간 순위 안 비움
  • 동일한 순위는 하나의 건수로 취급
  • 1, 2, 2, 3
• ROW_NUMBER
  • 동일 값에 대해서도 유일한 순위 부여
  • 1, 2, 3, 4

 

 

 

 

 

📍  권한 GRANT, REVOKE ?

• UPDATE PUPPY.CLIENT_INFORMATION SET CLIENT_NO = '0001' WHERE CLIENT_NAME='C_NAME';
• WHERE 절의 CLIENT_NAME = 'C_NAME' 도 SELECT 로 확인해야하기 때문에,
• HAE   :::   GRANT SELECT, UPDATE ON PUPPY.CLIENT_INFORMATION TO 현아 WITH GRANT OPTION;
•  
•  
• GRANT OPTION
  • 내가 받은 권한을 다른 사람에게도 줄 수 있음.
  • 현아   :::  GRANT SELECT, UPDATE ON PUPPY.CLIENT_INFORMATION TO 아현;
• 여기까지 각자 할 수 있는 것.
  • HAE : SELECT , UPDATE
  • 현아 : SELECT, UPDATE
  • 아현 : SELECT, UPDATE
• REVOKE CASCADE
  • 권한 줬던 사람들 모두에게서 권한 회수.
  • HAE   :::   REVOKE UPDATE ON PUPPY.CLIENT_INFORMATION FROM 현아 CASCADE;

• 여기까지 각자 할 수 있는 것.
  • HAE : SELECT , UPDATE
  • 현아 : SELECT
  • 아현 : SELECT

 

 

 

 

 

📍  ROLE ?

• 많은 DBMS 사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹.
• 사용자와 권한 사이에서 중개 역할을 수행함.
• EX) 팀장에게는 "사원별 특이사항" 이 보일 수 있게 따로 권한을 주는 것.

 

 

 

 

📍  PL/SQL ?

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

 

 

 

 

 

 

 

 

 

📍  절차형 SQL 모듈 ?

• 저장 모듈
  • SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램.
  • 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램.
  • ORACLE 의 저장모듈 
    • Procedure
    • User Defined Function
    • Trigger
•  
•  
• 저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미함.
  • Commit, Rollback 과 같은 TCL 사용가능.
  • CREATE Procedure 문법 사용
  • EXECUTE 명령어로 실행.
• 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보단,
  • 다른 SQL 문을 통하여 호출되고 그 결과를 리턴하는 SQL 의 보조적인 역할을 함.
• 트리거는 특정 테이블에 INSERT, UPDATE, DELETE 와 같은 DML 문이 수행되었을 때,
  • 데이터베이스에 의해서 자동으로 동작하도록 작성된 프로그램임. 
    • 자동으로 호출되고 수행됨.
  • 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용하려고 트리거를 씀.
  • TCL ( 커밋, 롤백 ) 사용 불가.
  • CREATE Trigger 문법 사용
  • 생성 후 자동으로 실행
  • 데이터베이스에 로그인하는 작업에도 정의할 수 있음.
•  

 

 

📍   ?

• N