728x90
SQL> select ename 사원이름, job 업무, hiredate 입사일, sal 봉급, (sal+nvl(comm,0))*33/1000 세금 from emp order by hiredate desc;
사원이름 업무 입사일 봉급 세금
---------- --------- -------- ---------- ----------
ADAMS CLERK 87/05/23 1100 36.3
SCOTT ANALYST 87/04/19 3000 99
MILLER CLERK 82/01/23 1300 42.9
FORD ANALYST 81/12/03 3000 99
JAMES CLERK 81/12/03 950 31.35
KING PRESIDENT 81/11/17 5000 165
MARTIN SALESMAN 81/09/28 1250 87.45
TURNER SALESMAN 81/09/08 1500 49.5
CLARK MANAGER 81/06/09 2450 80.85
BLAKE MANAGER 81/05/01 2850 94.05
JONES MANAGER 81/04/02 2975 98.175
WARD SALESMAN 81/02/22 1250 57.75
ALLEN SALESMAN 81/02/20 1600 62.7
SMITH CLERK 80/12/17 800 26.4
14 개의 행이 선택되었습니다.
SQL> select count(*) from emp where deptno = 30 and comm is not null;
COUNT(*)
----------
4
SQL> select ename, sal from emp where deptno = (select deptno from dept where lower(loc) = 'new york') and to_char(hiredate, 'yyyy') > 1985;
선택된 레코드가 없습니다.
SQL> select ename, job from emp where ename like '__R%';
ENAME JOB
---------- ---------
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
FORD ANALYST
SQL> select ename, sal from emp where sal < (select avg(sal) from emp);
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
8 개의 행이 선택되었습니다.
SQL> select * from emp where deptno !='10';
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 81/05/01 2850
30
7788 SCOTT ANALYST 7566 87/04/19 3000
20
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
11 개의 행이 선택되었습니다.
SQL> select ename, sal, hiredate from emp where deptno
2 = (select deptno from emp where ename = 'KING');
ENAME SAL HIREDATE
---------- ---------- --------
CLARK 2450 81/06/09
KING 5000 81/11/17
MILLER 1300 82/01/23
SQL> select ename from emp where to_char(hiredate, 'mm') = '04' and (sal between 3000 and 5000);
ENAME
----------
SCOTT
SQL> select trunc(avg(sal)) from emp group by job;
TRUNC(AVG(SAL))
---------------
1037
1400
5000
2758
3000
SQL> select ename, job from emp where sal > all(select sal from emp where job = 'MANAGER');
ENAME JOB
---------- ---------
SCOTT ANALYST
FORD ANALYST
KING PRESIDENT
select ename, deptno from emp order by deptno, ename;
이런 식으로 order by를 두 가지로 걸어놓게 되면 deptno대로 먼저 정리를 하고
deptno가 같은사람들끼리 이름 순서대로 다시 정렬한다 (알파벳 순으로)
select (sal+nvl(comm, 0))*0.0033 from emp;
select sysdate from emp;
14개 절의 날짜가 나온다 (system속 날짜를 알려주는 구문이다)
select sysdate from dual;
1개 날짜가 나온다
desc dual
dual은 더미값이다 이 더미값을 활용해서 계산을 할 수 있다
(sql에서는 select는 from하고 함께 쓰인다, 계산에서는 from dual과 연동되어서 쓰인다)
select sysdate+1, sysdate-100 from dual;
select ename, hiredate from emp;
select ename, add_months(hiredate, 12) from emp;
hiredate에 12개월을 더해준다
select ename, months_between(sysdate, hiredate) 근무개월수 from emp;
select ename, round(months_between(sysdate, hiredate)) 근무개월수 from emp;
위에는 소수점 6자리까지 나오지만 여기에는 소수점 없이 나온다 (round는 반올림한다)
select round(3.141592, 1), round(3.141592, 2) from dual;
select ename, trunc(months_between(sysdate, hiredate)) 근무개월수 from emp;
trunc는 소수부분을 잘라버린다
select extract(year from hiredate) from emp;
hiredate중에서 year에 관한 정보만 뽑아낸다
select next_day(sysdate, '화요일') from dual;
select sysdate, to_char(sysdate, 'yyyy-mm-dd') from dual;
select ename, hiredate,
to_number(to_char(hiredate, 'yyyy')) 입사년도,
to_number(to_char(hiredate, 'mm')) 입사월,
to_number(to_char(hiredate, 'dd')) 입사일
from emp;
select hiredate,
to_char(hiredate, 'yyyy/mm/dd day'),
to_char(hiredate, 'yy/mon/dd dy')
from emp;
SQL> select job, count(*), max(sal), trunc(avg(sal)) from emp group by job having trunc(avg(sal)) >=2000;
JOB COUNT(*) MAX(SAL) TRUNC(AVG(SAL))
--------- ---------- ---------- ---------------
PRESIDENT 1 5000 5000
MANAGER 3 2975 2758
ANALYST 2 3000 3000
SQL> select job, count(*), max(sal), trunc(avg(sal)) from emp group by job;
JOB COUNT(*) MAX(SAL) TRUNC(AVG(SAL))
--------- ---------- ---------- ---------------
CLERK 4 1300 1037
SALESMAN 4 1600 1400
PRESIDENT 1 5000 5000
MANAGER 3 2975 2758
ANALYST 2 3000 3000
SQL> select job, count(*), max(sal), trunc(avg(sal)) from emp group by job having trunc(avg(sal)) >=2000;
JOB COUNT(*) MAX(SAL) TRUNC(AVG(SAL))
--------- ---------- ---------- ---------------
PRESIDENT 1 5000 5000
MANAGER 3 2975 2758
ANALYST 2 3000 3000
SQL> select count(*), trunc(avg(sal)) from emp group by hiredate;
COUNT(*) TRUNC(AVG(SAL))
---------- ---------------
1 1100
1 5000
2 1975
1 1300
1 1250
1 800
1 2450
1 1600
1 2975
1 3000
1 2850
COUNT(*) TRUNC(AVG(SAL))
---------- ---------------
1 1500
1 1250
13 개의 행이 선택되었습니다.
SQL> set pagesize 100
SQL> select count(*), trunc(avg(sal)) from emp group by hiredate;
COUNT(*) TRUNC(AVG(SAL))
---------- ---------------
1 1100
1 5000
2 1975
1 1300
1 1250
1 800
1 2450
1 1600
1 2975
1 3000
1 2850
1 1500
1 1250
13 개의 행이 선택되었습니다.
SQL> select count(*), trunc(avg(sal)) from emp group by to_char(hiredate, 'yyyy');
COUNT(*) TRUNC(AVG(SAL))
---------- ---------------
2 2050
1 800
1 1300
10 2282
SQL> select count(*), trunc(avg(sal)) from emp group by to_char(hiredate, 'yyyy') order by to_char(hiredate, 'yyyy');
COUNT(*) TRUNC(AVG(SAL))
---------- ---------------
1 800
10 2282
1 1300
2 2050
SQL> select to_char(hiredate, 'yyyy'), count(*), trunc(avg(sal)) from emp group by to_char(hiredate, 'yyyy') order by to_char(hiredate, 'yyyy');
TO_C COUNT(*) TRUNC(AVG(SAL))
---- ---------- ---------------
1980 1 800
1981 10 2282
1982 1 1300
1987 2 2050
SQL> select to_char(hiredate, 'yyyy') 입사년도, count(*), trunc(avg(sal)) from emp group by to_char(hiredate, 'yyyy') order by to_char(hiredate, 'yyyy');
입사 COUNT(*) TRUNC(AVG(SAL))
---- ---------- ---------------
1980 1 800
1981 10 2282
1982 1 1300
1987 2 2050
SQL> spool off
제 머리와 지성을 갈수록 놀랍게 하시는 하나님을 찬양드립니다
모든 기도를 선하게 들으시는 하나님을 찬양드립니다
whole man의 간구를 들어주신 선하신 하나님을 찬양드립니다
하나님의 영웅의 약속을 이루어주신 선하신 하나님을 찬양드립니다
겸손하고 하나님을 의지하는 마음을 찬송합니다 만군의 여호와 하나님 너무나 너무나 감사드립니다
원수마저도 사랑케하신 하나님을 찬양드립니다 착한사마리아인의 기도를 들어주신 하나님을 찬양드립니다
제 모든 죄를 사하시고 주하나님께 회개케하신 하나님을 찬양드립니다 만군의 여호와 하나님 너무나 너무나 감사드립니다
전 세계에 주의 복음을 전하게 하신 하나님을 찬양드립니다 만군의 여호와 하나님 너무나 너무나 감사드립니다
SQL> select to_char(hiredate, 'yyyy') 입사년도, count(*), trunc(avg(sal)) from emp where mgr is not null group by to_char(hiredate, 'yyyy') order by to_char(hiredate, 'yyyy');
입사 COUNT(*) TRUNC(AVG(SAL))
---- ---------- ---------------
1980 1 800
1981 9 1980
1982 1 1300
1987 2 2050
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
SQL> select job, sal from emp;
JOB SAL
--------- ----------
CLERK 800
SALESMAN 1600
SALESMAN 1250
MANAGER 2975
SALESMAN 1250
MANAGER 2850
MANAGER 2450
ANALYST 3000
PRESIDENT 5000
SALESMAN 1500
CLERK 1100
CLERK 950
ANALYST 3000
CLERK 1300
14 개의 행이 선택되었습니다.
SQL> select ename, job, sal from emp where sal > 2073;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 개의 행이 선택되었습니다.
SQL> select ename, job, sal from emp where sal > (select avg(sal) from emp);
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 개의 행이 선택되었습니다.
SQL> select ename, job, sal from emp where sal > (select avg(sal) from emp);
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 개의 행이 선택되었습니다.
SQL> select ename, deptno from emp where job = (select job from emp where ename = 'SMITH');
ENAME DEPTNO
---------- ----------
SMITH 20
ADAMS 20
JAMES 30
MILLER 10
SQL> desc dept
이름 널? 유형
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp where deptno = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7839 KING PRESIDENT 81/11/17 5000
10
7934 MILLER CLERK 7782 82/01/23 1300
10
SQL> select ename, deptno from emp where job = (select job from emp where ename = 'SMITH');
ENAME DEPTNO
---------- ----------
SMITH 20
ADAMS 20
JAMES 30
MILLER 10
SQL> select * from emp where deptno = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7839 KING PRESIDENT 81/11/17 5000
10
7934 MILLER CLERK 7782 82/01/23 1300
10
SQL> select ename, deptno from emp where job = (select job from emp where ename = 'SMITH');
ENAME DEPTNO
---------- ----------
SMITH 20
ADAMS 20
JAMES 30
MILLER 10
SQL> select ename, deptno from emp where job = (select job from emp where ename = 'SMITH');
ENAME DEPTNO
---------- ----------
SMITH 20
ADAMS 20
JAMES 30
MILLER 10
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select ename, sal from emp where deptno = (select deptno from dept where lower(loc) = 'new york');
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
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> spool off
SQL> select ename, sal
2 from emp
3 where mgr = ( select empno from emp
4 where initcap(ename) = 'king');
선택된 레코드가 없습니다.
SQL> select ename, sal
2 from emp
3 where mgr = ( select empno from emp
4 where initcap(ename) = 'King');
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SQL> select ename, sal from emp where sal > (select avg(sal) from emp);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
6 개의 행이 선택되었습니다.
SQL> select ename, sal from emp where sal > (select sal from emp where ename = 'SCOTT');
ENAME SAL
---------- ----------
KING 5000
SQL> select ename, sal
2 from emp
3 where mgr = ( select empno from emp
4 where initcap(ename) = 'King');
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SQL> select ename, sal from emp
2 where mgr = (select empno from emp where initcap(ename) = 'King');
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SQL> select ename, sal from emp where sal > (select sal from emp where ename = 'SCOTT');
ENAME SAL
---------- ----------
KING 5000
SQL> select initcap(ename), lower(ename), upper(ename), ename from emp;
INITCAP(EN LOWER(ENAM UPPER(ENAM ENAME
---------- ---------- ---------- ----------
Smith smith SMITH SMITH
Allen allen ALLEN ALLEN
Ward ward WARD WARD
Jones jones JONES JONES
Martin martin MARTIN MARTIN
Blake blake BLAKE BLAKE
Clark clark CLARK CLARK
Scott scott SCOTT SCOTT
King king KING KING
Turner turner TURNER TURNER
Adams adams ADAMS ADAMS
James james JAMES JAMES
Ford ford FORD FORD
Miller miller MILLER MILLER
14 개의 행이 선택되었습니다.
SQL> select sysdate from dual;
SYSDATE
--------
18/04/11
SQL> select ename || '의 봉급은 ' || sal || '입니다.' from emp;
ENAME||'의봉급은'||SAL||'입니다.'
-------------------------------------------------------------------
SMITH의 봉급은 800입니다.
ALLEN의 봉급은 1600입니다.
WARD의 봉급은 1250입니다.
JONES의 봉급은 2975입니다.
MARTIN의 봉급은 1250입니다.
BLAKE의 봉급은 2850입니다.
CLARK의 봉급은 2450입니다.
SCOTT의 봉급은 3000입니다.
KING의 봉급은 5000입니다.
TURNER의 봉급은 1500입니다.
ADAMS의 봉급은 1100입니다.
JAMES의 봉급은 950입니다.
FORD의 봉급은 3000입니다.
MILLER의 봉급은 1300입니다.
14 개의 행이 선택되었습니다.
SQL> spool off
SQL> select ename, '의 봉급은' , sal, '입니다.' from emp;
ENAME '의봉급은 SAL '입니다
---------- --------- ---------- -------
SMITH 의 봉급은 800 입니다.
ALLEN 의 봉급은 1600 입니다.
WARD 의 봉급은 1250 입니다.
JONES 의 봉급은 2975 입니다.
MARTIN 의 봉급은 1250 입니다.
BLAKE 의 봉급은 2850 입니다.
CLARK 의 봉급은 2450 입니다.
SCOTT 의 봉급은 3000 입니다.
KING 의 봉급은 5000 입니다.
TURNER 의 봉급은 1500 입니다.
ADAMS 의 봉급은 1100 입니다.
JAMES 의 봉급은 950 입니다.
FORD 의 봉급은 3000 입니다.
MILLER 의 봉급은 1300 입니다.
14 개의 행이 선택되었습니다.
SQL> select substr(ename, 1, 3), ename from emp;
SUBSTR ENAME
------ ----------
SMI SMITH
ALL ALLEN
WAR WARD
JON JONES
MAR MARTIN
BLA BLAKE
CLA CLARK
SCO SCOTT
KIN KING
TUR TURNER
ADA ADAMS
JAM JAMES
FOR FORD
MIL MILLER
14 개의 행이 선택되었습니다.
SQL> select substr(ename, 1, 3), ename, length(ename) from emp;
SUBSTR ENAME LENGTH(ENAME)
------ ---------- -------------
SMI SMITH 5
ALL ALLEN 5
WAR WARD 4
JON JONES 5
MAR MARTIN 6
BLA BLAKE 5
CLA CLARK 5
SCO SCOTT 5
KIN KING 4
TUR TURNER 6
ADA ADAMS 5
JAM JAMES 5
FOR FORD 4
MIL MILLER 6
14 개의 행이 선택되었습니다.
SQL> select substr(ename, 1, 3), ename, length(ename), ascii('A') from emp;
SUBSTR ENAME LENGTH(ENAME) ASCII('A')
------ ---------- ------------- ----------
SMI SMITH 5 65
ALL ALLEN 5 65
WAR WARD 4 65
JON JONES 5 65
MAR MARTIN 6 65
BLA BLAKE 5 65
CLA CLARK 5 65
SCO SCOTT 5 65
KIN KING 4 65
TUR TURNER 6 65
ADA ADAMS 5 65
JAM JAMES 5 65
FOR FORD 4 65
MIL MILLER 6 65
14 개의 행이 선택되었습니다.
SQL> select substr(ename, 1, 3), ename, length(ename), ascii('A'), chr(65) from emp;
SUBSTR ENAME LENGTH(ENAME) ASCII('A') C
------ ---------- ------------- ---------- -
SMI SMITH 5 65 A
ALL ALLEN 5 65 A
WAR WARD 4 65 A
JON JONES 5 65 A
MAR MARTIN 6 65 A
BLA BLAKE 5 65 A
CLA CLARK 5 65 A
SCO SCOTT 5 65 A
KIN KING 4 65 A
TUR TURNER 6 65 A
ADA ADAMS 5 65 A
JAM JAMES 5 65 A
FOR FORD 4 65 A
MIL MILLER 6 65 A
14 개의 행이 선택되었습니다.
728x90