[Oracle SQL] 토드 설치

1. 토드월드 접속

https://www.toadworld.com/ 

2. downloads 접속

3. Toad for Oracle 선택

4. OS에 맞는 것 선택

5. Download

====

1) Equal Join (두 개의 테이블 데이터 row가 동일 할 경우 사용)

select e.ename, d.dname

from emp e, dept d

where e.deptno = d.deptno;

====

select * from dept;

=

select * from emp where deptno=10;

=

2) Non-Equal Join (​범위에 대한 Join)

select * from salgrade;

=

select ename, sal from emp where rownum <5;

=

select e.ename, e.sal, s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

두 개의 테이블 데이터 중 where이하에 있는 e.sal의 데이터 값이 s.losal과 s.hisal의 범위에 알맞은 s.grade를 출력하라는 의미

=====

3) Outer Join (두개의 테이블 row가 같지 않을 경우 사용)

select e.ename, d.deptno, d.dname from emp e, dept d

where e.deptno (+) = d.deptno;

테이블 row가 부족한 부분에 (+)를 붙임

=====

4) Self Join (하나의 테이블 내에서 두 개의 컬럼데이터가 동일한 데이터를 가지고 있을 때 사용)

select empno, ename, job, mgr from emp;

=

select a.ename || '의 상사는 ' || b.ename ||' 이다. ' as 직속상사관계

from emp a, emp b

where a.mgr = b.empno;

===

(연습문제)

select * from emp;

select * from dept;

1. 사원 테이블에서 사원번호, 사원명, 부서번호 출력 ( 5개)

select empno, ename, deptno from emp where rownum < 6;

2.

select empno, ename, deptno from emp order by sal desc;

3.

select empno, ename, comm from emp where job='SALESMAN' and comm is not null;

4.

select ename, sal, hiredate from emp;

5.

select ename, sal, to_char(hiredate,'yyyy-mm-dd') hh from emp;

6.

select empno, ename, deptno from emp where to_char(hiredate,'yyyy') = '1980'

7.

select ename||'('||empno||')' from emp where ename='SMITH'

8.

select e.empno, e.ename, d.dname, e.hiredate from emp e join dept d on e.deptno = d.deptno

9.

select * from emp where deptno = (select deptno from emp where ename='SMITH')

10.

select * from emp where deptno = (select deptno from emp where ename='SMITH') and sal>(select sal from emp where ename='SMITH') 

11.

select ename,deptno,sal from emp where sal = (select max(sal) from emp where deptno = (select deptno from emp where ename='SMITH'))

12.

select b.* from 

    (select rownum rn, a.* from 

        (select ename, sal from emp order by sal desc) a

    ) b

where rn <=5;

13.

select b.* from 

    (select rownum rn, a.* from 

        (select ename, sal from emp order by sal desc) a

    ) b

where 4<rn and rn<11;

14.

select ename from emp where ename like '%S$_%' escape '$';

=======

select ename,sal from emp where rownum < 5;

select ename,sal from emp order by sal desc;

select rownum, ename, sal from emp;

select rownum, ename, sal from emp where rownum <= 5;

select rownum, ename, sal from emp where rownum <5 order by sal desc;

select rownum, ename, sal from emp order by sal desc;

select rownum, ename, sal from emp where rownum >2 ;

select rownum rn, a.* from (select rownum rn, ename, sal from emp) a where rn > 2;