고급 SQL 이번에는 고급 SQL 질의에 대해서 실습을 하겠습니다. Database Laboratory.

Presentation on theme: "고급 SQL 이번에는 고급 SQL 질의에 대해서 실습을 하겠습니다. Database Laboratory."— Presentation transcript:

1 고급 SQL 이번에는 고급 SQL 질의에 대해서 실습을 하겠습니다. Database Laboratory

2 차례 join subquery exists unions IF ~ ELSE CASE Inner / Outer join
고급 SQL에서 다룰 내용은 내부/외부 조인과 질의 내 네스팅된 질의를 수행하는 서브쿼리에 대해서 살펴본 후에 EXISTS UNION, IF~ ELSE, CASE 키워드를 이용한 질의를 사용 예제를 통해서 확인하는 과정으로 진행 하겠습니다. Database Laboratory

3 join ER-Diagram Account, branch, account_branch Table
그림과 같이 acccount와 branch 개체와 관계 릴레이션인 account_branch 테이블이 다대일 매핑 관계를 가지고 있는 ER 다이어그램 입니다. account 테이블에는 account_number를 주 키로, branch_name과 balance가 애트리뷰트로 설정되어 있고 branch 테이블에는 branch_name이 주 키로, branch_city, assets이 애트리뷰트로 설정되어 있습니다. 두 엔티티 간에 관계 역할을 하고 있는 account_branch 릴레이션은 각 테이블의 주 키를 애트리뷰트로 가지고 있습니다. 관계를 가진 테이블에 Database Laboratory

4 join data 입력 branch account account_branch Database Laboratory
왼쪽은 account table, 오른쪽은 branch table, 그리고 가운데는 account_branch 관계테이블에 데이터를 입력한 결과를 보여주고 있습니다. 여기서, account_branch 는 account와 branch의 관계 테이블이지만 관계라는 것은 개념일 뿐 모두 동일한 테이블이기 때문에 account_branch 테이블에 들어가서 데이터 값을 넣어주지 않으면 가운데의 쿼리문을 작성하고 실행했을 때 결과 값이 나오지 않습니다. account_branch Database Laboratory

5 join SELECT * FROM account, branch 결과 Database Laboratory
다음은 SELECT * FROM account, branch와 같은 질의를 했을 경우, account와 branch 테이블을 조인한 결과를 그림과 같이 볼 수 있습니다. 결과 값이 길어서 생략하였습니다. Database Laboratory

6 join inner join과 outer join 비교 내부조인 (inner join) : 외부조인(outer join) :
기준이 되는 table이 양쪽 모든 테이블에 공통되는 컬럼의 값을 비교해서 테이블을 통합한 다. 두 테이블에서 조인조건을 만족하는 행만 출력한다. 외부조인(outer join) : 어느 한쪽 table이 기준이 되어 기준이 되는 테이블에서 모든 data를 반환하고, 기준이 되지 않는 쪽의 table에서는 조건을 만 족하는 data를 반환하게 되는 join을 말한다. 만약 기준이 되지 않는 table에서 data 가 없는 경우에는 해당 column에 NULL 값을 반환하게 된다. left join right join 앞에서 조인결과를 살펴 봤듯이 상당한 양의 결과를 반환하고 있습니다. 여러 가지 join 중에서 내부조인과 외부조인에 대해서 알아보도록 하겠습니다. - 내부조인은 모든 테이블에 공통되는 컬럼의 값을 비교해서 테이블을 통합합니다. SQL Server는 두 테이블에서 join 조건을 만족하는 행만 출력합니다. - 외부조인은 내부조인처럼 조인조건을 만족하는 행을 통합한 결과 집합을 출력합니다. 그러나 해당조건을 만족하는 행과 함께 left 및 right 외부조인은 테이블들 중 하나의 테이블에 일치하지 않는 행들을 추가로 반환합니다. 소스테이블의 컬럼은 데이터를 담고 있지만, 다른 테이블의 컬럼은 NULL 값을 담고 있습니다. 또한 조인된 테이블 중 하나에서 데이터의 완전한 리스트가 필요할 때 left 또는 right join을 사용할 수 있습니다. Database Laboratory

7 join = = inner join (내부조인) SELECT 절에서 가져올 column을 정의
별칭 사용 가능 FROM 절에서 필요한 table을 나열한 후 WHERE 조건에서 공통 조건을 명시 이름이 같은 컬럼이 1 개일 경우 WHERE a.X = b.X = INNER JOIN, ON a.X = b.X where 절에 조건을 주어 가져올 row 수를 제한하는 내부 조인은 가져올 컬럼을 정의하고 from절에서 필요한 table을 나열한 후에 where 조건에서 이름이 같은 컬럼을 명시하여 질의를 할 수 있습니다. 같은 이름의 컬럼이 1 개일 경우는 where 절에 공통 조건을 명시한 것은 inner join, on 다음에 공통 조건을 사용하면 결과 값이 같다. 또한 같은 이름의 컬럼이 2 개 이상일 경우는 on 다음에 and 로 연결한 것이 using 다음에 공통 컬럼 이름을 나열하면 결과가 같다. 실습에서는 컬럼이 1 개일 경우에만 실습을 하겠습니다. 이름이 같은 컬럼이 2 개일 경우 INNER JOIN, ON (a.X = b.X) and (a.Y = b.Y) = INNER JOIN, USING(X,Y) Database Laboratory

8 join where 절에서 공통 조건을 사용한 Inner join
branch와 account 테이블에 공통되는 컬럼의 값을 비교 해서 테이블을 통합한다 // column 별칭 사용 // 공통조건 명시 where 절에서 공통 조건을 사용한 내부조인에 대해서 살펴보면 branch와 account 테이블에 공통되는 컬럼 branch_name을 비교해서 테이블을 통합하는 질의와 결과를 보여주고 있습니다. branch를 a로 account를 b로 별칭을 만든 후 branch와 account를 내부 조인하는데 공통조건이 branch의 branch_name과 account의 branch_name이 같으며 where 절에는 branch_name을 오름차순으로 정렬하라는 질의를 수행한 것입니다. Database Laboratory

9 join inner join, ON 을 사용한 내부조인 // inner join 사용 Database Laboratory
다음은 inner join 과 on keyword를 사용하여 내부조인을 한 경우 입니다. 결과는 같은 것을 확인 할 수 있습니다. Database Laboratory

10 join LEFT Outer join (외부조인) 왼쪽에 있는 branch table이 기준
branch table에서 모든 data를 반환하고, branch와 account의 지사 이름이 일치하는 결과를 반환한다 기준(branch) LEFT OUTER JOIN은 왼쪽에 있는 branch 테이블이 기준이며, branch table에서 모든 데이터를 반환하고, branch와 account의 branch_name이 일치하는 결과를 반환합니다. 기준 테이블인 branch의 branch_name의 데이터들에 account 테이블의 branch_name 데이터들이 모두 포함되므로 NULL 값은 출력되지 않았습니다. Database Laboratory

11 join LEFT join (LEFT Outer join과 같은 의미) Database Laboratory

12 join RIGHT Outer join 오른쪽에 있는 account table이 기준
account table에서 모든 data를 반환하고, branch와 account의 지사 이름이 일치하는 결과를 반환한다. 기준(branch) LEFT Outer join과 반대로 오른쪽에 있는 account 테이블이 기준이며, account 테이블에서 모든 data를 반환하고 branch와 account의 지사 이름이 일치하는 결과를 반환하고 있습니다. 기준이 되지 않는 테이블 branch에 data : North Town, Pownal 이 없기 때문에 NULL 값이 반환된 것을 확인 할 수 있습니다 . Database Laboratory

13 join RIGHT join (RIGHT Outer join과 같은 의미) Database Laboratory

14 join Management Studio를 이용한 inner join
1. [account Table]-[우측 마우스 클릭-상위 200개 행 편집] Management Studio를 이용한 내부조인방법에 대해서 알아보도록 하겠습니다. account table 에서 우측 마우스 클릭해서 상위 200개 행 편집을 누르면 위와 같은 그림이 생성됩니다. 그 상태에서 오른쪽 버튼을 다시 클릭하여 다이어그램으로 들어갑니다. 2. 다이어그램 선택 Database Laboratory

15 join Management Studio를 이용한 inner join
3. [오른쪽 마우스 클릭]-[테이블 추가 클릭]-[테이블추가]- [branch 테이블 추가 클릭]-[닫기] 그럼 다음 다시 오른쪽 마우스를 클릭하여 테이블 추가를 선택하면 ‘테이블 추가’ 가 생성됩니다. 여기서 branch 테이블을 선택하고 추가를 누른 후 닫기 버튼을 클릭합니다. 그러면, Database Laboratory

16 join Management Studio를 이용한 inner join Database Laboratory
위의 그림과 같이 생성됩니다. Database Laboratory

17 join Management Studio를 이용한 inner join(표 형태와 질의어 창 생성)
다시 오른쪽 버튼을 누르고 [창]-[조건]을 클릭하시면 표 형태가 생성됩니다. 그리고 Database Laboratory

18 join Management Studio를 이용한 inner join (표 형태와 질의어 창 생성)
그리고 [창]에서 [SQL]을 누르시면 질의어가 생성되었음을 알 수 있습니다. 질의어를 보시면 ON 다음에 branch_name이 같은 공통조건으로 생성된 것을 확인할 수 있습니다. Database Laboratory

19 join Management Studio를 이용한 inner join(출력 형태 설정)
diagram에서 출력할 column을 선택하거나 표 형태에서 테이블, 열, 정렬순서 등을 설정한 뒤 (실행 버튼)을 클릭하면 Diagram에서 출력할 column을 선택하거나 표 형태에서 테이블, 열, 정렬순서 등을 설정한 뒤에 실행버튼을 클릭하면 Database Laboratory

20 join Management Studio를 이용한 inner join(결과) Database Laboratory
쿼리 분석기에 내부 조인한 질의어와 같은 질의를 보여주며 결과 또한 같다는 것을 확인 할 수 있습니다. 지금까지 Management Studio를 사용한 내부조인 실습이었습니다. Database Laboratory

21 Union Union 연산자로 합집합 만들기
account, branch 테이블의 SELECT문 결과를 단일 결과 집합으로 보고 싶을 때 사용한다 유니온 연산자로 합집합을 만들 수 있는데, 이 연산자는 account, branch 테이블의 select 문 결과를 단일 결과 집합으로 보고 싶을 때 사용할 수 있습니다. 질의는 account 테이블에서 잔고가 1000인 모든 컬럼과 branch 테이블에서 자산이 인 결과를 UNION ALL을 사용하여 합집합 했을 경우 출력결과를 확인 할 수 있습니다. Database Laboratory

22 subquery subquery 이용하기
서브 쿼리는 단일값을 반환하고 SELECT, INSERT, UPDATE, DELETE 문이나 다른 하위 쿼리 내부에 중첩된 SELECT 쿼리이다 서브 쿼리는 단일 값을 반환하고 SELECT, INSERT, UPDATE, DELETE 문이나 다른 하위 쿼리 내부에 중첩된 SELECT 쿼리를 말합니다. 잔고가 400 달러 이상을 가진 지사이름을 출력한 서브쿼리를 먼저 수행하고 이 결과에 한해서 내포한 쿼리에서는 branch 테이블의 branch_name 과 assets를 출력하라는 질의문을 수행한 결과를 오른쪽 그림에서 보여주고 있습니다. Database Laboratory

23 subquery EXISTS와 서브 쿼리 EXISTS를 사용하는 것은 서브 쿼리에서 주어진 조건을 만족하는 데이터를 판단하여 존재하는 값들만 검색값으로 반환한다 exists와 서브 쿼리를 함께 사용할 수도 있는데 exists를 사용하는 것은 서브 쿼리에서 주어진 조건을 만족하는 데이터를 판단하여 존재하는 값들만 검색값으로 반환하는 역할을 합니다. 서브 쿼리에서 질의를 먼저 수행 후 내포 한 쿼리를 수행하는 순서로 수행됩니다. 잔고가 400 달러 이하인 branch_name의 결과값에 존재하는 branch_name 과 assets를 출력하라는 질의를 수행하면 Horseneck와 Bennington 시가 출력되었습니다. Database Laboratory

24 IF ~ ELSE 구문 IF 조건 { sql_statement | statement_block } [ELSE
IF~ ELSE 구문을 사용하여 질의를 보여주고 있습니다. IF 조건 다음에는 질의문 또는 질의 문 블록이 한번 이상 반복 될 수 있으며 ELSE 문은 옵션이고 사용된다면 IF에서와 마찬가지로 질의문을 사용할 수 있습니다. 오른쪽 질의문은 account 테이블로부터 잔고의 평균이 300 달러 이상이면 branch 테이블로부터 assets를 출력합니다. 300 달러 미만을 경우에는 잔고가 부족하다라는 메시지를 출력합니다 Database Laboratory

25 CASE 여러 조건을 평가하고 각 조건에 맞는 단일 값을 반 환하는데 사용한다. Database Laboratory
case문에서는 여러 조건을 평가하고 각 조건에 맞는 단일 값을 반환하는데 사용할 수 있습니다. branch 테이블로부터 branch_name를 출력하고 assets를 신용등급으로 나타낼 수 있도록 질의문을 작성하였다 일 경우는 신용등급이 AAA이고 30000일경우는 신용등급이 D가 출력된 것을 볼 수있으며 when절에 명시하지 않았을 경우는 NULL 값이 출력 됩니다. 그럼 이번 주 온라인 실습을 마치겠습니다. 수고하셨습니다. Database Laboratory

26 Report 다음 페이지를 참조(column name, data type, length, key)하고 쿼리 분석기 를 반드시 사용하여 customer, loan, borrower table을 만들고 다음과 같은 질의를 한 결과를 출력하여 제출하시오. customer table에 column을 추가 하는 질의문과 customer table을 열었을 때 결과를 출력하는 질의를 작성하시오 branch_name이 P 로 시작하고 amount가 1500 이상인 loan_number를 출력하는 질의를 작성하시오 amount가 1500 이상이면 5% 이자율을, 1500 이하이면 무조건 3% 이자율을 적 용하였을 경우 한달 동안의 총합과 평균을 구하는 질의를 작성하시오 branch_name 이번주 리포트는 교과서를 참조(column name, data type, length, key)하고 쿼리 분석기를 반드시 사용하여 customer, loan, borrower table을 만들고 다음과 같은 질의를 한 결과를 출력하여 제출하시오. customer table에 column을 추가 하는 질의문과 customer table을 열었을 때 결과를 출력하는 질의를 작성하시오 branch_name이 P 로 시작하고 amount가 1500 이상인 loan_number를 출력하는 질의를 작성하시오 amount가 1500 이상이면 5% 이자율을, 1500 이하이면 무조건 3% 이자율을 적용하였을 경우 한달 동안의 총합과 평균을 구하는 질의를 작성하시오 Hallym Univ. DB Lab.

27 Report 만약 loan에서 amount의 평균이 1200 이상일 경우에 branch_name 과 loan_number를 출력하고 그렇지 않을 경우 에는 “대출할 수 없습니다” 라는 메시지를 출력하는 1. 질의 를 작성하고 2.질의 결과를 출력하시오. branch_name 이번주 사이버 강의 리포트는 4 주차 사이버 강의에서 생성한 테이블을 사용하면 됩니다. 만약 loan에서 amount의 평균이 1200 이상일 경우에 branch_name 과 loan_number를 출력하고 그렇지 않을 경우에는 “대출할 수 없습니다” 라는 메시지를 출력하는 1. 질의를 작성하고 2.질의 결과를 출력하면 됩니다. Hallym Univ. DB Lab.

28

29 레포트 제출 제출방법 실습 과정을 캡쳐 후 한글, word 파일에 캡쳐 부분에 대한 설명과 SQL구문에 간단한 주석을 넣어서 제출 하시기바 랍니다. 실습 화면 캡쳐 시 자신의 학번 데이터베이스가 보이도록 하시기 바랍니다. 레포트제출 제출기한 2013년 10월 16일 11시59분까지 Database Laboratory