19.09.26. (SQL Developer 다운,실행/여러가지 JOIN)

* 윈도우 환경에서는 sql developer를 사용 가능하지만, 리눅스 환경에서는 sqlplus밖에 사용되지 않기 때문에 sqlplus도 자주 사용하며 연습하기!!!!

​

-oracle 사이트에서 download 클릭 sql develop 다운로드

https://www.oracle.com/tools/downloads/sqldev-v192-downloads.html

​

(1) SQL Developer 실행 - '+'누르기 - Name : 임의설정 - 사용자 이름(연결할 계정)과 비번 입력, SID : orcl

테스트 누르면 옆에 '성공'뜸 - 위쪽 '+' 테이블 클릭 - 위에 사람모양 SQL 클릭 후 확인

​

- 새접속을 하고 싶다면? 기존 워크시트에 create user 사용자 이름 identified by 비밀번호;

권한 부여? grant create session to 사용자 이름;

위에 사람모양 SQL 클릭 후 확인

​

(SQL Developer에서 ctrl키를 누르고 sql문 작성한 것에 테이블명을 클릭하면 해당 테이블로 이동 가능)

​

(2) JOIN : 동일한 열의 이름을 데이터베이스끼리 연결해주는 것

: 두 개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 있을 때 한 번의 질의만으로 원하는 결과를 얻을 수 있도록 하기 위해 조인을 사용한다.

: 일반적으로 Primnary Key(PK)나 Foreign Key(FK)의 연관에 의해 성립됨

​

* 식별자은 중복값에만 붙여줘도 OK~~

ORA-00918: 열의 정의가 애매합니다 ---> 식별자(테이블 명 or 별칭) 붙여주기

​

1. oracle join = equi join (where절 '=' 쓰는것)

​

select emp.empno, emp.ename, dept.dname

from emp, dept

where emp.deptno = dept.deptno;

​

select empno, ename, dname

from emp, dept

where emp.deptno = dept.deptno;

​

select e.empno, e.ename, d.dname

from emp e, dept d (별칭 - from에서는 as 쓰면 안됨!, select에서는 가능)

where e.deptno = d.deptno;

​

select s.stu_no,s.stu_name,e.sub_no,e.enr_grade

from student s, enrol e

where s.stu_no = e.stu_no;

​

(* 3개 테이블 이용할 경우 WHERE절 AND로 연결)

​

2. natural join (from절에 쓰는 것)

​

select empno, ename, dname

from emp natural join dept;

(*보통 NATURAL JOIN에는 식별자를 사용하지 않는다.)

(*컬럼 이름이 같은 경우가 2개 이상 있을 경우 사용할 수 없다)

(* 3개 테이블 이용할 경우 테이블1 natural join 테이블2 natural join 테이블3;)

​

3. join ~ using (from절에 쓰는 것)

​

select emp.empno, emp.ename, dept.dname

from emp join dept using(deptno);

(*보통 JOIN ~ USING에서는 식별자를 사용하지 않는다.)

(* 3개 테이블 이용할 경우 테이블1 join 테이블2 using( ) join 테이블3 using( );)

​

4. join ~on (from절에 쓰는 것)

​

select emp.empno, emp.ename, dept.dname

from emp join dept on emp.deptno = dept. deptno;

(* 3개 테이블 이용할 경우 테이블1 join 테이블2 on subject.sub_no = enrol.sub_no join 테이블3 on enrol. stu_no = student.stu_no;)

​

5. inner join ~ on (from절에 쓰는 것) : A 테이블과 B 테이블의 교집합 부분 구할 때

(양쪽 테이블에 모두 있는 정보만 출력한다. 즉, 한쪽 테이블에 NULL값이 있으면 출력이 되지 않음)

​

select emp.empno, emp.ename, dept.dname

from emp inner join dept on emp.deptno = dept.deptno;

​

문1 학생들이 듣고 있는 과목 번호, 점수를 확인하시오.

학번, 학생명, 과목번호, 점수를 출력하시오.

​

1.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student, enrol

where student.stu_no = enrol.stu_no;

​

2.select stu_no, stu_name, sub_no, enr_grade

from student natural join enrol;

​

3. select stu_no, stu_name, sub_no, enr_grade

from student join enrol using(stu_no);

​

4.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student join enrol on student.stu_no = enrol.stu_no;

​

5.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student inner join enrol on student.stu_no = enrol.stu_no;

​

문2 부서번호가 30이고, 급여가 1500이상인 사원의 이름, 부서명, 급여를 출력하시오.

​

1.SELECT e.ename, d.dname, e.sal

FROM emp e, dept d

WHERE e.deptno = d.deptno AND (d.deptno = 30 AND e.sal >= 1500);

​

2.SELECT ename, dname, sal

FROM emp Natural Join dept

WHERE deptno = 30 AND emp.sal >= 1500;

​

3.SELECT ename, dname, sal

FROM emp JOIN dept USING (deptno)

WHERE deptno = 30 AND sal >= 1500;

​

4.SELECT e.ename, d.dname, e.sal

FROM emp e JOIN dept d ON e.deptno = d.deptno

WHERE e.deptno = 30 AND e.sal >= 1500;

​

5.SELECT emp.ename, dept.dname, emp.sal

FROM emp INNER JOIN dept ON emp.deptno = dept.deptno

WHERE emp.deptno = 30 AND emp.sal >= 1500;

​

문3 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력하시오. (사원수는 *)

​

1.select dept.dname, count(*)

from dept, emp

where dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

2.select dept.dname, count(*)

from dept natural join emp

group by dept.dname

having count(emp.empno) > 5;

​

3.select dept.dname, count(*)

from dept join emp using(deptno)

group by dept.dname

having count(emp.empno) > 5;

​

4.select dept.dname, count(*)

from dept join emp on dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

5.select dept.dname, count(*)

from dept inner join emp on dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

문4 ADAMS 사원이 근무하는 부서이름과 지역이름을 출력하시오.

​

select dname, loc

from dept natural join emp

where ename = 'ADAMS';

​

문5 NEW YORK이나 DALLAS 지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색하시오.

​

select empno, ename, loc

from emp join dept using(deptno)

where loc = 'NEW YORK' or loc = 'DALLAS'

order by 1;

​

​

문6 부서이름이 ACCOUNTING이거나, 지역이름이 CHICAGO인 사원의 사원번호와 사원이름을 검색하시오.

​

select empno, ename, dname, loc

from emp join dept using(deptno)

where dname = 'ACCOUNTING' or loc = 'CHICAGO';

​

​

(3) Non-Equi Join : 조인 조건에 "=" 외의 비교연산자를 사용하는 조인

(급여 등급 구할 때, BETWEEN ~ AND 사용하는 것처럼~~)

​

문7 사원번호, 사원이름, 급여, 급여등급을 급여등급별 사원번호 순으로 검색하시오.

​

select empno, ename, sal, grade

from emp, salgrade

where emp.sal between salgrade.losal

and salgrade.hisal

order by 1;

​

(4) SELF JOIN

​

select my.empno, my.ename, mgr.empno, mgr.ename

from emp my, emp mgr

where my.mgr= mgr.empno;

​

ex) 매니저가 KING인 사원들의 이름과 직급을 출력하시오.

​

SELECT E1.ENAME, E1.JOB

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO AND E1.JOB = 'MANAGER'

AND E2.ENAME = 'KING';

​

**ex) SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하시오.

​

(DEPTNO가 같은 사원)

SELECT E1.ENAME, E2.ENAME

FROM EMP E1, EMP E2

WHERE E1.DEPTNO = E2.DEPTNO

AND E1.ENAME = 'SCOTT';

​

문10 각 부서의 이름과 가장 많은 월급의 크기를 출력하시오.

​

select dname, max(sal)

from dept, emp

where dept.deptno = emp. deptno

group by dname;

​

(5) OUTER JOIN : 2개 이상의 테이블을 조인할 때, 어느 한쪽의 테이블에는 해당하는 데이터가 있으나,

다른 테이블에는 없는 경우, 테이블의 모든 정보를 출력하고자 할 때 사용

(만족하는 행이 없는 행 = NULL 값인 행도 포함시켜 출력)

​

1.FULL : 두개의 테이블 모두 표시 (왼쪽에만 있는 값 출력, 오른쪽에만 있는 값도 출력)

​

SELECT ENAME, DNAME, LOC

FROM EMP FULL OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

2.LEFT : 왼쪽 테이블 모두 표시 (NULL값이 존재해도 출력)

​

(EMP 테이블과와 DEPT 테이블에서 DEPTNO값이 같으니까 하나의 표로 만드는 것)

SELECT ENAME, DNAME, LOC

FROM EMP LEFT OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

SELECT ENAME, DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO(+);

​

​

3.RIGHT : 오른쪽 테이블 모두 표시 (NULL값이 존재해도 출력)

​

SELECT ENAME, DNAME, LOC

FROM EMP RIGHT OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

SELECT ENAME, DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

​

문11 사원번호, 사원이름, 상사번호, 상사이름을 모두 출력하는 쿼리를 작성하시오.

(단, 사장님도 포함하여 출력할 수 있도록 작성하시오.) * SELF JOIN,LEFT 사용

​

SELECT A.EMPNO, A.ENAME, B.EMPNO, B.ENAME

FROM EMP A LEFT OUTER JOIN EMP B

ON A.MGR = B. EMPNO;

​

SELECT A.EMPNO, A.ENAME, B.EMPNO, B.ENAME

FROM EMP A, EMP B

WHERE A.MGR = B.EMPNO(+);

​

**문12 사원번호, 사원이름, 직책, 관리자이름, 근무위치를 출력하시오.

(단, 사장님도 포함하여 출력할 수 있도록 작성하시오.)

​

SELECT E1.EMPNO, E1.ENAME, E1.JOB, E2.ENAME, D.LOC

FROM EMP E1 LEFT OUTER JOIN EMP E2 ON E1.MGR = E2.EMPNO

JOIN DEPT D ON E1.DEPTNO = D.DEPTNO;

​

select e1.empno, e1.ename, e1.job, e2.ename, d.loc

from emp e1, emp e2, dept d

where e1.mgr = e2.empno(+)

and e1.deptno = d.deptno;

​

​

***문13 사원 JAMES의 사원번호, 이름, 급여, 급여등급, 부서명과 그의 상사 이름, 상사의 부서명을 출력하시오.

​

SELECT E1.EMPNO, E1.ENAME, E1.SAL,GRADE, D1.DNAME,

E2.ENAME, D2.DNAME

FROM EMP E1, EMP E2, SALGRADE, DEPT D1, DEPT D2

WHERE E1.DEPTNO = D1.DEPTNO

AND E1.SAL BETWEEN LOSAL AND HISAL

AND E1.MGR = E2.EMPNO

AND E2.DEPTNO = D2.DEPTNO

AND E1.ENAME = 'JAMES';

​