오라클 기초 실습내용 2/3
기본 sql 사용과 해당문법의 설명을 확인하면서 기초를 다시 확인할수있을듯함..
워낙 오래전 자료다 보니... 지우는것보단...이렇게라도 .... 가끔보게되면
뭔가 새로운 기분이 들지않을까..해서......
---------------------------------------------------------------------
---------------------------------------------------------------------
login: XXXXXX
Password:
Last login: Thu Feb 17 11:29:40 from 210.119.58.174
Sun Microsystems Inc. SunOS 5.5.1 Generic May 1996
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
공지사항 : Oracle Login 변경(oracle실습은 Team계정으로 login 하여 사용하세요)
- from : XXXXX (1999/06/14)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tjaiit% sqlplus team4/team4p
SQL*Plus: Release 8.0.6.0.0 - Production on Thu Feb 17 11:33:15 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Objects option
PL/SQL Release 8.0.6.0.0 - Production
SQL> create table insi( name number(8) not null, age number(2) ) ;
Table created.
SQL> desc insi; //desc : 생성된 테이블에 대한 데이타타입보여줌 내림차순
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL NUMBER(8)
AGE NUMBER(2)
SQL> alter table insi //alter : 테이블 변경(modify)
2 modify name varchar2(8); //alter : 테이블 변경(modify)
Table altered.
SQL> desc insi;
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(8) //number(8)가 varchar2(8)로 데이타타입이 바뀐다.
AGE NUMBER(2)
SQL> alter table insi //alter : 테이블 변경(modify)
2 modify name varchar2(10); //alter : 테이블 변경(modify)
Table altered.
SQL> desc insi;
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(10) //varchar(8)가 varchar2(10)로 데이타타입이 바뀐다.
AGE NUMBER(2)
SQL> alter table insi
2 modify name varchar2(8); //테이블내에 자료가 없을때 데이타형의 자리수 축소...가능
Table altered.
SQL> desc insi;
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(8) //varchar2(10)이 varchar2(8)로 데이타타입이 바뀐다.
AGE NUMBER(2)
SQL> alter table insi //테이블내에 추가
2 add address varchar2(15);
Table altered. //테이블 추가 끝~~
SQL> desc insi; //확인.
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(8)
AGE NUMBER(2)
ADDRESS VARCHAR2(15) //새로 추가된 칼럼
SQL> insert into insi //------------------테이블내에 자료 입력.
2 values('&name',&age,'&address');
Enter value for name: aaaa
Enter value for age: 23
Enter value for address: 서울 종로구
old 2: values('&name',&age,'&address')
new 2: values('aaaa',23,'서울 종로구')
1 row created.
SQL> select * from insi;
NAME AGE ADDRESS
-------- ---------- ---------------
aaaa 23 서울 종로구
SQL> alter table insi
2 modify age char(2); //age의 데이타타입을 number(2)에서 char(2)로 수정한다.
modify age char(2) // 기
* // 러
ERROR at line 2: // 나 !! error 다. 왜! 이미 데이터가 들어가있는 상태이기땜시..
ORA-01439: column to be modified must be empty to change datatype
SQL> alter table insi
2 modify age number(4); //age number(2)를 number(4)로 늘림.
Table altered. // 수정 성공!
SQL> select * from insi; //table 확인
NAME AGE ADDRESS
-------- ---------- ---------------
aaaa 23 서울 종로구
SQL> desc insi //table insi의 자료형 확인.
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(8)
AGE NUMBER(4) //수정된사항 반영!
ADDRESS VARCHAR2(15)
SQL> alter table insi
2 modify age number(2); //반대로 age number(4)를 number(2)로 수정 (=줄임).
modify age number(2)
* // 역시
ERROR at line 2: // error가 난다!
ORA-01440: column to be modified must be empty to decrease precision or scale
SQL> alter table insi //insi 테이블내에 phone이란 칼럼 추가.
2 add phone varchar2(8);
Table altered.
SQL> select * from insi;
//추가....
NAME AGE ADDRESS PHONE
-------- ---------- --------------- --------
aaaa 23 서울 종로구
SQL> alter table insi
2 modify phone varchar2(8) not null; //phone의 속성에다 not null을 추가한다....
modify phone varchar2(8) not null // 긴데...
* // Error 가
ERROR at line 2: // 난다.... not null뗌시..
ORA-02296: cannot enable (TEAM4.) - null values found
SQL> drop table insi; //테이블 삭제
Table dropped.
SQL> select * from tab; //전체 테이블 목록 보여줌
///생략///
SQL> rollback; //복구...
Rollback complete.
SQL> select * from insi; //drop으로 삭제했기때문에 복구 불가.
select * from insi
*
ERROR at line 1:
ORA-00942: table or view does not exist
경고: 이제는 ORACLE 에 연결되어 있지 않습니다. // connect ....
SQL> connect scott/tiger // id/pw
연결되었습니다.
SQL> select * from emp; //emp테이블 보여줌
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> desc emp; //데이터 타입 보여줌
명칭 널? 유형
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc
사용법: DESCRIBE [user.]object[.subobject|@db_link] [column]
SQL> select * from tab; //전체의 테이블
TNAME TABTYPE CLUSTERID
------------------------------ ------- ---------
BONUS TABLE
DEPT TABLE
EMP TABLE
MASTER TABLE
S TABLE
SALGRADE TABLE
6 개의 행이 선택되었습니다.
SQL> desc emp;
명칭 널? 유형
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from emp; // emp의 내용
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> select empno, ename, sal, comm // 칼럼명
2 from emp // 테이블 emp에서 가져온다.
3 where is null; // 조건이 null
where is null // Error -> Null이 들어가있는 칼럼이 지정안됨
*
라인 3 에 오류:
ORA-00936: 식이 없습니다
SQL> select empno, ename, sal, comm
2 from emp
3 where comm is null; //칼럼 comm이 Null인 조건.
EMPNO ENAME SAL COMM
--------- ---------- --------- ---------
7369 SMITH 800
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
10 개의 행이 선택되었습니다.
SQL> select empno, ename, sal, comm
2 from emp
3 where comm is not null; //위와 반대.
EMPNO ENAME SAL COMM
--------- ---------- --------- ---------
7499 ALLEN 1600 300
7521 WARD 1250 500
7654 MARTIN 1250 1400
7844 TURNER 1500 0
SQL> select empno, ename, sal, comm
2 from emp
3 order by comm desc;
EMPNO ENAME SAL COMM
--------- ---------- --------- ---------
7369 SMITH 800
7566 JONES 2975
7782 CLARK 2450
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7900 JAMES 950
7934 MILLER 1300
7902 FORD 3000
7876 ADAMS 1100
7654 MARTIN 1250 1400
7521 WARD 1250 500
7499 ALLEN 1600 300
7844 TURNER 1500 0
14 개의 행이 선택되었습니다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> desc emp
명칭 널? 유형
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select ename, job, deptno
2 from emp
3 where job = 'manager' //소문자.
4 and deptno <> 30;
선택된 레코드가 없습니다.
SQL> select ename, job, deptno
2 from emp
3 where job='MANAGER' //대문자.
4 and deptno <> 30;
//출력--------/
ENAME JOB DEPTNO
---------- --------- ---------
JONES MANAGER 20
CLARK MANAGER 10
SQL> select ename, job, deptno
2 from emp
3 where job='MANAGER'
4 and deptno ^= 30; //<>와 ^=는 같다.
ENAME JOB DEPTNO
---------- --------- ---------
JONES MANAGER 20
CLARK MANAGER 10
SQL> select ename, job, sal
2 from emp
3 where sal between 2000 and 3000; // between조건: 책 실습 1-12 page
ENAME JOB SAL
---------- --------- ---------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450 ||
SCOTT ANALYST 3000
FORD ANALYST 3000
결과는 같다.
SQL> select ename, job, sal
2 from emp
3 where sal >=2000 and sal <=3000; // between 의 수학적 표현
ENAME JOB SAL
---------- --------- ---------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
FORD ANALYST 3000
SQL> select ename deptno //칼럼명ename 이 deptno로 엘리어스된다. ','가 없어서
2 from emp
3 where ename like 'S%'; //emp테이블중 ename이 대문자 S로 시작하는 것을 찾는다. like는 대소문자 구분
DEPTNO
----------
SMITH
SCOTT
SQL> select ename deptno
2 from emp
3 where ename like '%K';
DEPTNO
----------
CLARK
SQL> select ename deptno
2 from emp
3 where ename like 'F___'; //f로 시작하고 자리수가 4개인것
DEPTNO
----------
FORD
SQL> select ename deptno
2 from emp
3 where ename like '%M%'; //ename중 M이 들어간것 모두....
DEPTNO
----------
SMITH
MARTIN
ADAMS
JAMES
MILLER
SQL> select ename, job
2 from emp
3 where job in('CLERK', 'ANALYST');
ENAME JOB
---------- ---------
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
6 개의 행이 선택되었습니다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> select distinct deptno //distinct중복된 칼럼을 없앤다.
2 from emp;
DEPTNO
---------
10
20
30
SQL> select ename,deptno
2 from emp
3 where ename like 'S%';
ENAME DEPTNO
---------- ----------
SMITH 20
SCOTT 20
SQL> c /'S%'/'s%' //대문자에서 소문자로 변경
3* where ename like 's%'
SQL> / //위 명령 재 실행....
ENAME DEPTNO
---------- ----------
smith
SQL> select ename deptno //엘리어스
2 from emp
3 where ename like 'S%';
DEPTNO
----------
SMITH
SCOTT
SQL> c /'S%'/'s%'
3* where ename like 's%'
SQL> /
DEPTNO
----------
smith
SQL> select ename 이름, deptno "Emp No" //여기도 엘리어스....
2 from emp
3 where deptno=10; //조건
이름 Emp No
---------- ---------
CLARK 10
KING 10
MILLER 10
SQL> select ename 이름, deptno "Emp No" //엘리어스 Emp No가 문자열이고 공백이 있기때문에 " "으로 .....
2 from emp e //엘리어스
3 where deptno=10;
이름 Emp No
---------- ---------
CLARK 10
KING 10
MILLER 10
SQL> select * from emp e; //------------엘리어스 시작----
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
//-----------엘리어스 끝----
SQL> select * from e
2 ;
select * from e
*
라인 1 에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 //--당근!!
SQL> select empno, ename, job, emp.deptno, dname
2 from emp, dept
3 where emp,deptno = dept.deptno; //에러 .->, 으로
where emp,deptno = dept.deptno
*
라인 3 에 오류:
ORA-00920: 관계 연산자가 부적합합니다
SQL> select empno, ename, job, emp.deptno, dname
2 from emp, dept
3 where emp.deptno = dept.deptno;
EMPNO ENAME JOB DEPTNO DNAME
--------- ---------- --------- --------- --------------
7369 SMITH CLERK 20 RESEARCH
7499 ALLEN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7566 JONES MANAGER 20 RESEARCH
7654 MARTIN SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
7782 CLARK MANAGER 10 ACCOUNTING
7788 SCOTT ANALYST 20 RESEARCH
7839 KING PRESIDENT 10 ACCOUNTING
7844 TURNER SALESMAN 30 SALES
7876 ADAMS CLERK 20 RESEARCH
7900 JAMES CLERK 30 SALES
7902 FORD ANALYST 20 RESEARCH
7934 MILLER CLERK 10 ACCOUNTING
14 개의 행이 선택되었습니다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> select x.ename, x.sal, x.job, y.ename, y.sal, y.job
2 from emp x, emp y
3 where x.sal > y.sal
4 and y.ename = 'JONES'; //연구해봐....
ENAME SAL JOB ENAME SAL JOB
---------- --------- --------- ---------- --------- ---------
SCOTT 3000 ANALYST JONES 2975 MANAGER
KING 5000 PRESIDENT JONES 2975 MANAGER
FORD 3000 ANALYST JONES 2975 MANAGER
SQL> select * from dept;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> select ename, dept.deptno, loc
2 from emp, dept
3 where emp.deptno(+) = dept.deptno; //emp.deptno(+): 자식 dept.deptno :부모
//조건은 부모만 가진 필드값을 위주로 emp.deptno(+) = dept.deptno을 만족하는
ENAME DEPTNO LOC //값을 출력
---------- --------- -------------
CLARK 10 NEW YORK
KING 10 NEW YORK
MILLER 10 NEW YORK
SMITH 20 DALLAS
ADAMS 20 DALLAS
FORD 20 DALLAS
SCOTT 20 DALLAS
JONES 20 DALLAS
ALLEN 30 CHICAGO
BLAKE 30 CHICAGO
MARTIN 30 CHICAGO
JAMES 30 CHICAGO
TURNER 30 CHICAGO
WARD 30 CHICAGO
40 BOSTON
15 개의 행이 선택되었습니다.
SQL> select ename, empno, dept.deptno, loc
2 from emp, dept
3 where emp.deptno(+) = dept.deptno;
ENAME EMPNO DEPTNO LOC
---------- --------- --------- -------------
CLARK 7782 10 NEW YORK
KING 7839 10 NEW YORK
MILLER 7934 10 NEW YORK
SMITH 7369 20 DALLAS
ADAMS 7876 20 DALLAS
FORD 7902 20 DALLAS
SCOTT 7788 20 DALLAS
JONES 7566 20 DALLAS
ALLEN 7499 30 CHICAGO
BLAKE 7698 30 CHICAGO
MARTIN 7654 30 CHICAGO
JAMES 7900 30 CHICAGO
TURNER 7844 30 CHICAGO
WARD 7521 30 CHICAGO
40 BOSTON
15 개의 행이 선택되었습니다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- ---------------- --------- --------- ---------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> select worker.ename, manager.ename manager
2 from emp worker, emp manager
3 where worker.mgr = manager.empno; //뜻 -> 일하는사람 : 고용주.
ENAME MANAGER
---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
13 개의 행이 선택되었습니다.
SQL> select deptno, sum(sal)
2 from emp
3 group by deptno;
DEPTNO SUM(SAL)
--------- ---------
10 8750
20 10875
30 9400
'IT TIP > Oracle' 카테고리의 다른 글
| Oracle dict 활용 & Compile ,Drop 쿼리 (0) | 2019.06.24 |
|---|---|
| 테스트용 실습 문제와 풀이 60문제 (0) | 2017.04.13 |
| 오라클 테스트를 위한 자료테이블 DESC (0) | 2017.04.13 |
| sqlplus - 기초실습3 (0) | 2017.04.13 |
| sqlplus - 기초실습1 (0) | 2017.04.13 |
| 사용자정의 SPLIT함수. (0) | 2017.04.06 |
| REGEXP_SUBSTR함수로.SPLIT하기 (0) | 2017.04.06 |
| 계층구조 START WITH...CONNECT BY (0) | 2017.04.06 |
| 프로시저내 다이나믹 SQL 예제1 (0) | 2017.04.05 |
| 그룹내 순위결정하는 함수 - ROW_NUMBER() ,RANK() ,DENSE_RANK() (0) | 2017.04.05 |