[DB]SQL(3)

📚데이터 조작 언어

📄고급 SELECT 문

• 집계 함수
  테이블에 존재하는 전체 또는 일부 레코드를 대상으로 통계적인 정보를 생성하고자 할 때 사용하는 함수를 집계 함수라고 합니다.

함수명	기능
AVG	컬럼에 있는 값들의 평균을 구함
MIN	컬럼에 있는 값 중 가장 작은 값을 구함
MAX	컬럼에 있는 값 중 가장 큰 값을 구함
SUM	컬럼에 있는 값들의 합을 구함
COUNT	컬럼에 있는 값들의 개수를 구함

📍단과대학의 개수를 출력하시오.

SELECT COUNT(DISTINCT 단과대학) AS 단과대학수
  FROM 학과

• 그룹 질의
  집계 함수는 테이블에 존재하는 전체 레코드를 대상으로 수행되어 단 하나의 값으로 결과를 생성합니다. 그러나 테이블에 존재하는 레코드를 다수의 그룹으로 분류하고 각 그룹별로 집계 함수를 적용하기 위해서는 그룹 질의를 적용해야 합니다. 그룹 질의를 위해 GROUP BY 절을 사용하고 그룹 내에 조건 지정을 원하는 경우 HAVING 절을 사용합니다.

/* SELECT 절에 그룹의 기준과 집계함수 
이외의 컬럼은 포함될 수 없음 */
SELECT 질의
  GROUP BY 컬럼

📍소속학과별 교수의 수를 출력하시오.

/* SELECT 절에 교수이름 컬럼을 추가하려고 하면
그룹의 기준과 집계함수에 해당하는 컬럼이 아니기 때문에 
포함될 수 없음 */
SELECT 소속학과, COUNT(*) AS 교수수, 교수이름(X)
  FROM 교수
  GROUP BY 소속학과

• HAVING 절
  GROUP BY 연산과 집계 함수는 레코드를 그룹화하고 그룹에 대한 집계 정보를 출력합니다. 이때 주어진 조건을 만족시키는 정보만 출력하고자 한다면 HAVING 절을 이용합니다.

SELECT 질의
  GROUP BY 컬럼
  HAVING 조건

• WHERE: 레코드에 대한 조건
• HAVING 절: 집계 결과 레코드에 대한 조건

📍2개 이상의 전공을 신청한 학생의 학생번호와 신청 전공 수를 출력하시오.

SELECT 학생번호, COUNT(*) AS 신청_전공수
  FROM 전공
  GROUP BY 학생번호
  HAVING 신청_전공수 >= 2

• 중첩 질의
  중첩 질의는 SELECT 문 내부에 다른 SELECT 문이 들어있는 형태의 질의를 말합니다. 일반적으로 내부 질의의 처리결과를 외부 질의에서 재사용합니다. 중첩 질의는 두 가지 유형으로 구분할 수 있습니다.

-FROM 절에서의 중첩 질의 활용: FROM 절에서의 결과 집합을 SELECT에서 재검색

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM(SELECT 컬럼1, 컬럼2, ..., 컬럼m
          FROM 테이블
          WHERE 조건)
  WHERE 조건

-WHERE 절에서의 중첩 질의 활용: WHERE 절에서의 결과 집합을 활용하여 외부 질의에서 레코드 출력 여부 결정. IN, NOT IN, EXISTS, NOT EXISTS 사용.

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블
  WHERE 컬럼i연산자(SELECT 컬럼j
                        FROM 테이블2
                        WHERE 조건)

• 교집합과 차집합 연산
  여러 레코드가 포함된 리스트 중 일치 여부를 확인하기 위해 IN 연산자를 사용합니다. 차집합 연산에 해당하는 EXCEPT 연산은 한 테이블에 포함되지만 다른 테이블에는 포함되지 않는 레코드 집합을 추출할 때 사용합니다.

📍’컴퓨터과학과’를 전공하는 학생 중 ‘수학과’도 전공하는 학생의 학생번호, 이수학점을 출력하시오.

SELECT 학생번호, 이수학점
  FROM 전공
  WHERE 학과이름 = '수학과' AND
        학생번호 IN (SELECT 학생번호 FROM 전공
                      WHERE 학과이름 = '컴퓨터과학과')

• EXISTS와 NOT EXISTS
  EXISTS나 NOT EXISTS사 사용되면 외부 질의의 각 레코드에 대해 내부 질의의 조건을 만족하는지 검사하게 됩니다. EXISTS는 외부 질의의 각 레코드에 대해 내부 질의의 결과에 레코드가 존재하면 참을 반환하고, 존재하지 않으면 거짓을 반환합니다. NOT EXISTS는 이와 반대로 동작합니다.

📍’컴퓨터과학과’소속 학생 중 수강신청을 하지 않은 학생의 학생번호를 검색하시오.

SELECT A.학생번호
  FROM 전공 AS A
  WHERE 학과이름 = '컴퓨터과학과' AND
        NOT EXISTS (SELECT 학생번호 
                    FROM 수강 AS B
                    WHERE A.학생번호 = B.학생번호)

📄조인 연산을 이용한 SELECT 문

조인 질의는 두 개 이상의 테이블에서 테이블 간의 관련성을 이요하여 임시적으로 레코드를 통합한 후 하나의 테이블로 결과를 만들어 내는 질의기법을 말합니다.

• 크로스 조인
  크로스 조인은 레코드를 결합하는 조건 없이 모든 조합의 레코드를 결합하는 연산입니다. 관계 대수의 카티션 프로덕트와 동일합니다.

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블1, CROSS JOIN 테이블2
    [... CROSS JOIN 테이블m]
  WHERE 조건

📍30세 이상인 학생과 모든 계좌에 대해 크로스 조인한 결과를 출력하시오.

SELECT 학생.*, 계좌.*
  FROM 학생 CROSS JOIN 계좌
  WHERE 학생.나이 >= 30

• 내부 조인
  두 개 이상의 테이블에서 조인 조건을 만족하는 레코드 만 결합하여 출력 결과에 포함시키는 연산입니다. 조인 조건은 WHERE 절이 아닌 ON 절에 기록합니다.

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블1, INNER JOIN 테이블2
  ON 조인조건1
  [WHERE 조건]

📍30세 이상인 학생의 학생이름과 나이, 그리고 그 학생이 소유한 계좌의 계좌번호, 잔액을 출력하시오.

SELECT 학생.학생이름, 학생.나이
       계좌.계좌번호, 계좌.잔액
  FROM 학생 INNER JOIN 계좌
  ON 학생.학생번호 = 계좌.학생번호
  WHERE 학생.나이 >= 30

💡Oracle 사의 구문 형식

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블1, 테이블2, [... 테이블이름m]
  WHERE 조건 AND 조인조건1 [... AND 조인조건p]

• 자연 조인
  두 개 이상의 테이블을 하나의 테이블로 결합하는 내부 조인과 매우 유사합니다. 두 테이블에 동일한 이름의 컬럼에 대한 값이 같은 레코드를 결합하는 내부 조인입니다.

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블1 NATURAL JOIN 테이블2
  [WHERE 조건]

• 외부 조인
  내부 조인은 조인조건에 일치하는 레코드만 결합하여 결과를 생성합니다. 따라서 조인 결과에 정보의 손실이 발생합니다. 하지만 외부 조인은 조인조건에 맞지 않는 레코드도 질의의 결과에 포함시키는 질의입니다.

  • 왼쪽 외부 조인
  • 오른쪽 외부 조인
  • 완전 외부 조인

SELECT 컬럼1, 컬럼2, ..., 컬럼n
  FROM 테이블1 LEFT|RIGHT[OUTER] JOIN 테이블2
  ON 테이블이름1.컬럼 = 테이블이름.컬럼
  [WHERE 조건]

📍학생의 학생번호, 학생이름과 그 학생이 수강신청한 과목의 과목코드, 신청시각을 출력하시오.(단, 수강신청을 하지 않은 학생도 결과에 포함시키고 과목코드를 기준으로 오름차순 정렬한다.)

SELECT A.학생번호, A.학생이름, B.과목코드, B.신청시각
  FROM 학생 AS A LEFT OUTER JOIN 계좌 AS B
  ON A.학생번호 = B.학생번호
  ORDER BY 과목 코드 ASC

• 셀프 조인
  셀프 조인은 한 테이블이 자기 자신과 조인되는 것을 말합니다. 동일한 이름의 테이블에 대한 조인이므로 반드시 테이블 이름에 대한 별칭이 의무적으로 사용됩니다.

SELECT 별칭1.컬럼1, 별칭1.컬럼2, ..., 별칭1.컬럼n,
       별칭2.컬럼1, 별칭2.컬럼2, ..., 별칭2.컬럼m
  FROM 테이블1 AS 별칭1 INNER|OUTER JOIN 테이블2 AS 별칭2
  ON 조인조건
  [WHERE 절]

📚뷰의 사용

뷰는 하나 이상의 원본 테이블로부터 유도되어 일반 테이블처럼 조작할 수 있는 가상 테이블입니다. 물리적으로 저장되지 않고 데이터 사전에 뷰에대한 정의만 저장됩니다.

• 데이터 독립성: 원본 테이블의 구조가 바뀌어도 뷰를 이용한 작업은 정의만 변경되어 응용프로그램에 영향이 없음

• 데이터 보안: 사용자에게 원본 테이블의 일부 컬럼에 대한 접근을 허용하여 보안 효과 향상

• 다양한 구조의 테이블 사용: 사용자의 요구사항에 맞는 테이블의 구조 제공

• 작업의 단순화: 복잡한 질의문을 뷰로 단순화

• 데이터의 무결성: WITH CHECK OPTION을 이용하여 뷰 생성에 위배되는 수정작업을 거부

📄뷰의 생성

SELECT 문으로 검색될 수 있는 어떤 테이블도 뷰로 정의할 수 있습니다. 즉, 생성되는 뷰의 구조는 SELECT 문의 결과로 결정됩니다.

CREATE VIEW 뷰이름 AS
  (SELECT 컬럼1, 컬럼2, ..., 컬럼n
      FROM 테이블
      [WHERE 조건])
[WITH CHECK OPTION]

📄뷰의 수정

ALTER VIEW를 이용해 생성된 뷰를 수정할 수 있습니다.

ALTER VIEW 기존뷰이름(컬럼1, 컬럼2, ... 컬럼n) AS
  ( SELECT 컬럼1, 컬럼2, ..., 컬럼n
    [FROM 테이블]
    [WHERE 조건] )

📄뷰의 삭제

DROP VIEW 문을 사용해 필요없는 뷰를 삭제할 수 있습니다. 삭제된 뷰를 근거로 생성된 다른 뷰 또한 사용할 수 없습니다. 뷰를 삭제해도 뷰의 근거가 되는 실제 물리적인 테이블은 아무런 영향을 받지 않습니다.

DROP VIEW 뷰이름

📄뷰와 관련된 데이터 조작

뷰는 가상의 테이블이기 때문에 뷰와 관련되 놎작은 테이블 조작과 부분적을 동일하게 수행할 수 있습니다. 하지만 뷰에 대한 INSERT 문은 테이블에서와 다릅니다. 뷰가 원본 테이블에서 NOT NULL로 정의된 컬럼에 대한 제약조건을 감추고 있을 수 있기 때문입니다.

• 뷰를 이용한 데이터 검색
  뷰를 이용한 데이터 검색은 테이블에서 데이터를 검색하는 방법과 동일합니다. 하지만 기본 테이블에는 존재하지만 뷰 정의에서 제외된 컬럼의 정보는 검색이 불가능합니다.

SELECT * FROM 뷰이름
  WHERE 조건

• 뷰를 이용한 데이터 삽입
  뷰를 이용한 데이터 삽입은 INSERT 문이 뷰가 아닌 원본 테이블에서 실행되어 삽입이 이루어집니다.

뷰를 이용한 데이터 삽입 유의 사항입니다.

1. PRIMARY KEY, NOT NULL 등의 제약사항이 위배되는 경우 삽입이 불가능

2. 원본 테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하는 경우 실행 불가능

3. 조인 질의 또는 그룹 질의가 적용된 뷰는 데이터 삽입 및 수정이 불가능

4. WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없지만 뷰에 맞지 않는 조건일 경우 실행 불가능