Home [데이터베이스 시스템] 7강 - SQL
Post
Cancel

[데이터베이스 시스템] 7강 - SQL

Posted Apr 16, 2025 Updated Jul 1, 2025
By mxxikr
read 31 min

💡해당 게시글은 방송통신대학교 정재화 교수님의 '데이터베이스 시스템' 강의를 개인 공부 목적으로 메모하였습니다.



학습 개요

  • 데이터베이스에서 방대한 데이터를 효율적으로 활용하려면 단순한 데이터 조회를 넘어 복잡한 조건에 따른 검색, 여러 테이블의 데이터 결합, 재사용 가능한 결과 집합 생성 등의 고급 데이터 처리 능력이 필요함
  • 만약 이러한 기능이 없다면 사용자는 비효율적으로 데이터를 개별 조회하고 수작업으로 결합하는 등 많은 시간을 소모하게 될 것임
  • 따라서 중첩 질의, 조인 질의, 뷰와 같은 데이터 활용 기술을 익히는 것이 요구됨
  • 중첩 질의, 조인 질의, 뷰의 개념과 필요성을 이해하고, 이를 활용하여 복합적인 데이터 검색과 가상의 테이블을 생성하는 방법을 학습함



주요 용어

  • 그룹 질의
    • 특정 기준으로 레코드를 그룹화하고 각 레코드 그룹에 대해 집계 함수를 적용하는 질의
  • 중첩 질의
    • SELECT 문 내부에서 독립적으로 실행 가능한 또 다른 SELECT 문이 내포되어 있는 질의
  • 조인 질의
    • 테이블 간의 관련성을 이용하여 두 개 이상의 테이블에서 데이터를 검색하는 질의 기법
  • 뷰
    • 데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블



강의록

중첩 질의의 이해

중첩 질의의 개념

  • SELECT 문 내부에서 독립적으로 실행 가능한 또 다른 SELECT 문이 내포되어 있는 질의
    • 일반적으로 내부 질의의 처리 결과를 외부 질의에서 재사용하여 처리하는 과정
  • 중첩 질의의 종류
    • FROM 절에서의 중첩 질의 활용
      • FROM 절에서의 결과 집합을 SELECT 문에서 재검색
    • WHERE 절에서의 중첩 질의 활용
      • WHERE 절에서의 결과 집합을 활용하여 외부 질의에서 레코드의 출력 여부를 결정
      • IN, NOT IN, EXISTS, NOT EXISTS 사용

중첩 질의의 형식

1
2
3
4
5
6
7

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

1
2
3
4
5
6
7

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

중첩 질의의 사용 1

  • 학과별 교수의 평균 연봉이 70,000,000 미만인 학과 중 가장 높은 평균 연봉을 출력하시오.

    교수번호교수이름직위소속학과연봉
    186432-760829최우성조교수생활과학과52000000
    201547-634895현경석정교수생활과학과66000000
    189414-790829한용윤조교수법학과45000000
    191924-730620이동휘부교수행정학과51000000
    194634-810228김규식정교수컴퓨터과학과70000000
    194834-760517정재화부교수컴퓨터과학과53000000
    201216-158465정용제조교수국어국문학과55000000
    210315-549413황지수부교수유아교육과52000000

  1. 학과별 교수의 평균 연봉 먼저 구함

    1
2
3

     SELECT 소속학과, AVG(연봉) AS 평균연봉
     FROM 교수
         GROUP BY 소속학과
    소속학과평균연봉
    국어국문학과55000000.00
    법학과45000000.00
    생활과학과59000000.00
    유아교육과52000000.00
    컴퓨터과학과61500000.00

  2. 7천만원 미만 구하고 결과 중 가장 높은 연봉 출력

    1
2
3
4
5
6
7

     SELECT MAX(d.평균연봉) AS 평균연봉
     FROM (
         SELECT 소속학과, AVG(연봉) AS 평균연봉
             FROM 교수
             GROUP BY 소속학과
     ) AS d
     WHERE d.평균연봉 < 70000000
    평균연봉
    61500000.00
    • 내부 질의 결과가 하나의 테이블로 도출

중첩 질의의 사용 2

  • 직위가 ‘부교수’이고 이수 구분이 ‘교양’인 과목을 강의하는 교수의 이름과 소속학과를 출력하시오.

    과목코드과목명학점선수과목이수구분교수번호
    COM11컴퓨터의 이해3 교양…
    COM12파이썬 프로그래밍 기초3 교양…
    COM24자료구조3COM12전공필수…
    COM31데이터베이스 시스템3COM24전공필수…
    COM34알고리즘3COM24일반선택…
    COM44클라우드 컴퓨팅3 전공필수…
    ECE24놀이지도3 전공필수…
    ECE31유아언어교육3ECE31전공필수…
    1
2

      SELECT 교수번호 FROM 과목
      WHERE 이수구분 = '교양'
    교수번호
    …
    …
    …
    1
2
3
4
5
6
7

      SELECT A.교수이름, A.소속학과
      FROM 교수 AS A
      WHERE A.직위 = '부교수' AND A.교수번호 IN (
          SELECT B.교수번호 
              FROM 과목 B
              WHERE B.이수구분 = '교양'
          )
    교수이름소속학과
    정재화컴퓨터과학과

  • ‘생활과학과’ 소속 학생 중 수강 신청을 하지 않은 학생의 학생 번호를 출력하시오

    과목코드학생번호신청시각
    KO03201831-3312152019-02-11 13:31:45
    COM34201831-3312152020-02-20 13:54:22
    COM24201831-3312152019-08-22 12:23:31
    COM12201831-3312152019-08-21 23:25:25
    LAW21201834-0212162016-02-11 08:21:22
    KO03201834-0212162016-11-12 02:16:51
    HE25201834-0212162017-08-01 01:24:54

  1. 생활과학과 한 학생의 학생 번호를 가져옴

    1
2
3

     SELECT 학생번호
         FROM 전공
         WHERE 학과이름 = '생활과학과'
    학생번호
    201926-880215
    202031-354516

  2. 해당 학생 번호가 ‘수강’ 테이블에 있는지 조회

    1
2
3
4
5
6
7

     SELECT A.학생번호
     FROM 전공 AS A
     WHERE A.학과이름 = '생활과학과' AND NOT EXISTS (
         SELECT B.학생번호
             FROM 수강 B
             WHERE A.학생번호 = B.학생번호
         )
    • NOT EXISTS
      • 검색 결과가 없으면 그 학생의 정보를 출력
      • 내부 질의에 검색 결과가 없으면 True

조인 질의의 이해

조인 질의

  • 테이블 간의 관련성을 이용하여 두 개 이상의 테이블에서 데이터를 검색하는 질의 기법
    • 두 개 이상의 테이블을 결합하여 하나의 결과 집합을 생성하는 방법
  • ER 모델링 및 정규화 기법으로 여러 테이블로 분리된 정보를 일시적으로 하나의 레코드로 통합
  • 조인 질의의 종류
    • 내부 조인(Inner Join)
      • 교집합을 의미하며, 두 테이블에 공통적으로 존재하는 레코드를 결합
    • 외부 조인(Outer Join)
      • 합집합의 개념으로, 한 테이블에는 존재하지만 다른 테이블에는 존재하지 않는 레코드도 결과에 포함

내부 조인

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

  • ANSI SQL 표준과 사실 상의 표준인 Oracle 사가 제안한 조인 형식이 사용

    1
2
3
4

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

내부 조인의 사용 1

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

    학생이름나이학생번호
    유관순118201834-021216
    지정천32201926-880215
    안창호42201931-781109
    박은식61201934-080621
    안중근41201934-790902
    손병희59201978-610408
    윤봉길112202031-816515
    학생번호잔액
    201831-331215800000
    201834-021216600000
    201978-610408400000
    201931-781109400000
    201926-880215300000
    201934-790902100000
    201934-080621300000
    202034-5965411200000
    1
2
3

      SELECT 학생이름, 나이, 학생번호
      FROM 학생 
      WHERE 나이 >= 30
    학생이름나이학생번호잔액
    유관순118201834-021216600000
    지정천32201926-880215300000
    안창호42201931-781109400000
    박은식61201934-080621300000
    안중근41201934-790902100000
    손병희59201978-610408400000
    윤봉길112202031-816515150000
    1
2
3
4

      SELECT 학생.학생이름, 학생.나이, 계좌.계좌번호, 계좌.잔액
      FROM 학생 INNER JOIN 계좌
          ON 학생.학생번호 = 계좌.학생번호
          WHERE 학생.나이 >= 30
    학생이름나이학생번호잔액
    유관순118201834-021216600000
    지정천32201926-880215300000
    안창호42201931-781109400000
    박은식61201934-080621300000
    안중근41201934-790902100000
    손병희59201978-610408400000
    1
2
3
4

      SELECT 학생.학생이름, 학생.나이, 계좌.계좌번호, 계좌.잔액
      FROM 학생, 계좌
          WHERE 학생.학생번호 = 계좌.학생번호 
              AND 학생.나이 >= 30
    학생이름나이학생번호잔액
    유관순118201834-021216600000
    지정천32201926-880215300000
    안창호42201931-781109400000
    박은식61201934-080621300000
    안중근41201934-790902100000
    손병희59201978-610408400000

내부 조인의 사용 2

  • ‘컴퓨터과학과’소속의 교수가 강의하는 과목에 대해 과목별 수강하는 학생 수를 과목코드와 함께 출력하시오

    image.png

    1
2
3
4
5

      SELECT * 
      FROM 교수 INNER JOIN 과목
          ON 교수.교수번호 = 과목.교수번호
              INNER JOIN 수강
          ON 과목.과목코드 = 수강.과목코드

    1
2
3
4
5
6
7

      SELECT * 
      FROM 교수 INNER JOIN 과목
          ON 교수.교수번호 = 과목.교수번호
                  INNER JOIN 수강
          ON 과목.과목코드 = 수강.과목코드
      WHERE 교수.소속학과 = '컴퓨터과학과'
      GROUP BY = 과목.과목코드

    1
2
3
4
5
6
7

      SELECT 과목.과목코드, COUNT(수강.학생번호)
      FROM 교수 INNER JOIN 과목
          ON 교수.교수번호 = 과목.교수번호
                  INNER JOIN 수강
          ON 과목.과목코드 = 수강.과목코드
      WHERE 교수.소속학과 = '컴퓨터과학과'
      GROUP BY = 과목.과목코드

자연 조인

  • 두 개 이상의 테이블을 하나의 테이블로 결합하는 내부 조인과 매우 유사한 기능
  • 두 테이블에 동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합하는 내부 조인
    • 컬럼의 이름이 같으면, 컬럼의 이름이 같은 것끼리 붙는 형태
1
2
3

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

외부 조인의 개념

image.png

  • 내부 조인(inner join)은 조인 조건에 일치하는 레코드만 결합하여 결과를 생성
    • 조인 결과에 정보의 손실이 발생
  • 외부 조인은 조인 조건에 맞지 않는 레코드도 질의의 결과에 포함 시키는 질의
  • 외부 조인의 종류
    • 왼쪽 외부 조인(left outer join)
    • 오른쪽 외부 조인(right outer join)
    • 완전 외부 조인(full outer join)

외부 조인 구문 형식

1
2
3
4
5
6
7

SELECT 별칭1.컬럼1, 별칭1.컬럼2, ..., 별칭1.컬럼m,
        별칭2.컬럼2, ..., 별칭2.컬럼n,
    FROM 테이블1 AS 별칭1
        LEFT | RIGHT [OUTER] JOIN
        테이블2 AS 별칭2
    ON 별칭1.컬럼i = 별칭2.컬럼j
    [WHERE 절]

외부 조인의 사용

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

    과목코드학생번호신청시각
    COM11201934-7909022019-02-28 08:32:54
    COM11202026-5909302019-02-20 16:00:21
    COM11202078-0806212019-02-21 15:21:54
    COM12201831-3312152019-08-21 23:25:25
    COM12201931-7811092018-08-02 03:25:16
    COM12201978-6104082015-02-24 10:25:40
    1
2
3
4
5
6

      SELECT A.학생번호, A.학생이름, B.과목코드, B.신청시각
      FROM 학생 AS A
          LEFT OUTER JOIN
          수강 2 AS B
      ON A.학생번호 = B.학생번호
      ORDER BY B.과목코드 ASC
    학생번호학생이름과목코드신청시각
    202031-354516조정대NULLNULL
    201934-080621박은식NULLNULL
    201934-790902안중근COM112019-02-28 08:32:54
    202078-080621강신영COM112019-02-21 15:21:54
    202026-590930정용민COM112019-02-20 16:00:21
    201931-781109안창호COM122018-08-02 03:25:16
    • 조건에 만족하지 않는 컬럼 값들은 NULL 로 채워짐

외부 조인의 실행 과정

  • 학생

    학생이름학생번호
    강신영202078-080621
    안중근201934-790902
    조정대202031-354516
    정용호202034-596541

  • 수강

    학생번호과목코드
    202078-080621COM11
    201934-790902COM11
    201931-781109COM12
    202034-596541COM24

  • 조인 결과

    학생이름학생번호과목코드
    강신영202078-080621COM11
    안중근201934-790902COM11
    조정대202031-354516NULL
    정용호202034-596541COM24
  • 외부 조인이 LEFT일지 RIGHT 일지 파악할 것
  • 수강 테이블이 앞에 위치한다면 RIGHT OUTER JOIN으로 변경

셀프 조인

  • 한 테이블이 자기 자신과 조인되는 질의

  • 동일한 테이블에 대한 조인이므로 반드시 테이블 이름에 대한 별칭이 의무적으로 사용

    1
2
3
4
5
6

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

셀프 조인의 사용

  • 과목의 과목코드, 과목명 그리고 그 과목의 선수과목의 과목코드, 과목명을 모두 출력하시오. 단, 선수과목이 없는 과목도 결과에 포함시킨다.

    과목코드과목명학점선수과목이수구분교수번호
    COM11컴퓨터의 이해3 교양…
    COM12파이썬 프로그래밍 기초3 교양…
    COM24자료구조3COM12전공필수…
    COM31데이터베이스 시스템3COM24전공필수…
    COM34알고리즘3COM24일반선택…
    COM44클라우드 컴퓨팅3 전공필수…
    1
2
3

      SELECT B.과목명, B.과목코드, A.과목명 AS 선수과목명, A.과목코드 AS 선수과목코드
  		FROM 과목 AS A RIGHT OUTER JOIN 과목 AS B
  		     ON A.과목코드 = B.선수과목코드
    과목명과목코드선수과목명선수과목코드
    컴퓨터의 이해COM11  
    파이썬 프로그래밍 기초COM12  
    자료구조COM24인터넷과 정보사회COM12
    데이터베이스 시스템COM31자료구조COM24
    알고리즘COM34자료구조COM24
    클라우드 컴퓨팅COM44  

뷰의 사용

뷰의 개념

  • 데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블(virtual table)
    • 데이터 독립성
      • 원본 테이블의 구조가 바뀌어도 뷰를 이용한 작업은 정의만 변경되어 응용 프로그램에 영향이 없음
      • 실제 데이터를 저장하지 않고, 뷰 정의에 따라 질의가 실행될 때 데이터를 동적으로 생성
    • 데이터 보안
      • 사용자에게 원본 테이블의 일부 컬럼에 대한 접근을 허용하여 보안 효과를 향상
      • 기본 테이블의 일부 컬럼이나 행만을 보여주는 효과를 통해 데이터 보안을 유지
    • 다양한 구조의 테이블 사용
      • 사용자의 요구 사항에 맞는 테이블의 구조를 제공
    • 작업의 단순화
      • 복잡한 질의문을 뷰로 단순화
    • 데이터 무결성
      • WITH CHECK OPTION을 이용하여 뷰 생성에 위배되는 수정 작업을 거부

뷰의 생성

  • 생성되는 뷰의 구조는 SELECT 문의 결과로 결정

    1
2
3
4
5
6

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

뷰의 수정 및 삭제

  • 뷰의 수정은 생성과 동일하게 새로운 SELECT 문의 결과로 변경

    1
2
3
4
5

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

  • 뷰의 삭제는 일반적인 데이터베이스 객체 삭제와 동일

    1

      DROP VIEW 뷰이름

뷰 생성의 예

  • 컴퓨터 과학과 소속의 학생정보와 학과이름 및 이수학점을 출력하는 ‘컴퓨터과학과_학생’ 뷰를 생성하시오

    학생번호학생이름성별생년월일나이전화번호학과이름이수학점
    201831-331215김마리아여1991-06-1830010-0000-0002컴퓨터과학과39
    201934-790902안중근남1979-09-0242010-0000-0006컴퓨터과학과 
    202026-590930정용민남2003-05-1918010-0000-0012컴퓨터과학과137
    202031-816515윤봉길남1908-06-21113010-0000-0009컴퓨터과학과12
    202034-596541정용호남2000-01-2321010-0000-0008컴퓨터과학과117
    202078-080621강신영남1991-06-2630010-0000-0011컴퓨터과학과96
    1
2
3

      SELECT 학생.*, 전공.전공, 전공.학과이름, 전공.이수학점
      FROM 학생 NATURAL JOIN 전공
      WHERE 전공.학과이름 = '컴퓨터과학과'

    1
2
3
4
5

      CREATE VIEW 컴퓨터과학과_학생 AS (
      SELECT 학생.*, 전공.전공, 전공.학과이름, 전공.이수학점
          FROM 학생 NATURAL JOIN 전공
          WHERE 전공.학과이름 = '컴퓨터과학과'
      )

뷰를 이용한 데이터 검색

  • 뷰는 가상의 테이블이므로 데이터 조작은 테이블 조작과 동일하게 수행

    1
2

      SELECT 컬럼1, ..., 컬럼n FROM 뷰이름
  		WHERE 조건

    1
2

      SELECT 컬럼1, ..., 컬럼n FROM 뷰이름
  		WHERE 조건 AND 뷰 정의 조건

뷰를 이용한 데이터 삽입

  • 뷰에 대한 INSERT 문은 원본 테이블에서 실행
  • INSERT 문 실행 불가능한 경우
    • PRIMARY KEY, NOT NULL 등의 제약 사항이 위배되는 경우 삽입 불가능
    • 원본 테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하는 경우 실행 불가능
    • 조인 질의 또는 그룹 질의가 적용된 뷰는 데이터 삽입 및 수정이 불가능
    • WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없지만 뷰에 맞지 않는 조건일 경우 실행 불가능



연습 문제

  1. 다음은 조인의 종류와 결과에 대한 다이어그램이다. (ㄴ)과 같은 형태의 조인을 무엇이라고 하는가? (단, A, B는 테이블을 나타낸다.)

    image.png

    a. 내부 조인

    • 주어진 다이어그램은 두 개의 테이블에서 연관성이 있는 레코드를 결합하여 출력할 때, 연관성의 범위를 나타냄
    • 좌측부터 외부 조인, 내부 조인, 오른쪽 외부 조인을 의미함

  2. 강의 스키마를 참조하여 아래의 명령을 수행하기 위한 SQL 명령문으로 정확하게 작성된 것은?

    강의번호 (PK)강의이름이수구분교수번호 (FK)선수강의 (FK)
    COM101파이썬 프로그래밍 기초교양C01 
    COM301데이터베이스전공C01COM101
    COM302데이터베이스설계및구현전공C02COM301
    BSN103경영학개론교양B01 
    BSN302인적자원관리전공B02 
    1

     선수강의가 있는 강의의 강의이름, 강의번호와 그 강의에 대한 선수강의의 강의이름을 출력하라

    a.

    1
2

     SELECT A.강의이름, A.강의번호, B.강의이름
     FROM 강의 A INNER JOIN 강의 B ON A.선수강의 = B.강의번호
    • 요청 된 질의 결과를 생성하기 위해서는 강의 정보와 선수 강의 정보가 필요함
    • 따라서 테이블이 자기 자신과 조인하는 셀프 조인이 사용됨
    • 셀프 조인은 반드시 테이블 이름에 대한 별칭이 요구 됨
    • 또한 선수 강의가 있는 과목 정보만 출력하므로 내부 조인이 적용 됨

  3. 뷰를 통해 레코드 조작 시, 뷰의 정의된 조건에 맞지 않는 레코드에 대한 조작을 방지하는 옵션은?

    a. WITH CHECK OPTION

    • WITH CHECK OPTION 은 뷰를 통해 데이터 변경(INSERT, UPDATE, DELETE) 작업을 할 때, 뷰의 정의된 조건에 맞는 데이터만 변경하도록 제한하는 옵션임



정리 하기

  • 중첩 질의는 SELECT 문 내부에서 독립적으로 실행 가능한 또 다른 SELECT 문이 내포되어있는 질의로, 일반적으로 내부 질의의 처리 결과를 외부 질의에서 재사용하는 과정으로 처리됨
    • FROM절과 WHERE 절에 SELECT 문을 내포시키는 형식이 있으며 중첩 질의를 위해 IN, EXISTS, NOT 연산자가 사용됨
  • 조인은 테이블 간의 관련성을 이용하여 두 개 이상의 테이블에서 데이터를 검색하는 질의 기법으로, 종류로는 내부 조인, 자연 조인, 외부 조인(왼쪽, 오른쪽, 완전), 셀프 조인이 있음
  • 뷰는 데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블로, 데이터의 독립성, 데이터 보안, 작업 단순화, 데이터 무결성 등의 위해 사용함



체크 포인트

  1. 다음 릴레이션 고객과 주문에 대한 완전 외부 조인 연산의 결과로 (가), (라)에 들어갈 내용은?

    • 고객

      고객ID고객이름주소
      ok홍길동서울
      pk강감찬경기

    • 주문

      주문번호고객ID주문제품
      100ok운동화
      101ak장갑

    a. ok, NULL

  2. 뷰(VIew)의 장점이 아닌 것은?

    a. 뷰 자체로 인덱스를 가짐

    • 뷰(View)의 장점
      • 데이터 보안 용이
      • 사용자 데이터 관리 용이
      • 논리적 독립성 제공
Knou, 데이터베이스 시스템
knou database
Recently Updated
Contents

[파이썬 프로그래밍 기초] 7강 - 반복 구조

[데이터베이스 시스템] 8강 - 정규화