SQL-모델링, join

* 모델링

일반 컬럼들이 프라이머리 키 컬럼에 함수적 종속관계에 있음.

언어적으로 표현하면 DNO -> (DNAME, LOC) 또는 DNO는 *(*는 일반 컬럼)를 식별할 수 있다. 

 

* 등가 조인과 비등가 조인

SELECT 테이블1.컬럼, 테이블2.컬럼, ... FROM 테이블1, 테이블2, ...WHERE 조인_조건 AND 일반_조건

- SELECT절에 테이블명을 적어줘야 하지만 참조하려는 테이블중 컬럼명이 유일할 경우 테이블 명 생략 가능

- FROM절에 테이블 2개 이상이 오면 JOIN절

- JOIN 조건의 갯수는 테이블이 n개라고 가정하면 최소 n-1개 이상이다.

- 등호를 사용 하는 등가 조인과 부등호를 사용하는 비등가 조인이 있다.

- 테이블간 관계를 파악하고 있어야 JOIN을 사용할 수 있다. 

 

ex 1) 비등가 조인

안영희의 급여를 보고 sal grade가 몇 인지 조사하는 과정

emp.sal between salgrade.losal and salgrade.hisal -> 이러한 경우 비등가 조인

 

ex 2) 등가조인

 

student.sno = score.sno

score.cno = course.cno 

course.pno = professor.pno

위의 3가지 구문은 등가 조인이다.

 

조인문을 만드는 과정

[예제]

#SQL > SELECT eno 사번, ename 이름, emp.dno 부서번호, dept.dno 부서번호, dname 부서명 FROM dept, emp WHERE dept.dno = emp.dno;

-> 각 사원의 근무 부서를 검색

 

#SQL > SELECT loc 근무처, d.dno 부서번호, dname 부서명, eno 사번, ename 이름 FROM dept d, emp e WHERE d.dno=e.dno AND loc = '광주';

-> 광주에서 근무하는 직원의 명단을 검색(부서번호와 부서명도 검색)

 

#SQL > SELECT eno 사번, ename 이름, sal*1.1 인상된_급여, grade 등급 FROM emp, salgrade WHERE sal*1.1 BETWEEN losal AND hisal;

-> 각 직원의 급여를 10% 인상 후에 급여 등급을 검색

 

[실습]

1. 송강 교수가 강의하는 과목을 검색

SELECT p.pname 교수_이름, c.cname 강의하는_과목

FROM professor p, course c

WHERE p.pno = c.pno AND p.pname = '송강';

 

2. 과목명에 화학이 포함된 과목을 강의하는 교수의 명단 검색

SELECT p.pname 교수_이름, c.cname 화학이_포함된_과목명

FROM professor p, course c

WHERE p.pno = c.pno AND c.cname LIKE '%화학%';

 

3. 학점이 2학점인 과목과 이를 강의하는 교수를 검색

SELECT p.pname 교수_이름, c.st_num 학점이_2인_과목

FROM professor p, course c

WHERE p.pno = c.pno AND c.st_num = 2;

 

4. 화학과 교수가 강의하는 과목을 검색

SELECT p.pname 교수_이름, p.section 속한_과, c.cname 강의하는_과목

FROM professor p, course c

WHERE p.pno = c.pno AND p.section = '화학';

 

5. 화학과 1학년 학생의 기말고사 성적을 검색

SELECT student.sno 학번, student.sname 학생_이름, student.syear 학년, student.major 전공, score.cno 과목_번호, score.result 기말_성적 

FROM student, score  

WHERE (student.sno = score.sno AND student.syear = 1 AND student.major = '화학') ORDER BY student.sname;

 

6. 일반화학 과목의 기말고사 점수를 검색

SELECT s.sno 학번, c.cname 과목명, s.result 기말고사_점수

FROM course c, score s

WHERE c.cno = s.cno AND c.cname = '일반화학';

 

7. 화학과 1학년 학생의 일반화학 기말고사 점수를 검색

SELECT student.major 전공, student.sno 학번, student.sname 학생_이름, student.syear 학년, course.cname 과목명, score.result 기말_성적

FROM student, score, course  

WHERE student.sno = score.sno AND course.cno=score.cno AND student.syear = 1 AND student.major = '화학'  AND course.cname = '일반화학';

 

8. 화학과 1학년 학생이 수강하는 과목을 검색

SELECT student.major 전공, student.sno 학번, student.sname 학생_이름, student.syear 학년, course.cname 수강하는_과목, course.st_num 학점

FROM student, score, course  

WHERE student.sno = score.sno AND course.cno = score.cno AND student.syear = 1 AND student.major = '화학' ORDER BY course.cname, course.st_num desc;

 

9. 일반화학 과목에서 평가 점수가 A인 학생의 명단을 검색

SELECT student.sno 학번, student.sname 이름, course.cname 과목명, scgrade.grade 평가_점수

FROM student , scgrade , course , score

WHERE course.cno=score.cno AND student.sno = score.sno AND course.cname = '일반화학' AND score.result BETWEEN loscore AND hiscore AND grade ='A' ;

 

10. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순서로 검색

SELECT student.sno 학번, student.sname 이름, professor.pname 교수, score.result 점수

FROM student, course, professor, score

WHERE student.sno = score.sno AND score.cno = course.cno AND course.pno = professor.pno AND professor.pname = '송강' ORDER BY score.result desc;

 

11. 화학과 1학년 학생의 기말고사 성적을 학점(A, B, C, D , F)으로 검색

SELECT student.major 학과, student.sno 학번, student.sname 이름, course.cname 과목명, scgrade.grade 학점 
FROM student, course, score, scgrade 
WHERE student.sno=score.sno AND score.cno=course.cno AND student.syear = 1 AND student.major = '화학' AND score.result BETWEEN loscore AND hiscore ORDER BY scgrade.grade;

 

12. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색

SELECT student.sno 학번, student.sname 이름, professor.pname 교수, course.cname 과목_이름, scgrade.grade 학점

FROM student, course, professor, score, scgrade 

WHERE student.sno = score.sno AND score.cno = course.cno AND course.pno = professor.pno AND professor.pname = '송강' AND score.result BETWEEN loscore AND hiscore AND grade ='A';

 

13. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색

SELECT professor.pname 교수, professor.section 학과, course.cname 과목명

FROM professor, course, student, score 

WHERE student.sno = score.sno AND score.cno = course.cno AND course.pno = professor.pno AND student.major = '화학' AND student.syear = 1 ORDER BY professor.pname;

 

* 자기 참조 조인(self join) 

- 참조해야 하는 데이터가 같은  테이블에 있을 경우 테이블을 별도의 명칭으로 출력해서 일반 조인과 같이 사용.

 

[예제 2]각 사원을 관리하는 사수의 이름을 검색

SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.eno;

 

* 외부 조인(outer join)

- 아직 비어있는 데이터까지 출력해주는 옵션. 대부분 Primary Key가 아닌 Foreign Key에 해당

 

[예제]

SELECT d.dno 부서번호, dname 부서명, ename 사원명

FROM dept d, emp e

WHERE d.dno = e.dno

ORDER BY 1;

-> 각 부서 별로 사원을 검색. 만약 d.dno는 존재하지만 e.dno는 존재하지 않을 경우 출력이 안 되는 단점이 있다.

SELECT d.dno 부서번호, dname 부서명, ename 사원명

FROM dept d, emp e

WHERE d.dno = e.dno(+)

ORDER BY 1;

-> 사원이 아직 존재하지 않는 부서까지 포함해서 각 부서 별로 사원을 검색.

 

[실습]

1. 학생 중에 동명이인을 검색

SELECT s1.sno 학번, s2.sname 이름 

FROM student s1, student s2

WHERE s1.sname=s2.sname AND s1.sno != s2.sno;

 

2. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색

SELECT p.section 학과,  p.pno 교수_번호, p.pname 이름, c.cname 담당_과목

FROM professor p, course c

WHERE p.pno = c.pno(+)

ORDER BY p.section;

 

3. 이번 학기에 등록 된 모든 과목과 담당 교수를 학점 순으로 검색

SELECT c.cname 과목명, c.pno 담당교수, c.st_num 학점

FROM course c, professor p

WHERE c.pno = p.pno(+)

ORDER BY c.st_num desc;

 

4. 직원 중에 자신의 관리자보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색

SELECT e1.eno 사번, e1.ename 이름, e1.sal 급여, e1.mgr 관리자_번호, e2.ename 관리자_이름, e2.sal 관리자_급여 

FROM  emp e1, emp e2

WHERE e1.mgr = e2. eno AND e1.sal > e2.sal;

 

5. 교수의 정보와 교수가 담당하는 과목명을 검색

SELECT p.pno, p.pname, p.section, p.orders, p.hiredate, c.cname

FROM professor p, course c

WHERE p.pno = c.pno(+)

ORDER BY p.pno;

 

6. 직원과 사수의 명단을 검색. 단, 직원 명단은 모든 직원 명단이 출력되어야 함.

SELECT e1. eno 사번, e1.ename 이름,  e1.mgr 사수_번호, e2.ename 이름

FROM emp e1, emp e2

WHERE e1.mgr = e2.eno(+);