database[SQL 문법]
SQL> --SQL(DDL,DML,DCL) 중 DML 데이터 조작어를 학습
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
7934 MILLER CLERK 7782 82/01/23
1300 10
14 rows selected.
SQL> --SQL 툴 명령어 중 글자수를 늘리는 명령어
SQL> set line 200
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
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 rows selected.
SQL> set line 250
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
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 rows selected.
SQL> select * from emp where sal>=1250;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
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
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
11 rows selected.
SQL> select ename from emp where sal>=1250;
ENAME
--------------------
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
FORD
MILLER
11 rows selected.
SQL> select ename from emp where sal>3000;
ENAME
--------------------
KING
SQL> select ename from emp where sal<3000;
ENAME
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
TURNER
ADAMS
JAMES
MILLER
11 rows selected.
SQL> select ename from emp where sal<3000;
ENAME
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
TURNER
ADAMS
JAMES
MILLER
11 rows selected.
SQL> select ename from emp where sal<3000 order by sal asc;
ENAME
--------------------
SMITH
JAMES
ADAMS
WARD
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE
JONES
11 rows selected.
SQL> select * from emp where sal<3000 order by sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7900 JAMES CLERK 7698 81/12/03 950 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7566 JONES MANAGER 7839 81/04/02 2975 20
11 rows selected.
SQL> select * from emp where sal<3000 order by sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7369 SMITH CLERK 7902 80/12/17 800 20
11 rows selected.
SQL> select ename, job, empno from emp where deptno=30;
ENAME JOB EMPNO
-------------------- ------------------ ----------
ALLEN SALESMAN 7499
WARD SALESMAN 7521
MARTIN SALESMAN 7654
BLAKE MANAGER 7698
TURNER SALESMAN 7844
JAMES CLERK 7900
6 rows selected.
SQL> select ename, job, empno from emp where deptno=30 order by empno asc;
ENAME JOB EMPNO
-------------------- ------------------ ----------
ALLEN SALESMAN 7499
WARD SALESMAN 7521
MARTIN SALESMAN 7654
BLAKE MANAGER 7698
TURNER SALESMAN 7844
JAMES CLERK 7900
6 rows selected.
SQL> select empno, ename, job from emp where deptno=30 order by empno asc;
EMPNO ENAME JOB
---------- -------------------- ------------------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7844 TURNER SALESMAN
7900 JAMES CLERK
6 rows selected.
SQL> select ename, job, sal from emp where job='SALESMAN' order by sal desc;
ENAME JOB SAL
-------------------- ------------------ ----------
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
MARTIN SALESMAN 1250
WARD SALESMAN 1250
SQL> --page세팅 (한페이지당 몇 건씩 보고 싶은지 변경 가능)
SQL> set pagesize 20
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 rows selected.
SQL> select ename, hiredate from emp where hiredate > '81/02/20' order by hiredate asc;
ENAME HIREDATE
-------------------- --------
WARD 81/02/22
JONES 81/04/02
BLAKE 81/05/01
CLARK 81/06/09
TURNER 81/09/08
MARTIN 81/09/28
KING 81/11/17
JAMES 81/12/03
FORD 81/12/03
MILLER 82/01/23
SCOTT 87/04/19
ADAMS 87/05/23
12 rows selected.
SQL> select ename, comm from emp where comm=0;
ENAME COMM
-------------------- ----------
TURNER 0
SQL> --커미션 계약을 하지 않은 사원들의 이름, 커미션을 출력 하되, 커미션 기준 오름 차순
SQL> select ename, comm from emp where comm='null';
select ename, comm from emp where comm='null'
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select ename, comm from emp where comm=null;
no rows selected
SQL> select ename, comm from emp where comm=NULL;
no rows selected
SQL> select ename, comm from emp where comm='';
no rows selected
SQL> select ename, comm from emp where not in comm;
select ename, comm from emp where not in comm
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename, comm from emp where comm is NULL;
ENAME COMM
-------------------- ----------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
10 rows selected.
SQL> select ename, comm from emp where comm is not NULL order by comm desc;
ENAME COMM
-------------------- ----------
MARTIN 1400
WARD 500
ALLEN 300
TURNER 0
SQL> select ename, comm from emp where comm is not NULL order by ename asc;
ENAME COMM
-------------------- ----------
ALLEN 300
MARTIN 1400
TURNER 0
WARD 500
SQL> --null은 빈공백이므로, 아무런 데이터도 들어있지 않는 뜻, 따라서 값 비교 연산자인 =으로는
SQL> --판단할 수 없다, 특수 연산자인 is 연산자를 이용해야 한다.
SQL> spool off