조인 문을 만든 과정
- 조인 문을 위한 사전 준비
조인문을 생성하는 과정
step 1. 지문에서 검색 대상과 조건을 구분한다.
- 이를 통해 일단 select 절, where 절, order by 절을 구성한다.
- select 절은 step 5에서 작성자의 의도에 따라 추가될 수 있다.
step 2. select 절과 where 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
step 3. 테이블간의 관계를 확인한다.
- 관계를 확인하는 단계에서 테이블이 추가될 수 있다.
step 4. 조인 조건을 기술한다.
step 5. 전체 문장을 다듬는다.
예제 1. 위에 제시한 단계별로 지문으로부터 SQL문을 만들어 보자.
화학과 학생의 일반화학 기말고사 점수를 검색한다.
step 1. 지문에서 검색 대상과 조건을 찾는다.
'화학과 학생의 일반 화학 기말고사 점수; 지문에서 검색 컬럼과 조건을 찾는다
- 검색 대상
- 기말 고사 점수 : result
- 조건
- 화학과 학생 : major='화학'
- 일반 화학 과목 : cname='일반 화학'
select
result
from
where major = '화학' and cname = '일반화학'
step 2. select 절과 where 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
select 절과 where 절에 사용된 컬럼이 포함된 테이블을 from 절에 기술한다.
- result : score 테이블
- major : student 테이블
- cname : course 테이블
select
result
from
student, course, score
where major = '화학' and cname= '일반화학'
step 3. 테이블간의 관계를 확인한다.
student, course, score 테이블의 관계가 끊어지지 않도록 관계를 확인한다.
student
- sno - score - cno - course
- 테이블간의 관계가 끊어지는 경우 중간에 새로운 테이블을 삽입해서 관계가 끊어지지 않도록 조치한다.
select
result
from
student, course, score
where major='화학' and
cname='일반화학'
step 4. 조인 조건을 기술한다.
from절에 쓰인 테이블 간에 조인 조건을 기술한다.
select
result
from
student, course, score
where
student.sno=score.sno
and
course.cno=score.cno
and major = '화학' and cname='일반화학'
step 5. 전체 문장을 다듬는다.
step 4.까지 조인 문장이 완성 되었으나 select 문을 읽을 때 해석을 돕기 위해 새로운 컬럼을 select 절에 추가하거나, 별명을 지정하거나 등의 부가적인 요소들을 점검한다. 추가된 내용 만들어진 select 문의 논리적인 구성에 영향을 끼치지 않아야 한다.
가능한 결과 값만으로 지문의 요구 조건을 표현 할 수 있어야 한다. 예를 들어 result 만을 검색하면 누구의 성적인 확인이 어렵다.
select
major, cname, sno, sname, result
from
student, course, score
where
student.sno=score.sno
and
course.cno=score.cno
and major='화학' and cname='일반화학'
4학년 학생이 수강하는 과목을 강의하는 교수의 명단을 검색한다.
step 1. 검색 대상과 조건을 찾는다.
- 검색 대상
- 교수 명단 : pname
- 조건
- 4학년 학생 : syear=4
select
pname
from
where
syear=4;
step 2. select 절과 where 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
- pname : professor
- syear : student
select
pname
from
student, professor
where
syear=4;
step 3. 테이블간의 관계를 확인한다.
student
- ??? - professor
student와 professor 테이블 둘 사이에는 직접적인 관계가 없다.
student
- eno - score - cno - course - pno - professor
student와 professor의 관계를 위해서 score와 course 테이블이 필요하다
select
pname
from
student, course, score, professor
where
syear = 4;
step 4. 조인 조건을 기술한다.
4개 테이블간의 조인 조건을 기술한다.
select
pname
from
student, course, professor
where
student.sno=score.sno
and
course.cno=score.cno
and
course.pno=professor.pno
and
syear -4;
step 5. 전체 문장을 다듬는다.
학생들은 각 과목을 혼자 수강 하는 것이 아니라 여러 명이 수강하는 것이므로 pname은 수강인원수만큼 중복된다. 그러므로 distinct 추가한다.
- 문장을 다듬기 전에 일단 실행해보면 어떻게 수정해야 하는지 결정하기 쉽다.
필요하다면 별명을 추가하거나 컬럼을 추가할 수 있다.
from 절에 테이블에 별명이 정의 되어 있다면 문장전체를 이를 적용한다.
select distinct professor.pno, pname "교수 명단"
from student, course, score, professor
where
student.sno=score.sno
and
course.cno=score.cno
and
course.pno=professor.pno
and
syear = 4;
또는
select distinct p.pno, pname "교수 명단"
from
student s, course c, score r, professor p
where
s.sno=r.sno
and
c.cno=r.cno
and
p.pno=p.pno
and
syear=4;
자기 참조 조인 (self join)
select 별명1.컬럼1, … 별명2.컬럼1...
from 테이블 별명1, 테이블 별명2,….
where 조인_조건
and 일반_조건
예제 2. 각 사원을 관리하는 사수의 이름을 검색한다.
select
e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
from
emp e1, emp e2
where
e1.mgr = e2.eno;
외부 조인(outer join)
select 테이블1.컬럼, ...테이블2.컬럼,…
from 테이블1, 테이블2, …
where 조인_조건(+)
and 일반_조건
예제 3. 각 부서별로 사원을 검색한다. (외부 조인과 일반 조인 결과물의 비교)
select d.dno 부서번호, dname 부서명, ename 사원명
from
dept d, emp e
where
d.dno = e.dno
order
by 1;
select d.dno 부서번호, dname 부서명, ename 사원명
from
dept d, emp e
where
d.dno = e.dno(+)
order
by 1;
(+)는 외부 조인을 했을때 데이터가 없을 수도 있는 쪽 join 컬럼에 추가해준다.
실습
1. 학생 중에 동명이인을 검색한다.
select distinct st1.sno, st1.sname, st2.sno, st2.sname
from student st1, student st2
where st1.sno != st2.sno and st1.sname=st2.sname;
# != 같지 않다라는 뜻이다.
처음하는 개념이라 한참 헤맸다.
2. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색한다.
select
c.cname, p.pname
from
course c, professor p
where
p.pno=c.pno
order
by p.section;
3. 이번 학기 등록된 모든 과목과 담당 교수를 학점 순으로 검색한다.
select
distinct c.cname, p.pname, s.result
from
professor p, course c, score s
where
p.pno=c.pno
and
c.cno=s.cno
order
by s.result desc;
4. 직원 중에 자신의 관리자보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색한다.
select
e1.ename, e1.sal, e2.ename, e2.sal
from
emp e1, emp e2
where
e1.mgr = e2.eno
and
e1.sal > e2.sal;
5. 교수의 정보와 교수가 담당하는 과목명을 검색한다.
select
p.*, c.cname
from
professor p, course c
where
p.pno=c.pno;
6. 직원과 사수의 명단을 검색한다. 단 직원 명단은 모든 직원 명단이 출력되어야 한다.
select e1.ename 직원, e2.ename 사수
from
emp e1, emp e2
where e1.mgr = e2.eno(+);
# (+)를 넣어줘야 사수가 없는 사람도 출력 될 수 있다.
댓글 없음:
댓글 쓰기