-- table 조회~ 밑에 테이블들은 scott/tiger 계정으로 접속해야 조회 가능.
selct * --> 이건 컬럼 싹다 출력!!!
from tab;
TNAME TABTYPE
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
--HR(Human Resource) 인적자원데이터
BONUS(보너스)
DEPT(부서)
EMP(직원)
SALGRADE(급여등급)
*테이블 구조,골격 조회
테이블명
desc[ribe] 테이블명
SQL> describe emp;
Name Null? Type
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 *
2 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
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 82/01/23 1300 1012 rows selected.
SQL> describe dept
Name Null? Type
DEPTNO(부서번호) NOT NULL NUMBER(2)
DNAME(부서명) VARCHAR2(14)
LOC(위치) VARCHAR2(13)
-> location
SQL> select *
2 from dept;
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> select deptno, dname, loc
2 from dept;
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> desc salgrade
Name Null? Type
GRADE(등급) NUMBER
LOSAL(최저급여) NUMBER
HISAL(최고급여) NUMBER
SQL> select grade, losal, hisal
2 from salgrade;
or
SQL> select *
2 from salgrade;
GRADE LOSAL HISAL 1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999SQL> describe bonus;
Name Null? Type
ENAME(사원명) VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
--bonus 테이블의 내용조회
select *
from bonus;
SQL> select *
2 from bonus;
no rows selected
데이터가 없다는 뜻!!!!
bonus만 insert를 하지 않았다.
--부서번호 오름차순 조회
1 select [deptno, dname, loc]
2 from dept order by deptno asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON--부서명 오름차순 조회
1 select [deptno, dname, loc]
2 from dept order by dname asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
30 SALES CHICAGO--위치 오름차순 조회
1 select [deptno, dname, loc]
2 from dept order by loc asc
SQL> /
DEPTNO DNAME LOC 40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK-- 사원명, 입사일 조회
1 select ename, hiredate
2* from emp
SQL> /
ENAME HIREDATE
SMITH 80/12/17
ALLEN 81/02/20
WARD 81/02/22
JONES 81/04/02
MARTIN 81/09/28
BLAKE 81/05/01
CLARK 81/06/09
KING 81/11/17
TURNER 81/09/08
JAMES 81/12/03
FORD 81/12/03
MILLER 82/01/23
-- 사원명, 입사일 조회하여 사원명 오름차순으로 정렬 출력
ENAME HIREDATE
ALLEN 81/02/20
BLAKE 81/05/01
CLARK 81/06/09
FORD 81/12/03
JAMES 81/12/03
JONES 81/04/02
KING 81/11/17
MARTIN 81/09/28
MILLER 82/01/23
SMITH 80/12/17
TURNER 81/09/08
WARD 81/02/22
--고참부터 사원명, 입사일 조회 (입사일 오름차순!!)
1 select ename, hiredate
2* from emp order by hiredate asc
SQL> /
ENAME HIREDATE
SMITH 80/12/17
ALLEN 81/02/20
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
--사원명, 급여, 커미션 조회~
1 select ename, sal, comm
2* from emp
SQL> /
ENAME SAL COMM
SMITH 800
ALLEN 1600 300
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500 0
JAMES 950
FORD 3000
MILLER 1300
12 rows selected.
--급여 많이 받는 순으로 정렬 조회~(내림차순!!)
1 select ename, sal, comm
2* from emp order by sal desc
SQL> /
ENAME SAL COMM
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600 300
TURNER 1500 0
MILLER 1300
MARTIN 1250 1400
WARD 1250 500
JAMES 950
SMITH 800
12 rows selected.
null은 알수 없는 값, 미확정 된 값 이기 때문에
아무리 정렬을 해도 null이 먼저 출력된 다음에 확정된 값, 입력된 값들 만 가지고 정렬을 한다.
null 은 즉 무한대를 뜻하기도 한다. 그래서 먼저 출력.
null = ∞
*null은 무한대
*값에 null이 포함된 상태에서 숫자 오름차순이면
숫자 작 -> 큰 순으로 무한대이므로 가장 마지막 출력
--커미션 많이 받는 사원부터 출력
1 select ename, sal, comm
2* from emp order by comm desc
SQL> /
ENAME SAL COMM
SMITH 800
BLAKE 2850
FORD 3000
JAMES 950
KING 5000
JONES 2975
MILLER 1300
CLARK 2450
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
12 rows selected.
--급여 + comm 합 조회~==>null이 포함된 계산결과는 null
1 select ename, sal, comm, sal+comm
2* from emp
SQL> /
ENAME SAL COMM SAL+COMM
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500 0 1500
JAMES 950
FORD 3000
MILLER 1300
12 rows selected.
SQL> ed
Wrote file afiedt.buf
위에 결과에서 내림차순 추가.
1 select ename, sal, comm, sal+comm
2* from emp order by ename desc
SQL> /
ENAME SAL COMM SAL+COMM
WARD 1250 500 1750
TURNER 1500 0 1500
SMITH 800
MILLER 1300
MARTIN 1250 1400 2650
KING 5000
JONES 2975
JAMES 950
FORD 3000
CLARK 2450
BLAKE 2850
ALLEN 1600 300 1900
12 rows selected.
-- nvl(표현식, null 대체값)함수 이용 해서 null 데이터 대체 하기
1 select ename, sal, comm, nvl(comm, 0)
2* from emp
SQL> /
ENAME SAL COMM NVL(COMM,0)
SMITH 800 0
ALLEN 1600 300 300
WARD 1250 500 500
JONES 2975 0
MARTIN 1250 1400 1400
BLAKE 2850 0
CLARK 2450 0
KING 5000 0
TURNER 1500 0 0
JAMES 950 0
FORD 3000 0
MILLER 1300 0
12 rows selected.
-- 대체 한 후에 더한 값 조회하여 출력 (ex. 커미션이 미확정된 사원의 경우 0으로 가정하여 급여와 커미션의 합을 구하세요.)
1 select ename, sal, comm, nvl(comm, 0), sal+nvl(comm, 0)
2* from emp
SQL> /
ENAME SAL COMM NVL(COMM,0) SAL+NVL(COMM,0)
SMITH 800 0 800
ALLEN 1600 300 300 1900
WARD 1250 500 500 1750
JONES 2975 0 2975
MARTIN 1250 1400 1400 2650
BLAKE 2850 0 2850
CLARK 2450 0 2450
KING 5000 0 5000
TURNER 1500 0 0 1500
JAMES 950 0 950
FORD 3000 0 3000
MILLER 1300 0 1300
12 rows selected.
-- 대체 한 후에 더한 값에 대한 컬럼 내림차순으로
1 select ename, sal, comm, nvl(comm, 0), sal+nvl(comm, 0)
2* from emp order by sal+nvl(comm, 0) desc
SQL> /
ENAME SAL COMM NVL(COMM,0) SAL+NVL(COMM,0)
KING 5000 0 5000
FORD 3000 0 3000
JONES 2975 0 2975
BLAKE 2850 0 2850
MARTIN 1250 1400 1400 2650
CLARK 2450 0 2450
ALLEN 1600 300 300 1900
WARD 1250 500 500 1750
TURNER 1500 0 0 1500
MILLER 1300 0 1300
JAMES 950 0 950
SMITH 800 0 800
12 rows selected.
-- nvl(표현식, null 대체값)
-- nvl함수 응용
-- nvl2(표현식, null 아닌값 대체값, null 대체값)
comm의 값이 null이 아닌 값은 20으로 대체하고 null이면 10으로 대체하여 출력
1 select ename, sal, comm, nvl(comm, 0), nvl2(comm, 20, 10)
2* from emp
SQL> /
ENAME SAL COMM NVL(COMM,0) NVL2(COMM,20,10)
SMITH 800 0 10
ALLEN 1600 300 300 20
WARD 1250 500 500 20
JONES 2975 0 10
MARTIN 1250 1400 1400 20
BLAKE 2850 0 10
CLARK 2450 0 10
KING 5000 0 10
TURNER 1500 0 0 20
JAMES 950 0 10
FORD 3000 0 10
MILLER 1300 0 10
12 rows selected.
*NULL관련(P.143)
-- nvl(표현식, null 대체값)
-- nvl2(표현식, null 아닌값 대체값, null 대체값)
-- NULLIF(표현식1, 표현식2)
표현식 1이 표현식 2와 같다면 null을 리턴
같지않다면 표현식1을 리턴
1 select ename, sal, comm, NULLIF(sal, comm)
2* from emp
SQL> /
ENAME SAL COMM NULLIF(SAL,COMM)
SMITH 800 800
ALLEN 1600 300 1600
WARD 1250 500 1250
JONES 2975 2975
MARTIN 1250 1400 1250
BLAKE 2850 2850
CLARK 2450 2450
KING 5000 5000
TURNER 1500 0 1500
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
12 rows selected.
--COALESCE(표현식1, 표현식2,.....)
NULL이 아닌 최초의 표현식을 리턴
두 컬럼 비교해서 한쪽이 null이면 다른 null이 아닌 값 출력하고
둘 다 null이 아니면 최초의 값을 출력한다.(처음 조회한값, 먼저 출력된 컬럼)
1 select ename, comm, sal, COALESCE(comm, sal)
2* from emp
SQL> /
ENAME COMM SAL COALESCE(COMM,SAL)
SMITH 800 800
ALLEN 300 1600 300
WARD 500 1250 500
JONES 2975 2975
MARTIN 1400 1250 1400
BLAKE 2850 2850
CLARK 2450 2450
KING 5000 5000
TURNER 0 1500 0
JAMES 950 950
FORD 3000 3000
ENAME COMM SAL COALESCE(COMM,SAL)
MILLER 1300 1300
12 rows selected.
---------------------------------곱하기 연산 추가 예제--------------------------------------
1 select ename, sal, sal13
2 from emp
SQL> /
ENAME SAL SAL*13
SMITH 800 10400
ALLEN 1600 20800
WARD 1250 16250
JONES 2975 38675
MARTIN 1250 16250
BLAKE 2850 37050
CLARK 2450 31850
KING 5000 65000
TURNER 1500 19500
JAMES 950 12350
FORD 3000 39000
ENAME SAL SAL*13
MILLER 1300 16900
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select ename, sal, comm, sal13, (sal*13)+comm
2 from emp
SQL> /
ENAME SAL COMM SAL13 (SAL13)+COMM
SMITH 800 10400
ALLEN 1600 300 20800 21100
WARD 1250 500 16250 16750
JONES 2975 38675
MARTIN 1250 1400 16250 17650
BLAKE 2850 37050
CLARK 2450 31850
KING 5000 65000
TURNER 1500 0 19500 19500
JAMES 950 12350
FORD 3000 39000
ENAME SAL COMM SAL13 (SAL13)+COMM
MILLER 1300 16900
12 rows selected.
--공백값 까지 고려한 조회!!!
1 select ename, sal, comm, sal13, (sal*13)+nvl(comm, 0)
2 from emp
SQL> /
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)
SMITH 800 10400 10400
ALLEN 1600 300 20800 21100
WARD 1250 500 16250 16750
JONES 2975 38675 38675
MARTIN 1250 1400 16250 17650
BLAKE 2850 37050 37050
CLARK 2450 31850 31850
KING 5000 65000 65000
TURNER 1500 0 19500 19500
JAMES 950 12350 12350
FORD 3000 39000 39000
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)
MILLER 1300 16900 16900
12 rows selected.
--워급에 3% 더 준다.
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03)
2 from emp
SQL> /
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)+(SAL*0.03)
SMITH 800 10400 10424
ALLEN 1600 300 20800 21148
WARD 1250 500 16250 16787.5
JONES 2975 38675 38764.25
MARTIN 1250 1400 16250 17687.5
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
KING 5000 65000 65150
TURNER 1500 0 19500 19545
JAMES 950 12350 12378.5
FORD 3000 39000 39090
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)+(SAL*0.03)
MILLER 1300 16900 16939
12 rows selected.
--연봉 많은 사람부터 정렬!!!!
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03)
2 from emp
3 order by (sal13)+nvl(comm, 0)+(sal0.03) desc
SQL> /
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)+(SAL*0.03)
KING 5000 65000 65150
FORD 3000 39000 39090
JONES 2975 38675 38764.25
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
ALLEN 1600 300 20800 21148
TURNER 1500 0 19500 19545
MARTIN 1250 1400 16250 17687.5
MILLER 1300 16900 16939
WARD 1250 500 16250 16787.5
JAMES 950 12350 12378.5
SMITH 800 10400 10424
12 rows selected.
--위와 같은데 4라고 표현한것. 컬럼이 4번째 컬럼이기 때문에 이런식으로 표현 가능.. 하지만 좋은 방식은 아니다.
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03)
2 from emp
3 order by 4 desc
SQL> /
ENAME SAL COMM SAL13 (SAL13)+NVL(COMM,0)+(SAL*0.03)
KING 5000 65000 65150
FORD 3000 39000 39090
JONES 2975 38675 38764.25
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
ALLEN 1600 300 20800 21148
TURNER 1500 0 19500 19545
MILLER 1300 16900 16939
MARTIN 1250 1400 16250 17687.5
WARD 1250 500 16250 16787.5
JAMES 950 12350 12378.5
SMITH 800 10400 10424
--입력된 컬럼 명 바꾸기, 또는 별칭 별명 붙이기(한글로붙이면 깨진다.)
"annual income" 공백을 넣을거면 따옴표로 묶어야 하고 공백을 안쓸거면 무조건 다 붙여 써야한다. annual_income 언더바는 인식 가능
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03) "annual income"
2 from emp
3 order by (sal13)+nvl(comm, 0)+(sal0.03) desc
SQL> /
ENAME SAL COMM SAL*13 annual income
KING 5000 65000 65150
FORD 3000 39000 39090
JONES 2975 38675 38764.25
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
ALLEN 1600 300 20800 21148
TURNER 1500 0 19500 19545
MARTIN 1250 1400 16250 17687.5
MILLER 1300 16900 16939
WARD 1250 500 16250 16787.5
JAMES 950 12350 12378.5
SMITH 800 10400 10424
12 rows selected.
--별칭을 붙이면 별칭으로 정렬하면 된다.
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03) "annual income"
2 from emp
3 order by "annual income" desc
SQL> /
ENAME SAL COMM SAL*13 annual income
KING 5000 65000 65150
FORD 3000 39000 39090
JONES 2975 38675 38764.25
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
ALLEN 1600 300 20800 21148
TURNER 1500 0 19500 19545
MARTIN 1250 1400 16250 17687.5
MILLER 1300 16900 16939
WARD 1250 500 16250 16787.5
JAMES 950 12350 12378.5
SMITH 800 10400 10424
--기호 대소문자 구분 가능!! as는 생략가능
1 select ename, sal, comm, sal13, (sal13)+nvl(comm, 0)+(sal0.03) as "Annual Income!!@@###"
2 from emp
3 /
SQL> /
ENAME SAL COMM SAL*13 Annual Income!!@@###
KING 5000 65000 65150
FORD 3000 39000 39090
JONES 2975 38675 38764.25
BLAKE 2850 37050 37135.5
CLARK 2450 31850 31923.5
ALLEN 1600 300 20800 21148
TURNER 1500 0 19500 19545
MARTIN 1250 1400 16250 17687.5
MILLER 1300 16900 16939
WARD 1250 500 16250 16787.5
JAMES 950 12350 12378.5
SMITH 800 10400 10424
"alias(별칭)
:컬럼의 별칭. 복잡한 컬럼을 명료하게 명명. 이해도를 높이고 간결하게 사용하고자 하는 목적
문법> 컬럼명 [as] 별칭 ---> order by 절에서 정렬을 해줄떄 사용된다!!!!!!!!!!!!!!!
큰 따옴표로 묶었을 때 ->"별칭" : 공백O, 대소문자 구분, 특수문자
큰 따옴표로 안 묶었을 때 -> 별칭:공백X, 대소문자 구분X, 특수문자X
-- 사원번호가 7369인 사원번호,사원명조회
select empno, ename
2 from emp
3* where empno=7369;
*조건
*논리연산자
NOT
OR => 나열된 조건중에 단 1개이상을 만족 시=> true리턴
AND => 나열된 모든 조건을 만족 시=> true리턴
단, 1개의 조건이라도 불만족 시 => false리턴
-- 부서번호 30인 부서에 근무하면서 급여가 1250 이상인 사번, 이름, 입사일, 부서번호 조회
1 select empno, ename, hiredate, deptno, sal
2 from emp
3* where deptno=30 and sal>=1250
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7499 ALLEN 81/02/20 30 1600
7521 WARD 81/02/22 30 1250
7654 MARTIN 81/09/28 30 1250
7698 BLAKE 81/05/01 30 2850
7844 TURNER 81/09/08 30 1500--정렬까지 추가!
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where deptno=30 and sal>=1250
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7654 MARTIN 81/09/28 30 1250
7521 WARD 81/02/22 30 1250
7844 TURNER 81/09/08 30 1500
7499 ALLEN 81/02/20 30 1600
7698 BLAKE 81/05/01 30 2850--급여가 1250이상이면서 1600이하 조건 추가
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where deptno=30 and sal>=1250 and sal<=1600
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7521 WARD 81/02/22 30 1250
7654 MARTIN 81/09/28 30 1250
7844 TURNER 81/09/08 30 1500
7499 ALLEN 81/02/20 30 1600*Between A and B : A와 B사이 --->결과는 위와 동일 (교재 116)
--급여가 1250이상이상이면서 급여가 1600이하
급여가 1250이상~1600이하
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where deptno=30 and sal BETWEEN 1250 and 1600
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7521 WARD 81/02/22 30 1250
7654 MARTIN 81/09/28 30 1250
7844 TURNER 81/09/08 30 1500
7499 ALLEN 81/02/20 30 1600/*Between A and B : A와 B사이
-- 부서번호 30인 부서에 근무하는 사원중 급여가 1250 이상이고 1600이하이면서
입사일이 81년 9월에 입사한 사번, 이름, 입사일, 부서번호 조회
hiredate>='81/09/01' AND hiredate<='81/09/30'
hiredate BETWEEN '81/09/01' AND '81/09/30'1 select empno, ename, hiredate, deptno
2 from emp
3 where deptno = 30 and sal BETWEEN 1250 AND 1600 and hiredate BETWEEN '81/09/01' AND '81/09/30'
4* order by deptno asc, sal asc
1 select empno, ename, hiredate, deptno
2 from emp
3 where deptno = 30 and (sal BETWEEN 1250 AND 1600) and (hiredate BETWEEN '81/09/01' AND '81/09/30')
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO 7654 MARTIN 81/09/28 30
7844 TURNER 81/09/08 30--급여가 1250이거나 1600또는 3000아니면 4000 받는 사원조회
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where sal = 1250 or sal = 1600 or sal = 3000 or sal = 4000
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7902 FORD 81/12/03 20 3000
7521 WARD 81/02/22 30 1250
7654 MARTIN 81/09/28 30 1250
7499 ALLEN 81/02/20 30 1600--IN(값1, 값2....값N)=>()안에 나열된 값과 일치
//위와 결과 동일
//in 은 괄호 안에 열거된 것과 일치되는 것을 모두 나열해라
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where sal in(1250, 1600, 3000, 4000)
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7902 FORD 81/12/03 20 3000
7521 WARD 81/02/22 30 1250
7654 MARTIN 81/09/28 30 1250
7499 ALLEN 81/02/20 30 1600--NOT IN(값1, 값2....값N)=>()안에 나열된 값과 일치하지 않는 값 나열
//
//NOT in 은 괄호 안에 열거된 것과 일치되는 것 외에 모두 나열해라
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where NOT (sal in(1250, 1600, 3000, 4000))
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7934 MILLER 82/01/23 10 1300
7782 CLARK 81/06/09 10 2450
7839 KING 81/11/17 10 5000
7369 SMITH 80/12/17 20 800
7566 JONES 81/04/02 20 2975
7900 JAMES 81/12/03 30 950
7844 TURNER 81/09/08 30 1500
7698 BLAKE 81/05/01 30 2850
//결과는 동일1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where sal not in(1250, 1600, 3000, 4000)
4* order by sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7369 SMITH 80/12/17 20 800
7900 JAMES 81/12/03 30 950
7934 MILLER 82/01/23 10 1300
7844 TURNER 81/09/08 30 1500
7782 CLARK 81/06/09 10 2450
7698 BLAKE 81/05/01 30 2850
7566 JONES 81/04/02 20 2975
7839 KING 81/11/17 10 50008 rows selected.
--BETWEEN에 NOT연산자 추가 예제 (NOT BETWEEN A AND B: A와 B사이가 아닌)
--급여가 1250~1600 사이가 아닌 사원 조회
1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where NOT(sal BETWEEN 1250 AND 1600)
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7782 CLARK 81/06/09 10 2450
7839 KING 81/11/17 10 5000
7369 SMITH 80/12/17 20 800
7566 JONES 81/04/02 20 2975
7902 FORD 81/12/03 20 3000
7900 JAMES 81/12/03 30 950
7698 BLAKE 81/05/01 30 2850
//결과는 위와 동일.1 select empno, ename, hiredate, deptno, sal
2 from emp
3 where sal NOT BETWEEN 1250 AND 1600
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL 7782 CLARK 81/06/09 10 2450
7839 KING 81/11/17 10 5000
7369 SMITH 80/12/17 20 800
7566 JONES 81/04/02 20 2975
7902 FORD 81/12/03 20 3000
7900 JAMES 81/12/03 30 950
7698 BLAKE 81/05/01 30 28507 rows selected.
*NOT 연산자
-- 부서번호 30인 부서에 근무하는 사원중 급여가 1250 이상이고 1600이하이면서
입사일이 81년 9월에 입사한 사람을 빼고!!! 사번, 이름, 입사일, 부서번호 조회
1 select empno, ename, hiredate, deptno
2 from emp
3 where NOT(deptno = 30 and (sal BETWEEN 1250 AND 1600) and (hiredate BETWEEN '81/09/01' AND '81/09/30'))
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO 7934 MILLER 82/01/23 10
7782 CLARK 81/06/09 10
7839 KING 81/11/17 10
7369 SMITH 80/12/17 20
7566 JONES 81/04/02 20
7902 FORD 81/12/03 20
7900 JAMES 81/12/03 30
7521 WARD 81/02/22 30
7499 ALLEN 81/02/20 30
7698 BLAKE 81/05/01 3010 rows selected.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&먼저 진행했던 논리연산자 쿼리&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
--research 부서 또는 부서번호가 10인 부서에 대한 정보 조회
1 select *
2 from dept
3 where dname = 'RESEARCH' or deptno = 10
4* order by dname asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
-- 달라스 또는 뉴욕에 있는 부서는?
SQL> select *
2 from dept
3 where loc = 'DALLAS' or loc = 'NEW YORK';
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLASUPPER 함수에 오름차순 까지 사용
1 select *
2 from dept
3 where loc = UPPER('dallas') or loc = UPPER('new york')
4* order by dname asc
SQL> .
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
--사번, 사원명, 급여, 입사일 조회
1 select empno, ename, sal, hiredate
2* from emp
SQL> /
EMPNO ENAME SAL HIREDATE 7369 SMITH 800 80/12/17
7499 ALLEN 1600 81/02/20
7521 WARD 1250 81/02/22/
7566 JONES 2975 81/04/02
7654 MARTIN 1250 81/09/28
7698 BLAKE 2850 81/05/01
7782 CLARK 2450 81/06/09
7839 KING 5000 81/11/17
7844 TURNER 1500 81/09/08
7900 JAMES 950 81/12/03
7902 FORD 3000 81/12/03
7934 MILLER 1300 82/01/2312 rows selected.
--7369이거나, 급여가 3000이상인 사원 조회
1 select empno, ename, sal, hiredate
2 from emp
3* where empno=7369 or sal>=3000
SQL> /
EMPNO ENAME SAL HIREDATE 7369 SMITH 800 80/12/17
7839 KING 5000 81/11/17
7902 FORD 3000 81/12/03--위에 조건에 입사일이 81/06/25 이후에 입사한 사원 추가
1 select empno, ename, sal, hiredate
2 from emp
3* where empno=7369 or sal>=3000 or hiredate>='81/06/26'
SQL> /
EMPNO ENAME SAL HIREDATE 7369 SMITH 800 80/12/17
7654 MARTIN 1250 81/09/28
7839 KING 5000 81/11/17
7844 TURNER 1500 81/09/08
7900 JAMES 950 81/12/03
7902 FORD 3000 81/12/03
7934 MILLER 1300 82/01/237 rows selected.
--위에 조건에 급여 오름차순 추가!!!!
1 select empno, ename, sal, hiredate
2 from emp
3 where empno=7369 or sal>=3000 or hiredate>='81/06/26'
4* order by sal asc
SQL> /
EMPNO ENAME SAL HIREDATE 7369 SMITH 800 80/12/17
7900 JAMES 950 81/12/03
7654 MARTIN 1250 81/09/28
7934 MILLER 1300 82/01/23
7844 TURNER 1500 81/09/08
7902 FORD 3000 81/12/03
7839 KING 5000 81/11/177 rows selected.
--위에 조건에 부서번호가 30인 사람까지 추가 출력.!!! 그리고 컬럼도 추가!
1 select deptno, empno, ename, sal, hiredate
2 from emp
3 where empno=7369 or sal>=3000 or hiredate>='81/06/26' or deptno=30
4* order by sal asc
SQL> /
DEPTNO EMPNO ENAME SAL HIREDATE 20 7369 SMITH 800 80/12/17
30 7900 JAMES 950 81/12/03
30 7521 WARD 1250 81/02/22
30 7654 MARTIN 1250 81/09/28
10 7934 MILLER 1300 82/01/23
30 7844 TURNER 1500 81/09/08
30 7499 ALLEN 1600 81/02/20
30 7698 BLAKE 2850 81/05/01
20 7902 FORD 3000 81/12/03
10 7839 KING 5000 81/11/1710 rows selected.
위에 조건에서 1차로 부서번호 오름차순으로 정렬하고 2차로 그안에서 부서번호 같은 애들끼리는 연봉순으로 오름차순 정렬해!
1 select empno, ename, deptno, sal, hiredate
2 from emp
3 where empno=7369 or sal>=3000 or hiredate>='81/06/26' or deptno=30
4* order by deptno asc, sal asc
SQL> /
EMPNO ENAME DEPTNO SAL HIREDATE 7934 MILLER 10 1300 82/01/23
7839 KING 10 5000 81/11/17
7369 SMITH 20 800 80/12/17
7902 FORD 20 3000 81/12/03
7900 JAMES 30 950 81/12/03
7521 WARD 30 1250 81/02/22
7654 MARTIN 30 1250 81/09/28
7844 TURNER 30 1500 81/09/08
7499 ALLEN 30 1600 81/02/20
7698 BLAKE 30 2850 81/05/0110 rows selected.
-- 사원번호가 7369이거나 급여가 3000이상 또는 입사일이 81/06/25 이후에 입사한
사원번호, 사원명, 부서번호, 급여, 입사일을 부서번호 오름차순, 부서번호가 같으면 급여를 적게받는 사원부터, 동일부서내 급여도 같다면 입사일이 늦은 사원부터 정렬 하여 출력!
1 select empno, ename, deptno, sal, hiredate
2 from emp
3 where empno=7369 or sal>=3000 or hiredate>='81/06/26' or deptno=30
4* order by deptno asc, sal asc, hiredate desc
SQL> /
EMPNO ENAME DEPTNO SAL HIREDATE 7934 MILLER 10 1300 82/01/23
7839 KING 10 5000 81/11/17
7369 SMITH 20 800 80/12/17
7902 FORD 20 3000 81/12/03
7900 JAMES 30 950 81/12/03
7654 MARTIN 30 1250 81/09/28
7521 WARD 30 1250 81/02/22
7844 TURNER 30 1500 81/09/08
7499 ALLEN 30 1600 81/02/20
7698 BLAKE 30 2850 81/05/0110 rows selected.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
*비교연산자
a=b : 같다
a!=b, a<>b : 같지않다 (같지 않다는 것은 크거나 작다는 뜻)
a>b : a는 b보다 크다 (초과)
a>=b : a는 b보다 크거나 같다 (이상)
a<b : a는 b보다 작다 (미만)
a<=b : a는 b보다 작거나 같다 (이하)
주의. 문자는 ''(작은따옴표)로 감싸야 한다.
--81년 2월 20일에 입사한 사원번호, 이름, 입사일 조회
1 select empno, ename, hiredate
2* from emp where hiredate = '81/02/20'
2* from emp where hiredate = '81-02-20' --->이것도 되네????
SQL> /
EMPNO ENAME HIREDATE 7499 ALLEN 81/02/20--사원명이 FORD인 사원번호, 사원이름을 조회!!!
1 select empno, ename
2 from emp
3* where ename='FORD'
SQL> /
EMPNO ENAME 7902 FORD1 select empno, ename
2 from emp
3* where empno!=7369
SQL> 사번이 7369와 같지 않은 것 출력
EMPNO ENAME 7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
7934 MILLER--같지 않다는 것은 크거나 작다는 뜻이므로!!!(위와 동일한 결과 출력)
1 select empno, ename
2 from emp
3* where empno<>7369
SQL> /
EMPNO ENAME 7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
7934 MILLER11 rows selected.
--문자데이터는 맞춤법, 스펠링, 대소문자가 정확히 일치해야한다.
--대소문자 구분 해서 정확히 쿼리문 입력 해야한다.
1 select empno, ename
2 from emp
3* where ename='Ford'
SQL> /
no rows selected
--UPPER()대문자로 변환해주는 함수 사용
SQL> ed
Wrote file afiedt.buf
1 select empno, ename
2 from emp
3* where ename=UPPER('Ford')
SQL> /
EMPNO ENAME 7902 FORD--실제 출력되는건 셀렉트 절에 있는 empno, ename 이므로 ename을 LOWER()함수 처리하면 된다.
1 select empno, ename, LOWER(ename)
2 from emp
3* where ename=UPPER('Ford') --->>>안에 유저가 입력한 Ford는 입력값이라 보면된다
SQL> /
EMPNO ENAME LOWER(ENAME) 7902 FORD fordINITCAP(char) : char의 첫 문자는 대문자로, 나머지는 소문자로 반환
첫 문자 인식 기준은 공백 그리고 알파벳과 숫자를 제외한 문자
LOWER(char) : 소문자 변환 후 반환
UPPER(char) : 대문자 변환 후 반환
--대소문자 상관없이 사원명이 FORD인 사원의 이름 출력되는건
-- ename => 사원명컬럼에 입력된 그대로 출력
INITCAP(ename) => 첫 문자는 대문자로 출력
LOWER(ename) => 소문자 변환 후, 출력
UPPPER('Ford') => 대문자 변환 후, 출력
1 select ename, INITCAP(ename), LOWER(ename), UPPER('Ford')
2 from emp
3* where ename=UPPER('Ford')
SQL> /.
ENAME INITCAP(ENAME) LOWER(ENAME) UPPER('F
FORD Ford ford FORD
-- 별명쓰려면 정렬문 까지 써야한다.별 의미는 없다.
1 select ename, INITCAP(ename), LOWER(ename) as "ename"
2 from emp
3 where ename=UPPER('Ford')
4* order by "ename" desc
SQL> /
ENAME INITCAP(ENAME) ename
FORD Ford ford
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
-------*|| 연결연산자(concat과 유사) (교재 p112)
SQL> select deptno, dname
2 from dept
3 order by dname asc;
DEPTNO DNAME 10 ACCOUNTING
40 OPERATIONS
20 RESEARCH
30 SALESSQL> ed
Wrote file afiedt.buf
1 select deptno || dname
2 from dept
3* order by dname asc
SQL> /
DEPTNO||DNAME
10ACCOUNTING
40OPERATIONS
20RESEARCH
30SALES
SQL> ed
Wrote file afiedt.buf
1 select deptno || ' : ' || dname
2 from dept
3* order by dname asc
SQL> /
DEPTNO||':'||DNAME
10 : ACCOUNTING
40 : OPERATIONS
20 : RESEARCH
30 : SALES
---부서번호 40 을 찾는 조건 where 절까지 추가
1 select deptno || ' : ' || dname
2 from dept
3 where deptno = 40
4* order by dname asc
SQL> /
DEPTNO||':'||DNAME
40 : OPERATIONS
--부서번호가 40이거나 30인 부서번호, 부서명 조회
1 select deptno, dname
2 from dept
3 where deptno = 40 or deptno=30
4* order by dname asc
SQL> /
DEPTNO DNAME 40 OPERATIONS
30 SALES--concat()
CONCAT(char1, char2) : 두 문자를 붙여 반환 (교재 p131)
예) CONCAT('I Have', ' A Dream') I Have A Dream
1 select 'empName:', ename
2* from emp
SQL> /
'EMPNAME:' ENAME
empName: SMITH
empName: ALLEN
empName: WARD
empName: JONES
empName: MARTIN
empName: BLAKE
empName: CLARK
empName: KING
empName: TURNER
empName: JAMES
empName: FORD
empName: MILLER
12 rows selected.
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1 select concat('empName: ', ename)
2* from emp
SQL> /
CONCAT('EMPNAME:',ENAME)
empName: SMITH
empName: ALLEN
empName: WARD
empName: JONES
empName: MARTIN
empName: BLAKE
empName: CLARK
empName: KING
empName: TURNER
empName: JAMES
empName: FORD
empName: MILLER
12 rows selected.
1 select concat(ename, job)
2* from emp
SQL> /
CONCAT(ENAME,JOB)
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
KINGPRESIDENT
TURNERSALESMAN
JAMESCLERK
FORDANALYST
MILLERCLERK
--함수안에 함수가 있으면
=> 함수가 중첩되어있으면
=> 가장 안쪽에 위치한 함수부터 실행된다.
--아래에서는 안쪽에 위치한 concat(ename, ' : ')부터 실행된다.
'MILLER : '
--바깥쪽에 있는 concat('MILLER : ', job)이 실행되었다.
--concat함수 2번 쓰면 컬럼끼리 중간에 문자를 삽입하여 이을수 있다.
1 select concat(concat(ename, +' : '), job)
2* from emp
SQL> /
CONCAT(CONCAT(ENAME,+':'),JOB)
SMITH : CLERK
ALLEN : SALESMAN
WARD : SALESMAN
JONES : MANAGER
MARTIN : SALESMAN
BLAKE : MANAGER
CLARK : MANAGER
KING : PRESIDENT
TURNER : SALESMAN
JAMES : CLERK
FORD : ANALYST
MILLER : CLERK
12 rows selected.
*NULL조건
--커미션이 미확정된 사원조회
=>커미션이 null인 사원조회
where comm not in(0, 300, 500, 1400)
null은 = 이것이 아니라 is로 해야한다.
1 select empno, ename, hiredate, deptno, sal, comm
2 from emp
3 where comm is NULL
4* order by comm asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL COMM 7369 SMITH 80/12/17 20 800
7566 JONES 81/04/02 20 2975
7698 BLAKE 81/05/01 30 2850
7934 MILLER 82/01/23 10 1300
7839 KING 81/11/17 10 5000
7900 JAMES 81/12/03 30 950
7902 FORD 81/12/03 20 3000
7782 CLARK 81/06/09 10 24508 rows selected.
is NOT NULL
=>커미션이 확정된 사원조회
1 select empno, ename, hiredate, deptno, sal, comm
2 from emp
3 where comm is NOT NULL
4 order by comm asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL COMM 7844 TURNER 81/09/08 30 1500 0
7499 ALLEN 81/02/20 30 1600 300
7521 WARD 81/02/22 30 1250 500
7654 MARTIN 81/09/28 30 1250 1400*애매모호한 조건
LIKE '~~'
*wild card
*wild card의 와 %가 아닌 단순한 특수문자임을 표현하기 위한 방법
문법> *escape '문자' 또는 '특수문자', '기호'
'문자', '문자'% 이런식으로 적용
% : 0글자 이상의 자릿수
_ : 1글자 자릿수
LIKE 'T%' : 대문자 T로 시작하는 .....거 다 찾아라
~ 몇글자가 되든지
LIKE '%R' : 대문자 R로 끝나는 .....거 다 찾아라
몇글자가 되든지
특정글자로 시작
1 select empno, ename, hiredate, deptno, sal, comm
2 from emp
3 where ename LIKE 'T%'
4* order by comm asc
SQL> /
EMPNO ENAME HIREDATE DEPTNO SAL COMM 7844 TURNER 81/09/08 30 1500 0-- 이름에 A가 포함된 사원명 조회~
1 select empno, ename
2 from emp
3 where ename LIKE '%A%'
4* order by comm asc
SQL> /
EMPNO ENAME 7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7782 CLARK
7900 JAMES6 rows selected.
-- 이름에 2번쨰 글자가 A인 사원명 조회 --->_언더바를 이용
1 select empno, ename
2 from emp
3 where ename LIKE '_A%'
4* order by comm asc
SQL> /
EMPNO ENAME 7521 WARD
7654 MARTIN
7900 JAMES--이름 'M'으로 시작해서 'R'로 끝나는 사원명 조회~
1 select empno, ename
2 from emp
3 where ename LIKE 'M%R'
4* order by comm asc
SQL>
SQL> /
EMPNO ENAME 7934 MILLER--이름이 'M'으로 시작해서 'R'로 끝나는 이름 글자수가 6인 사원명 조회
1 select empno, ename
2 from emp
3 where ename LIKE 'M____R'
4* order by comm asc
SQL> /
EMPNO ENAME 7934 MILLER++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--사번, 사원명, 급여, 업무
--업무 오름차순
SQL> select empno, ename, sal, job
2 from emp
3 order by job asc;
EMPNO ENAME SAL JOB 7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
7900 JAMES 950 CLERK
7369 SMITH 800 CLERK
7566 JONES 2975 MANAGER
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7521 WARD 1250 SALESMAN
7499 ALLEN 1600 SALESMAN
7654 MARTIN 1250 SALESMAN12 rows selected.
GROUP BY
--동일 업무를 하는 사원들의 사번, 사원명, 급여, JOB조회 -->그룹바이로 할 수 없음.
1 select empno, ename, sal, job
2 from emp
3 group by job
SQL> /
select empno, ename, sal, job
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
============> 이유 : GROUP BY 절에서 사용된 컬럼이 아닌 일반 컬럼은 select절에서 작성하면 안된다.
해결방법 : GROUP BY 그룹바이에 기준이 되는 컬럼만 셀렉트절에 쓸 수 있다!!!!!! 나머지 컬럼은 모두 삭제 해야한다.
1 select job
2 from emp
3* group by job
SQL> /
JOB
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
//대신에 count 함수를 이용해서 카운트를 해준다. --->업무별 사원수 조회! 그럴때 그룹바이와 카운트를 쓴다.
1 select job, count(job)
2 from emp
3* group by job
SQL> /
JOB COUNT(JOB)
CLERK 3
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 1
---업무별 사원수 조회, 최고급여, 최저급여, 총급여, 평균급여 조회~
1 select job, count(job), max(sal), min(sal), sum(sal), avg(sal)
2 from emp
3* group by job
SQL> /
JOB COUNT(JOB) MAX(SAL) MIN(SAL) SUM(SAL) AVG(SAL)
CLERK 3 1300 800 3050 1016.66667
SALESMAN 4 1600 1250 5600 1400
PRESIDENT 1 5000 5000 5000 5000
MANAGER 3 2975 2450 8275 2758.33333
ANALYST 1 3000 3000 3000 3000
//정렬까지 ~
1 select job, count(job), max(sal), min(sal), sum(sal), avg(sal)
2 from emp
3 group by job
4* order by count(job) desc
SQL> /
JOB COUNT(JOB) MAX(SAL) MIN(SAL) SUM(SAL) AVG(SAL)
SALESMAN 4 1600 1250 5600 1400
MANAGER 3 2975 2450 8275 2758.33333
CLERK 3 1300 800 3050 1016.66667
ANALYST 1 3000 3000 3000 3000
PRESIDENT 1 5000 5000 5000 5000
--부서별 사원수, 부서에서 제일 많이 받는 최고급여, 최저급여, 부서별 평균급여
1 select deptno, count(deptno), max(sal), min(sal), sum(sal), avg(sal)
2 from emp
3 group by deptno
4* order by avg(sal) desc
SQL> /
DEPTNO COUNT(DEPTNO) MAX(SAL) MIN(SAL) SUM(SAL) AVG(SAL) 10 3 5000 1300 8750 2916.66667
20 3 3000 800 6775 2258.33333
30 6 2850 950 9400 1566.66667--count(컬럼명) : 해당컬럼의 확정값을 카운트 한다.(NULL)제외
--count(*) : null푸함해서 모든 값을 카운트 한다.
==>아래코드에서 comm에는 (0, 300, 500, 1400) 데이터만 카운트해서 4라는 결과가 나온다.
count(deptno), count(job), count(*) ..... 모두 같은 값.
기준은 DEPTNO이고 컬럼에 입력된 값의 개수로 카운트 하기 때문에 comm 빼고는 모두 같은 값 출력 (확정값만 카운트한다! NULL은 미확정값)
*은 NULL이든 아니든 다 카운트한다!
1 select count(), count(empno), count(ename), count(job), count(sal), count(comm), count(deptno)
2 from emp
SQL> /
COUNT(*) COUNT(EMPNO) COUNT(ENAME) COUNT(JOB) COUNT(SAL) COUNT(COMM) COUNT(DEPTNO)
12 12 12 12 12 4 12※그래서 카운트 할때는 count(*)보다는 데이터가 다 들어가 있는 컬럼을 카운트 하는게 좋다. NOT NULL PK 컬럼!!! empno
--동일부서에 근무하는 사원조회
1 select deptno, count(deptno)
2 from emp
3* group by deptno
SQL> /
DEPTNO COUNT(DEPTNO) 30 6
20 3
10 3--동일부서내 같은 일을 하는 사원 조회
1 select deptno, job, count(job)
2 from emp
3 group by deptno, job
4* order by deptno asc, job asc
SQL> /
DEPTNO JOB COUNT(JOB) 10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 1
20 CLERK 1
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 49 rows selected.
DEPTNO 를 그룹으로 묶은 결과내에서 동일한 JOB을 가진 사람들의 수를 count한다..
근데 1명이어도... 출력... 앞에서 먼저 필터되어서 다 출력 하는듯???
즉먼저 부서번호로 필터링 된 인원들의 job별로 인원을 출력!~
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
분석 함수 !!!!!보고서 작성 시 매우 중요!
1 select deptno, job, count(job)
2 from emp
3 group by cube(deptno, job)
4* order by deptno asc, job asc
SQL> /
DEPTNO JOB COUNT(JOB) 10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
10 3
20 ANALYST 1
20 CLERK 1
20 MANAGER 1
20 3
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
30 6
ANALYST 1
CLERK 3
MANAGER 3
PRESIDENT 1
SALESMAN 4
1218 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select deptno, job, count(job)
2 from emp
3 group by rollup(deptno, job)
4* order by deptno asc, job asc
SQL> /
DEPTNO JOB COUNT(JOB) 10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
10 3
20 ANALYST 1
20 CLERK 1
20 MANAGER 1
20 3
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
30 6
1213 rows selected.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- distict : 중복되는 데이터는 한 번만 출력하기
- -job을 종류별로 조회
1 select distinct job
2 from emp
3* order by job asc
SQL> /
JOB
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
--사원들이 근무하는 부서를 종류별로 조회~ ( not null 왜 넣은 이해 안감);;;
select distinct deptno
from emp
where deptno is NOT NULL
order by deptno asc
SQL> /
DEPTNO 10
20
30--->>--->> dept 테이블로 해야한다고 생각;;;; (그런데 이러면 distinct를 안써도 된다. 부서가 무조건 1개씩만 있는 테이블이라서)
1 select distinct deptno, dname
2 from dept
3 where deptno is NOT NULL
4* order by deptno asc
SQL> /
DEPTNO DNAME 10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
90 dept90
91 dept91
92 dept_92
93 dept%938 rows selected.
--부서별로 근무하는 사원수, 급여총합, 평균급여를 사원수가 많은 부서부터 출력
1 select deptno, count(empno), sum(sal), avg(sal)
2 from emp
3 group by deptno
4* order by count(empno) desc
SQL> /
DEPTNO COUNT(EMPNO) SUM(SAL) AVG(SAL) 30 6 9400 1566.66667
10 3 8750 2916.66667
20 3 6775 2258.33333empno가 NOT NULL 컬럼이기 때문에 수를 셀때는 이런 컬럼을 써주는 것이 좋다.
1 select deptno, count(empno), sum(sal), avg(sal), ceil(avg(sal)), floor(avg(sal))
2 from emp
3 group by deptno
4* order by count(empno) desc
SQL> /
DEPTNO COUNT(EMPNO) SUM(SAL) AVG(SAL) CEIL(AVG(SAL)) FLOOR(AVG(SAL)) 30 6 9400 1566.66667 1567 1566
10 3 8750 2916.66667 2917 2916
20 3 6775 2258.33333 2259 2258평균 구한 값에 대한 올림값 버림값!!!! 더욱 더 보기 편하게!!
소수점 첫째 자리를 DB에서는 0번째 자리로 처리 해야한다.
*ceil() : 소수점 값에 대해서 올림 1566.66667 1567
*floor() : 소수점 값에 대해서 내림 1566.66667 1566
*round(값, 자릿수) : 반올림
- 자릿수 : 일의 자리(-1)1 select round(1566.6712, 0), round(1566.6712, 1), round(1566.6712, 2)
2* from dual
SQL> / : 소수점 첫번째자리(기준, 0) : 소수점 두번째자리(기준, 1) : 소수점 세번째자리(기준, 2)
ROUND(1566.6712,0) ROUND(1566.6712,1) ROUND(1566.6712,2)
1567 1566.7 1566.67--아무것도 안지정하면 소수점 첫번쨰 자리 (기준0번째 자리)에서 처리!
1 select round(1566.6712), round(1566.6712, 0), round(1566.6712, 1), round(1566.6712, 2)
2* from dual
SQL> /
ROUND(1566.6712) ROUND(1566.6712,0) ROUND(1566.6712,1) ROUND(1566.6712,2)
1567 1567 1566.7 1566.67--일의 자리에서 반올림! -1
1 select round(1566.6712, -1), round(1566.6712), round(1566.6712, 0), round(1566.6712, 1), round(1566.6712, 2)
2* from dual
SQL> /
ROUND(1566.6712,-1) ROUND(1566.6712) ROUND(1566.6712,0) ROUND(1566.6712,1) ROUND(1566.6712,2)
1570 1567 1567 1566.7 1566.67----부서별로 근무하는 사원수, 소수점 첫번째 자리에서 반올림한 평균급여를 사원수가 많은 부서부터 출력
-- 반올림함수를 사용!!!!
1 select deptno, count(deptno), round(avg(sal))
2 from emp
3 group by deptno
4* order by count(deptno) desc
SQL> /
DEPTNO COUNT(DEPTNO) ROUND(AVG(SAL)) 30 6 1567
10 3 2917
20 3 2258----부서별로 근무하는 사원수를 출력하고, p152 (having절)
근무하는 사원수가 6명인 부서의(먼저 group by를 하고 having 절을 이용하라는 얘기)소수점 첫번째 자리에서 반올림한 평균급여를 사원수가 많은 부서부터 출력
1 select deptno, count(deptno), round(avg(sal))
2 from emp
3 group by deptno
4 having count(deptno) = 6
5* order by count(deptno) desc
SQL> /
DEPTNO COUNT(DEPTNO) ROUND(AVG(SAL)) 30 6 1567--6명 이하라서 다 출력
1 select deptno, count(deptno), round(avg(sal))
2 from emp
3 group by deptno
4 having count(deptno)<= 6
5* order by count(deptno) desc
SQL> /
DEPTNO COUNT(DEPTNO) ROUND(AVG(SAL)) 30 6 1567
10 3 2917
20 3 2258--부서별 사원들이 받는 평균급여가 2917 이상인
부서번호, 사원수, 평균급여 출력
단, 평균급여는 소수점 첫번쨰 자리에서 반올림.
1 select deptno, count(deptno), round(avg(sal), 0)
2 from emp
3 group by deptno
4* having round(avg(sal), 0)>= 2917
SQL> /
DEPTNO COUNT(DEPTNO) ROUND(AVG(SAL),0) 10 3 2917****그냥 팁!!!!
where 절로 1개의 조건을 지정하는 쿼리 문에서는 일반 컬럼만 사용 하든지 아니면 sum, avg 같은 그룹단위의 컬럼만 쓰든지 해야한다!!!!
밑에 그룹바이 절과 해빙절 까지 써야 셀렉트절에 일반 컬럼과 avg, sum, min, max 같은 컬럼 같이 사용가능
-- 같은 업무를 하는 사원들의 최저급여, 최고급여를 조회, 단, 많은 급여순로 정렬
1 select min(sal), max(sal)
2 from emp
3 group by job
4* order by max(sal) desc
SQL> /
MIN(SAL) MAX(SAL)
5000 5000
3000 3000
2450 2975
1250 1600
800 1300--위의 결과에서 최고급여가 1600~3000인 결과만 출력
1 select job, min(sal), max(sal)
2 from emp
3 group by job
4 having max(sal) BETWEEN 1600 and 3000
5* order by max(sal) desc
SQL> /
JOB MIN(SAL) MAX(SAL)
ANALYST 3000 3000
MANAGER 2450 2975
SALESMAN 1250 1600
--비트윈 말고 그냥 비교연산 구문으로 (결과는 위와 동일)
1 select job, min(sal), max(sal)
2 from emp
3 group by job
4 having max(sal)>= 1600 and max(sal)<=3000
5* order by max(sal) desc
SQL> /
JOB MIN(SAL) MAX(SAL)
ANALYST 3000 3000
MANAGER 2450 2975
SALESMAN 1250 1600
--10번 또는 30번 부서에 근무하는 사원수, 평균급여, 최고급여를
1 select deptno, count(empno), min(sal), max(sal), max(sal)-min(sal)
2 from emp
3 where deptno =10 or deptno = 30
4 group by deptno
5* order by max(sal)-min(sal) asc
SQL> /
DEPTNO COUNT(EMPNO) MIN(SAL) MAX(SAL) MAX(SAL)-MIN(SAL) 30 6 950 2850 1900
10 3 1300 5000 37001 select deptno, count(empno), min(sal), max(sal), max(sal)-min(sal)
2 from emp
3 group by deptno
4 having deptno =10 or deptno = 30
5* order by max(sal)-min(sal) asc
SQL> /
DEPTNO COUNT(EMPNO) MIN(SAL) MAX(SAL) MAX(SAL)-MIN(SAL) 30 6 950 2850 1900
10 3 1300 5000 37001 select deptno, count(empno), min(sal), max(sal), max(sal)-min(sal)
2 from emp
3 group by deptno
4 having deptno in (10, 30)
5* order by max(sal)-min(sal) asc
SQL> /
DEPTNO COUNT(EMPNO) MIN(SAL) MAX(SAL) MAX(SAL)-MIN(SAL) 30 6 950 2850 1900
10 3 1300 5000 3700where 절과 having 절 둘다 쓸수 있다면 where절에 먼저 써서 필터링을 하는것이 훨씬 퍼포먼스가 좋은 쿼리문!!!!!
내생각!
(where절을 먼저 써서 10, 30 을 필터링해도 되고 그룹바이 다음에 해빙절을 써서 조건을 10, 30 필터를 걸어도 같은데..
국어를 잘해야할듯... 그룹으로 묶은 다음의 조건인지 그룹묶기 전에 조건인지...)
--10을 숫자가 아닌 문자타입으로 넣었을떄 문자 -> 숫자 형변환 함수 사용!
1 select deptno, count(empno), min(sal), max(sal), max(sal)-min(sal)
2 from emp
3 where deptno = to_number('10') or deptno = 30
4 group by deptno
5* order by max(sal)-min(sal) asc
SQL> /
DEPTNO COUNT(EMPNO) MIN(SAL) MAX(SAL) MAX(SAL)-MIN(SAL) 30 6 950 2850 1900
10 3 1300 5000 3700SQL> ed
--30을 숫자가 아닌 문자타입으로 변경 -> 문자 형변환 함수 사용!
3 where deptno = to_number('10') or deptno = to_char(30) --->이거 아닌지 의심...ㅋㅋ
3 where deptno = to_number('10') or deptno = to_char('30') -->이것도 되긴 한다.
3 where deptno = to_number('10') or to_char(deptno) = '30' --> 이것도 동일 (문자를 문자로 변경하는것)
1 select deptno, count(empno), min(sal), max(sal), max(sal)-min(sal)
2 from emp
3 where deptno = to_number('10') or to_char(deptno) = 30
4 group by deptno
5* order by max(sal)-min(sal) asc
SQL> /
DEPTNO COUNT(EMPNO) MIN(SAL) MAX(SAL) MAX(SAL)-MIN(SAL) 30 6 950 2850 1900
10 3 1300 5000 3700*함수(4장)
-내장 함수
-사용자정의 함수 => PL/SQL
-암묵적 함수
-명시적 함수
*데이터 타입
-문자 함수
-char
-varchar2
-숫자 함수
-number
-날짜 함수
-date
*형변환 함수
to_number(문자) : 문자 -> 숫자
to_char(
) : ~
들어가는 데이터 타입을 문자타입으로 변경
(숫자 또는 날짜) : 숫자 -> 문자, 날짜 -> 문자
to-date(문자) : 문자 -> 날짜
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
*집합연산자(p163)
-제한사항 :
1)집합연산자로 연결되는 각 select문의 select리스트의 개수와 데이터 타입은 일치해야한다.
2)집합연산자로 select문을 연결할 때 order by 절은 맨 마지막 문장에서만 사용.
select 컬럼명
from 테이블명
집합연산자
select 컬럼명
from 테이블명;
--교집합(intersect) -->값이 겹쳐지는 동일한 컬럼이 있는 테이블끼리 해야한다.
1 select deptno
2 from dept
3 intersect
4 select deptno
5* from emp
SQL> /
DEPTNO 10
20
30--차집합(minus)
1 select deptno
2 from dept
3 minus
4 select deptno
5* from emp
SQL> /
DEPTNO 40
90
91
92
93--합집합(union)중복값 제외 출력 / (union all) 중복값 상관없이 두 컬럼 값 모두 출력
1 select deptno
2 from dept
3 union
4 select deptno
5* from emp
SQL> /
DEPTNO 10
20
30
40
90
91
92
938 rows selected. 1 select deptno
2 from dept
3 union all
4 select deptno
5* from emp
SQL> /
DEPTNO 10
20
30
40
90
91
92
93
20
30
30
20
30
30
10
10
30
30
20
1020 rows selected.
★★★★★★★함수
★★★★★★★join, subquery
★★★★★★★DML
조회~
selct { | 컬럼명, 컬럼명} -->컬럼명 여러개 가능 다 출력은 *조회~
selct { | 컬럼명, [AS] alias} -->컬럼명 여러개 가능 다 출력은 *
from 테이블
[where 조건]
[group by 그룹기준]
[group by 그룹1차기준, ...그룹n차 기준];
[having 그룹조건] --> 그룹바이의 조건절 독단적으로 쓸수 없다. 무조건 그룹바이 절 선행되어야 한다.
[ORDER BY 정렬방법];
[ORDER BY 정렬1차기준 정렬방법, ..., 정렬n차 기준 정렬방법];
*정렬방법 -->정렬방법을 기재하지 않으면 기본 오름차순!
- 오름차순(기본) : 1
100 작은수에서 큰수로A->Z, ㄱ->ㅎ, 예전->최근 - 내림차순 : 100
1 큰수에서 작은수로z->A, ㅎ->ㄱ, 최근->예전
*입력 (p.95)
INSERT into 테이블명[(컬럼명, 컬럼명, ...컬럼명)] --->테이블 안에 모든 컬럼에 데이터를 넣어줄거면 컬럼명 생략가능
values(값, 값, ......값); [where 조건]
--부서테이블에 90, dept90, SEOUL 입력!
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
1 INSERT into dept(deptno, dname, loc)
2* values(90, 'dept90', 'SEOUL')
SQL> /
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
90 dept90 SEOUL&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
1 insert into dept(deptno, dname, loc)
2* values(91, 'dept91', 'JEJU')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into dept(deptno, dname, loc)
2* values(92, 'dept_92', 'DOKDO')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into dept(deptno, dname, loc)
2* values(93, 'dept%93', 'PUSAN')
SQL> /
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
90 dept90 SEOUL
91 dept91 JEJU
92 dept_92 DOKDO
93 dept%93 PUSAN8 rows selected.
SQL> commit;
Commit complete.
--부서명에 가 포함된 부서번호, 부서명, 지역 조회
1 select deptno, dname, loc
2 from dept
3* where dname LIKE '%$%' escape '$'
SQL> /
DEPTNO DNAME LOC 92 dept_92 DOKDO--부서명에 %문자가 포함된 부서 조회
1 select deptno, dname, loc
2 from dept
3* where dname LIKE '%@%%' escape '@'
SQL> /
DEPTNO DNAME LOC 93 dept%93 PUSAN&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
*수정
UPDATE
[where 조건]
*삭제
Delete
[where 조건]
*query문(질의어)
SQLPLUS 명령어
;생략가능
단어축약
show user : 접속중인 user명 조회
conn[ect]:다른 user로 접속
exit:종료
ed[it] : 버퍼에 저장된 쿼리문 불러와 편집
/:실행(run) 실행했던 쿼리문을 보여주지 않고 실행한다.
run : 실행했던 쿼리문을 보여주고 실행한다.
@
desc[ribe]
set linesize 400 행당 출력할 글자 수 설정
set pagesize 200 출력할 행 수 설정
----연습----
--부서번호 10 인 사람 출력
1 select *
2* from emp where deptno = '10'
SQL> /
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
emp에서 mgr이 7698 인 사람들을 내림차순으로 출력1 select *
2* from emp where mgr = '7698' order by ename desc
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30dept 테이블에 deptno를 내림차순으로 모두 출력 (2가지방법 )
SQL> select *
2 from dept order by deptno desc;
DEPTNO DNAME LOC 40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK1 select deptno, dname, loc
2* from dept order by deptno desc
SQL> /
DEPTNO DNAME LOC 40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
dept 테이블에 deptno를 오름차순으로 모두 출력 (2가지방법 ) --->asc 굳이 안해줘도 오름차순으로 출력 된다. 기본값이라.1 select *
2* from dept order by deptno asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 1 select deptno, dname, loc
2* from dept order by deptno asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONdept 테이블에 dname을 오름차순으로 모두 출력
1 select [deptno, dname, loc]
2 from dept order by dname asc
SQL> /
DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
30 SALES CHICAGOdept 테이블에 loc를 사전순으로 모두 출력(오름차순)
1 select *
2* from dept order by loc asc
SQL> /
DEPTNO DNAME LOC 40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK-- 연산하는 테이블(더미데이터?)
1 select 1+1
2* from dual
SQL> /
1+1 2SQL> desc dual
Name Null? Type
DUMMY VARCHAR2(1)
-- 현재 날짜 시간 조회 (얘도 더미테이블 이용.)아마 연산이나 날짜 계산할때 쓰이는 테이블 같음.
SQL> select sysdate
2 from dual;
SYSDATE
22/11/17
//임프토 할때 cmd 창에서 dmp파일 위치로 이동하고 거기서 아래 명령어 입력!!!(p39~41)
C:\backup>
imp ora_user/hong file=expall.dmp log=empall.log ignore=y grants=y rows=y indexes=y full=y
imp ora_user/hong file=expcust.dmp log=expcust.log ignore=y grants=y rows=y indexes=y full=y
-->두번째 것은 캐릭터셋 안 맞아서 포기~ 그냥 첫번째 것만
'DataBase & DBMS' 카테고리의 다른 글
| 22.11.23 다양한 insert문= 고급insert 함수 (0) | 2023.02.16 |
|---|---|
| 22.11.22 Oracle DB / PL-SQL 예제 쿼리 작성 및 각종 함수 응용 (0) | 2023.02.15 |
| 22.11.18 Oracle DB / PL-SQL 기본 쿼리 및 함수, DML 연습_2 (0) | 2023.02.15 |
| 22.11.17 Oracle DB / PL-SQL 기본 쿼리 및 함수, DML 연습 (0) | 2023.02.15 |
| 22.11.16 Oracle DB 설치 및 기본 세팅 (0) | 2023.02.15 |