<19, 20> Natural Join / Using Join

웬만하면 Natural로 때려박는게 편하더라

[Join을 표현하는 여러가지 방법]
[Natuaral Join - 자연조인] : 컬럼이름과 자료형이 일치하는 것을 찾아서 조인

1) 자연 조인으로 각 사원의 근무 부서를 검색
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
NATURAL JOIN dept;

2) 광주에서 근무하는 직원 명단 (부서번호, 부서명)
SELECT loc 근무처, dno 부서번호, dname 부서명, eno 사번, ename 이름
FROM dept
NATURAL JOIN emp
WHERE loc='광주';

SELECT loc 근무처, dno 부서번호, dname 부서명, eno 사번, ename 이름
FROM emp
NATURAL JOIN dept
WHERE loc='광주';


[Using 절을 이용한 Join]
: Natural Join 은 콩통 컬럼이 1개만 존재할 때 사용 가능/ 둘 이상인 경우는 사용불가.
이 때 등가조인이나 Using 절을 이용한 Join을 사용해야 한다...

3) Using 절 조인으로 각 사원의 근무 부서를 검색
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
JOIN dept USING (dno); -- ()로 공통컬럼 지정

4) 광주에서 근무하는 직원 명단 (부서번호, 부서명)
SELECT loc 근무처, dno 부서번호, dname 부서명, eno 사번, ename 이름
FROM dept
JOIN emp Using (dno)
WHERE loc ='광주';

5) 화학과 1학년 학생들의 유기화학 점수를 검색한다
유기화학 : course
점수 : score
화학과 1학년 학생들 : student

SELECT sno 학번, sname 이름, cname 과목명, result 점수
FROM student
JOIN score USING (sno)    -- student.sno = score.sno
JOIN course USING (cno)   -- score.cno = course.cno
WHERE major ='화학'
AND syear =1
AND cname = '유기화학';

SELECT s.sno 학번, sname 이름, cname 과목명, result 점수
FROM student s, score r, course c
WHERE s.sno=r.sno AND r.cno=c.cno 
AND major ='화학'
AND syear =1
AND cname = '유기화학';

SELECT sno 학번, sname 이름, cname 과목명, result 점수
FROM student
NATURAL JOIN score
NATURAL JOIN course
WHERE major ='화학'
AND syear =1
AND cname = '유기화학';

 

연습문제

1) 송강 교수가 강의하는 과목을 검색한다
SELECT pno 교수번호, pname 이름, cname 강의과목
FROM professor
Natural JOIN course
WHERE pname='송강';


2) 화학 관련 과목을 강의하는 교수의 명단을 검색한다
SELECT pno 교수번호, pname 이름, cname 강의과목
FROM professor
NATURAL JOIN course
WHERE cname LIKE '%화학%';


3) 학점이 2학점인 과목과 이를 강의하는 교수를 검색한다
SELECT pno 교수번호, pname 교수이름, cname 과목, st_num 학점
FROM professor
NATURAL JOIN course
WHERE st_num ='2';


4) 화학과 교수가 강의하는 과목을 검색한다
SELECT pno 교수번호, section 소속학과, pname 이름, cname 과목
FROM professor
NATURAL JOIN course
WHERE section ='화학';


5) 화학과 1학년 학생의 기말고사 성적을 검색한다
SELECT sno 학번, major 학과, syear 학년, sname 이름, result 성적
FROM student
NATURAL JOIN score
WHERE major='화학' AND syear='1';

6) 일반화학 과목의 기말고사 점수를 검색한다
SELECT cno 과목번호 , cname 과목이름, result 성적
FROM course
NATURAL JOIN score
WHERE cname = '일반화학';

7) 화학과 1학년 학생의 일반화학 기말고사 점수를 검색한다
SELECT sno 학번, major 학과, syear 학년, sname 이름, cname 과목이름, result 성적
FROM student
NATURAL JOIN score
NATURAL JOIN course
WHERE major = '화학'
AND syear = '1'
AND cname = '일반화학';

8) 화학과 1학년 학생이 수강하는 과목을 검색한다
SELECT sno 학번, major 학과, syear 학년, sname 이름, cname 과목
FROM student 
NATURAL JOIN score
NATURAL JOIN course
WHERE major = '화학'
AND syear = '1';
--ORDER BY cname;

--      SELECT sno 학번, major 학과, syear 학년, sname 이름, cname 과목
--      FROM student 
--      NATURAL JOIN course              -- student - score - course 은 접점이 생기기 때문에 오류나는것..
--      JOIN score USING (sno)
--      WHERE major = '화학'
--      AND syear = '1';


      SELECT sno 학번, major 학과, syear 학년, sname 이름, cname 과목
      FROM student 
      NATURAL JOIN score        
      JOIN course USING (cno)
      WHERE major = '화학'
      AND syear = '1';


SELECT sno 학번, major 학과, syear 학년, sname 이름, cname 과목
FROM student 
JOIN score USING (sno)
JOIN course USING (cno)
WHERE major = '화학'
AND syear = '1';

SELECT * FROM emp;