방송대 컴퓨터과학과/데이터베이스시스템

방송대 데이터베이스시스템 - 6강. SQL (3)

꼽파 2023. 5. 27. 10:54

  • 1. 데이터 검색 (2)

  • 2. 뷰의 사용

    • 1. 데이터 검색 (2)

    집계 함수

    특정 컬럼에 집계함수를 통해 다양한 통계 연산을 수행할 수 있는 기능

    SELECT 절 또는 HAVING 절에 기술

     

    집계 함수의 종류

    COUNT 컬럼에 있는 값들의 개수
    SUM 컬럼에 있는 값들의 합
    AVG 컬럼에 있는 값들의 평균
    MAX 컬럼에서 가장 큰 값
    MIN 컬럼에서 가장 작은 값

     

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

    출처 : 6강 강의록

    SELECT COUNT(단과대학)
        FROM 학과
    		중복 O
    SELECT COUNT(DISTINCT 단과대학)
        FROM 학과
    		중복 X
    DISTINCT: 중복 제거
    SELECT COUNT(DISTINCT 단과대학) AS 단과대학수
        FROM 학과
    		별칭 붙여주기: AS 별칭
    · 의미 전달력 향상
    · 여러 가지 과정에서 긴 쿼리를 굉장히 짧게 단축시킬수 있는 제공

    그룹 질의(GROUP BY 절)

    특정 기준으로 레코드를 그룹화하고 각 레코드 그룹에 대해 집계함수를 적용하는 질의

    SELECT 질의
	GROUP BY 컬럼

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

     

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

    SELECT 소속학과, COUNT(*) AS 교수수
        FROM 교수
        GROUP BY 소속학과

    관계형 모델은 하나의 컬럼값은 단 하나의 의미만을 갖는 값으로 이루어짐 (컬럼의 원자성)

    해당 칼럼 안에는 값의 의미가 한 개인 것만 들어가는데,
    교수이름 컬럼이 오게 되면 학과별 교수 이름 중 생활과학과는 컬럼값이 2개 들어감.
    → 관계형모델의 원래 가장 기본적인 특성인 컬럼값의 원자성을 완전히 위배하게 됨.
    → 집계 함수(COUNT)와 그룹의 기준(소속학과)이 되는 것 이외에는 포함 불가

    HAVING 절

    그룹질의의 결과 레코드에 대해 출력 조건을 기술하기 위한 절

    SELECT 질의
    GROUP BY 컬럼
    HAVING 조건

     

    조건 기술절

    • WHERE : 레코드에 대한 조건을 기술

    • HAVING : 집계 결과 레코드에 대한 조건을 기술

     

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

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

    중첩 질의

    SELECT 문 내부에서 독립적으로 실행 가능한 또 다른 SELECT 문이 내포되어 있는 질의

    일반적으로 내부 질의(1)의 처리결과를 외부 질의(2)에서 재사용

     

    중첩 질의의 종류

    • FROM 절에서의 중첩 질의 활용

    - FROM 절에서의 결과 집합을 SELECT에서 재검색

    • WHERE 절에서의 중첩 질의 활용

    - WHERE 절에서의 결과 집합을 활용하여 외부 질의에서 레코드의 출력 여부를 결정

    - IN, NOT IN, EXISTS, NOT EXSISTS 사용

    FROM 절에서의 중첩 질의 WHERE 절에서의 중첩 질의
    학과별 교수의 평균 연봉이 70,000,000 미만인 학과 중 가장 높은 평균 연봉을 출력하시오. '생활과학과' 소속 학생 중 수강신청을 하지 않은 학생의 학생번호를 출력하시오.
    SELECT 소속학과, AVG(연봉) AS 평균연봉
        FROM 교수
        GROUP BY 소속학과    		 SELECT 학생번호
        FROM 전공
        WHERE 학생이름 = '생활과학과'
    SELECT MAX(d.평균연봉) AS 평균연봉
        FROM (SELECT 소속학과, AVG(연봉) AS 평균연봉
                        FROM 교수
                        GROUP BY 소속학과) AS d
        WHERE d.평균연봉 < 70000000    		 SELECT A.학생번호
        FROM 전공 AS A
        WHERE A.학과이름 = '생활과학과' AND
            NOT EXISTS (SELECT B.학생번호 FROM 수강 B
                                       WHERE A.학생번호 = B.학생번호)
    		생활과학과 학생의 학생번호만 추출함.
    학생 번호를 가지고 다시 한번 수강 테이블에서 조회
    그 결과 레코드가 있으면 거짓, 없으면 참

    조인 질의

    테이블 간의 관련성을 이용하여 두 개 이상의 테이블에서 데이터를 검색하는 질의 기법

    ER 모델링 및 정규화 기법으로 여러 테이블로 분리된 정보를 통합하여 검색 시 유용

     

    조인 질의의 종류

    크로스 조인
    (cross join)    		 조건 없이 모든 조합의 레코드를 결합
    관계대수의 카티션 프로덕트 연산과 동일함.    		 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
        FROM 테이블1 CROSS JOIN 테이블2
            [ ... CROSS JOIN 테이블이름m ]
            WHERE 조건
    내부 조인
    (inner join)
    		 조인조건에 일치하는 레코드만 결합하여 결과를 생성하는 결합 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
        FROM 테이블1 INNER JOIN 테이블2
        ON 조인조건1
            [ ... INNER JOIN 테이블이름m
                ON 조인조건p ]
        WHERE 조건
    자연 조인
    (natural join)    		 두 테이블에 동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합하는 내부 조인 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
            FROM 테이블1 NATURAL JOIN 테이블2
            [WHERE 조건]
    외부 조인(outer join)  
    - 왼쪽 외부 조인
    (left outer join)
    		 왼쪽에 있는 테이블에 존재하는 모든 레코드는 조인 결과에 포함되고, 조인 조건에 맞는 오른쪽 레코드만 연결됨.

    조건에 맞지 않는 레코드의 컬럼은 NULL로 대체됨.    		 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
            FROM 테이블1 LEFT OUTER JOIN 테이블2
            ON 테이블이름1.컬럼 = 테이블이름2.컬럼
            [WHERE 조건]
    - 오른쪽 외부 조인
    (right outer join)
    		 오른쪽에 있는 테이블에 존재하는 모든 레코드는 조인 결과에 포함되고, 조인 조건에 맞는 왼쪽 레코드만 연결됨.

    조건에 맞지 않는 레코드의 컬럼은 NULL로 대체됨.    		 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
            FROM 테이블1 RIGHT OUTER JOIN 테이블2
            ON 테이블이름1.컬럼 = 테이블이름2.컬럼
            [WHERE 조건]
    - 완전 외부 조인
    (full outer join)
    		 양쪽에 있는 테이블에 존재하는 모든 레코드는  조인 결과에 포함되고, 조인 조건에 맞는 레코드만 연결됨.

    조건에 맞지 않는 레코드의 컬럼은 NULL로 대체됨.    		 SELECT 컬럼1, 컬럼2, ... , 컬럼m,
            FROM 테이블1 FULL OUTER JOIN 테이블2
            ON 테이블이름1.컬럼 = 테이블이름2.컬럼
            [WHERE 조건]
    셀프조인, 자기조인
    (self join)    		 한 테이블이 자기 자신과 조인되는 것
    동일한 테이블 이름이 중복되어 컬럼 이름만 기술하면 어느 쪽 테이블에 속하는지 알 수 없음
    → 반드시 서로 다른 2개의 별칭    		 SELECT 별칭1.컬럼1, ... , 별칭n.컬럼m,
            FROM 테이블1 AS 별칭1 INNER/OUTER JOIN 테이블2
            ON 테이블이름1.컬럼 = 테이블이름2.컬럼
            [WHERE 조건]

    내부조인

    두 개 이상의 테이블에서 조인 조건을 만족하는 레코드만 결합하여 출력 결과에 포함시키는 연산

    조인 조건은 WHERE 절이 아닌 ON 절에 기록

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

    출처 : 6강 강의록

     

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

    SELECT 학생이름, 나이, 학생번호
        FROM 학생
        WHERE 나이 >= 30
    <SQL>

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


    <SQL(오라클)>

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

    · FROM 절에 테이블 명만 적음
    · WHERE절에 조인에 대한 조건, ON절 (X)

    자연조인

    두 개 이상의 테이블을 하나의 테이블로 결합하는 내부 조인과 매우 유사한 기능

    두 테이블에 동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합하는 내부 조인

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

    같은 컬럼 이름의 값끼리 결합하라는 조건이 생략됨.

    → 조인 조건을 기술하는 ON 절이 없음.

     

    학생의 학생이름, 나이, 성별 그리고 학생이 전공한 학과의 학과이름, 이수학점을 출력하라.

    SELECT A.학생이름, A.나이, A.성별, B.학과이름, B.이수학점
	FROM 학생 A NATURAL JOIN 전공 B

    외부조인

    내부 조인(inner join)은 조인조건에 일치하는 레코드만 결합하여 결과를 생성

    → 조인 결과에 정보의 손실이 발생

    외부 조인은 조인조건에 맞지 않는 레코드도 질의의 결과에 포함시키는 질의

    외부 조인의 종류 외부 조인 구문형식
    • 왼쪽 외부 조인(left outer join)
    왼쪽에 있는 테이블에 존재하는 모든 레코드는 조인 결과에 포함되고, 조인 조건에 맞는 오른쪽 레코드만 연결됨.

    • 오른쪽 외부 조인(right outer join)
    오른쪽에 있는 테이블에 존재하는 모든 레코드는 조인 결과에 포함되고, 조인 조건에 맞는 왼쪽 레코드만 연결됨.

    • 완전 외부 조인(full outer join)
    양쪽에 있는 테이블에 존재하는 모든 레코드는 조인 결과에 포함되고, 조인 조건에 맞는 레코드만 연결됨.

     

    학생의 학생번호, 학생이름과 그 학생이 수강신청한 과목의 과목코드, 신청시각을 출력하시오.

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

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

    셀프조인(자기조인)

    한 테이블이 자기 자신과 조인되는 형태

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

    출처 : 6강 강의록

     

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

    SELECT B.과목명, B.과목코드
	   A.과목명 AS 선수과목명, A.과목코드 AS 선수과목코드
   FROM 과목 AS A RIGHT OUTER JOIN 과목 AS B
   ON A.과목코드 = B.선수과목

    2. 뷰의 사용

    뷰의 개념

    데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상 테이블(virtual table)

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

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

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

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

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

    뷰의 생성, 수정, 삭제, 데이터 검색

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

    CREATE  VIEW 뷰이름 AS
        ( SELECT 컬럼1, 컬럼2, ..., 컬럼n
            FROM 테이블
            [WHERE 조건])
    [WITH CHECK OPTION]    		 SELECT 학생.*, 전공.학과이름, 전공.이수학점
    FROM 학생 NATURAL JOIN 전공
    WHERE 전공.학과이름='컴퓨터과학과'
    뷰 수정 생성과 동일하게 새로운 SELECT 문의 결과로 변경

    ALTER VIEW 뷰이름(컬럼1, 컬럼2, ..., 컬럼n) AS
        ( SELECT 컬럼1, 컬럼2, ..., 컬럼n
            FROM 테이블
            [WHERE 조건] )    		 ALTER VIEW 고객계좌_보유자 AS
        ( SELECT A.학생번호, A.학생이름, A.나이, A.전화번호,
                B.계좌번호, B.잔액
             FROM 학생 A NATURAL JOIN 계좌 B
             WHERE B.잔액 >= 800000
         );
    뷰 삭제 DROP VIEW 뷰이름 DROP VIEW 고객계좌_보유자
    검색 데이터 조작은 테이블 조작과 동일하게 수행

    SELECT 컬럼1, ... , 컬럼2 
        FROM 뷰이름
        WHERE 조건    		 SELECT *
        FROM 컴퓨터과학과_학생
        WHERE 성별 = '여';

    뷰와 관련된 데이터 조작

    • 뷰에 대한 INSERT문은 원본 테이블에서 실행

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

    ex. 학생번호를 수정하는데 이 학생번호로 수정한 값이 다른 학생 번호와 겹침

    → 수정이나 삽입 불가능

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

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

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

     

    CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2)
);

CREATE VIEW employee_view AS
SELECT id, name
FROM employees
WHERE age > 30;

     

    employee_view 뷰는 employees 테이블을 기반으로 만들어진 뷰이고, id와 name 두 개의 컬럼만 존재함.

    employee_view 뷰에는 age와 salary라는 컬럼이 존재하지 않음.

    → employee_view 뷰에 직접 데이터를 삽입하는 것은 불가능함.

         대신 데이터를 employees 테이블에 삽입한 후, employee_view 뷰를 통해 해당 데이터를 조회할 수 있음.

     

    뷰는 데이터를 직접 저장하지 않고 기존의 테이블에 있는 데이터를 가공하여 보여주는 역할을 수행함.

    → 뷰에는 해당 데이터의 원본 테이블과 일치하는 컬럼들만 존재할 수 있음.

    728x90