정보처리기사 실기 스터디[2022-10-07] - SQL 응용(데이터베이스 기본, 응용 SQL 작성, 활용)

📌 Study

🔸 2022-10-07

[SQL 활용]

1. 데이터베이스 기본

• 서브쿼리 : SQL 문 안에 또 다른 SQL 문
  • 알려지지 않은 기준을 위한 검색을 위해 사용

 

• 서브쿼리 유형
  • FROM 절 서브쿼리 : 서브쿼리가 FROM 절 안에 들어있는 형태, 인라인 뷰, 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용 가능
  • WHERE 절 서브쿼리 : 서브쿼리가 WHERE 절 안에 들어있는 형태, 중첩 서브쿼리

 

• SELECT 서브쿼리 예시

  SELECT
  (SELECT MAX(가격) AS 가격
    FROM 도서 A
    WHERE A.책번호 = B.책번호 AND A.책명 = '자료구조')
  FROM 도서가격 B;

 

• FROM 서브쿼리 예시

  SELECT MAX(가격) AS 가격
  FROM 도서가격 A (SELECT 책번호
                  FROM 도서
                  WHERE 책명 = '자료구조') B
  WHERE A.책번호 = B.책번호;

 

• WHERE 서브쿼리 예시

  SELECT MAX(가격) AS 가격
  FROM 도서가격
  WHERE 책번호 IN (SELECT 책번호
                  FROM 도서
                  WHERE 책명 = '자료구조');

 

• 집합 연산자 : 두 테이블 이상에서 여러 질의의 결과를 하나로 통합

 

• 집합 연산자 유형
  • UNION : 중복 행이 제거된 쿼리 집합
  • UNION ALL : 중복 행이 제거되지 않은 쿼리 집합
  • INTERSECT : 교집합
  • MINUS : 차집합

 

• INSERT 명령어

  INSERT INTO 학생(학번, 성명, 학년, 과목)
  VALUES(2022, '대훈', 4, '자료구조');

 

• UPDATE 명령어

  UPDATE 학생
  SET 주소 = '용인'
  WHERE 이름 = '대훈';

 

• DELETE 명령어

  DELETE 
  FROM 학생
  WHERE 이름 = '대훈';

 

• DCL : DBA가 데이터 보안, 무결성 유지 등을 위해 사용하는 언어

 

• DCL 유형
  • GRANT : 사용권한 부여
  • REVOKE : 사용권한 회수

 

  GRANT UPDATE 
  ON 학생
  TO 대훈;

  REVOKE UPDATE 
  ON 학생
  FROM 대훈;

 

2. 응용 SQL 작성하기

• 데이터 분석 함수 : 데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수

 

• 데이터 분석 함수 종류
  • 집계 함수 : 하나의 결과값을 반환하는 함수
  • 그룹 함수 : 중간 합계 데이터를 산출하는 함수
  • 윈도 함수 : 온라인 분석 처리 용도

 

• 집계 함수 구문

  SELECT 컬럼1, 컬럼2, ..., 집계함수
  FROM 테이블명
  [WHERE 조건]
  GROUP BY 컬럼1, 컬럼2, ...
  [HAVING 조건식(집계함수 포함)]

 

• 집계 함수 종류

COUNT	복수 행의 줄 수 반환
SUM	복수 행의 해당 컬럼 간 합계 계산
AVG	복수 행의 해당 컬럼 간 평균 계산
MAX	복수 행의 해당 컬럼 중 최대값 계산
MIN	복수 행의 해당 컬럼 중 최소값 계산
STDDEV	복수 행의 해당 컬럼 간 표준편차 계산
VARIAN	복수 행의 해당 컬럼 간 분산 계산

 

  SELECT STDDEV(국어), VARIAN(국어)
  FROM 학생;

  SELECT 부서, 직책, SUM(급여)
  FROM 급여
  GROUP BY 부서, 직책;

 

• 그룹 함수 : 그룹 별 속성 값 집계 함수

 

• 그룹 함수 유형
  • ROLLUP : 중간 집계값 산출
  • CUBE : 다차원 집계
  • GROUPING SETS : 개별 집계

 

• 예시
  • 부서별 연봉 테이블에서 부서명-직위에 해당되는 연봉정보, 연봉합계, 전체 연봉합계 구하는 쿼리

 

• ROLLUP

  SELECT 부서명, 직위, SUM(연봉)
  FROM 연봉
  GROUP BY ROLLUP(부서, 직위);

 

• CUBE

  SELECT 부서명, 직위, SUM(연봉)
  FROM 연봉
  GROUP BY CUBE(부서, 직위);

 

• GROUPING SETS

  SELECT 부서명, 직위, SUM(연봉)
  FROM 연봉
  GROUP BY GROUPING SETS(부서, 직위);

 

• 윈도 함수 => OLAP 함수, GROUP BY (x)

 

• 순위 함수
  • RANK => 2위, 2위, 2위, 5위
  • DENSE_RANK => 2위, 2위, 2위, 3위
  • ROW_NUMBER => 2위, 3위, 4위, 5위

 

  SELECT 이름, 연봉
    RANK() OVER (ORDER BY 연봉 DESC) A,
    DENSE_RANK() OVER (ORDER BY 연봉 DESC) B,
    ROW_NUMBER() OVER (ORDER BY 연봉 DESC) C
  FROM 직원;

 

• 절차형 SQL : 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

 

• 절차형 SQL 종류
  • 프로시저 : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리 집합
  • 사용자 정의 함수 : 일련의 SQL 처리, 수행결과를 단일 값으로 반환
  • 트리거 : DB에서 삭입, 삭제 등의 이벤트가 발생할 때마다 자동으로 수행되는 SQL

 

• DBMS_OUTPUT : 절차형 SQL이 정상적으로 구현되었는지 테스트 목적으로 많이 사용
  • DBMS_OUTPUT(문자열); = 개행 없이 문자열 출력하는 프로시저
  • DBMS_OUTPUT.PUT_LINE(문자열); = 문자열 출력 후 개행하는 프로시저

 

• 프로시저 : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리 집합

 

• 프로시저 구성
  • DECLARE : 정의(프로시저 명칭, 변수 등)
  • BEGIN/END : 논리적 프로세스 구성
  • CONTROL : 순차적 처리, 조건문과 반복문 이용하여 처리
  • SQL : DML 주로 사용
  • EXCEPTION : SQL 실행될 때 예외 발생 시 처리 방법 정의
  • TRANSACTION : DBMS 적용 및 취소 결정

 

• 사용자 정의 함수 구성
  • DECLARE : 정의(프로시저 명칭, 변수 등)
  • BEGIN/END : 논리적 프로세스 구성
  • CONTROL : 순차적 처리, 조건문과 반복문 이용하여 처리
  • SQL : DML 주로 사용
  • EXCEPTION : SQL 실행될 때 예외 발생 시 처리 방법 정의
  • RETURN : 호출문에 대한 함수값 반환

 

• 트리거 구성
  • DECLARE : 정의(프로시저 명칭, 변수 등)
  • EVENT : 트리거 실행되는 타이밍
  • BEGIN/END : 논리적 프로세스 구성
  • CONTROL : 순차적 처리, 조건문과 반복문 이용하여 처리
  • SQL : DML 주로 사용
  • EXCEPTION : SQL 실행될 때 예외 발생 시 처리 방법 정의

 

• 옵티마이저 : SQL을 빠르고 효율적으로 수행할 최적의 처리경로를 생성하는 DBMS 핵심 엔진

 

규칙기반 옵티마이저(RBO)	비용기반 옵티마이저(CBO)
사전 등록된 규칙에 따라 질의 실행 계획 선택	모든 접근 경로 고려
규칙(우선순위) 기반	비용(수행 시간) 기반
인덱스 구조, 연산자, 조건절 등	레코드 개수, 블록 개수, 인덱스 높이, 분포 등
사용자가 원하는 처리경로 유도	이해도 낮아도 성능보장 가능

 

• 옵티마이저 역할
  • 쿼리 변환
  • 비용 산정
  • 계획 생성

 

• 옵티마이저는 명시적인 힌트를 통해 실행계획 변경