학습목표 학습목표 이번 강좌에는 8장에서 학습한 기본 검색질의 보다 고급스런 질의 처리 방법들에 대하여 학습한다. 동일한 값을 갖는 레코드들을 그룹으로 묶어서 처리하는 그룹질의 수행방법과 그룹함수를 사용하여 간단한 통계처리를 하는 질의 수행방법 그리고 2개 이상의 테이블을.

Presentation on theme: "학습목표 학습목표 이번 강좌에는 8장에서 학습한 기본 검색질의 보다 고급스런 질의 처리 방법들에 대하여 학습한다. 동일한 값을 갖는 레코드들을 그룹으로 묶어서 처리하는 그룹질의 수행방법과 그룹함수를 사용하여 간단한 통계처리를 하는 질의 수행방법 그리고 2개 이상의 테이블을."— Presentation transcript:

1 학습목표 학습목표 이번 강좌에는 8장에서 학습한 기본 검색질의 보다 고급스런 질의 처리 방법들에 대하여 학습한다. 동일한 값을 갖는 레코드들을 그룹으로 묶어서 처리하는 그룹질의 수행방법과 그룹함수를 사용하여 간단한 통계처리를 하는 질의 수행방법 그리고 2개 이상의 테이블을 사용하는 질의 방법들에 대하여 학습한다. 또한 하나의 질의문 내부에 다른 질의문을 포함하는 형태의 부속질의 사용법에 대한 것도 학습 한다.

2 1. 그룹질의 사용법 익히기 그룹질의 같은 종류의 데이터를 묶어서 검색하는 질의
단순그룹질의와 그룹함수를 적용한 그룹질의가 있음 GROUP By절을 사용 WHERE절 사용 불가 조건 지정시 HAVING절을 함께 사용 함 구문형식 : SELECT [ DISTINCT ]  [그룹함수] 필드1 [,필드2,....]   FROM 테이블1 [, 테이블2, ....]   GROUP BY 필드1 [,필드2,...] [HAVING 조건] 그룹함수의 종류와 기능들 종류 사용 예 의미 sum sum(단가)  단가들의 합 avg avg(단가) 단가들의 평균 값 max max(단가) 단가들 중 최대값 min min(단가) 단가들 중 최소값 count count(단가) 단가들의 개수

3 그룹질의 그룹질의에 대한 개념도

4 그룹질의 실습에 사용된 예제 테이블

5 1.1 단순 그룹질의 동일한 값을 갖는 레코드들을 그룹으로 묶어서 검색질의를 수행 함 단순 그룹질의
동일한 값을 갖는 레코드들을 그룹으로 묶어서 검색질의를 수행 함 ■ 실습 : “상품 테이블에서 사이즈를 기준으로 그룹화하여 사이즈를 검색하시오.” ■ 명령 : SELECT 사이즈 FROM 상품 GROUP BY 사이즈 ■ 결과 : 연습 : “상품” 테이블에서 소코드를 기준으로 그룹화하여 소코드를 검색하시오. 정답 : SELECT 소코드 FROM 상품 GROUP BY 소코드

6 그룹 질의한 결과에 대해 조건을 지정하여 검색질의를 수행 함
HAVING절&그룹함수 1.2 HAVING절을 이용한 그룹질의 그룹 질의한 결과에 대해 조건을 지정하여 검색질의를 수행 함 ■ 실습 : “주문 테이블에서 고객ID를 기준으로 그룹화하여 고객ID별로 주문금액의 합이 원보다 큰 고객ID와 주문금액의 합을 ‘주문금액계’란 이름으로 검색하시오 .” ■ 명령 : SELECT  고객ID, SUM(주문금액) AS 주문금액계  FROM  주문  GROUP BY 고객ID  HAVING  SUM(주문금액) > ■ 결과 : 연습 : “주문” 테이블에서 상품코드를 기준으로 그룹화하여 상품코드별로 주문금액의 합이 만원보다 큰 상품코드와 주문금액합을 검색하시오. 정답 : SELECT  상품코드, SUM(주문금액) AS 주문금액합  FROM  주문  GROUP BY 상품코드  HAVING  SUM(주문금액) >

7 그룹단위로 묶어 그룹함수를 적용하여 검색질의를 수행 함
그룹 함수 질의 1.3 그룹함수 질의 그룹단위로 묶어 그룹함수를 적용하여 검색질의를 수행 함 그룹을 지정하지 않는 경우 전체를 한 그룹으로 작업 함 그룹함수의 종류 : SUM, AVG, MAX, MIN, COUNT함수가 있음 ■ 실습 : “소분류코드 테이블에서 대코드를 기준으로 그룹화하여 대코드별로 단가의 합계를 ‘단가합계’란 이름으로 검색하시오.” ■ 명령 : SELECT 대코드, SUM(단가) as 단가합계 FROM 소분류코드 GROUP BY 대코드 ■ 결과 : 연습 : “소분류코드” 테이블에서 업체코드를 기준으로 그룹화하여 업체코드별로 단가의 합계를 합계란 이름으로 검색하시오. 정답 : SELECT 업체코드, SUM(단가) AS 합계  FROM  소분류코드  GROUP BY 업체코드

8 2개 이상의 테이블을 사용하여 검색질의를 수행 함
2개 이상 테이블 이용 1.4 2개 이상의 테이블을 이용한 질의 2개 이상의 테이블을 사용하여 검색질의를 수행 함 종류 : UNION절, UNION ALL절, INTERSECT절,MINUS절 등이 있음 구문형식 :   SELECT  필드 FROM 테이블1 [WHERE 조건]     UNION   SELECT 필드 FROM 테이블2 [WHERE  조건]     [UNION ....][ORDER BY 필드1,...] ■ 실습 : “소분류코드 테이블에서 단가 이상에 해당하는 레코드의 소코드 필드 검색결과와 상품 테이블에서 재고수량이 15 이상에 해당하는 레코드의 소코드 필드를 검색한 결과를 통합하여 검색하시오” ■ 명령 : SELECT   소코드 FROM  소분류코드 WHERE  단가 >=            UNION            SELECT   소코드 FROM  상품 WHERE   재고수량 > 15

9 2개 이상 테이블 이용 ■ 결과 : 연습 : “고객”테이블에서 고객등급이 ‘실버’에 해당하는 고객의 고객ID필드를 검색한 결과와 ‘부가정보’테이블에서 성별이 ‘남’에 해당하는 레코드의 고객ID필드를 검색한 결과를 검색하시오 정답 : SELECT 고객ID from 고객 where 고객등급=‘실버’ union SELECT 고객ID from 부가정보 where 성별=‘남’

10 테이블의 관련성을 이용하여 다수의 테이블에서 검색질의를 수행 함
조인 2. 조인(Join)질의 익히기 테이블의 관련성을 이용하여 다수의 테이블에서 검색질의를 수행 함 FROM절에는 최소한 2개 이상의 테이블이 존재해야 함 WHERE절에는 테이블들의 행들이 공통적인 값을 가지는 필드를 조건필드로 사용 함 공통적인 값을 가지는 필드는 기본키(부모테이블)와 외래키(자식테이블)를 사용 함 조인의 종류 동등연산를 사용한 조인질의 등호(=)기호를 사용하여 조건값이 정확히 일치하는 경우에 사용하는 조인질의 Alias를 사용한 조인 질의 테이블이나 필드명에 별칭을 사용하여 조인 질의를 하는 경우에 사용 함 비-동등 조인 질의 등호(=) 기호를 제외한 다른 비교연산자를 사용하여 조인 질의를 하는 경우에 사용 함 외부 조인 질의 조인조건에 맞지 않는 필드값들도 검색결과에 포함시키는 경우 사용 함 셀프-조인 질의 동일한 테이블을 가지고 조인질의를 하는 경우에 사용 함

11 2.1 엔터프라이즈 관리자를 이용한 조인질의 관리자에서 조인질의
1. 엔터프라이즈 관리자-> 데이터베이스-> Manpower->테이블->고객 클릭-> 마우스 오른쪽버튼 클릭->테이블 열기->쿼리

12 관리자에서 조인질의 2. 화면의 빈 영역에서 마우스 오른쪽버튼 클릭->팝업 메뉴->테이블 추가

13 관리자에서 조인질의 3. 테이블추가 대화상자에서 부가정보 선택->추가->닫기 애니메이션 먼저 처리바람

14 관리자에서 조인질의 4. 검색을 원하는 필드들 체크표시->별칭들 모두 지우기->도구상자의 실행도구 클릭

15 동등(=)기호를 사용하여 검색질의를 수행 함
동등연산자 조인질의 2.2 동등연산자를 사용한 조인질의 동등(=)기호를 사용하여 검색질의를 수행 함 정확히 조건값이 일치하는 경우 사용하는 조인 질의 내부조인(Inner Join) 또는 이퀴조인(Equi Join)이라 함 구문형식 :    SELECT   테이블1.필드, 테이블2.필드,      FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.필드 = 테이블2.필드 ■ 실습 : “고객테이블과 부가정보테이블을 사용하여 두 개의 테이블에서 고객ID가 같은 레코드들에 대하여 조인 질의하여 고객 테이블의 모든 정보와 부가정보 테이블의 성별을 함께 검색하시오.” ■ 명령 : SELECT   고객.*, 부가정보.성별        FROM 고객 INNER JOIN 부가정보 ON 고객.고객ID = 부가정보.고객ID

16 동등연산자 조인질의 ■ 결과 : 연습 : “납풉업체”테이블에서 업체코드필드와 ‘소분류코드’테이블의 업체코드 필드를 조인하여 납품업체테이블의 업체코드,업체명,주소필드와 소분류코드 테이블의 소분류명,단가 필드를 검색하시오. 정답 : SELECT  납품업체.업체코드,납품업체.업체명, 납품업체.주소,소분류코드 소분류명, 소분류코드.단가 from 납품업체,소분류코드 where 납품업체.업체코드=소분류코드.업체코드

17 테이블이나 필드명에 별명(별칭)을 붙여서 사용
Alias사용한 조인질의 2.3 테이블의 Alias(별명)를 사용한 조인 테이블이나 필드명에 별명(별칭)을 붙여서 사용 질의에서 사용할 테이블의 실제 이름 대신에 사용할 새로운 테이블 이름을 지정하는 것 별명은 현재 별명을 사용하고 있는 검색 질의문 내에서만 유효 자신의 테이블을 조인 질의하는 경우에는 반드시 별명을 사용해야 함 구문형식 :    SELECT 별명1.필드, 별명2.필드,...... FROM 테이블1 별명1, 테이블2 별명2      WHERE 별명1.필드=별명2.필드 ■ 실습 : “고객 테이블의 별명은 cus로 부가정보 테이블의 별명은 det로 지정하여 두 개의 테이블에서 고객ID가 같은 레코드들에 대하여 고객ID, 성명, 주소, 직업, 가입일자를 조인질의 검색하시오.“ ■ 명령 : SELECT  cus.고객ID, cus.성명, cus.주소, det.직업, det.가입일자            FROM 고객 cus INNER JOIN 부가정보 det ON cus.고객ID = det.고객ID

18 Alias사용 조인질의 ■ 결과 : 연습 : “납풉업체”테이블을 sup로 ‘소분류코드’테이블을 dept로 별명을 정하여 업체코드 필드를 가지고 조인하여 업체코드,업체명, 담당자,소분류명필드를 검색하시오. 정답 : SELECT  sup.업체코드,sup.업체명, sup.담당자,dept.소분류명 from 납품업체 sup,소분류코드 dept where sup.업체코드=dept.업체코드

19 비-동등 조인 실습에 사용된 예제 테이블 학사 테이블 등급 기준표 테이블

20 2.4 비-동등 조인(Non-Equi Join)
비-동등 조인질의 2.4 비-동등 조인(Non-Equi Join) 동등 연산자(=)가 아닌 다른 비교 연산자를 사용하여 두 개 이상의 테이블에서 조인 질의 Non-Equi Join 또는 Cross join이라고도 함 구문형식 :   SELECT 별칭1.필드1, 별칭1.필드2, 별칭2.필드1       FROM 테이블1 별칭1 CROSS JOIN 테이블2 별칭2       WHERE (별칭1.필드 BETWEEN 별칭2.필드2 AND 별칭2.필드3) ■ 실습 : “학사테이블의 취득점수가 속하는 등급을 등급기준표 테이블의 하한 필드값과 상한 필드값을 조건으로 참조하여 조건에 해당하는 레코드들의 번호, 이름, 취득점수, 등급필드를 검색하시오. “ ■ 명령 : SELECT A.번호, A.이름, A.취득점수, B.등급         FROM  학사 A CROSS JOIN 등급기준표 B         WHERE A.취득점수 BETWEEN B.하한 AND B.상한

21 비-동등 조인질의 ■ 결과 :

22 조인 질의 조건에 맞지 않는 필드들도 검색 결과에 함께 포함하고자 할 때 사용
외부 조인 2.5 외부 조인(Outer Join) 조인 질의 조건에 맞지 않는 필드들도 검색 결과에 함께 포함하고자 할 때 사용 외부조인의 종류 왼쪽 외부 조인(Left Outer Join) : LEFT OUTER JOIN을 기준으로 왼쪽에 기술되어 있는 테이블이 주체가 됨 오른쪽 외부 조인(Right Outer Join) : 오른쪽에 기술된 테이블이 주체가 됨 구문형식 :    SELECT 테이블1.필드1, 테이블2.필드, FROM      테이블1 [LEFT OUTER JOIN | RIGHT OUTER JOIN]      테이블2 ON 테이블1.필드 = 테이블2.필드 ■ 실습 : “상품 테이블과 주문 테이블을 상품코드를 이용하여 왼쪽 외부조인 질의하여 상품테이블에서 상품코드, 색상코드, 사이즈 필드를 주문 테이블에서 고객ID, 주문일자 필드를 검색하시오.” ■ 명령 : SELECT 상품.상품코드, 상품.색상코드, 상품.사이즈, 주문.고객ID, 주문.주문일자            FROM 상품 LEFT OUTER JOIN 주문 ON 상품.상품코드 = 주문.상품코드

23 외부 조인 ■ 결과 : 연습 : “상품”테이블과 “소분류코드”테이블을 소코드 필드를 이용하여
오른쪽외부 조인하여 상품테이블에서 소코드,사이즈필드를 소분류코드 테이블에서 단가,업체코드필드를 검색하시오. 정답 : SELECT 상품.소코드, 상품.사이즈, 소분류코드.단가,소분류코드.업체코드            FROM 상품 RIGHT OUTER JOIN 소분류코드 ON 상품.소코드 = 소분류코드.소코드

24 외부 조인 엔터프라이즈관리자를 이용한 외부조인 엔터프라이즈 관리자 실행->고객 테이블 클릭-> 쿼리창 표시
마우스 오른쪽 버튼 클릭->부가정보 테이블 추가 다이아몬드 모양에서 마우스 오른쪽 버튼 클릭 팝업 메뉴-> 외부 조인 방법선택

25 외부 조인 5. 쿼리창에서 검색을 원하는 필드들 체크 표시-> 별칭을 모두 삭제 6. 도구상자의 실행 도구 클릭

26 동일한 테이블을 가지고 조인 질의를 하는 경우에 사용 함
셀프 조인 2.6 셀프 조인 (Self-Join) 동일한 테이블을 가지고 조인 질의를 하는 경우에 사용 함 셀프 조인 또는 자기 조인이라 함 동일한 테이블에 대해 반드시 서로 다른 2개의 별명(alias)을 사용해야 함 구문형식 :    SELECT 별명1.필드, 별명2.필드,......       FROM 테이블1 별명1 INNER JOIN 테이블1 별명2      ON 별명1.필드1 = 별명2.필드2 ■ 실습 : “주문납품 테이블에서 주문일자와 납품일자가 동일한 레코드들을 모두 조인 질의하여 모든 필드를 검색하시오.” ■ 명령 : SELECT  a.*  FROM 주문납품 a INNER JOIN 주문납품 b        ON a.주문일자 = b.납품일자

27 셀프 조인 ■ 결과 : 주문납품 테이블

28 3. 부속질의(Subquery) 익히기 부속 질의 명확하지 않는 기준을 사용하여 데이터를 검색하고자 하는 경우에 주로 사용 함
명확하지 않는 기준을 사용하여 데이터를 검색하고자 하는 경우에 주로 사용 함 한 개의 질의문내에 또 다른 질의문을 포함하는 형태의 질의를 말함 여러개의 부속질의를 사용 할 때에는 AND 또는 OR과 같은 논리연산자를 함께 사용 함 관계연산자(=, >, >=, <, <=, <>)와 IN, NOT IN과 같은 연산자도 함께 사용이 가능 함 SELECT문의 조건절(WHERE)에 부속질의를 사용하는 방식이 일반적임 부속 질의는 괄호를 사용해야 하며 ORDER BY절은 함께 사용할 수 없음 부속질의에는 그룹함수(SUM, AVG, MIN, MAX)나 GROUP BY절을 HAVING절과 함께 사용할 수 있음 구문형식 :   SELECT  필드1, 필드2,....  FROM   테이블1         WHERE  (조건 (SELECT  필드1 FROM 테이블2                                WHERE  조건))

29 ■ 실습 : “색상이 ‘그린’에 해당하는 레코드들을 상품테이블에서 모두 검색하시오.”
부속 질의 ■ 실습 : “색상이 ‘그린’에 해당하는 레코드들을 상품테이블에서 모두 검색하시오.” ■ 명령 : SELECT   * FROM  상품 WHERE           (색상코드 = (SELECT   색상코드  FROM  색상분류 WHERE  색상 = '그린')) ■ 결과 : 연습 : 고객의 성명이 강수찬인 회원의 고객ID, 마일리지, 직업 ,가입일자 정보를 부가정보 테이블에서 검색하시오. 정답 : SELECT 고객ID,마일리지,직업,가입일자 FROM 부가정보 WHERE 고객ID=(SELECT 고객ID FROM 고객 WHERE 성명=‘강수찬’)

30 해당하는 레코드들의 상품코드, 사이즈, 재고수량을 검색하시오.
부속 질의 ■ 실습 : “상품 테이블에서 소분류명이 ‘가죽쟈켓’ 이면서 색상이 ‘블랙’에 해당하는 레코드들의 상품코드, 사이즈, 재고수량을 검색하시오.” ■ 명령 : SELECT 상품코드, 사이즈, 재고수량 FROM 상품 WHERE (소코드 = (SELECT 소코드 FROM 소분류코드 WHERE 소분류명 = '가죽쟈켓'))               AND             (색상코드 = (SELECT 색상코드 FROM 색상분류 WHERE   색상 = '블랙')) ■ 결과 : 연습 : 상품 테이블에서 소분류명이 ‘사파리’ 이면서 색상이 ‘베이지’에 해당하는 레코드들의 상품코드, 사이즈, 재고수량을 검색하시오. 정답 : SELECT 상품코드, 사이즈, 재고수량 FROM 상품 WHERE (소코드 = (SELECT 소코드 FROM 소분류코드 WHERE 소분류명 = ‘사파리'))    AND  (색상코드 = (SELECT 색상코드 FROM 색상분류 WHERE   색상 = '베이지'))

31 GROUP BY 내부조인(Inner Join)
단원 정리 단원정리 1 ★ 동일한 값을 갖는 레코드들을 그룹으로 묶어 검색하고자 할 때 사용 절을 한다.  ? GROUP BY 단원정리 2 ★ 조인의 종류로는 , 비-동등조인(Non-Equi Join), 외부조인(Ounter Join), 자기조인(Self Join)등이 있다. ? 내부조인(Inner Join)

32 부속질의 SUM AVG 단원 정리 단원정리 3 질의라 한다. ? 단원정리 4 ? ?
★ 한 개의 질의문내에 또 다른 질의문을 사용하는 질의어 방식 질의라 한다. ? 부속질의 SUM 단원정리 4 ★ 필드값의 합계를 구하는 이고 필드값의 평균을 구하는 함수는 이다. ? ? AVG