Oracle SQL

인삼주·2023년 4월 25일

DB 모델링과 OracleSQL

목록 보기

11/16

BETWEEN ... AND

컬럼 BETWEEN 값1 AND 값2
: 컬럼에서 값1과 값2 사이에 있는 값을 가져옴

ex) 2013년 2월 20일부터 2018년 5월 1일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.

SELECT ENAME 사원명, JOB 담당업무, HIREDATE 입사일
FROM EMP
WHERE HIREDATE BETWEEN '2013-02-20' AND '2018-05-01';

NOT BETWEEN ... AND

WHERE NOT 칼럼 BETWEEN 값1 AND 값2
: 칼럼에서 값1과 값2 사이에 포함되지 않는 데이터를 출력하고 싶을 때

ex) 급여가 400에서 500사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.

SELECT ENAME 사원명, SAL 급여
FROM EMP
WHERE NOT SAL BETWEEN 300 AND 500;

IS NULL

WHERE 칼럼 IS NULL;
:널값 조회

ex) 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.

SELECT ENAME 사원명, JOB 담당업무
FROM EMP
WHERE MGR IS NULL;

IS NOT NULL

WHERE 칼럼 IS NOT NULL;
:널값이 아닌 데이터 출력

ex) 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 급여 기준으로 내림차순 정렬하여 표시하시오

SELECT ENAME 사원명, SAL 급여, COMM 커미션
FROM EMP
WHERE COMM IS NOT NULL
ORDER BY SAL DESC;

LIKE

WHERE 컬럼 LIKE (와일드카드 문자열)
: 특정 조건에 맞는 문자열 추출

와일드카드

 _      : 한글자
'가%'   :'가'로 시작하는 모든 것
'%가'%' :'가'가 포함되어 있는 모든 것
'%가'   :'가'로 끝나는 모든 것
'_가%'  : 앞에 한글자가 있고, 두번째 글자가 '가'인 모든 것

ex) 이름의 세번째 문자가 '우'인 사원의 이름을 표시하시오.

SELECT ENAME 사원명
FROM EMP
WHERE ENAME LIKE '__우';

ex) 이름에 '우'와 '박'을 모두 포함하고 있는 사원의 이름을 표시하시오.

SELECT ENAME 사원명
FROM EMP
WHERE ENAME LIKE '%우%' AND ENAME LIKE '%박%';

UPPER, LOWER, INITCAP

UPPER : 대문자 변환
LOWER : 소문자 변환
INITCAP : 첫번쨰 문자 대문자 변환

SELECT UPPER(컬럼명) FROM 테이블;
SELECT LOWER(컬럼명) FROM 테이블;
SELECT INITCAP(컬럼명) FROM 테이블;

ex) 입력된 REMARK 속성에 저장된 영문자를 모두 소문자/대문자/첫자대문자로 조회하시오.

SELECT UPPER(REMARK) 대문자
      ,LOWER(REMARK) 소문자
      ,INITCAP(REMARK) 첫글자대문자
FROM EMP;

SUBSTR

SUBSTR(컬럼명, 시작위치, 문자개수)
:문자열 자르기

ex) SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.

SELECT ENAME 사원명, SUBSTR(HIREDATE,1,2) 입사년도, SUBSTR(HIREDATE,4,2) 입사월
FROM EMP;

ex) SUBSTR 함수를 사용하여 4월에 입사한 사원을 출력하시오.

SELECT ENAME 사원명, SUBSTR(HIREDATE,4,2) 입사월
FROM EMP
WHERE SUBSTR(HIREDATE,4,2)='04';

MOD 함수

MOD(m,n)
:m을 n으로 나누었을 때 나머지를 반환

ex) MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.

SELECT ENAME 사원명, EMPNO 사원번호
FROM EMP
WHERE MOD(EMPNO,2)=0;

SYSDATE

SELECT SYSDATE FROM DUAL;
: 현재 날짜 조회

TO_DATE 함수

: 주어진 문자열 또는 컬럼의 데이터를 날짜 포맷으로 변환해주는 함수

SELECT TO_DATE('문자A','날짜포맷A')
FROM 테이블명;

ex) 올해 몇일이 지났는지 출력하시오.
현재 날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터형을 일치시키시오.

SELECT TO_DATE('2023-04-25','YYYY-MM-DD')- TO_DATE('2023-01-01','YYYY-MM-DD')
FROM DUAL;

SELECT TO_DATE(SYSDATE)- TO_DATE('23-01-01')
FROM DUAL;

NVL 함수

NVL("컬럼명","지정값")
:값이 null인 경우, 지정값을 출력하고 그렇지 않으면 원래값 그대로 출력

ex) 사원들의 상관 사번을 출력하되, 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오(NVL)

SELECT NVL(MGR,0) 사번
FROM EMP;

NVL2 함수

NVL("컬럼명","지정값1","지정값2"
:값이 null인 경우, 지정값1을 출력하고 그렇지 않으면 지정값2를 출력

(NULL 관련 함수)

NULLIF

COALESCE

DECODE 함수

DECODE(컬럼명,'값1','출력A','출력B')
:컬럼이 값1이라면 출력A를, 아니라면 출력B를 실행.

ex) DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. (직급이 '분석가'인 사원은 200, '영업사원'인 사원은 180, '관리자'인 사원은 150,'회계업무'인 사원은 130씩 인상)

SELECT ENAME 사원명, JOB 직급, SAL 기존급여, 
DECODE(JOB,'분석가',SAL+200,'영업사원',SAL+180,'관리자',SAL+150,'회계업무',SAL+130,SAL+0) 인상급여
FROM EMP;

CASE WHEN

SELECT CASE '개구리' WHEN '철호' THEN '아니다'
                 WHEN '너' THEN '아니다'
                 WHEN '나' THEN '나다'
                 ELSE '모르겠다'
                 END RESULT
FROM DUAL;

SELECT CASE WHEN '너구리' = '철호' THEN '아니다'
            WHEN '너구리' = '너' THEN '아니다'
            WHEN '너구리' = '너구리' THEN '너구리가 맞다'
            ELSE '모르겠다'
            END RESULT
FROM DUAL;

MAX, MIN, SUM, AVG

max: 최고
min: 최저
sum: 합계
avg: 평균

ex) 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. (평균에 대해서는 정수로 반올림하시오)

SELECT MAX(SAL) 최고액, MIN(SAL) 최저액, SUM(SAL) 총액, ROUND(AVG(SAL)) 평균급여
FROM EMP;

GROUP BY

GROUP BY 그룹으로 묶을 칼럼명
:그룹별로 통계를 내거나 비교할 때 씀

ex)각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 출력하시오. (평균에 대해서는 정수로 반올림하시오)

SELECT JOB 업무, MAX(SAL) 최고액, MIN(SAL) 최저액, SUM(SAL) 총액, ROUND(AVG(SAL)) 평균급여
FROM EMP
GROUP BY JOB;

ex) COUNT(*) 함수를 이용하여 담당업무가 동일한 사원수를 출력하시오.

SELECT JOB 담당업무, COUNT(*)
FROM EMP
GROUP BY JOB;

EQUI JOIN

: SELECT A.칼럼명, B.칼럼명,...
FROM 테이블 A, 테이블 B
WHERE A.칼럼명 = B.칼럼명;

두 개의 테이블 간에 칼럼 값이 정확하게 일치하는 경우 사용되는 방법 (PK와 FK를 사용한 조인)
JOIN의 조건은 WHERE절에 기술, '=' 연산자를 사용하여 표현.

ex) EQUI 조인을 사용하여 김민욱 사원의 부서번호와 부서 이름을 출력하시오.

SELECT E.ENAME "사원이름", D.DEPTNO "부서번호", D.DNAME "부서명"
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO AND E.ENAME='김민욱';

ex) EQUI 조인과 WildCARD를 사용하여 이름에 '민'자가 포함된 모든 사원의 이름과 부서명을 출력하시오.

SELECT E.ENAME 사원이름, D.DNAME 부서명
FROM EMP E, DEPT D
WHERE E.DEPTNO= D.DEPTNO 
AND E.ENAME LIKE '%민%';

INNER JOIN

SELECT <열 목록>
FROM <첫번째 테이블> INNER JOIN <두번째 테이블>
ON <조인 조건>
[WHERE 검색조건]

: 두 개 혹은 두 개이상의 테이블을 조인할 때 사용.
INNER JOIN을 JOIN이라고만 써도 INNER JOIN으로 인식
ON 연산자 대신 USING 연산자를 사용할 수도 있음.

ex) INNER JOIN과 USING 연산자를 사용하여 10번 부서에 속하는 모든 담당 업무의 고유목록 (한번씩만표시)를
부서의 지역명을 포함하여 출력하시오.

SELECT EMP.EMPNO 사원번호, DEPT.LOC 지역명, EMP.JOB 담당업무
FROM EMP INNER JOIN DEPT USING (DEPTNO)
WHERE DEPTNO=10;

ex) INNER JOIN과 ON 연산자를 사용하여 사원 이름과 함께 그 사원이 소속된 부서이름과 지역명을 출력하시오.

SELECT E.ENAME "사원이름", D.DNAME "부서명", D.LOC "지역명"
FROM DEPT D INNER JOIN EMP E ON D.DEPTNO= E.DEPTNO;

USING과 ON 연산자

USING

: 두 개의 테이블이 내부 조인으로 조인 될 때 조인하고자 하는 두 테이블의 칼럼명이 같은 경우, 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할

ON

: 조인조건의 칼럼명이 서로 다르거나, 비등가조인일 경우 많이 사용
WHRER절을 대신해 JOIN에 대한 조건을 쓴다

-- oracle EQUI JOIN 

SELECT e.ename, d.loc 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- ON절을 사용한 JOIN 

SELECT e.ename, d.loc 
FROM emp e JOIN dept d 
ON (e.deptno = d.deptno);

NATURAL JOIN

:똑같은 컬럼이름이 2개 이상이면 에러가 난다?

ex) NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 커미션, 지역명을 출력하시오.

SELECT EMP.ENAME 사원이름, DEPT.DNAME 부서이름, EMP.COMM 커미션, DEPT.LOC 지역
FROM EMP NATURAL JOIN DEPT 
WHERE COMM IS NOT NULL;

ex) NATURAL JOIN을 이용하여 대전에 근무하는 모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오.

SELECT ENAME 사원이름, JOB 업무, DEPTNO 부서번호, DNAME 부서명, LOC 지역
FROM EMP NATURAL JOIN DEPT
WHERE LOC='대전';

SELF JOIN

: 동일한 테이블 사이의 조인
같은 테이블을 두 개 이상으로 나누어서 사용.

ex) SELF JOIN을 사용하여 사원의 이름 및 사원번호를 관리자 번호와 함께 출력하시오.

SELECT A.ENAME 사원이름, A.MGR 관리자번호, B.ENAME 관리자이름
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO;

ex) SELF JOIN을 사용하여 지정한 사원의 이름, 부서번호, 지정한 사원과 동일한 부서에서 근무하는 사원을 출력하시오. (고영우)

SELECT B.ENAME 사원이름, B.DEPTNO 부서번호
FROM EMP A, EMP B
WHERE A.ENAME='고영우' AND A.DEPTNO=B.DEPTNO;

ex) 사원번호가 7369인 사원과 담당업무가 같은 사원을 표시(사원이름과 담당업무)하시오.(본인제외)

SELECT B.ENAME 사원이름, B.JOB 담당업무
FROM EMP A, EMP B
WHERE A.EMPNO='7369' AND A.JOB=B.JOB AND NOT B.EMPNO='7369';

ex) 사원번호가 7499인 사원보다 급여가 많은 사원을 표시하시오.

SELECT B.ENAME 사원이름, B.JOB 담당업무, B.SAL 급여
FROM EMP A, EMP B
WHERE A.EMPNO='7499' AND A.SAL<B.SAL
ORDER BY B.SAL DESC;

https://tychejin.tistory.com/108

테이블 생성

https://gent.tistory.com/322

CREATE TABLE 테이블명
(

ROWNUM

ex) 평균급여가 가장 적은 JOB과 JOB 급여평균을 구하시오.

SELECT JOB 담당업무, ASD 평균급여
FROM( SELECT JOB, ROUND(AVG(SAL),0) ASD FROM EMP
GROUP BY JOB
ORDER BY AVG(SAL) ASC)
WHERE ROWNUM= 1;

SELECT TABLE_NAME, ROWNUM FROM USER_TABLES;

https://wakestand.tistory.com/244

서브쿼리

SELECT E.EMPNO 사원번호, E.ENAME 이름, E.JOB 담당업무, E.SAL 급여
FROM EMP E, (SELECT JOB, AVG(SAL)SAL FROM EMP WHERE JOB='영업사원')E1
WHERE E.SAL< E1.SAL AND NOT E.JOB= '영업사원';

ROLLUP

소그룹간의 합계를 계산하는 함수

SELECT PROD_LGU, PROD_BUYER, COUNT(*), SUM(PROD_COST)
FROM PROD 
GROUP BY PROD_LGU, ROLLUP(PROD_BUYER);

ROLLUP을 사용하면 GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 모두 구할 수 있음.

-- 참고
https://myjamong.tistory.com/191

CUBE

ROLLUP 함수는 소계와 합계를 순서에 맞게 반환하지만, CUBE 함수는 계산 가능한 모든 소계와 합계를 반환.

-- 참고
https://for-my-wealthy-life.tistory.com/44

UNION vs UNION ALL

A집합과 B집합의 열의 개수가 동일해야함
매핑되는 열의 자료형이 동일해야한다 (A1열이 숫자형이면 B1열도 숫자형이어야하고...)

1) UNION(합집합) FULL OUTER JOIN
조인과 다름. 열이 동일해야한다 *조인은 열의 개수 상관없었음

중복데이터는 1회만 출력
자동정렬 O

 SELECT *
 FROM DEPART A, STUDENT B
 WHERE A.DEP_CODE = B.STUD_DEP(+)
 UNION
 FROM DEPART A, STUDENT B
 WHERE A.DEP_CODE(+) = B.STUD_DEP;

 SELECT *
 FROM DEPART A FULL OUTER JOIN STUDENT B
 ON( A.DEP_CODE = B.STUD_DEP);

2) UNION ALL