주안점 : 서브쿼리(sub Query), 조인(join)
보기문제1) 서브쿼리(where)
titles 테이블의 평균을 구한 후, 그 평균 이상의 데이터의 모든 내용을 출력하는 쿼리문을 작성하십시오.
답)
select * from titles
where price >= (select avg(price) from titles);
참고) 서브 쿼리도 함수를 사용하는 것과 같은 맥락이라고 생각한다면 어려운 개념은 아닙니다. 함수가 결과값을 돌려주는 것과 같이 ‘>=’ 이후의 서브 쿼리도 결과값을 반환합니다.
보기문제2) 서브쿼리(from, inline-view)
titles 테이블에서 실제 존재하는 price 라는 필드의 구문에서
15달러 초과의 데이터를 가져온 후 이것을 virtual_price 라는 가상 컬럼으로 명명(이름붙임) 하십시오.
답)
select avg(price) from
(select * from titles where price > 15)
as virtual_price;
참고) from 구문 안에 있는 서브쿼리는 뷰(view)처럼 취급되기 때문에 inline-view 라고 한다. MS SQL에서는 인라인 뷰에 “as”를 필히 삽입합니다.
보기문제3) 서브쿼리(group by / having)
select title, type, max(price) as '최고가격'
from titles
group by title, type
having max(price) > (select avg(price) from titles);
/*
잊지 말것은 group by 사용시 select문(메인 쿼리)에서
group by 에서 언급한 필드순대로 기입하지 않으면 에러가
난다는 것을 기억해야 한다. 위의 상황에서 만약 title, type의 순서가
바뀌거나 존재하지 않게 된다면 오류가 나게 된다.
*/
보기문제4) 내부 조인(Inner Join)
# 조인(Join) : 다수의 테이블이나 뷰(view)를 연결하여 원격 데이터들을 한꺼번에 묶어서 다루고자 할 때 사용하는 것. 테이블과 뷰(view)간에도 연결이 가능하다.
(외래 키(foreign key)를 이용하여 다수의 테이블에서 데이터를 한꺼번에 검색할 수 있는 방법)
# 내부 조인(inner join) : 두 테이블의 값이 일치하는 데이터를 가지고 온다.
exec sp_help titles;
exec sp_help publishers;
/* 쿼리 분석기에서 titles 테이블의 SQL 구조를 살펴보면 publishers 테이블과 외래 키 pub_id로 관계가 이루어져 있는 것을 확인할 수 있다. */
.......(전략).....FOREIGN KEY ( pub_id ) REFERENCES publishers ( pub_id )
이 상황을 숙지한 상태에서 교과서 p.154를 보도록 한다.
select * from titles;
select * from publishers;
실제로 확인해 보면 pub_id를 이용하여 두 테이블 데이터를 연결할 수 있음을 확인할 수 있다.
select title, price, pub_name
from titles, publishers
where titles.pub_id = publishers.pub_id; -- MS SQL식 문법(T-SQL)
select title, price, pub_name
from titles inner join publishers
on titles.pub_id = publishers.pub_id; -- ANSI 문법
select title, price, pub_name
from titles join publishers
on titles.pub_id = publishers.pub_id; -- ANSI 문법 “inner”를 생략할 수 있다.
보기문제5) 내부 조인(inner join)과 별칭(alias)
# “qty” 라는 필드는 titles 테이블에는 없고 sales 테이블에는 있다. 바로 이러한 없는 부분들을 다른 테이블에서 가지고 오는 것이다.
select titles.title_id, qty, title
from titles inner join sales
on titles.title_id = sales.title_id;
select t.title_id '타이틀 아이디', qty '판매량', title '타이틀'
from titles as t join sales as s
on t.title_id = s.title_id;
-- 알리아스(alias)는 간단하게 문장을 쓰기 위한 별명(nickname)이라고 보면 된다.
-- 이때까지 우리가 “가상 컬럼”이라고 사용하였던 “as~” 라고 사용했던 것이 바로 별칭(alias)이다. 위에서 “from titles t”를 “from titles as t”라고 써도 된다.
보기문제6) 외래 키(foreign key)
sales 테이블의 외래키 제약 조건을 살펴보도록 한다.(쿼리 분석기에서 확인할 것)
ex) ...(전략).....foreign key (pub_id) references publishers ( pub_id );
앞 부분에 constraint 라는 제약조건을 선언하는 부분이 나올 수 있다. 이에 대해서는 추후에 언급하도록 한다.
# 외래 키의 조건(중요)
(1) 부모 테이블의 참조키(referential key) 기본 키(primary key) 또는 unique 제약조건(constraint)이 있는 필드이거나 고유 인덱스를 가져야 한다.
(2) 자식 테이블의 외래키(foreign key)는 부모 테이블의 참조 키와 자료형(데이터 형식)이 동일해야 한다.
(3) 부모 테이블과 자식 테이블은 같은 서버의 같은 데이터베이스에 저장되어 있어야 한다.
보기문제7) 외부 조인(outer join) (left outer join)
# 외부 조인은 어느 한쪽의 데이터를 모두 가져온다. 동일할 때 데이터를 가져오는 inner join과 다른 것이 이 점이다.
# Left Join은 왼쪽 테이블을 기준으로 하여 조인하는 것을 말한다.
오른쪽 테이블에서 일치하는 값이 없어도 왼쪽 테이블의 데이터를 모두 가져 온다.
select t.title_id, qty, title
from titles t left outer join sales s
on t.title_id = s.title_id; -- 여기서 outer는 생략할 수 있다.
-- NULL 값을 가진 두 개의 레코드가 교과서에서 언급한 판매된 적이 없는 책을 말하는 것이다. 보통 이러한 책들은 서점에서도 출판사나 총판 등에 반품 조치하게 된다. 바로 이럴 때 이용할 수 있는 것이다.
보기문제8) 외부 조인(outer join) (right outer join)
# 오른쪽 테이블을 기준으로 조인한다. 왼쪽 테이블에 일치하는 값이 없더라도 오른쪽 테이블의 데이터를 모두 가져 온다.
select t.title_id, qty, title
from titles t right outer join sales s
on t.title_id = s.title_id; -- 여기서 outer는 생략할 수 있다.
-- 왼쪽 조인과 오른쪽 조인을 한꺼번에 실행하여 보면 차이를 한눈에 알 수 있습니다.
보기문제8) 서브쿼리(in)
교과서 참조할 것.
