데이터베이스 실행준비
cmd
sqlplus
scott
tiger
SET LINESIZE 500
===========================================
(4) IS NULL,IS NOT NULL - NULL인지 검사
예1) 커미션이 NULL인 사원들의 모든 정보를 조회
SELECT * FROM EMP
WHERE COMM IS NULL;
예2) 커미션이 NULL이 아닌 사원들의 모든 정보를 조회
SELECT * FROM EMP
WHERE COMM IS NOT NULL;
(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;
예2) 사원번호가 7300부터 7500 사이인 사원들의 사원번호,이름,급여,부서번호를
조회해 보시오.
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE EMPNO BETWEEN 7300 AND 7500;
(6) ANY연산자 - 조건이 하나만 일치하면 됨
OR연산자와 같음, --Sub Query 때 사용
예1) 10번 부서 또는 30번 부서의 사원의 사원번호,부서번호,급여 조회
SELECT EMPNO,DEPTNO,SAL
FROM EMP
WHERE DEPTNO=ANY(10,30); -- DEPTNO 가 10번과 같거나 30과 같으면 참
(**IN은 일치하는 값만 찾음, ANY는 다양한 연산자(비교)를 사용가능**)
예2) 부서번호가 20번보다 작거나 30번보다 작은 부서의 사원의
사원번호,부서번호,급여 조회
SELECT EMPNO,DEPTNO,SAL
FROM EMP
WHERE DEPTNO<ANY(20,30);
(7) ALL연산자 - 조건이 모두 만족해야 됨
(AND연산자와 기능 같음)
예1) 부서번호가 20번보다 작고 30번보다 작은 부서의 사원의
사원번호,부서번호,급여 조회
SELECT EMPNO,DEPTNO,SAL
FROM EMP
WHERE DEPTNO<ALL(20,30);
예2) 부서번호가 10번과 20번보다 큰 부서에 근무하는 사원들의 모든 정보 조회하기
SELECT *
FROM EMP
WHERE DEPTNO>ALL(10,20);
====================================================================
퀴즈1
====================================================================
6) 함수(Function)
- 어떠한 일을 수행하는 기능으로써 주어진 인수(argment)를 재료로 처리하여
그 결과값을 반환
(1) 함수의 종류
- 단일행 함수 : 하나의 행(row)당 하나의 결과값을 반환하는 함수
- 복수행 함수 : 여러개의 행당 하나의 결과값을 반환하는 함수
(2) 단일행 함수
<1> 문자함수
- CONCAT(컬럼명,'붙일문자') : 문자열연결
|| <- 같은 기능
SELECT CONCAT(ENAME,'사원'),DEPTNO FROM EMP;
SELECT ENAME||'사원',DEPTNO FROM EMP; => 더 자주 사용됨
- LOWER('문자열') : 문자열을 소문자로 리턴
SELECT LOWER(ENAME) FROM EMP;
- UPPER('문자열') : 문자열을 대문자로 리턴
SELECT UPPER('hello world') FROM DUAL;
(DUAL ; 구문형식을 맞추기위한 가상의 테이블)
- INSTR('문자열','위치찾는문자열') : 문자열의 위치찾기
SELECT INSTR('test@jhta.net','@') FROM DUAL;
(찾는 위치가 없으면 0을 리턴함)_(자바에서는 -1을 리턴함->0부터 자릿수가 시작하므로)
- SUBSTR('문자열',시작위치,갯수) : 문자열 추출하기
SELECT SUBSTR('HELLO WORLD',1,5) FROM DUAL;
예)
입사한 월이 '12'인 사원들의 모든 정보를 조회하기(SUBSTR)
-- 80/12/17
SELECT * FROM EMP
WHERE SUBSTR(HIREDATE,4,2)='12';
- LENGTH('문자열') : 문자열길이구하기
SELECT LENGTH(ENAME) "이름글자수" FROM EMP;
- REPLACE('문자열','찾는문자열','바꿀문자열') : 문자열바꾸기
SELECT REPLACE('THE LION','LION','TIGER') FROM DUAL;
- LPAD('문자열',자릿수,'채울문자') : 왼쪽남는자리에 문자열채우기
SELECT LPAD('HELLO',10,'*') FROM DUAL;
- RPAD('문자열',자릿수,'채울문자') : 오른쪽남는자리에 문자열채우기
SELECT RPAD('HELLO',10,'*') FROM DUAL;
- LTRIM('문자열','없앨문자') : 왼쪽에 문자열 없애기
SELECT LTRIM('THE LION','THE') FROM DUAL;
- RTRIM('문자열','없앨문자') : 오른쪽에 문자열 없애기
SELECT RTRIM('ABCD ',' ') FROM DUAL; --오른쪽 공백문장없애기
======================================================================
<2> 날짜함수(**)
- SYSDATE (**) : 현재날짜와 시간얻어오기
SELECT SYSDATE FROM DUAL;
--날짜(DATE)는 연산이 가능
SELECT SYSDATE+20 "20일후날짜" FROM DUAL;
예) EMP테이블의 사원들의 사원번호, 근속일수를 출력해 보세요.
SELECT EMPNO "사원번호",SYSDATE-HIREDATE "근속일수" FROM EMP;
사원번호 근속일수
---------- ----------
7369 14314.4753
7499 14249.4753
7521 14247.4753
7566 14208.4753
7654 14029.4753
7698 14179.4753
7782 14140.4753
7839 13979.4753
7844 14049.4753
7900 13963.4753
7902 13963.4753
--시분초까지 SYSDATE 함수에 담겨있기 때문에 소수점까지 출력
- ADD_MONTHS(날짜,더할개월수)
SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) "3개월후"
FROM DUAL;
SYSDATE 3개월후
-------- --------
20/02/25 20/05/25
예) 사원들의 사원번호,이름,입사일,입사일로부터 1년후의 날짜 조회하기
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE,12) "입사일기준1년후"
FROM EMP;
EMPNO ENAME HIREDATE 입사일기
---------- -------------------- -------- --------
7369 SMITH 80/12/17 81/12/17
7499 ALLEN 81/02/20 82/02/20
7521 WARD 81/02/22 82/02/22
7566 JONES 81/04/02 82/04/02
7654 MARTIN 81/09/28 82/09/28
7698 BLAKE 81/05/01 82/05/01
7782 CLARK 81/06/09 82/06/09
7839 KING 81/11/17 82/11/17
7844 TURNER 81/09/08 82/09/08
7900 JAMES 81/12/03 82/12/03
7902 FORD 81/12/03 82/12/03
EMPNO ENAME HIREDATE 입사일기
---------- -------------------- -------- --------
7934 MILLER 82/01/23 83/01/23
- MONTHS_BETWEEN(날짜1,날짜2) : 날짜사이의 개월수 구하기
예) 근무개월 수 구하기
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) "근무개월수" FROM EMP;
**중요** <3> TO_CHAR(날짜,'출력형식')
- 날짜형식을 지정된 형식의 문자열로 얻어오는 함수
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD AM HH:MI:SS') -- AM 오전/오후 표시
FROM DUAL;
TO_CHAR(SYSDATE,'YYYY/MM/DDHH:MI:SS')
--------------------------------------
2020/02/25 11:37:08
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')-- 24시간제로 표시
FROM DUAL;
Q1) EMP테이블 사원들의 입사년도를 년/월/일 시/분/초 형식으로 출력되도록 해보세요.
SELECT TO_CHAR(HIREDATE,'YYYY/MM/DD HH:MI:SS') "입사날짜"
FROM EMP;
입사날짜
-------------------
1980/12/17 12:00:00
1981/02/20 12:00:00
1981/02/22 12:00:00
1981/04/02 12:00:00
1981/09/28 12:00:00
1981/05/01 12:00:00
1981/06/09 12:00:00
1981/11/17 12:00:00
1981/09/08 12:00:00
1981/12/03 12:00:00
1981/12/03 12:00:00
입사날짜
-------------------
1982/01/23 12:00:00
..
# 출력컬럼 사이즈 변경
SQL> column 컬럼명 format a30
<4> TO_DATE('날짜형식의문자열','해석할서식')
- 문자를 날짜로 변환하는 함수
SELECT TO_DATE('2020/02/25','YYYY/MM/DD') FROM DUAL;
TO_DATE(
--------
20/02/25
SELECT TO_DATE('20/01/05','YY/MM/DD')+10
FROM DUAL;
TO_DATE(
--------
20/01/15
==========================================================
퀴즈2
==========================================================
<5> 수학함수
- ABS(숫자) : 절대값구하기
- CEIL(숫자) : 올림값구하기
SQL> SELECT CEIL(4.1) FROM DUAL;
CEIL(4.1)
----------
5
- FLOOR(숫자) : 내림값구하기
- ROUND(숫자) : 반올림값구하기
- MOD(숫자,나눌값) : 나머지값구하기
- TRUNC(숫자,절삭할소수점자릿수) : 소수이하 절삭하기
<6> NVL(컬럼,NULL일때 치환할값)
<5> 수학함수
- ABS(숫자) : 절대값구하기
SELECT ABS(-10) FROM DUAL;
- CEIL(숫자) : 올림값구하기
SQL> SELECT CEIL(4.1) FROM DUAL;
CEIL(4.1)
----------
5
- FLOOR(숫자) : 내림값구하기
SELECT FLOOR(4.99) FROM DUAL;
FLOOR(4.99)
-----------
4
- ROUND(숫자) : 반올림값구하기
SELECT ROUND(4.56) FROM DUAL;
ROUND(4.56)
-----------
5
SELECT ROUND(4.34567,3) FROM DUAL;
--소수이하 3번째 자리까지 반올림해서 출력
ROUND(4.34567,3)
----------------
4.346
- MOD(숫자,나눌값) : 나머지값구하기
SELECT MOD(10,3) FROM DUAL;
MOD(10,3)
----------
1
- TRUNC(숫자,절삭할소수점자릿수) : 소수이하 절삭하기
SELECT TRUNC(3.4567,3) FROM DUAL;
TRUNC(3.4567,3)
---------------
3.456
<6> NVL(컬럼,NULL일때 치환할값)
SELECT EMPNO,ENAME,JOB,NVL(COMM,0) COMM FROM EMP;
EMPNO ENAME JOB COMM
---------- -------------------- ------------------ ----------
7369 SMITH CLERK 0
7499 ALLEN SALESMAN 300
7521 WARD SALESMAN 500
7566 JONES MANAGER 0
7654 MARTIN SALESMAN 1400
7698 BLAKE MANAGER 0
7782 CLARK MANAGER 0
7839 KING PRESIDENT 0
7844 TURNER SALESMAN 0
7900 JAMES CLERK 0
7902 FORD ANALYST 0
EMPNO ENAME JOB COMM
---------- -------------------- ------------------ ----------
7934 MILLER CLERK 0
* COMM 평균구하기
SQL> SELECT AVG(COMM) FROM EMP
AVG(COMM)
----------
550
SQL> SELECT AVG(NVL(COMM,0)) FROM EMP;
AVG(NVL(COMM,0))
----------------
183.333333
COULM 갯수도 0으로 셀 때 NVL사용.
연산일 때는 null을 사용 안 함
테이블의 값이 없을 때->null
게시판 만들 때, 조회수가 늘어날 때, null에서 1이 될 수 없기 때문에
AVG함수 통해서 null인경우 0으로 치환
6) 함수(Function)
- 어떠한 일을 수행하는 기능으로써 주어진 인수(argment)를 재료로 처리하여
그 결과값을 반환
(1) 함수의 종류
- 단일행 함수 : 하나의 행(row)당 하나의 결과값을 반환하는 함수
- 복수행 함수 : 여러개의 행당 하나의 결과값을 반환하는 함수
(3) 복수행함수
<1> COUNT(컬럼명) : 행의 갯수
SELECT COUNT(EMPNO) FROM EMP;
COUNT(EMPNO)
------------
12
SELECT COUNT(COMM) FROM EMP; --NULL은 COUNT하지 않음
COUNT(COMM)
-----------
4
SELECT COUNT(*) FROM EMP; --EMP 테이블 수를 센 것
COUNT(*)
----------
12
<2> SUM(컬럼명) : 해당 컬럼의 총합
SELECT SUM(SAL) FROM EMP;
SUM(SAL)
----------
24925
<3> AVG(컬럼명) : 해당 컬럼의 평균
--급여의 평균 구해보세요
SELECT AVG(SAL) FROM EMP;
AVG(SAL)
----------
2077.08333
<4> MIN(컬럼명),MAX(컬럼명) : 최소,최대값
SELECT MIN(SAL) "가장낮은급여",MAX(SAL) "가장높은급여" FROM EMP;
가장낮은급여 가장높은급여
------------ ------------
800 5000
(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;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 6775
10 8750
예2) 직업별 사원들의 급여평균을 구하시오.
SELECT JOB,AVG(SAL)
FROM EMP
GROUP BY JOB;
JOB AVG(SAL)
------------------ ----------
CLERK 1016.66667
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
예3) 급여가 2000이상인 사원들의 부서별 급여총합 구하기
SELECT DEPTNO,SUM(SAL) "급여합"
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO;
DEPTNO 급여합
---------- ----------
30 2850
20 5975
10 7450
예4) 급여가 2000이상인 사원들의 부서별 급여총합 구하기,급여합이 높은순으로 출력
SELECT DEPTNO,SUM(SAL) 급여합
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO
ORDER BY 급여합 DESC; --ORDER BY 절에 ALIAS 사용가능(급여합 자리 SUM(SAL) 넣어도 같은 결과)
--ORDER절이 맨 마지막에 실행되므로 가능
Q1) 부서별 최대급여,최소 급여를 구하세요.
SELECT DEPTNO,MIN(SAL) "가장낮은급여",MAX(SAL) "가장높은급여" FROM EMP
GROUP BY DEPTNO;
DEPTNO 가장낮은급여 가장높은급여
---------- ------------ ------------
30 950 2850
20 800 3000
10 1300 5000
Q2) 부서별 근무인원수를 구해 보세요. 근무인원수가 많은 순으로 출력
SELECT DEPTNO,COUNT(EMPNO) 인원수
FROM EMP
GROUP BY DEPTNO
ORDER BY 인원수 DESC;
DEPTNO 인원수
---------- ----------
30 6
20 3
10 3
Q3) 각 직업별 평균급여와 최대급여,최소급여를 구해 보세요.
SELECT JOB,AVG(SAL) "평균급여",MAX(SAL) "최대급여",MIN(SAL) "최소급여" FROM EMP
GROUP BY JOB;
JOB 평균급여 최대급여 최소급여
------------------ ---------- ---------- ----------
CLERK 1016.66667 1300 800
SALESMAN 1400 1600 1250
PRESIDENT 5000 5000 5000
MANAGER 2758.33333 2975 2450
ANALYST 3000 3000 3000
Q4) 급여가 2000이상인 사원들의 부서별 평균급여를 구하고
평균급여가 높은순으로 정렬되서 출력되도록 하세요.
SELECT DEPTNO,AVG(SAL) "평균급여"
FROM EMP
WHERE SAL>=2000
GROUP BY DEPTNO
ORDER BY 평균급여 DESC;
DEPTNO 평균급여
---------- ----------
10 3725
20 2987.5
30 2850
Q5) 부서번호가 10번인 사원들의 직업별 급여의 평균을 구하고 평균급여가
높은 순으로 조회해 보세요.
SELECT JOB,AVG(SAL) "평균급여" FROM EMP
WHERE DEPTNO=10
GROUP BY JOB
ORDER BY 평균급여 DESC;
JOB 평균급여
------------------ ----------
PRESIDENT 5000
MANAGER 2450
CLERK 1300
예5) 사원들의 부서별 평균급여를 구하시오. 단 급여평균이 2000이상인
부서만 출력되도록 하세요.
SELECT DEPTNO,AVG(SAL) "평균급여"
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>=2000;
DEPTNO 평균급여
---------- ----------
20 2258.33333
10 2916.66667
예6) 부서별로 같은 직업을 갖는 사원들의 급여 평균을 조회하시오.
SELECT DEPTNO,JOB,AVG(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO,JOB;
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
Q1) 부서별 같은 직업을 갖는 사원들의 최대급여를 구하고 부서별 오름차순,
최대급여가 높은 순으로 조회해 보세요.
SELECT DEPTNO,JOB,MAX(SAL) "최대급여"
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO, 최대급여 DESC;
DEPTNO JOB 최대급여
---------- ------------------ ----------
10 PRESIDENT 5000
10 MANAGER 2450
10 CLERK 1300
20 ANALYST 3000
20 MANAGER 2975
20 CLERK 800
30 MANAGER 2850
30 SALESMAN 1600
30 CLERK 950
'MEMO' 카테고리의 다른 글
| 깃, 깃헙의 정체(ft. 쉽게 설명) (0) | 2020.03.09 |
|---|---|
| 27일차 메모(오라클 3일차_SELECT/INSERT/UPDATE/DELETE/SUBQUERY/단일행서브쿼리/복수행단일쿼리/TOP-N절/ROWID/ROWNUM) (0) | 2020.03.09 |
| 커리큘럼 (0) | 2020.02.25 |
| SQL, 쿼리 뜻 (0) | 2020.02.25 |
| 25일차 메모(오라클 설치, 오라클 명령어) (0) | 2020.02.24 |