오라클 SQL 8강

8강

--조인(JOIN)

--CROSS JOIN

SELECT STUDENT.*,ENROL.*

FROM STUDENT CROSS JOIN ENROL;

SELECT * FROM STUDENT;

SELECT * FROM ENROL;

--EQUAL JOIN이란?

--WHERE을 이용한 EQUAL JOIN

SELECT STUDENT.STU_NO,STU_NAME,STU_DEPT,ENR_GRADE

FROM STUDENT,ENROL

WHERE STUDENT.STU_NO=ENROL.STU_NO;

 

--NATURAL JOIN을 이용한 EQUAL JOIN

SELECT STU_NO,STU_NAME,STU_DEPT,ENR_GRADE

FROM STUDENT NATURAL JOIN ENROL;

 

--JOIN USING을 이용한 EQUAL JOIN

SELECT STU_NO,STU_NAME,STU_DEPT,ENR_GRADE

FROM STUDENT JOIN ENROL USING(STU_NO);

 

--JOIN ON을 이용한 EQUAL JOIN

SELECT ENROL.STU_NO,STU_NAME,STU_DEPT,ENR_GRADE

FROM STUDENT JOIN ENROL ON(STUDENT.STU_NO=ENROL.STU_NO);

 

--101번 과목을 수강하는 학생들의 학번과 이름을 검색하여라

SELECT * FROM ENROL;

SELECT STUDENT.STU_NO,STU_NAME

FROM STUDENT , ENROL

WHERE STUDENT.STU_NO=ENROL.STU_NO

AND SUB_NO=101;

 

SELECT STU_NO,STU_NAME

FROM STUDENT NATURAL JOIN ENROL

WHERE SUB_NO=101;

 

SELECT STU_NO,STU_NAME

FROM STUDENT JOIN ENROL USING(STU_NO)

WHERE SUB_NO=101;

 

SELECT ENROL.STU_NO,STU_NAME

FROM STUDENT JOIN ENROL ON STUDENT.STU_NO=ENROL.STU_NO

WHERE SUB_NO=101;

 

--과목번호101 또는 과목번호102를 수강하는 학생의 학번과 이름을 검색하라

SELECT STUDENT.STU_NO,STU_NAME

FROM STUDENT,ENROL

WHERE STUDENT.STU_NO=ENROL.STU_NO

AND (SUB_NO=101 OR SUB_NO=102);

 

--3개 테이블이상을 조인하는 경우

--컴퓨터개론과목을 수강하는 학생들의 학번 이름 과목이름을 검색하라

SELECT * FROM SUBJECT;

SELECT * FROM ENROL;

SELECT * FROM STUDENT;

SELECT STUDENT.STU_NO,STU_NAME,SUB_NAME

FROM STUDENT,ENROL,SUBJECT

WHERE STUDENT.STU_NO=ENROL.STU_NO

AND ENROL.SUB_NO=SUBJECT.SUB_NO

AND SUB_NAME='컴퓨터개론';

 

SELECT STU_NO,STU_NAME,SUB_NAME

FROM STUDENT NATURAL JOIN ENROL NATURAL JOIN SUBJECT

WHERE SUB_NAME='컴퓨터개론';

 

SELECT STU_NO,STU_NAME,SUB_NAME

FROM STUDENT JOIN ENROL USING (STU_NO)

JOIN SUBJECT USING (SUB_NO)

WHERE SUB_NAME='컴퓨터개론';

 

SELECT ENROL.STU_NO,STU_NAME,SUB_NAME

FROM STUDENT JOIN ENROL ON(STUDENT.STU_NO=ENROL.STU_NO)

JOIN SUBJECT ON(ENROL.SUB_NO=SUBJECT.SUB_NO)

WHERE SUB_NAME='컴퓨터개론';

 

--NON EQUAL JOIN이란?

SELECT * FROM SALGRADE;

SELECT EMPNO,ENAME,SAL,GRADE

FROM EMP,SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL;

 

--SELF JOIN

--사원테이블에서 자신의 상급자를 구하는 쿼리문을 작성하라

SELECT * FROM EMP;

SELECT A.EMPNO AS 사원번호, A.ENAME AS 사원이름,

B.EMPNO AS 상급자사원번호,B.ENAME AS 상급자사원이름

FROM EMP A,EMP B

WHERE A.MGR=B.EMPNO;

 

--JOIN ON을 이용하여 SELF JOIN을 실행하는 방법

SELECT A.EMPNO AS 사원번호,A.ENAME AS 사원이름,

B.EMPNO 상급자사원번호,B.ENAME 상급자이름

FROM EMP A JOIN EMP B ON A.MGR=B.EMPNO;

 

--각각의 테이블의 이름도 함께 명사

SELECT TABLE_NAME FROM USER_TABLES;

SELECT * FROM EMP A,DEPT B

WHERE A.DEPTNO=B.DEPTNO

ORDER BY EMPNO;

 

SELECT EMPNO,ENAME,A.DEPTNO,DNAME,LOC

FROM EMP A,DEPT B

WHERE A.DEPTNO=B.DEPTNO;

 

SELECT EMPNO,ENAME,A.DEPTNO,DNAME,LOC

FROM EMP A,DEPT B

WHERE A.DEPTNO=B.DEPTNO

ORDER BY DEPTNO DESC,EMPNO DESC;

 

--8-6

SELECT EMPNO,ENAME,SAL,A.DEPTNO,DNAME,LOC

FROM EMP A ,DEPT B

WHERE A.DEPTNO=B.DEPTNO

AND SAL>=3000;

 

--8-7 비등가 JOIN

SELECT * FROM EMP A,SALGRADE B

WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;

 

--8-8 자체 JOIN

--자신의 상급자의 사원번호와 사원이름을 검색하라

SELECT * FROM EMP;

SELECT A.EMPNO AS 자신사원번호, B.EMPNO AS 상급자사원번호,

A.ENAME AS 자신사원이름 , B.ENAME AS 상급자사원이름

FROM EMP A, EMP B

WHERE A.MGR=B.EMPNO;

 

--8-9

SELECT A.EMPNO,A.ENAME,B.MGR,B.EMPNO AS MGR_EMPNO,B.ENAME AS MGR_ENAME

FROM EMP A , EMP B

WHERE A.MGR(+)=B.EMPNO

ORDER BY A.EMPNO;

 

--OUTER JOIN?

SELECT A.*,SUB_NAME

FROM ENROL A,SUBJECT B

WHERE A.SUB_NO=B.SUB_NO

ORDER BY 1;

 

SELECT * FROM SUBJECT;

SELECT A.*,SUB_NAME

FROM ENROL A RIGHT OUTER JOIN SUBJECT B

ON A.SUB_NO=B.SUB_NO

ORDER BY 1;