(3) 문자열결합연산자 (||)
SELECT EMPNO || '사원의 이름은' || ENAME || '입니다' FROM EMP;
결과>>
EMPNO||'사원의이름은'||ENAME||'입니다'
--------------------------------------------------------------------------------
7369사원의 이름은SMITH입니다
7499사원의 이름은ALLEN입니다
7521사원의 이름은WARD입니다
7566사원의 이름은JONES입니다
7654사원의 이름은MARTIN입니다
7698사원의 이름은BLAKE입니다
7782사원의 이름은CLARK입니다
7839사원의 이름은KING입니다
7844사원의 이름은TURNER입니다
7900사원의 이름은JAMES입니다
7902사원의 이름은FORD입니다
EMPNO||'사원의이름은'||ENAME||'입니다'
--------------------------------------------------------------------------------
7934사원의 이름은MILLER입니다
(4) IS NULL,IS NOT NULL - NULL인지 검사
예1) 커미션이 NULL인 사원들의 모든 정보를 조회
SELECT*FROM EMP 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
7839 KING PRESIDENT 81/11/17 5000 10
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
예2) 커미션이 NULL이 아닌 사원들의 모든 정보를 조회
SELECT*FROM EMP 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
(5) BETWEEN A AND B : A와 B사이의 값
예1) 급여가 1000부터 3000사이인 사원들의 사원번호,이름,급여를 조회하시오.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>=1000 AND SAL<=3000;
->BETWEEN 사용
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 3000;
결과>>
EMPNO ENAME SAL
---------- -------------------- ----------
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7844 TURNER 1500
7902 FORD 3000
7934 MILLER 1300
예2) 사원번호가 7300부터 7500 사이인 사원들의 사원번호,이름,급여,부서번호를
조회해 보시오.
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE EMPNO BETWEEN 7300 AND 7500;
결과>>
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
(6) ANY연산자 - 조건이 하나만 일치하면 됨
예1) 10번 부서 또는 30번 부서의 사원의 사원번호,부서번호,급여 조회
SELECT EMPNO, DEPTNO, SAL FROM EMP
WHERE DEPTNO IN(10, 30);
->ANY 연산자 사용
SELECT EMPNO, DEPTNO, SAL FROM EMP
WHERE DEPTNO=ANY(10,20);
결과>>
EMPNO DEPTNO SAL
---------- ---------- ----------
7369 20 800
7566 20 2975
7782 10 2450
7839 10 5000
7902 20 3000
7934 10 1300
예2) 부서번호가 20번보다 작거나 30번보다 작은 부서의 사원의
사원번호,부서번호,급여 조회
SELECT EMPNO, DEPTNO, SAL
FROM EMP
WHERE DEPTNO<ANY(20,30);
결과>>
EMPNO DEPTNO SAL
---------- ---------- ----------
7369 20 800
7566 20 2975
7782 10 2450
7839 10 5000
7902 20 3000
7934 10 1300
(7) ALL연산자 - 조건이 모두 만족해야 됨
예1) 부서번호가 20번보다 작고 30번보다 작은 부서의 사원의
사원번호,부서번호,급여 조회
SELECT EMPNO, DEPTNO, SAL
FROM EMP
WHERE DEPTNO<ALL(20,30);
결과>>
EMPNO DEPTNO SAL
---------- ---------- ----------
7782 10 2450
7839 10 5000
7934 10 1300
예2) 부서번호가 10번과 20번보다 큰 부서에 근무하는 사원들의 모든 정보 조회하기
SELECT*FROM EMP
WHERE DEPTNO>ALL(10,20);
결과>>
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) 함수(Function)
- 어떠한 일을 수행하는 기능으로써 주어진 인수(argment)를 재료로 처리하여
그 결과값을 반환
(1) 함수의 종류
- 단일행 함수 : 하나의 행(row)당 하나의 결과값을 반환하는 함수
- 복수행 함수 : 여러개의 행당 하나의 결과값을 반환하는 함수
(2) 단일행 함수
<1> 문자함수
- CONCAT(컬럼명,'붙일문자') : 문자열연결
SELECT '사원이름=>' || ENAME FROM EMP;
SELECT CONTACT('사원이름=>',ENAME) 사원이름 FROM EMP;
- LOWER('문자열') : 문자열을 소문자로 리턴
SELECT ENAME, LOWER(ENAME) FROM EMP;
- UPPER('문자열') : 문자열을 대문자로 리턴
SELECT UPPER('tiger') FROM DUAL;
- INSTR('문자열','위치찾는문자열') : 문자열의 위치찾기(찾는 문자가 없으면 0리턴)
SELECT INSTR('test@naver.com','@') FROM DUAL;
SELECT INSTR('test@naver.com','b') FROM DUAL; -- 찾는 문자가 없으면 0을 리턴
결과>>
INSTR('TEST@NAVER.COM','B')
---------------------------
0
- SUBSTR('문자열',시작위치,갯수) : 문자열 추출하기
SELECT SUBSTR('test@naver.com',1,4) FROM DUAL;
결과>>
SUBSTR('
--------
test
SELECT SUBSTR('test@naver.com',6) FROM DUAL; -- 6번째 위치에서부터 문자열 끝까지 추출
결과>>
SUBSTR('TEST@NAVER
------------------
naver.com
예) 입사한 월이 '12'인 사원들의 모든 정보를 조회하기(SUBSTR)
SELECT*FROM EMP WHERE SUBSTR(HIREDATE,4,2)='12';
결과>>
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
7902 FORD ANALYST 7566 81/12/03 3000 20
- LENGTH('문자열') : 문자열길이구하기
SELECT LENGTH('HELLO') FROM DUAL;
- REPLACE('문자열','찾는문자열','바꿀문자열') : 문자열바꾸기
SELECT REPLACE('THE TIGER', 'TIGER', 'LION') FROM DUAL;
- LPAD('문자열',자릿수,'채울문자') : 왼쪽남는자리에 문자열채우기
SELECT LPAD('HELLO',10) FROM DUAL;
SELECT LPAD('HELLO',10,'*') FROM DUAL;
- RPAD('문자열',자릿수,'채울문자') : 오른쪽남는자리에 문자열채우기
SELECT RPAD('HELLO',10,'*') FROM DUAL;
예) 사원번호, 이름을 조회해서 출력하세요. 단 이름은 앞자리 2자리만 보이도록 하세요.(ex. AL**, SM***)
SELECT EMPNO, RPAD(SUBSTR(ENAME,1,2),LENGTH(ENAME),'*') FROM EMP;
- LTRIM('문자열','없앨문자') : 왼쪽에 문자열 없애기
SELECT LTRIM('THE TIGER', 'THE') FROM DUAL;
- RTRIM('문자열','없앨문자') : 오른쪽에 문자열 없애기
SELECT RTRIM('TIGER IS', 'IS') FROM DUAL;
<2> 날짜함수(**)
- SYSDATE (**) : 현재날짜와 시간얻어오기
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE+30 FROM DUAL; -- 날짜를 연산(+,-) 할 수 있음
- ADD_MONTHS(날짜,더할개월수)
SELECT ADD_MONTHS(SYSDATE,10) FROM DUAL;
예) 사원들의 사원번호,이름,입사일,입사일로부터 1년후의 날짜 조회하기
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE,12) FROM EMP;
- MONTHS_BETWEEN(날짜1,날짜2) : 날짜사이의 개월수 구하기
SELECT EMPNO,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
<3> TO_CHAR(날짜,'출력형식')
- 날짜형식을 지정된 형식의 문자열로 얻어오는 함수
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD AM HH:MI:SS') FROM DUAL; -- 오전/오후도 출력됨
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL; -- 24시간 단위로 출력됨
Q1) EMP테이블 사원들의 입사년도를 년/월/일 시/분/초 형식으로 출력되도록 해보세요.
SELECT EMPNO, TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') FROM EMP;
# 출력컬럼 사이즈 변경
SQL> column 컬럼명 format a30
<4> TO_DATE('날짜형식의문자열','해석할서식')
- 문자를 날짜로 변환하는 함수
SELECT TO_DATE('2021/04/08','YYYY/MM/DD') FROM DUAL;
TO_DATE(
--------
21/04/08
SELECT TO_DATE('2021/04/08','YYYY/MM/DD')+5 FROM DUAL;
TO_DATE(
--------
21/04/13
<5> 수학함수
- ABS(숫자) : 절대값구하기
SELECT ABS(-10) FROM DUAL;
- CEIL(숫자) : 올림값구하기
SELECT CEIL(4.1) FROM DUAL;
- FLOOR(숫자) : 내림값구하기
SELECT FLOOR(4.1) FROM DUAL;
- ROUND(숫자) : 반올림값구하기
SELECT ROUND(4.6) FROM DUAL;
- MOD(숫자,나눌값) : 나머지값구하기
SELECT MOD(10,3) FROM DUAL;
- TRUNC(숫자,절삭할소수점자릿수) : 소수이하 절삭하기
SELECT TRUNC(10.12345,3) FROM DUAL; -- 소수 이하 3자리까지 출력(나머지는 절삭)
SELECT ROUND(10.12345,3) FROM DUAL; -- 소수 이하 3자리까지 출력(반올림해서 출력)
<6> NVL(컬럼,NULL일때 치환할값)
SELECT EMPNO, ENAME, SAL, NVL(COMM,0) FROM EMP; (3) 복수행함수(그룹함수)
<1> COUNT(컬럼명) : 행의 갯수
SELECT COUNT(EMPNO) FROM EMP;
<2> SUM(컬럼명) : 해당 컬럼의 총합
SELECT SUM(SAL) 급여합 FROM EMP;
<3> AVG(컬럼명) : 해당 컬럼의 평균
SELECT AVG(SAL) 급여평균 FROM EMP;
<4> MIN(컬럼명),MAX(컬럼명) : 최소,최대값
SELECT MAX(SAL) 최대급여, MIN(SAL) 최소급여 FROM EMP;
(4) GROUP BY : 컬럼을 기준으로 그룹으로 묶음
- 형식
< 실행순서 >
SELECT 컬럼명,.. -------- 5
FROM 테이블명 -------- 1
WHERE 조건절 -------- 2
GROUP BY 컬럼명 -------- 3
HAVING 조건절 -------- 4
ORDER BY 기준컬럼 -------- 6
예1) 부서별 사원들의 급여합을 구하시오.
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT EMPNO, DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO; -- GROUP BY절을 사용하면 그룹의 기준컬럼과 그룹함수와 사용할 수 있음
예2) 직업별 사원들의 급여평균을 구하시오.
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB;
예3) 급여가 2000이상인 사원들의 부서별 급여총합 구하기
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO;
예4) 급여가 2000이상인 사원들의 부서별 급여총합 구하기,급여합이 높은순으로 출력
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO;
SELECT DEPTNO, SUM(SAL) 급여합
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO
ORDER BY 급여합 DESC;
Q1) 부서별 최대급여,최소 급여를 구하세요.
SELECT DEPTNO, MAX(SAL), MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
Q2) 부서별 근무인원수를 구해 보세요. 근무인원수가 많은 순으로 출력
SELECT DEPTNO, COUNT(*) 인원수
FROM EMP
GROUP BY DEPTNO;
Q3) 각 직업별 평균급여와 최대급여,최소급여를 구해 보세요.
SELECT JOB, AVG(SAL), MAX(SAL), MIN(SAL)
FROM EMP
GROUP BY JOB;
Q4) 급여가 2000이상인 사원들의 부서별 평균급여를 구하고 평균급여가 높은순으로 정렬되서 출력되도록 하세요.
SELECT DEPTNO, AVG(SAL) 평균급여
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO
OUDER BY 평균급여 DESC;
Q5) 부서번호가 10번인 사원들의 직업별 급여의 평균을 구하고 평균급여가 높은 순으로 조회해 보세요.
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
WHERE DEPTNO=10
GROUP BY DEPTNO, JOB
ORDER BY AVG(SAL);
예5) 사원들의 부서별 평균급여를 구하시오. 단 급여평균이 2000이상인 부서만 출력되도록 하세요.
SELECT DEPTNO, AVG(SAL) 급여평균
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;
예6) 부서별로 같은 직업을 갖는 사원들의 급여 평균을 조회하시오.
SELECT DEPTNO, JOB, AVG(SAL) 급여평균
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO;
Q1) 부서별 같은 직업을 갖는 사원들의 최대급여를 구하고 부서별 오름차순, 최대급여가 높은 순으로 조회해 보세요.
SELECT DEPTNO, JOB, MAX(SAL) 최대급여
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO ASC, 최대급여 DESC; (5) 서브쿼리
- 하나의 SQL문장절에 포함된 또 다른 SELECT문장. 두 번 질의를 해야 얻을 수 있는
결과를 한번의 질의로 해결이 가능하게 하는 쿼리
1) 단일행 서브쿼리.
- 서브쿼리의 실행결과가 하나의 행만을 리턴해 주는 쿼리. 즉 하나의 데이터만을 리턴
- 연산자 : =,<>,>,>=,<,<=
예1) 사원번호가 7369인 사원과 같은 직업을 갖는 사원들의 사원번호,이름,직업을
출력하시오.
SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO=7369;
SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB='CLERK';
->서브쿼리로 바꿔보면
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369);
예2) 사원급여가 평균급여보다 많은 사원들의 사원번호와 급여를 조회하시오.
SELECT AVG(SAL) FROM EMP;
SELECT EMPNO, SAL FROM EMP WHERE SAL>2077.08333;
->서브쿼리로 바꿔보면
SELECT EMPNO, SAL FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);
Q1) 'ALLEN'과 같은 부서에 근무하는 사원들의 모든 정보를 조회하시오.
SELECT*FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='ALLEN');
2) 복수행 서브쿼리
- 서브쿼리의 실행결과가 여러개의 행을 리턴
- 연산자 : IN,ANY,ALL
=,>,>=,<,<=,<> 연산자는 사용할 수 없음
예1) 부서번호가 10번인 사원들의 급여와 같은 급여를 받는 사원들의 이름과
급여를 출력하시오.
SELECT SAL FROM EMP WHERE DEPTNO=10;
==> 서브쿼리의 결과행이 여러개이면 =, >, >=, <, <=, <> 연산자는 사용할 수 없다. 이때 IN, ANY, ALL 연산자를 사용한다.
SELECT ENAME, SAL FROM EMP WHERE SAL=ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);
SELECT ENAME, SAL FROM EMP WHERE SAL IN(SELECT SAL FROM EMP WHERE DEPTNO=10);
예2) 부서번호가 10번인 사원의 급여와 같거나 많은 급여를 받는 사원의
이름과 급여를 출력하시오.
SELECE ENAME, SAL FROM EMP
WHERE SAL>=ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);
예3) 30번 부서의 가장 많은 급여보다 더 많은 급여를 받는 사원번호,급여를
조회하시오.
SELECT EMPNO, SAL
FROM EMP
WHERE SAL>ALL(SELECT SAL FROM WHERE DEPTNO=30);
Q1) 10번 부서사원들중에 20번 부서의 사원과 같은 직업을 갖는 / 사원의 사원번호,
직업,입사일을 조회하시오.
SELECT EMPNO, JOB, HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO=20);
Q2) 급여가 30번 부서의 최저급여보다 낮은 사원의 사원번호,이름,급여를 조회하시오.(ALL연산자사용)
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL<ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);
'ㅅ'