1. 데이터 검색(2)

그룹 질의

그룹: 특정한 특징들을 공유하는 원소들의 집합.

집계함수(COUNT, SUM, MIN ...)

1. 특정 컬럼에 집계함수를 통해 다양한 통계 연산을 수행할 수 있는 기능.
2. SELECT절 또는 HAVING절에 기술
   (출력의 일정 부분을 조정하는데 사용)
3. 집계함수의 종류
   • COUNT: 컬럼에 있는 값들의 개수
   • SUM: 컬럼에 있는 값들의 합
   • AVG: 컬럼에 있는 값들의 평균
   • MAX: 컬럼에서 가장 큰 값
   • MIN: 컬럼에서 가장 작은 값

집계 함수의 사용

질의문 Q1. 단과대학의 개수를 출력하시오.

SQL: SELECT COUNT(단과대학) FROM 학과
단과대학 중 중복되는 값들이 있음. -> 중복을 제거하고 싶은 경우

SQL: SELECT COUNT(DISTINCT 단과대학) FROM 학과

컬럼명이 복잡. 결과값 많을 문외한이 보는 경우 의미전달력이 떨어짐. 이 컬럼값을 의미값이 높도록 별칭을 부여할 수 있음 => AS 사용

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

---

그룹 질의(GROUPBY)

1. 특정 기준으로 레코드를 그룹화하고 각 레코드 그룹에 대해 집계 함수를 적용하는 질의
2. [주의] SELECT 절에 그룹의 기준과 집계 함수 이외의 컬럼은 포함될 수 없음.

구문형식: SELECT GROUP BY 컬럼

그룹질의의 사용

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

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

소속학과가 같은 레코들끼리 묶어서 그룹화시키고, 해당 그룹에 대해 COUNT를 시행한다.

💬 저는 생활과학과 교수들의 이름들도 보고 싶어요. 교수이름 컬럼을 추가하면 안되나요?
✔️ 그럼 어떻게 될까?

관계형 DBMS는 컬럼값의 원자성에 의해서 하나의 컬럼값은 단 하나의 의미만을 가진다. 교수 이름이라는 컬럼이 추가되면, 교수수가 두명인 경우 두명의 이름이 들어가게 된다. 이처럼 SELECT절에 그룹의 기준과 집계 함수 이외의 컬럼이 포함되는 경우 원자성을 위반하게 되므로 사용이 불가능하다.
그룹의 기준:소속학과, 집계함수:count(*)

---

HAVING 절

1. 그룹 질의의 결과 레코드에 대해 출력 조건을 기술하기 위한 절
   조건을 만족하는 결과만 보고 싶을 때 사용. groupby와 함께 사용되야 함.

2. 조건 기술 절(혼동하지 않도록 주의!)

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

그룹질의의 사용

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

이렇게 집계함수를 통해 구분한 결과중에, 내가 원하는 결과만 출력하고자 할 때 HAVING절을 사용한다!

---

중첩 질의

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

중첩 질의의 형식

개별적으로 혼자 작동할 수 있는 완전한 SELECT문이 내부에 삽입됨.

중첩 질의의 사용1

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

질의문을 사용해 테이블을 도출하고, 내부 질의문을 통해 해당 질의문을 또 다른 테이블인양 사용할 수 있다.

중첩 질의의 사용2

질의문 Q1. '생활과학과' 소속 학생 중 수강신청을 하지 않은 학생의 학생번호를 출력하시오.

생활과학과 학생의 학생번호를 찾아낸 뒤, 각 학생번호마다 수강신청 테이블에 가서 '이 학생 번호로 수강신청 내역이 있어?'라고 물어봐야 함. 수강신청한 내역이 있으면 출력하지 않고, 내역이 없으면 결과물로 나와야 함. 이럴 때, EXISTS, NOT EXISTS를 사용한다.

---

조인 질의

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

학생: 학생 테이블. 계좌: 계좌 테이블. 두개의 테이블에 나뉘어들어가 있는 데이트를 결합시켜 하나의 테이블로 출력해달라는 의미

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

내부 조인

1. 두 개 이상의 테이블에서 조인 조건을 만족하는 레코드만 결합하여 출력 결과에 포함시키는 연산
2. 조인 조건은 WHERE절이 아닌 ON절에 기록
3. ANSI SQL 표준과 사실상의 표준인 Oracle사가 제안한 조인 형식이 사용
   오라클 사가 힘이 쎄서, 그냥 표준말고 오라클에서 제안한 형식 역시 보편적으로 사용됨. 알아둬야 함

INNER JOIN: 테이블이 2개. FROM의 테이블 두개 사이에 "INNER JOIN"을 명시하여 결합한다.
ON: 막 연결하는거 아님. 어떠한 레코드와 어떠한 레코드를 결합시켜야 하는지에 관한 조인조건을 기술한다.

내부 조인의 사용

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

컬럼값이 어느 테이블에서 왔는지를 명시해줘야 함.(ex. 학생.나이 계좌.잔액)

구획없이 중복적으로 사용됨.
1. FROM절: 외부조인, 내부조인 구분하지 않음. 콤마로 여러 테이블 명시. WHERE절이 없으면 그냥 테이블 두개를 "CROSS JOIN"함.
2. WHERE절: JOIN에 대한 조건 (학생.학생번호=계좌.학생번호) , 레코드에 대한 조건 (학생.나이>=30) 을 한꺼번에 명시한다. (ON절을 명시하지 않는다.)

자연 조인

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

자연조인이라고 이미 조인에 대한 조건을 포함하고 있으므로 따로 ON절을 사용해 조인 조건을 명시하지 않습니다.

FROM절에서 사용하는 두 테이블 사이에 NATURAL JOIN 명시. 따로 ON을 사용하여 JOIN조건을 명시하지 않고, 바로 WHERE조건 사용함.

외부 조인

자주 사용하지는 않는데, 특수한 경우 유용하게 사용될 수 있으므로 알아두면 좋음

1. 내부 조인(inner join)은 조인조건에 일치하는 레코드만 결합하여 결과를 생성
   • 조인 결과에 정보의 손실이 발생
     (조인조건을 만족하지 않는 레코드들은 출력에서 배제되므로)
2. 외부 조인은 조인 조건에 맞지 않는 레코드도 질의의 결과에 포함시키는 질의.
   (조건에 맞는 레코드가 없는 경우 null로 비워 둠)
3. 외부 조인의 종류
   • 왼쪽 외부 조인 (left outer join)
   • 오른쪽 외부 조인 (right outer join)
   • 완전 외부 조인 (full outer join)

내부조인은 공통요소만 나오고, 외부조인은 공통 외 요소들도 출력한다!

외부 조인 구문 형식

차이점: LEFT|RIGHT [OUTER] JOIN. 외는 내부조인과 같음.

외부 조인의 사용

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

💬 학생 테이블(학생번호, 학생이름) 수강테이블(과목코드, 신청시각)의 결과를 조인을 통해 함께 출력해야 하는 상황. 내부조인을 사용하는 경우, 수강신청을 하지 않은 학생은 수강테이블과의 접점이 없어 결과 출력에 배제된다. 이처럼 두 테이블을 접목시켜 결과를 출력하는 조인에서 공통 분모가 없는 여집합 부분을 출력할때, 외부 조인을 사용할 수 있다!

외부 조인의 실행과정

조중대 학생은 수강신청을 하지 않았음. 그러나 LEFT OUTER JOIN으로 왼쪽 테이블(학생)은 모두 출력되므로, 수강 테이블의 항목들은 NULL인 상태로 결과가 출력된다.

외부 조인의 사용

수강신청을 하지 않은 조중대와 박은식은 계좌 항목은 NULL로 비워두고 학생정보가 출력되었다.

---

셀프 조인

재귀적 관계. 하나의 테이블이 자기자신의 복사본과 조인하는 경우에 사용.

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

셀프 조인의 사용

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

💬 과목에 대한 정보 뿐 아니라, 선수과목에 대한 정보 역시 과목 테이블에 있음. 따라서 과목테이블이 스스로와 JOIN해 정보를 도출해야 함. 뿐만 아니라 선수과목이 없는 과목도 출력해야 하므로 OUTER JOIN도 사용해야 한다.

---

---

2. 뷰의 사용

뷰의 개념

존재하지 않는, 논리상에서만 존재하는 가상의 테이블. 그러나 테이블로 다뤄 결과를 도출할 수 있는 객체.

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

뷰의 생성, 수정, 삭제

뷰의 생성

1. 생성되는 뷰의 구조는 SELECT 문의 결과로 결정 (CREATE 아님)
   

뷰의 수정 및 삭제

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

단, ALTER 시 각각의 컬럼 리스트가 동일하게 유지되어야 한다.
그럼 몰 바꾸는건데? 수정의 이미가 있음? 그냥 이름 변경용인가?

뷰 생성의 예

질의문 Q1. 컴퓨터과학과 소속의 학생정보와 학과이름 및 이수학점을 출력하는 '컴퓨터과학과_학생'뷰를 생성하시오.

1. 테이블 생성
   

동일한게 학생번호밖에 없으므로 굳이 ON으로 JOIN조건을 명시해줄 것 없이 NATURAL JOIN 사용하면 됨.

2. 해당 테이블을 가지고 있는 뷰를 생성.
   

뷰를 이용한 검색 및 수정

뷰를 이용한 데이터 검색

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

실행 시 나는 FROM에 뷰 이름만 입력해주면 됨. 실제 실행에서는 WHERE절에 내가 입력한 조건 뿐 아니라 뷰의 조건역시 AND와 함께 사용 됨.

뷰를 이용한 데이터 조작

1. 뷰에 대한 INSERT문은 원본 테이블에서 실행
   • PRIMARY KEY, NOT NULL등의 제약사항이 위배되는 경우 삽입이 불가능
     ex. 학생번호가 겹치는 경우 => PRIMARY KEY에 위배 => 작동안함
   • 원본 테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하는 경우 실행 불가능.
     원본테이블에는 있는데 뷰에는 없는 컬럼값 => 집어넣을 수 없음. 뷰에 없는 컬럼값을 INSERT해서 원본값에 넣는 것 자체가 불가능함.
   • 조인 질의 또는 그룹 질의가 적용된 뷰는 데이터 삽입 및 수정이 불가능
   • WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없지만 뷰에 맞지 않는 조건일 경우 실행 불가능.