- 57p. SCOTT 계정설정
- 데이터가 저장되는 단위 : table
- 항목 : 레코드
- 컬럼
- 기본키 PK(child table), 외래키 FK(parent table)
PK의 값을 가져다 쓰는 값은 FK - RDBMS(관계형 데이터베이스모델)-> 2차원 표의 형태
- Run SQL Command line에서 작업
- 테이블에서 중복항목이 많고, 같은 종류가 정리 되어있지않으면 '정규화가 이루어지지 않았다' 표현
- 도메인 : 값들의 집합
ex. 결제(현금, 신용카드, 00페이) - 80p 4-9장 셀렉트문
- 128p 함수
select문
[구문]
select 조회할 컬럼1, 조회할 컬럼2,...
from 테이블 1, 테이블2,...
where 조건
기본 select
- sql명령문은 명령문의 마지막에 ;을 추가해야 한다.
- 대소문자를 구분하지 않는다
- select절에 *를 표시하면 모든 컬럼을 조회하겠다는 의미
- select절에 원하는 컬럼만 조회할 수 있다.
- ||연산자를 이용해서 컬럼과 컬럼을 연결할 수 있다
- 오라클에서 문자열은 ' '로 표현
- 컬럼에 alias(별칭 - 가상의 컬럼명 사용 가능)를 사용할 수 있다.
[형식]
컬럼명 as alias
컬럼명 alias
conn scott/tiger , set linesize, select * from tab
SQL> conn scott/tiger
Connected.
SQL> set linesize 300
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLE
select * from emp
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 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 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.
select * from dept
SQL> select * from dept;
DEPTNO DNAME LOC_
---------- ---------------------------- ----
10 ACCOUNTING A1
20 RESEARCH B1
30 SALES C1
40 OPERATIONS A1
50 INSAdesc emp/테이블 정보 확인
여기서 desc는 내림차순이 아니라 테이블 정보 확인하는 명령인듯?
SQL> desc 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)
select empno,ename,deptno/원하는 컬럼만 조회
SQL> select empno,ename,deptno
2 from emp;
EMPNO ENAME DEPTNO
---------- -------------------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
EMPNO ENAME DEPTNO
---------- -------------------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
14 rows selected.
select ename || sal/ 컬럼과 컬럼 연결
SQL> select ename || sal
2 from emp;
ENAME||SAL
----------------------------------------------------------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
ENAME||SAL
----------------------------------------------------------------------------------------------------
JAMES950
FORD3000
MILLER1300
14 rows selected.
select ename || '의 급여는' || sal || '이다.'/ 컬럼 + 문자열
SQL> select ename || '의 급여는' || sal || '이다.'
2 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이다.
ENAME||'의급여는'||SAL||'이다.'
------------------------------------------------------------------------------------------------------------------------
JAMES의 급여는950이다.
FORD의 급여는3000이다.
MILLER의 급여는1300이다.
14 rows selected.
select ename || '의 급여는' || sal || '이다.' 정보/ 컬럼에 별칭사용
SQL> select ename || '의 급여는' || sal || '이다.' 정보
2 from emp;
정보
------------------------------------------------------------------------------------------------------------------------
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 rows selected.
select ename || '의 급여는' || sal || '이다.' as 정보, sal * 12 +comm 연봉 / 컬럼에 별칭&연산
SQL> select ename || '의 급여는' || sal || '이다.' as 정보, sal * 12 +comm 연봉
2 from emp;
정보 연봉
-----------------------------------------------
SMITH의 급여는800이다.
ALLEN의 급여는1600이다. 19500
WARD의 급여는1250이다. 15500
JONES의 급여는2975이다.
MARTIN의 급여는1250이다. 16400
BLAKE의 급여는2850이다.
CLARK의 급여는2450이다.
SCOTT의 급여는3000이다.
KING의 급여는5000이다.
TURNER의 급여는1500이다. 18000
ADAMS의 급여는1100이다.
정보 연봉
-----------------------------------------------
JAMES의 급여는950이다.
FORD의 급여는3000이다.
MILLER의 급여는1300이다.
14 rows selected.
select ename, deptno, sal, comm, sal * 12 + comm "급여의 합계"
SQL> select ename, deptno, sal, comm, sal * 12 + comm "급여의 합계"
2 from emp;
ENAME DEPTNO SAL COMM 급여의 합계
-------------------- ---------- ---------- ---------- -----------
SMITH 20 800
ALLEN 30 1600 300 19500
WARD 30 1250 500 15500
JONES 20 2975
MARTIN 30 1250 1400 16400
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500 0 18000
ADAMS 20 1100
ENAME DEPTNO SAL COMM 급여의 합계
-------------------- ---------- ---------- ---------- -----------
JAMES 30 950
FORD 20 3000
MILLER 10 1300
14 rows selected.
미션
- XXXX의 직업은 XXX입니다.로 출력하기
- ename,hiredate, sal, comm, sal의 10%인하급여출력
계산되어 추가되는 컬럼의 컬럼명: 인하급여
1번
SQL> select ename ||'의 직업은' || job ||'입니다.' from emp;
ENAME||'의직업은'||JOB||'입니다.'
-----------------------------------------------------------------------
SMITH의 직업은CLERK입니다.
ALLEN의 직업은SALESMAN입니다.
WARD의 직업은SALESMAN입니다.
JONES의 직업은MANAGER입니다.
MARTIN의 직업은SALESMAN입니다.
BLAKE의 직업은MANAGER입니다.
CLARK의 직업은MANAGER입니다.
SCOTT의 직업은ANALYST입니다.
KING의 직업은PRESIDENT입니다.
TURNER의 직업은SALESMAN입니다.
ADAMS의 직업은CLERK입니다.
ENAME||'의직업은'||JOB||'입니다.'
-----------------------------------------------------------------------
JAMES의 직업은CLERK입니다.
FORD의 직업은ANALYST입니다.
MILLER의 직업은CLERK입니다.
14 rows selected.2번
SQL> select ename, hiredate, sal, comm, sal *0.9 as 인하급여 from emp;
ENAME HIREDATE SAL COMM 인하급여
-------------------- -------- ---------- ---------- ----------
SMITH 80/12/17 800 720
ALLEN 81/02/20 1600 300 1440
WARD 81/02/22 1250 500 1125
JONES 81/04/02 2975 2677.5
MARTIN 81/09/28 1250 1400 1125
BLAKE 81/05/01 2850 2565
CLARK 81/06/09 2450 2205
SCOTT 82/12/09 3000 2700
KING 81/11/17 5000 4500
TURNER 81/09/08 1500 0 1350
ADAMS 83/01/12 1100 990
ENAME HIREDATE SAL COMM 인하급여
-------------------- -------- ---------- ---------- ----------
JAMES 81/12/03 950 855
FORD 81/12/03 3000 2700
MILLER 82/01/23 1300 1170
14 rows selected.
DISTINCT : SELECT 문 결과에서 중복 행 제거
SQL> select distinct job from emp;
JOB
------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
select절에 조건 추가하기
[구문]
select 조회할 컬럼1,2,
from 테이블 1,2,
where 컬럼명 연산자 비교값 => boolean을 리턴
94페이지
- 조회결과를 제한
- where절로 조건을 추가하는 경우 from절 다음에 추가해야 한다
- where절을 실행하면 조건이 true인 레코드만 출력
- 연산자는 >, >=, <, <=, =, <>(같지않다, 다르다)
- where절은 레코드 하나하나를 비교하며 셀릭트를 함을 기억
- 조건에 추가되는 비교값이 날짜데이터나 문자데이터면 ' '로 묶어서 표현
- 오라클은 대소문자를 구분하지 않으나 비교되는 값은 대소문자를 구분
- 비교할 조건이 여러 개인 경우 and나 or 연산자를 사용
and : 모든 조건이 만족
or : 추가한 조건 중 하나만 만족
ex) sal이 2500이상이고 job이 ANALIST인 모든 데이터를 조회
select *
from emp
where sal >= 2500
or job = 'ANALYST'
;ex) 10번, 20번 부서에 근무하는 사원의 모든 데이터를 조회
select *
from emp
where deptno=10 or deptno=20;- 동일한 컬럼에서 여러 조건을 or연산하는 경우 in연산자를 이용해서 작업이 가능
컬럼명 in(값1,값2...)
select *
from emp
where deptno in(10,20);- 동일한 컬럼에서 여러 조건을 and 연산하는 경우
between A and B 연산자 이용해서 작업
컬럼명 between 값1 and 값2
컬럼이 값1~2사이인 경우
sal가 1000에서 3000사이의 데이터를 조회
SQL> select *
2 from emp
3 where sal between 1000 and 3000;- not을 쓰면 결과를 부정
SQL> select *
2 from emp
3 where deptno not in(10,20);- like 연산자(대표문자와 함께 사용) 110p
_ : 문자 한자리를 의미
% : 모든문자
where 조건에 따라 출력
SQL> select *
2 from emp
3 where deptno = 30;
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
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
6 rows selected.
SQL> select ename, sal,comm
2 from emp
3 where sal >= 3000;
ENAME SAL COMM
-------------------- ---------- ----------
SCOTT 3000
KING 5000
FORD 3000
조건에 추가되는 비교값이 날짜데이터나 문자데이터면 ' '로 묶어서 표현
SQL> select *
2 from emp
3 where ename = 'ALLEN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
SQL> select ename,hiredate
2 from emp
3 where hiredate >= '1982/1/1';
ENAME HIREDATE
-------------------- --------
SCOTT 82/12/09
ADAMS 83/01/12
MILLER 82/01/23
비교되는 값은 대소문자를 구분
SQL> select *
2 from emp
3 where ename = 'allen';
no rows selected비교할 조건이 여러 개인 경우 and나 or 연산자를 사용
SQL> select *
2 from emp
3 where sal >= 2500
4 and job = 'ANALYST';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
SQL> select *
2 from emp
3 where sal >= 2500
4 or job = 'ANALYST'
5 ;
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
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7902 FORD ANALYST 7566 81/12/03 3000 20
급여가 1000이상 3000이하 표현 2가지 : and / between A and B
SQL> select *
2 from emp
3 where sal >=1000 and sal <=3000;
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 82/12/09 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
11 rows selected.
SQL> select *
2 from emp
3 where sal between 1000 and 3000;
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 82/12/09 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
11 rows selected.
like 연산자
_ : 문자 한자리를 의미
% : 모든문자
A로 시작하는 문자
SQL> select ename, sal,comm
2 from emp
3 where ename like 'A%';
ENAME SAL COMM
-------------------- ---------- ----------
ALLEN 1600 300
ADAMS 1100
SQL> select ename, sal,comm
2 from emp
3 where ename like '%A';
no rows selected
A로 끝나는 문자
SQL> select ename, sal,comm
2 from emp
3 where ename like '%A';
no rows selected
A가 들어가는 문자
SQL> select ename, sal,comm
2 from emp
3 where ename like '%A%';
ENAME SAL COMM
-------------------- ---------- ----------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
ADAMS 1100
JAMES 950
7 rows selected.
2번째 글자가 A인 문자
SQL> select ename, sal,comm
2 from emp
3 where ename like '_A%';
ENAME SAL COMM
-------------------- ---------- ----------
WARD 1250 500
MARTIN 1250 1400
JAMES 950
is null, is not null
SQL> select *
2 from emp
3 where comm is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
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
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7876 ADAMS CLERK 7788 83/01/12 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
10 rows selected.
SQL> select *
2 from emp
3 where comm is not null;
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
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
정렬 : order by
[문법]
select
from
where
order by 컬럼명 정렬방법, 컬럼명 정렬방법
- 기본 : asc 오름차순
- desc 내림차순
- 문자열이 저장된 컬럼을 기준으로 정렬을 면 같은문자열을 모으는작업과 동일
기본값 : 오름차순
SQL> select *
2 from emp
3 order by sal;
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 83/01/12 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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
14 rows selected.null값 포함된 desc내림차순
SQL> select *
2 from emp
3 order by comm desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7782 CLARK MANAGER 7839 81/06/09 2450 10
7902 FORD ANALYST 7566 81/12/03 3000 20
7900 JAMES CLERK 7698 81/12/03 950 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
14 rows selected.null값 포함된 asc 오름차순
SQL> select *
2 from emp
3 order by comm asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7876 ADAMS CLERK 7788 83/01/12 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
7698 BLAKE MANAGER 7839 81/05/01 2850 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02 2975 20
7369 SMITH CLERK 7902 80/12/17 800 20
7782 CLARK MANAGER 7839 81/06/09 2450 10
14 rows selected.
내림차순 오름차순 혼용
SQL> select deptno,job,ename,sal
2 from emp
3 order by deptno asc, sal desc;
DEPTNO JOB ENAME SAL
---------- ------------------ -------------------- ----------
10 PRESIDENT KING 5000
10 MANAGER CLARK 2450
10 CLERK MILLER 1300
20 ANALYST SCOTT 3000
20 ANALYST FORD 3000
20 MANAGER JONES 2975
20 CLERK ADAMS 1100
20 CLERK SMITH 800
30 MANAGER BLAKE 2850
30 SALESMAN ALLEN 1600
30 SALESMAN TURNER 1500
DEPTNO JOB ENAME SAL
---------- ------------------ -------------------- ----------
30 SALESMAN MARTIN 1250
30 SALESMAN WARD 1250
30 CLERK JAMES 950
14 rows selected.함수
[그룹함수]
- 그룹함수
- 그룹화된 데이터에 함수를 적용해서 결과를 출력하기 위해 사용하는 함수
- 통계
- 반드시 그룹화된 데이터에 사용
- 그룹함수는 where절에 사용할 수 없다.
=>where절은 테이블의 모든 행을 반복실행하면서 조건을 검사한다. - 그룹함수는 그룹에 적용하는 함수로 성격이 다르기 때문에 사용할 수 없다.
SQL> select *
2 from emp
3 where sal>avg(sal);
where sal>avg(sal)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
[단일행함수]
- 모든 행에서 함수를 적용해서 결과를 반영
- select절 where절에서 모두 사용이 가능
- 함수와 함수는 중첩이 가능 : 함수 안에서 함수를 호출해서 사용 가능
select ename, length(ename) from emp;
SQL> select ename, length(ename)
2 from emp;
ENAME LENGTH(ENAME)
-------------------- -------------
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
ENAME LENGTH(ENAME)
-------------------- -------------
JAMES 5
FORD 4
MILLER 6
14 rows selected.select ename, length(ename) from emp where length(ename)=4;
SQL> select ename, length(ename)
2 from emp
3 where length(ename)=4;
ENAME LENGTH(ENAME)
-------------------- -------------
WARD 4
KING 4
FORD 4select sum(sal),avg(sal) from emp; -> 그룹함수
SQL> select sum(sal),avg(sal) from emp;
SUM(SAL) AVG(SAL)
---------- ----------
29025 2073.21429
lower(소문자로 변경), upper(대문자로 변경), initcap(첫글자만 대문자로 나머지는 소문자로 변경)
SQL> select lower ('ORACLE ORACLE'), upper('oracle oracle'), initcap('oracle oracle')
2 from dual;
LOWER('ORACLEORACLE') UPPER('ORACLEORACLE') INITCAP('ORACLEORACLE')
-------------------------- -------------------------- --------------------------
oracle oracle ORACLE ORACLE Oracle Oraclechr, ascii
SQL> select chr(65),ascii('a') from dual;
CH ASCII('A')
-- ----------
A 97substr
SQL> select substr('oracle oracle',2) from dual;
SUBSTR('ORACLEORACLE',2)
------------------------
racle oracle=> 2번째 글자부터 출력
SQL> select substr('oracle oracle',2,3) from dual;
SUBSTR
------
rac=> 2번 위치에서 3글자 추출
cf.자바.. 오라클은 인덱스가 1번부터 시작
SQL> select substr('oracle oracle',-1,3) from dual;
SU
--
e
=> 뒤에서 1번째 글자에서 3글자 추출
SQL> select substr('oracle oracle',-5,3) from dual;
SUBSTR
------
rac
=> 뒤에서 5번째 글자에서 3글자 추출
instr
INSTR ( 문자열, 찾을 문자 값, 찾기 시작할 위치, 찾은 결과의 순번 )
SQL> select instr('oracle oracle','o') from dual;
INSTR('ORACLEORACLE','O')
-------------------------
1SQL> select instr('oracle oracle','j') from dual;
INSTR('ORACLEORACLE','J')
-------------------------
0
=> 해당 글자 없으면 0
SQL> select instr('oracle oracle','o',2 ) from dual;
INSTR('ORACLEORACLE','O',2)
---------------------------
8SQL> select instr('oracle oracle','o',-1,2 ) from dual;
INSTR('ORACLEORACLE','O',-1,2)
------------------------------
1
=> 뒤에서 첫번째 글자에서 시작해서 두번째 o찾기
concat : 문자연결
SQL> select concat('오라클','DBMS')from dual;
CONCAT('오라클','DBMS')
--------------------------
오라클DBMS두개씩만 연결할 수 있어서 더하고싶으면 중첩
SQL> select concat(concat('오라클','DBMS'),'안녕')from dual;
CONCAT(CONCAT('오라클','DBMS'),'안녕')
--------------------------------------
오라클DBMS안녕
replace
SQL> select replace('oracle database','database','db') from dual;
REPLACE('ORACLEDAT
------------------
oracle db
lpad, rpad : 특정문자 채워서 문자열 길이 맞추기
- lpad : 왼쪽에 특정문자를 채워서 문자열 길이 맞추기
- rpad : 오른쪽에 특정문자를 채워서 문자열 길이 맞추기
SQL> select lpad ('991012-',14,'*') from dual;
LPAD('991012-',14,'*')
----------------------------
*******991012-
SQL> select rpad ('991012-',14,'*') from dual;
RPAD('991012-',14,'*')
----------------------------
991012-*******
trim, ltrim, rtrim : 양쪽/왼쪽/오른쪽 공백 지우기
SQL> select ' oracle',ltrim(' oracle') from dual;
'ORACLE' LTRIM('ORACL
---------------------------- ------------
oracle oracle
SQL> select length( ' oracle'),length(ltrim(' oracle')) from dual;
LENGTH('ORACLE') LENGTH(LTRIM('ORACLE'))
---------------- -----------------------
14 6
SQL> select length( 'oracle '),length(rtrim('oracle ')) from dual;
LENGTH('ORACLE') LENGTH(RTRIM('ORACLE'))
---------------- -----------------------
13 6
SQL> select trim( ' oracle '), length(trim( ' oracle ')) from dual;
TRIM('ORACLE LENGTH(TRIM('ORACLE'))
------------ ----------------------
oracle 6
trim, ltrim, rtrim : 양쪽/왼쪽/오른쪽 특정문자 지우기
SQL> select 'oracleooooooooooo',rtrim('oracleoooooooooo','o') from dual;
'ORACLEOOOOOOOOOOO' RTRIM('ORACL
---------------------------------- ------------
oracleooooooooooo oracle
SQL> select trim('o'from'ooooooooooooracle oracleoooooo') from dual;
TRIM('O'FROM'OOOOOOOOOOO
------------------------
racle oracleSQL> select trim(both 'o'from'ooooooooooooracle oracleoooooo') from dual;
TRIM(BOTH'O'FROM'OOOOOOO
------------------------
racle oracle
SQL> select trim(trailing 'o'from'ooooooooooooracle oracleoooooo') from dual;
TRIM(TRAILING'O'FROM'OOOOOOOOOOOORACLEORACLEOOOO
------------------------------------------------
ooooooooooooracle oracleexam
select문 연습1
- 81년도에 입사한 사람들 중에서 job이 ' MANAGER'인 사람들의 성명과 직업을 다음 과 같은 형태로 출력하세요.
ex)JONES : MANAGER
SQL> select ename || ':' || job
2 from emp
3 where job='MANAGER' and hiredate like '81%';
ENAME||':'||JOB
----------------------------------------
JONES:MANAGER
BLAKE:MANAGER
CLARK:MANAGER- job이 'SALESMAN'이면서 급여가 1500이상인 데이터를 출력 (사번,성명,직업,급여 출력)
SQL> select empno,ename,job,sal
2 from emp
3 where job = 'SALESMAN' and sal>=1500;
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500- 급여(sal) 2000에서 3000사이의 사원 between ~ and 연산자를 이용하여 작업. 급여가 높은 순서대로 출력하세요.(사번,성명,급여)
SQL> select empno,ename,sal
2 from emp
3 where sal between 2000 and 3000;
EMPNO ENAME SAL
---------- -------------------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7902 FORD 3000- 82년도 이후에 입사했거나 급여가 5000이상인 사람을 출력. (사번,성명,급여,입사년월) - 82년포함
SQL> select empno,ename,sal,hiredate
2 from emp
3 where hiredate >='82/01/01' or sal >=5000;
EMPNO ENAME SAL HIREDATE
---------- -------------------- ---------- --------
7788 SCOTT 3000 82/12/09
7839 KING 5000 81/11/17
7876 ADAMS 1100 83/01/12
7934 MILLER 1300 82/01/23
- emp테이블에서 부서번호가 10이거나 20에 속하는 사원들 중에서 급여가 2000이상인 사원들 의 이름,급여,부서번호를 출력
SQL> select ename,sal,deptno
2 from emp
3 where deptno in (10,20) and sal>=2000;
ENAME SAL DEPTNO
-------------------- ---------- ----------
JONES 2975 20
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
FORD 3000 20- 급여가 1300에서 1700사이에 해당하는 사원의 성명,담당업무,급여,부서번호 조회
SQL> select ename,job,sal,deptno
2 from emp
3 where sal between 1300 and 1700;
ENAME JOB SAL DEPTNO
-------------------- ------------------ ---------- ----------
ALLEN SALESMAN 1600 30
TURNER SALESMAN 1500 30
MILLER CLERK 1300 10- 사원번호가 7902,7788,7566인 사원의 사원번호, 성명,담당업무,급여,입사일자 조회
SQL> select empno, ename, job,sal,hiredate
2 from emp
3 where empno in (7902,7788,7566);
EMPNO ENAME JOB SAL HIREDATE
---------- -------------------- ------------------ ---------- --------
7566 JONES MANAGER 2975 81/04/02
7788 SCOTT ANALYST 3000 82/12/09
7902 FORD ANALYST 3000 81/12/03- emp테이블에서 급여가 2800이상이고 job이 MANAGER인 사원의 사원번호,성명,담당업무, 급여,입사일자,부서번호를 조회하기
SQL> select *
2 from emp
3 where sal>=2800 and job='MANAGER';
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- emp테이블에서 JOB이 'MANAGER',"CLERK','ANALYST' 가 아닌 사원의 사원번호, 성명, 담당업무,급여,부서번호 출력
SQL> select *
2 from emp
3 where job not in('manager','clerk','analyst');
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 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 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.select문 연습2
- 두번째 이름에 L이 들어가는 사원의 사번, 이름, 입사일, 급여를 조회하세요
단, 급여의 내림차순으로 정렬하세요
SQL> select empno, ename,hiredate,sal
2 from emp
3 where ename like '_L%'
4 order by sal desc;
EMPNO ENAME HIREDATE SAL
---------- -------------------- -------- ----------
7698 BLAKE 81/05/01 2850
7782 CLARK 81/06/09 2450
7499 ALLEN 81/02/20 1600- 20번 부서의 사원들 중 관리자가 7902인 사원들을 조회하세요
SQL> select *
2 from emp
3 where deptno = 20 and mgr = 7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20- 10,20번 부서의 사원들 중 job이 ANALYST인 사원을 조회하세요
SQL> select *
2 from emp
3 where deptno in(10,20) and job='ANALYST';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20- comm이 있는 직원들의 사번, 이름, 급여, 연봉을 조회하세요
단, 연봉은 커미션도 포함시켜 계산합니다.
SQL> select empno,ename,sal,sal*12 +comm 연봉
2 from emp
3 where comm is not null;
EMPNO ENAME SAL 연봉
---------- -------------------- ---------- ----------
7499 ALLEN 1600 19500
7521 WARD 1250 15500
7654 MARTIN 1250 16400
7844 TURNER 1500 18000문자열 함수
- "SMITH의 급여 800만원"의 유형으로 출력할 수 있도록 concat함수를 써서 작업하세요. 단, 급여가 1000만원 미만인 직원에 대해서 작업
SQL> select ename, concat(concat(concat(ename,'의 '),'급여 '),'800만원')
2 from emp
3 where sal<1000;
ENAME CONCAT(CONCAT(CONCAT(ENAME,'의'),'급여'),'800만원')
-------------------- ---------------------------------------------------
SMITH SMITH의 급여 800만원
JAMES JAMES의 급여 800만원- 입사일이 81년인 직원에 대해서 각 직원의 이름과 입사일을 나타내기. (substr이용)
SQL> select ename,hiredate
2 from emp
3 where substr(hiredate,1,2)=81;
ENAME HIREDATE
-------------------- --------
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
10 rows selected.- 각 직원의 이름,job,급여를 나타내되 급여는 5자리로 나타내며 부족한 자릿수는 *로 표시한다. 급여가 2000만원 이하인 직원만 나타내기
SQL> select ename,job,lpad(sal,5,'*')
2 from emp
3 where sal<=2000;
ENAME JOB LPAD(SAL,5,'*')
-------------------- ------------------ ----------------
SMITH CLERK **800
ALLEN SALESMAN *1600
WARD SALESMAN *1250
MARTIN SALESMAN *1250
TURNER SALESMAN *1500
ADAMS CLERK *1100
JAMES CLERK **950
MILLER CLERK *1300
8 rows selected.- 3번의 결과에서 *를 없애고 출력해보세요..단,함수를 이용하여 작업
SQL> select ename,job,ltrim(lpad(sal,5,'*'),'*')
2 from emp
3 where sal<=2000;
ENAME JOB LTRIM(LPAD(SAL,5,'*'),'*')
-------------------- ------------------ ---------------------------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
8 rows selected.- emp 테이블에서 scott의 사원번호, 성명, 담당업무(소문자로), 부서번호를조회합니다.
SQL> select empno,ename,lower(job),deptno
2 from emp
3 where ename ='SCOTT';
EMPNO ENAME LOWER(JOB) DEPTNO
---------- -------------------- ------------------ ----------
7788 SCOTT analyst 20- emp 테이블에서 이름의 첫글자가 'K'보다크고 'Y'보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 조회한다. 단, 이름순으로 정렬하세요
SQL> select empno,ename,job,sal,deptno
2 from emp
3 where (substr(ename,1,1))>'K' and (substr(ename,1,1))<'Y'
4 order by ename;
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7654 MARTIN SALESMAN 1250 30
7934 MILLER CLERK 1300 10
7788 SCOTT ANALYST 3000 20
7369 SMITH CLERK 800 20
7844 TURNER SALESMAN 1500 30
7521 WARD SALESMAN 1250 30
6 rows selected.- emp 테이블에서 10번부서의 사원에 대하여 담당업무 중 좌측에 'A'를 삭제하고 급여 중 좌측의 1을 삭제하여 출력하세요
SQL> select trim('A' from job), trim('1'from sal)
2 from emp
3 where deptno=10;
TRIM('A'FROMJOB) TRIM('1'FROMSAL)
------------------ -------------------------------
MANAGER 2450
PRESIDENT 5000
CLERK 300본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.
