오라클 연산/급여계산
conn scott/tiger
select empno,ename,job from emp;
EMPNO ENAME JOB
------ ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
------ ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
+++++++++++++++++++++++++++++++++++
select deptno,dname,loc from dept;
re:
DEPTNO DNAME LOC
-------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
+++++++++++++++++++++++++++++++++++++++++++++++
--컬럼명은 다르지만 갯수와 데이터형이 같은면 합이 가능
select empno,ename,job from emp
union
select deptno,dname,loc from dept order by ename;
re:
EMPNO ENAME JOB
------- -------------- -------------
10 ACCOUNTING NEW YORK
7876 ADAMS CLERK
7499 ALLEN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7902 FORD ANALYST
7900 JAMES CLERK
7566 JONES MANAGER
7839 KING PRESIDENT
7654 MARTIN SALESMAN
7934 MILLER CLERK
EMPNO ENAME JOB
------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
30 SALES CHICAGO
7788 SCOTT ANALYST
7369 SMITH CLERK
7844 TURNER SALESMAN
7521 WARD SALESMAN
++++++++++++++++++++++++++++++++++++++++++++++++
select empno,ename,hiredate from emp
union
select deptno,dname,sysdate from dept
order by 3; --3 번째 컬럼으로 정렬
re:
EMPNO ENAME HIREDATE
------- -------------- --------
7369 SMITH 80/12/17
7499 ALLEN 81/02/20
7521 WARD 81/02/22
7566 JONES 81/04/02
7698 BLAKE 81/05/01
7782 CLARK 81/06/09
7844 TURNER 81/09/08
7654 MARTIN 81/09/28
7839 KING 81/11/17
7900 JAMES 81/12/03
7902 FORD 81/12/03
EMPNO ENAME HIREDATE
------- -------------- --------
7934 MILLER 82/01/23
7788 SCOTT 87/04/19
7876 ADAMS 87/05/23
10 ACCOUNTING 05/03/29
20 RESEARCH 05/03/29
30 SALES 05/03/29
40 OPERATIONS 05/03/29
+++++++++++++++++++++++++++++++++++++++++++++++++
SQL> desc user_objects 테이블에 관한 정보는 없음
---desc user_tables 테이블에 관한 정보 표시
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select table_name,TABLESPACE_NAME from user_tables;
re:
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BONUS SYSTEM
DEPT SYSTEM
DEPT2 SYSTEM
EMP SYSTEM
SALGRADE SYSTEM
++++++++++++++++++++++++++++++++++++++++++++++++++++++
---select object_name,object_type from user_objects;
이름 널? 유형
----------------------------------------- -------- ---------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---원하는 정보를 한곳에 모을때.
select table_name,TABLESPACE_NAME from user_tables
union
select INDEX_NAME,TABLESPACE_NAME from user_indexes;
re:
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------
BONUS SYSTEM
DEPT SYSTEM
DEPT2 SYSTEM
EMP SYSTEM
PK_DEPT SYSTEM
PK_EMP SYSTEM
SALGRADE SYSTEM
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--서브쿼리
가장 높은 급여를 받는 사람의 이름
select max(sal) from emp;
re:
MAX(SAL)
--------
5000
---값을 알고 사람 찾음
SQL> select ename from emp where sal=5000;
ENAME
----------
KING
--
select ename from emp
where sal=(select max(sal) from emp);
re:
ENAME
----------
KING
--------------------------------------------------
TURNER 보다 급여를 적게 받는 사람.
select ename from emp
where sal<(select sal from emp where ename like 'TURNER');
re:
ENAME
----------
SMITH
WARD
MARTIN
ADAMS
JAMES
MILLER
+++++++++++++++++++++++++++++++++++++
--30번 부서의 평균 급여보다 많은 급여를 받는 사람과 급여
1)SQL> select avg(sal) from emp where deptno=30 ;
re:
AVG(SAL)
----------
1566.66667
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--select ename,sal from emp where sal >(select avg(sal) from emp where deptno=30 );
re:
ENAME SAL
---------- ----------
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
--30번 부서의 급여보다 많은 급여를 받는 사람과 급여]
--all 사용시 (둘다 만족시)
select ename,sal from emp where sal >all (select sal from emp where deptno=30 );
re:
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
--any 사용시(한개라도 만족시)
select ename,sal from emp where sal >any (select sal from emp where deptno=30 );
re:
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
FORD 3000
ENAME SAL
---------- ----------
MILLER 1300
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-------부서별로 가장 작은 급여를 받는 사람이름과 급여
--in 선택의 결과가 여러개의 경우
--select min(sal),deptno from emp group by deptno;
re:
MIN(SAL) DEPTNO
-------- ----------
1300 10
800 20
950 30
--select ename,sal from emp
where (deptno,sal)
in(select deptno,min(sal) from emp group by deptno);
re:
ENAME SAL
---------- ----------
MILLER 1300
SMITH 800
JAMES 950
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------회사의 평균급여 이상을 받는 사람중 스미스와 같은 부서에 근무하는 사람이름과 급여
--select avg(sal) from emp;
--select deptno from emp where ename like 'SMITH';
--select ename,sal from emp
where sal>=(select avg(sal) from emp)and
deptno=(select deptno from emp where ename like 'SMITH');
re:
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
FORD 3000
--밑에 직원이 한명이라도 있는 사람
-select empno,ename,mgr from emp;
-select mgr from emp;
-select ename from emp
where empno in(select mgr from emp);
re:
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
===========sqlplus 환경
--show all
-- set colsep "|" --컬럼과 컬럼 사이 구분
--select * from dept;
re:
DEPTNO|DNAME |LOC
------|--------------|------------
10|ACCOUNTING |NEW YORK
20|RESEARCH |DALLAS
30|SALES |CHICAGO
40|OPERATIONS |BOSTON
++++++++++++++++++++++++++++++++++++++
--set heading off;
--select * from dept;
10|ACCOUNTING |NEW YORK
20|RESEARCH |DALLAS
30|SALES |CHICAGO
40|OPERATIONS |BOSTON
++++++++++++++++++++++++++++++
--set heading off;
--select * from dept;
10|ACCOUNTING |NEW YORK
20|RESEARCH |DALLAS
30|SALES |CHICAGO
40|OPERATIONS |BOSTON
4 개의 행이 선택되었습니다. (출력에 나옴)
create table longtest(
a long
);
-insert into longtest
values('1234567890123456789012345678901234567890');
-select * from longtest;
re:
1234567890123456789012345678901234567890
-set long 5; // 5바이트만 보여준다.
-select * from longtest;
re:
12345
++++++++++++++++++++++++++++++
drop table longtest;
drop table dept2;
SQL> select * from tab;
BONUS |TABLE |
DEPT |TABLE |
EMP |TABLE |
SALGRADE |TABLE |
4 개의 행이 선택되었습니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++===
SQL> set heading on --제목 출력
SQL> select * from tab;
TNAME |TABTYPE| CLUSTERID
------------------------------|-------|----------
BONUS |TABLE |
DEPT |TABLE |
EMP |TABLE |
SALGRADE |TABLE |
+++++++++++++++++++++++++++++++++++++++++++++++++++++
=--부서별 평급 급여수
select deptno,avg(sal) from emp group by deptno;
re:
DEPTNO| AVG(SAL)
------|----------
10|2916.66667
20| 2175
30|1566.66667
--평균 급여도 같이 출력
select e1.deptno,e1.ename,e1.sal,e2.avgsal
from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2
where e1.deptno=e2.deptno and e1.sal>e2.avgsal;
re:
DEPTNO|ENAME | SAL| AVGSAL
---------|--------|-----|----------
10|KING | 5000|2916.66667
20|FORD | 3000| 2175
20|SCOTT | 3000| 2175
20|JONES | 2975| 2175
30|ALLEN | 1600|1566.66667
30|BLAKE | 2850|1566.66667
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--테이블스페이스의 작성
conn /as sysdba
create tablespace expert
datafile 'd:\data\expert-1.dbf' size 10m,
'd:\data\expert-2.dbf' size 10m;
--테이블스페이스 삭제
drop tablespace expert
including contents and datafiles;
---사용자 생성
-create user special identified by test
default tablespace expert -- 기본 스페이스테이블
temporary tablespace temp7
quota unlimited on expert --기본스페이스테이블에 용량 제한 없음
quota 5m on ejb; --기존 ejb 에는 5m가 주겠다.
-grant connect, resource to special;
---사용자 연결
-conn special/test;
-select * from special;
---테이블스페이스 생성
create tablespace atest
datafile 'd:\data\atest.dbf' size 10m;
++++++++++++++++++++++++++++++++++++++++++++++++++++
-conn special/test
-create table juso1(
no number,
irum varchar2(20),
age number,
city varchar2(10),
hobby varchar2(20)
);
-select * from tab;
re:
TNAME |TABTYPE| CLUSTERID
------------------------------|-------|----------
JUSO1 |TABLE |
--
SQL> select table_name,TABLESPACE_NAME from user_tables;
re:
TABLE_NAME |TABLESPACE_NAME
------------------------------|------------------------------
JUSO1 |EXPERT
++++++++++++++++++++++++++++++++++
create table juso2(
no number,
irum varchar2(20),
age number,
city varchar2(10),
hobby varchar2(20)
)
tablespace ejb;
-select table_name,TABLESPACE_NAME from user_tables;
re:
TABLE_NAME |TABLESPACE_NAME
------------------------------|-------------------------
JUSO1 |EXPERT
JUSO2 |EJB
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
create table juso3(
no number,
irum varchar2(20),
age number,
city varchar2(10),
hobby varchar2(20)
)
tablespace atest;
-select table_name,TABLESPACE_NAME from user_tables;
re:
TABLE_NAME |TABLESPACE_NAME
------------------------------|---------------------
JUSO1 |EXPERT
JUSO2 |EJB
JUSO3 |ATEST
+++++++++++++++++++++++++++++++++++++++++++++
--atest 지우기
-conn /as sysdba
-drop tablespace atest;
re:
drop tablespace atest
*
1행에 오류:
ORA-01549: 테이블스페이스가 비어있지 않으므로 INCLUDING CONTENTS 옵션을 사용해
주십시오
--오라클 서버에서만 삭제
-drop tablespace atest INCLUDING CONTENTS;
--물리적 삭제
-drop tablespace atest INCLUDING CONTENTS and datafiles;