7강. SQL(4)

1. 중첩 질의의 개념

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

2. 중첩 질의의 형식

• FROM절에서의 중첩 질의

SELECT 컬럼1, 컬럼2, ..., 컬럼n  // 외부 질의
FROM ( SELECT 컬럼1, 컬럼2, ..., 컬럼n  // 내부 질의 
				FROM 테이블
				WHERE 조건 )
WHERE 조건

• WHERE 절에서의 중첩 질의

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

3. 중첩 질의의 사용

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

SELECT MAX(d.평균연봉) AS 평균연봉
FROM ( SELECT 소속학과, AVG(연봉) AS 평균연봉
			 FROM 교수
			 GROUP BY 소속학과 ) AS d
WHERE d.평균연봉 < 70000000

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

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

• 생활과학과’ 소속 학생 중 수강신청을 하지 않은 학생의 학생번호를 출력하시오.
  • NOT EXISTS을 통해 수강하지 않은 학생들의 레코드의 경우 True로 반환

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

4. 조인 질의

• 테이블 간의 관련성을 이용하여 두 개 이상의 테이블에서 데이터를 검색하는 질의 기법
• ER 모델링 및 정규화 기법으로 여러 테이블로 분리된 정보를 일시적으로 하나의 레코드로 통합
• 조인 질의의 종류
  • 내부 조인
  • 외부 조인

5. 내부 조인

• 두 개 이상의 테이블에서 조인 조건을 만족하는 레코드만 결합하여 출력 결과에 포함시키는 연산
• 조인 조건은 WHERE 절이 아닌 ON절에 기록 (표준)
  • Oracle에서는 WHERE절에서 조인 조건을 기록
• ANSI SQL 표준과 사실상의 표준인 Oracle사가 제안한 조인 형식 사용
  • 형식

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

6. 내부 조인 사용

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

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

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

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

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

7. 자연 조인

• 두 개 이상의 테이블을 하나의 테이블로 결합하는 내부 조인과 매우 유사한 기능
• 두 테이블에 동일한 이름의 컬럼에 대해 값이 같은 레코드를 결합하는 내부 조인
• 형식

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

8. 외부 조인의 개념

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

9. 외부 조인 구문형식

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

10. 셀프 조인

• 한 테이블이 자기 자신과 조인되는 질의
• 동일한 테이블에 대한 조인이므로 반드시 테이블 이름에 대한 별칭이 의무적으로 사용

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

11. 뷰의 개념

• 데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블 ( Virtual Table )
  • 테이블을 다양한 방식으로 볼 수 있는 객체가 뷰
  • 뷰 자체가 데이터를 저장하지는 않는다.
• 데이터 독립성
  • 원본 테이블의 구조가 바뀌어도 뷰를 이용한 작업은 정의만 변경되어 응용 프로그램에 영향이 없음
• 데이터 보안
  • 사용자에게 원본 테이블의 일부 컬럼에 대한 접근을 허용하여 보안 효과를 향상
• 다양한 구조의 테이블 사용
  • 사용자의 요구사항에 맞는 테이블의 구조를 제공
• 작업의 단순화
  • 복합한 질의문을 뷰로 단순화
• 데이터 무결성
  • WITH CHECK OPTION을 이용하여 뷰 생성에 위배되는 수정작업을 거부

12. 뷰의 생성

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

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

13. 뷰의 수정

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

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

14. 뷰의 삭제

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

DROP VIEW 뷰이름

15. 뷰를 이용한 데이터 검색

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

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

16. 뷰를 이용한 데이터 삽입

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