<집계 함수 (그룹 함수)>

 

  • 집계 함수 (Aggregate function): AVG(평균), COUNT(개수), MAX(최대값), MIN(최소값), SUM(합계)

 

SELECT [그룹열], 그룹 함수 (열 이름)

FROM 테이블명

WHERE 조건

GROUP BY 그룹명

HAVING 그룹 조건

ORDER BY 열이름

 

 

 

 

    • COUNT: 행의 개수 출력 (null값은 제외됨)

    • employees 테이블의 행의 개수 출력

 

1
2
SELECT COUNT(*)
FROM employees;
 cs

 

 

 

 

    • employees 테이블에서 commission_pct의 개수 출력

 

1
2
SELECT COUNT(commission_pct)
FROM employees;
 cs

 

 

 

 

    • null값도 세고 싶다면?

 

 

1
2
SELECT COUNT(NVL(commission_pct,0))
FROM employees;
 cs

 

 

 

 

 

    • MAX, MIN: 최대값과 최소값 employees 테이블에서 salary의 가장 큰 값과 작은 값 출력

 

1
2
SELECT MAX(salary), MIN(salary)
FROM employees;
 cs

 

 

 

 

    • employees 테이블의 부서의 개수 출력

 

1
2
SELECT COUNT(DISTINCT department_id)
FROM employees;
 cs

 

 

 

 

 

    • employees 테이블에서 department_id가 90인 부서의 직원 수 출력

 

1
2
3
SELECT COUNT(employee_id)
FROM employees
WHERE department_id = 90;
 cs

 

 

 

 

    • MAX, MIN 함수를 문자열에 적용

 

1
2
SELECT MAX(first_name), MIN(first_name)
FROM employees;
 cs

 

 

 

 

    • MAX, MIN 함수를 문자열에 적용 MAX(hire_date)는 가장 최근날짜, MIN(hire_date)는 가장 오래된 날짜.

 

1
2
SELECT MAX(hire_date), MIN(hire_date)
FROM employees;
 cs

 

 

 

 

    • SUM, AVG : 합계와 평균 (숫자만 가능)

 

1
2
SELECT SUM(salary), AVG(salary)
FROM employees;
 cs

 

 

 

 

 

    • employees 테이블에서 commission_pct의 평균값을 출력 (널값은 제외)

 

1
2
SELECT AVG(commission_pct)
FROM employees;
 cs

 

 

 

 

 

  • 각 부서별로 집계 함수를 내려면 필요한 것이 GROUP BY절

 

    • employees 테이블에서 department_id별 salary의 평균을 평균급여 이름으로 출력

 

1
2
3
SELECT department_id, ROUND(AVG(salary)) 평균급여
FROM employees
GROUP BY department_id;
 cs

 

 

 

 

    • employees 테이블에서 department_id별 salary의 평균을 평균급여 이름으로, salary의 합계를 총급여합계로, department_id를 부서번호로, 부서인원수 출력

 

1
2
3
4
5
6
SELECT department_id 부서번호,
ROUND(AVG(salary)) 평균급여,
SUM(salary) 총급여합계,
COUNT(*) 부서인원수
FROM employees
GROUP BY department_id;
 cs

 

 

 

 

 

    • employees 테이블에서 department_id별, job_id별 salary의 평균을 평균급여 이름으로, salary의 합계를 총급여합계로, department_id를 부서번호로, 부서인원수를 부서별 직업별 인원수로 출력

 

1
2
3
4
5
6
7
SELECT department_id 부서번호, job_id 직업번호,
    ROUND(AVG(salary)) 평균급여,
    SUM(salary) 총급여합계,
    COUNT(*) "부서별 직업별 인원수"
FROM employees
GROUP BY department_id, job_id
ORDER BY 부서번호 ASC;
 cs

 

 

 

 

  • HAVING 절: 그룹 함수를 이용한 조건절

 

1
2
3
4
5
SELECT department_id 부서번호, SUM(salary) 급여합계
FROM employees
WHERE SUM(salary) > 100000 --그룹함수 사용 에러
GROUP BY department_id
ORDER BY department_id;
 cs

 

    • WHERE절에 salary의 합계가 10000이 넘는 부서번호와 급여합계를 구하면 에러가난다.

    • WHERE절 대신에 HAVING절 사용

 

1
2
3
4
5
SELECT department_id 부서번호, SUM(salary) 급여합계
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000
ORDER BY department_id;
 cs

 

 

 

 

<집계 함수 예제>

 

 

  • employees 테이블에서 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합계 큰순으로 출력

 

1
2
3
4
5
6
7
SELECT department_id, MAX(salary) 최대급여,
MIN(salary) 최소급여,
SUM(salary) 급여합계,
ROUND(AVG(salary)) 평균급여
FROM employees
GROUP BY department_id
ORDER BY 급여합계 DESC;
 cs

 

 

 

 

  • employees 테이블에서 부서별, 직업별(job_id), 상사번호(manager_id)별로  그룹을 지어 salary 합계와 그룹별 직원의 숫자를 출력

 

1
2
3
4
5
6
7
8
SELECT department_id 부서번호,
job_id 직업,
manager_id 상사번호,
SUM(salary) 월급합계,
COUNT(*) 직원수
FROM employees
GROUP BY department_id, job_id, manager_id
ORDER BY 부서번호;
 cs

 

 

 

 

 

 

  • 부서별로 최고 월급을 뽑아서 평균을 내고, 최저 월급 또한 평균을 내어 출력

 

1
2
3
4
SELECT ROUND(AVG(MAX(salary))) 부서별최고월급평균,
ROUND(AVG(MIN(salary))) 부서별최저월급평균
FROM employees
GROUP BY department_id;
 cs

 

 

 

 

 

 

 

  • 직종별 평균 월급이 $10000을 초과하는 직종에 대해서 job_id와 월급여 합계를 조회. 어카운트 매니저 (AC_MGR)은 제외하고 월 급여 합계로 내림차순 정렬

 

1
2
3
4
5
6
SELECT job_id 직종, SUM(salary) 월급여합계
FROM employees
WHERE job_id NOT IN ('AC_MGR')
GROUP BY job_id
HAVING AVG(salary) > 10000
ORDER BY 월급여합계 DESC;
 cs

 

 

 

 

 

  • 부서번호 40을 제외한 부서별 평균 급여가 7000이하인 부서들의 평균 급여를 출력

 

1
2
3
4
5
SELECT department_id 부서번호, ROUND(AVG(salary)) 평균급여
FROM employees
WHERE department_id NOT IN ('40')
GROUP BY department_id
HAVING AVG(salary) <= 7000;
 cs

 

 

 

 

 

  • 직종별로 월급의 합계가 13000이상인 직종을 출력. 급여총액으로 내림차순 정렬하고 직종(job_id)에 'REP'들어있는 직종은 제외

 

1
2
3
4
5
6
SELECT job_id, SUM(salary) 급여총액
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) >= 13000
ORDER BY 급여총액 DESC;
 cs

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

조인 (JOIN) - 비 동등조인  (0) 2020.06.17
조인 (JOIN) - 동등조인  (0) 2020.06.17
DECODE 함수, CASE 함수  (0) 2020.06.16
NULL 관련 함수  (0) 2020.06.16
변환형 함수  (0) 2020.06.16

 

<DECODE 함수>

 

  • DECODE: 조건에 따라 데이터를 다른 값이나 컬럼 값으로 추출

    • DECODE (value, if1, then1, if2, then2...)

      • value값이 if1일 경우 then1 값을 반환,

      • value값이 if2일 경우 then2 값을 반환

 

1
2
3
4
5
6
SELECT last_name 이름, job_id, salary,
    DECODE(job_id, 'IT_PROG', salary*1.10,
                   'ST_CLERK', salary*1.15,
                   'SA_REP', salary*1.20,
                            salary) 수정월급
FROM employees;
 cs

 

 

 

 

 

<DECODE 함수 예제>

 

  • employees 테이블에서 DECODE 함수를 이용하여 월급에 따른 세율 출력

 

 

 

1
2
3
4
5
6
7
8
9
10
SELECT last_name 이름, job_id 직무, salary 월급,
    DECODE(TRUNC(salary/2000), '0', '0.00',
                              '1', '0.09',
                              '2', '0.2',
                              '3', '0.3',
                              '4', '0.4',
                              '5', '0.42',
                              '6', '0.44',
                              '0.45') 세율
FROM employees;
 cs

 

 

 

 

 

 

 

 

<CASE 함수>

 

  • CASE: DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수

    • 조건 연산자를 모두 사용

    • CASE함수는 IF, THEN, ELSE 구문과 비슷

    • WHEN다음에 여러 조건이 올 수 있음

 

1
2
3
4
5
6
7
SELECT last_name 이름, job_id, salary,
    CASE WHEN salary < 5000 THEN 'Low'
         WHEN salary < 10000 THEN 'Medium'
         WHEN salary < 20000 THEN 'Good'
         ELSE                      'Excellent'
    END "급여 수준"
FROM employees;
 cs

 

 

 

 

 

 

 

 

<CASE 함수 예제>

 

  • employees 테이블에서 job_id가 IT_PROG라면 employee_id, first_name, last_name, salary를 출력하되 salary가 9000이상이면 '상위급여', 6000과 8999사이면 '중위급여', 그 외는 '하위급여'라고 출력

 

1
2
3
4
5
6
7
SELECT employee_id, first_name, last_name, salary,
    CASE WHEN salary >= 9000 THEN '상위급여'
        WHEN salary >= 6000 THEN '중위급여'
         ELSE                      '하위급여'
    END "급여등급"
FROM employees
WHERE job_id = 'IT_PROG';
 cs

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

조인 (JOIN) - 동등조인  (0) 2020.06.17
집계 함수 (그룹 함수)  (0) 2020.06.16
NULL 관련 함수  (0) 2020.06.16
변환형 함수  (0) 2020.06.16
날짜형 함수  (0) 2020.06.15

<NULL 관련 함수>

 

  • NVL: NULL값을 다른 값으로 바꿀 때 사용. 모든 데이터 타입에 적용 가능

 

1
2
3
SELECT last_name, manager_id 매니저
FROM employees
WHERE last_name = 'King';
 cs

 

 

 

 

 

1
2
3
4
SELECT last_name, NVL(manager_id, 0) 매니저
FROM employees
WHERE last_name = 'King';
--manager_id에 있는 null값을 0으로 변환
 cs

 

 

 

 

  • NVL2 (ex, ex1, ex2): ex값이 NULL이 아니면 ex1, NULL이면 ex2

 

1
2
3
SELECT last_name, NVL2(manager_id, 1, 0) 매니저
FROM employees
WHERE last_name = 'King';
 cs

 

 

 

 

  • NULLIF (ex1, ex2): ex1과 ex2값이 동일하면 NULL, 동일하지 않으면 ex1으로 출력

 

1
2
SELECT NULLIF(1,1), NULLIF(1,2)
FROM DUAL;
 cs

 

 

 

 

 

  • COALESCE (ex1, ex2, ex3,...): ex1이 NULL이면 ex2 반환,  ex2도 NULL이면 ex3반환...

 

  • employees테이블에서 last_name, salary, commission_pct 출력하되 commission_pct가 NULL값이면 salary에서 2000인상

 

1
2
3
4
5
6
SELECT last_name 이름, salary 월급,
commission_pct 커미션pct,
COALESCE((salary+(commission_pct*salary)), salary+2000)
월급인상
FROM employees
ORDER BY 3;
 cs

 

 

 

 

 

<NULL 관련 함수 예제>

 

  • employees 테이블에서 이름, 월급 (salary), 커미션 (commission_pct)를 NVL 함수를 이용하여 연봉은 (월급*12)+(월급*12*커미션)으로 나타내고 commission_pct가 null값인 경우 0으로 치환

 

1
2
3
4
5
SELECT last_name 이름, salary 월급,
NVL(commission_pct,0) 커미션,
(salary*12)+(salary*12*NVL(commission_pct,0)) 연봉
FROM employees
ORDER BY 4 DESC;
 cs

 

 

 

 

 

  • NVL2함수를 이용하여 위의 예제에서 커미션이 있을때와 없을 때의 계산 방법도 출력

 

1
2
3
4
5
6
SELECT last_name 이름, salary 월급,
NVL(commission_pct,0) 커미션,
(salary*12)+(salary*12*NVL(commission_pct,0)) 연봉,
NVL2(commission_pct,'SAL+COMM','SAL') 연봉계산
FROM employees
ORDER BY 4 DESC;
 cs

 

 

 

 

  • employees 테이블에서 first_name과 last_name의 길이 LENGTH를 비교하여 같으면 null, 아니면 LENGTH(first_name)값을 출력

 

1
2
3
4
5
SELECT first_name, LENGTH(first_name) "글자수1",
last_name, LENGTH(last_name) "글자수2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) "결과"
FROM employees
ORDER BY last_name;
 cs

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

집계 함수 (그룹 함수)  (0) 2020.06.16
DECODE 함수, CASE 함수  (0) 2020.06.16
변환형 함수  (0) 2020.06.16
날짜형 함수  (0) 2020.06.15
숫자형 함수  (0) 2020.06.15

<변환형 함수>

 

  • 날짜를 문자로, 숫자를 문자로 바꾸는 TO_CHAR

    • TO_CHAR(날짜, 출력형식)

    • TO_CHAR(숫자, 출력형식)

    • <출력형식>

      • , (콤마): 3자리마다 , 를 찍음

      • . (온점): 소수점 형식으로 변환

      • 0: 왼쪽에 0을 삽입

      • $: $통화로 표시

      • L: Local 통화료 표시

      • XXXX: 16진수로 표시

 

1
2
SELECT TO_CHAR(12345678, '999,999,999') 콤마
FROM DUAL;
 cs

 

 

 

 

1
2
SELECT TO_CHAR(12345678, '999,999,999.99') 소수점
FROM DUAL;
 cs

 

 

 

1
2
SELECT TO_CHAR(12345678, '$999,999,999.99') 달러표시
FROM DUAL;
 cs

 

 

 

1
2
SELECT TO_CHAR(12345678, 'L999,999,999.99') L표시
FROM DUAL;
 cs

 

 

 

 

1
2
3
SELECT TO_CHAR(123, '09999') 제로표시 
--남는 공간을 0으로 채우기
FROM DUAL;
 cs

 

 

 

      • YYYY: 4자리 연도

      • YY: 2자리 연도

      • DAY: 요일 (월, 화..)

 

1
2
3
4
--날짜를 문자로 변환
--년, 월, 일, 시, 분, 초
SELECT TO_CHAR(sysdate, 'YY-MM-DD HH24:MI:SS') 현재날짜시간
FROM DUAL;
 cs

 

 

 

1
2
SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS') 현재날짜시간
FROM DUAL;
 cs

 

 

 

1
2
3
--365일 중 오늘이 며칠?
SELECT TO_CHAR(sysdate, 'DDD') 날짜
FROM dual;
 cs

 

 

 

1
2
3
--오늘의 월?
SELECT TO_CHAR(sysdate, 'MONTH') 몇월
FROM dual;
 cs

 

 

 

 

<TO_CHAR 예제>

 

  • employees 테이블에서 department_id가 100인 사원들의 입사일을 이용하여 employee_id, 입사월을 출력. (입사월은 월/년도 형식)

 

1
2
3
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY')
FROM employees
WHERE department_id = 100;
 cs

 

 

 

  • employees 테이블에서 salary가 10000달러가 넘는 사원들의 이름과 월급을 월급이 많은 순으로 출력 (월급의 표시형식은 $)

 

 

1
2
3
4
SELECT last_name "이름", TO_CHAR(salary, '$99,999.99') "월급"
FROM employees
WHERE salary > 10000
ORDER BY salary DESC;
 cs

 

 

 

  • 문자를 날짜로 바꾸는 TO_DATE

  • CHAR, VARCHAR2형을 DATE 타입으로 변환

    • TO_DATE(char, 출력형식)

    • CHAR: n바이트 크기를 가진 문자형 타입. n보다 작으면 나머지 공간을 공백으로 채워서 n바이트를 채움

    • VACHAR: n바이트의 크기를 가진 문자형 타입이지만 저장되는 타입의 크기가 n보다 작으면 n만큼의 길이만큼만 기억장소를 차지하는 가변형 타입. (4000byte까지 저장)

 

1
2
SELECT TO_DATE('2011-01-01', 'YYYY-MM-DD')
FROM dual;
 cs

 

 

 

  • 문자를 숫자로 바꾸는 TO_NUMBER

  • CHAR, VARCHAR2형을 숫자형식으로 변환

    • TO_NUMBER(char)

 

 

1
2
SELECT TO_NUMBER('0123123')+100
FROM dual;
 cs

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

DECODE 함수, CASE 함수  (0) 2020.06.16
NULL 관련 함수  (0) 2020.06.16
날짜형 함수  (0) 2020.06.15
숫자형 함수  (0) 2020.06.15
문자형 함수  (0) 2020.06.15

<날짜형 함수>

 

  • SYSDATE: 현재 날짜와 시간

1
2
SELECT SYSDATE
FROM DUAL;
 cs

 

 

 

    • 날짜+숫자: 날짜

1
2
SELECT SYSDATE, SYSDATE+3, SYSDATE-3
FROM DUAL;
 cs

 

 

 

 

 

    • 날짜-날짜: 총 일수

1
2
3
SELECT employee_id, SYSDATE, hire_date,
SYSDATE - hire_date, ROUND (SYSDATE-hire_date)
FROM employees;
 cs

 

 

 

 

 

    • 시간 계산

 

1
2
SELECT sysdate + 5/24 --시간 1(하루) 24시간 중 5시간
FROM DUAL;
 cs

 

 

 

 

 

 

 

    • month_between: 월을 계산

 

1
2
3
4
SELECT employee_id, first_name,
    ROUND(MONTHS_BETWEEN(sysdate,hire_date)),
    (sysdate-hire_date)/30
FROM employees;
 cs

 

 

 

 

 

 

    • add_months: 달을 더함

 

1
2
3
SELECT employee_id, first_name, hire_date,
ADD_MONTHS(hire_date,4) --4달 더하기
FROM employees;
 cs

 

 

 

 

 

    • next day(날짜, 다음번 나올 요일)

 

1
2
SELECT sysdate, next_day(sysdate, '월요일')
FROM DUAL;  
 cs

 

 

 

 

 

 

1
2
SELECT sysdate, next_day(sysdate, '화요일')
FROM DUAL;  
 cs

 

 

 

 

 

    • LAST_DAY (그 월의 마지막 날)

 

1
2
SELECT LAST_DAY(SYSDATE)
FROM DUAL;
 cs

 

 

 

 

 

    • 날짜의 반올림

 

1
2
3
4
5
SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH') 월, --날에서 반올림 1일
ROUND(hire_date, 'YEAR') 년 --월에서 반올림 1월 1일
FROM employees
WHERE MONTHS_BETWEEN (sysdate, hire_date) < 150;
 cs

 

 

 

 

 

<날짜형함수 예제>

 

  • employees 테이블에서 department_id가 100인 직원에 대해 오늘 날짜, hire_date, 오늘 날짜와 hire_date사이의 개월 수를 출력

 

1
2
3
SELECT sysdate, hire_date, MONTHS_BETWEEN(SYSDATE,hire_date)
FROM employees
WHERE department_id = 100;
 cs

 

 

 

  • employees 테이블에서 employee_id가 100과 106사이인 직원의 hire_date에 3개월을 더한 값, hire_date에 3개월을 뺀 값을 출력

 

1
2
3
4
SELECT hire_date, ADD_MONTHS(hire_date,3) "더하기_3개월",
ADD_MONTHS(hire_date,-3) "빼기_3개월"
FROM employees
WHERE employee_id BETWEEN 100 AND 106;
 cs

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

NULL 관련 함수  (0) 2020.06.16
변환형 함수  (0) 2020.06.16
숫자형 함수  (0) 2020.06.15
문자형 함수  (0) 2020.06.15
IS NULL 연산자, ORDER BY  (0) 2020.06.15

<숫자형 함수>

 

  • ROUND: 숫자를 반올림. 0은 소숫점 첫쨰 자리 

ex) ROUND(15,351,0)->15

 

1
2
3
4
5
6
7
SELECT ROUND(15.193, 1) 소수첫째자리,
    ROUND (15.193, 2) 소수둘째자리,
    ROUND(15.193,0) 정수,
    ROUND(15.193) 디폴트,
    ROUND(15.193,-1) "10의자리",
    ROUND(15.193,-2) "100의자리"
FROM DUAL;
 cs

 

 

 

  • TRUNC: 버림

 

1
2
3
4
5
SELECT TRUNC(15.79, 1) 소수첫째자리,
    TRUNC(15.79, 0) 정수,
    TRUNC(15.79) 디폴트,
    TRUNC(15.79,-1) "10의자리"
FROM DUAL;
 cs

 

 

 

 

 

 

  • MOD: 나누기 후 나머지를 구한다.

ex) MOD(15,2)-> 1

 

    • employees테이블에서 employeed_id가 짝수인 직원의 employee_id와 last_name을 조회하되, 첫번째 열로 정렬하고 employee_id의 열 이름은 짝수번으로 한다.

 

1
2
3
4
SELECT employee_id 짝수번, last_name
FROM employees
WHERE MOD(employee_id, 2)=0
ORDER BY 1;
 cs

 

 

 

 

 

<숫자형 함수 예제>

 

  • employees 테이블에서 salary를 30으로 나눈 후 나눈 값의 결과를 반올림하여 정수, 소수점 첫째 자리, 10의 자리로 출력

 

1
2
3
4
5
SELECT salary,
ROUND((salary/30),0) 정수,
ROUND((salary/30),1) 소수1,
ROUND((salary/30),-1) "10의자리"
FROM employees;
 cs

 

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

변환형 함수  (0) 2020.06.16
날짜형 함수  (0) 2020.06.15
문자형 함수  (0) 2020.06.15
IS NULL 연산자, ORDER BY  (0) 2020.06.15
LIKE 연산자  (0) 2020.06.15

<함수>

문자, 숫자, 날짜 값 등을 조작, 각 데이터 타입끼리 변환(단일 행 함수)

하나의 테이블에 있는 하나의 행을 처리

다중행 함수는 한 열의 여러 값을 처리, 하나의 값으로 나타냄

 

<단일 행 함수>

 

    • 문자형 함수

    • 대 소문자 함수

      • LOWER: 문자열을 소문자로 변환

      • UPPER: 문자열을 대문자로 변환

      • INITCAP: 첫문자만 대문자, 나머지는 소문자

ex) SQL Course-> Sql Course

 

1
2
SELECT 1+1
FROM DUAL; --DUAL테이블은 연습용 테이블
 cs

 

 

1
2
SELECT LOWER ('SQL COURSE'), UPPER ('sql course'), 
INITCAP ('SQL COURSE')
FROM DUAL;
 cs

 

 

 

 

    • 문자 함수를 WHERE절에 사용

    • employees 테이블에서 first_name이 PATRICK인 직원의 employee_id, first_name을 조회

1
2
3
SELECT employee_id, first_name
FROM employees
WHERE UPPER (first_name) = 'PATRICK';
 cs

 

 

 

    • 문자 조작 함수

      • CONCAT: 두 개의 문자열을 연결.

ex) CONCAT('Hello', 'World') -> HelloWorld

 

employees 테이블에서 first_name, last_name과 둘을 연결한 풀네임이란 별칭으로 조회

 

1
2
SELECT first_name, last_name, CONCAT (first_name, last_name) 
AS 풀네임
FROM employees;
 cs

 

 

 

      • SUBSTR: 문자열 내에서 지정된 위치의 문자열을 반환

      • SUBSRT (열이름, m, n): m은 시작문자, n은 잘라낼 길이

ex) SUBSTR('HelloWorld', 2,5) -> 두번째부터 5개를 잘라라. ->elloW

 

    • employees테이블에서 employee_id, first_name을 조회하고 first_name의 문자를 잘라서 조회

 

1
2
3
4
5
6
7
SELECT employee_id,
first_name,
SUBSTR (first_name, 1, 3), --첫번째부터 3개의 문자를 잘라낸다.
SUBSTR (first_name, 2, 4), --두번째부터 4개의 문자를 잘라낸다.
SUBSTR (first_name, 2, 3), --두번째부터 3개의 문자를 잘라낸다.
SUBSTR (first_name, -3) --음수: 끝에서부터 카운트. 
마지막 3개의 문자를 잘라낸다.
FROM employees;
 cs

 

 

 

 

 

    • LENGTH: 문자열의 길이를 반환. LENGTH('HelloWorld')->10

 

    • employees테이블에서 first_name과 first_name의 문자열의 길이를 조회

 

1
2
SELECT first_name, LENGTH (first_name)
FROM employees;
 cs

 

 

 

 

      • INSTR: 지정된 문자의 위치를 리턴.

      • INSTR(문자열, 검색문자, [시작위치, [횟수]])

INSTR (문자열, 찾을 문자 M,N)

M은 검사 시작 위치

N은 찾을 횟수

디폴트 값은 M,N 각각 1

ex) INSTR('HelloWorld', 'O')->5

 

    • employees 테이블에서 first_name이 'Nanette'인 직원의 이름의 e를 검색하여 조회

 

1
2
3
4
5
6
SELECT first_name,
INSTR (first_name, 'e', 2), --두번째 부터 e를 검색
INSTR (first_name, 'e', 5), --다섯번째 부터 e를 검색
INSTR (first_name, 'e', 1, 2) 
--첫번째부터 찾는데 2번째 나오는 e의 자릿수
FROM employees
WHERE first_name = 'Nanette';
 cs

 

 

 

 

      • TRIM: 접두어나 접미어를 잘라냄

ex) TRIM('H' FROM 'HelloWorld') -> elloWorld

 

      • LPAD, RPAD: 지정된 문자열의 길이 만큼 빈 부분에 문자를 채움.

ex) LPAD('World', 10, '*') ->****World

 

 

    • employees테이블에서 employee_id, first_name, salary를 조회하되 salary에서 10자리 중 빈 곳은 왼쪽정렬로 #을 채우고, 오른쪽 정렬로 *을 채워서 출력

1
2
3
SELECT employee_id, first_name, salary,
LPAD (salary, 10, '#'), RPAD (salary, 10, '*')
FROM employees;
 cs

 

 

 

 

  • 문자치환 함수

    • TRANSLATE: 문자 단위 치환된 값을 리턴

    • REPLACE: 문자열 단위 치환된 값을 리턴

        ex) REPLACE('hello', 'el', '*')->

    • employees테이블에서 job id에 ACCOUNT가 포함된 자료의 job_id의 ACCOUNT를 ACCNT로 치환하여 조회

1
2
3
SELECT job_id, REPLACE (job_id, 'ACCOUNT', 'ACCNT')
FROM employees
WHERE job_id LIKE '%ACCOUNT%';
 cs

 

 

 

<문자형 함수 예제>

 

  • 공백을 이용하여 성과 이름을 따로 출력

 

1
2
3
4
SELECT
'홍 길동' 성명,
SUBSTR('홍 길동', 1, INSTR('홍 길동', ' ')-1) 성
FROM DUAL;
 cs

 

 

 

1
2
3
4
SELECT
'제갈 길동' 성명,
SUBSTR('제갈 길동', 1, INSTR('제갈 길동', ' ')-1) 성
FROM DUAL;
 cs

 

 

1
2
3
4
5
SELECT
'제갈 길동' 성명,
SUBSTR('제갈 길동', 1, INSTR('제갈 길동', ' ')-1) 성,
SUBSTR('제갈 길동', INSTR('제갈 길동', ' ')+1) 이름 
--이름은 빈칸 자릿수보다 한자리 많음
FROM DUAL;
 cs

 

 

 

 

  • employees테이블에서 job_id의 4번째부터 3글자가 REP인 직원의 last_name과 job_id를 조회하되, job_id 앞에 '직업명이'란 글자를 붙여서 직업명이란 열로 출력

 

1
2
3
SELECT last_name, CONCAT('직업명이 ', job_id) AS 직업명
FROM employees
WHERE SUBSTRB(job_id, 4, 3)='REP';
 cs

 

 

 

  • employees테이블에서 first_name과 last_name를 붙여서 전체이름 열로 조회, last_name의 길이, last_name의 'a'가 몇번째 인지 출력

 

1
2
3
4
5
6
SELECT employee_id,
    CONCAT (first_name, last_name) 전체이름,
    last_name,
    LENGTH (last_name) 길이,
    INSTR (last_name, 'a') "'a'가 몇번째?"
FROM employees;
 cs

 

 

 

  • employees테이블에서 last_name을 소문자와 대문자로 각각 출력하고, email의 첫번째 문자는 대문자로 출력

 

1
2
3
4
5
SELECT last_name, LOWER(last_name) "LOWER적용",
UPPER(last_name) "UPPER적용",
email "이메일",
INITCAP (email) "INITCAP적용"
FROM employees;
 cs

 

 

 

 

  • employees테이블에서 job_id 데이터 값의 첫째 자리부터 시작해서 두 개의 문자를 출력

 

1
2
SELECT job_id, SUBSTR(job_id,1,2) "앞의2개"
FROM employees;
 cs

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

날짜형 함수  (0) 2020.06.15
숫자형 함수  (0) 2020.06.15
IS NULL 연산자, ORDER BY  (0) 2020.06.15
LIKE 연산자  (0) 2020.06.15
BETWEEN  (0) 2020.06.12

<IS NULL 연산자>

 

  • 데이터 값이 null인 경우를 조회하고자 할때 사용하는 연산자

  • null: 값이 없어 알 수 없는 값 (0이나 공백과는 다름)

 

 

  • employees테이블에서 comission_pct가 null인 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE commission_pct IS NULL;
 cs

 

 

 

 

  • employees테이블에서 comission_pct가 null이 아닌 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
 cs

 

 

 

 

 

<IS NULL 연산자 예제> 

 

  • employees 테이블에서 manager_id 가 null 값인 직원 정보를 출력

1
2
3
SELECT *
FROM employees
WHERE manager_id IS NULL;
 cs

 

 

 

 

 

<ORDER BY>

 

  • 행에 대해 정렬 순서를 지정

  • ORDER BY 열 이름 [ASC or DESC] (ASC: 오름차순, DESC: 내림차순]

  • ASC 오름차순은 생략 가능

 

 

  • employees 테이블의 모든 데이터를 last_name을 기준으로 내림차순으로 정렬하여 조회

1
2
3
SELECT *
FROM employees
ORDER BY last_name DESC;
 cs

 

 

 

 

  • employees 테이블의 모든 데이터를 salary가 낮은 순으로 정렬하여 조회

1
2
3
SELECT *
FROM employees
ORDER BY salary;
 cs

 

salary 뒤에 ASC을 생략하였음.

 

 

 

  • employees 테이블의 department_id, employe_id, first_name, last_name을 department_id로 정렬 한 후 employee_id로 정렬하여 조회

     

    1
    2
    3
    SELECT department_id, employee_id, first_name, last_name
    FROM employees
    ORDER BY department_id, employee_id;
    		 c

     

    department_id가 오름차순으로 정렬하고 department_id가 같을때는 employee_id가 오름차순으로 정렬된다.

     

 


 

 

 

  • employees 테이블의 department_id, last_name, salary*12를 연봉이란 별칭으로 조회하되 연봉으로 정렬

1
2
3
SELECT department_id, last_name, salary*12 AS "연봉"
FROM employees
ORDER BY "연봉" DESC;
 cs

 

 

 

 

  • employees테이블에서 department_id, last_name, salary*12를 연봉이란 별칭으로 조회하되 3번째 열의 내림차순으로 정렬

     

    1
    2
    3
    SELECT department_id, last_name, salary*12 AS "연봉"
    FROM employees
    ORDER BY 3 DESC;
    		 cs

     

     

(ORDER BY 1 DESC이라면 department_id가 큰 순, 2 DESC이라면 last_name이 큰 순으로 정렬된다.)

 

 


 

 

 

 

 

<ORDER BY 예제> 

  • employees 테이블에서 employee_id, first_name, last_name을 출력하고 employee_id를 기준으로 내림차순 정렬

    1
    2
    3
    SELECT employee_id, first_name, last_name
    FROM employees
    ORDER BY employee_id DESC;
    		 c

     

 

 

  • employees 테이블에서 job_id에 CLERK란 단어가 들어가는 직원들의 salary 가 높은 순으로 정렬

1
2
3
4
SELECT *
FROM employees
WHERE job_id LIKE '%CLERK%'
ORDER BY salary DESC;
 cs

 

 


 

 

 

  • employees 테이블에서 employee_id가 120에서 150번까지 직원을 department_id가 큰 순으로 정렬하고 department_id가 같을 시 salary가 큰 순으로 정렬

1
2
3
4
SELECT *
FROM employees
WHERE employee_id BETWEEN 120 AND 150
ORDER BY department_id DESC, salary DESC;
 cs

 

 


 

 

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

숫자형 함수  (0) 2020.06.15
문자형 함수  (0) 2020.06.15
LIKE 연산자  (0) 2020.06.15
BETWEEN  (0) 2020.06.12
IN, NOT IN  (0) 2020.06.12

<LIKE 연산자>

  • 조회 조건 값이 명확하지 않을 때 사용

  • %, _같은 기호 연산자(wild card)와 함께 사용 (%: 모든 문자, _: 한 글 자)

 

 

  • employees 테이블에서 last_name 값이 B로 시작하는 모든 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE last_name LIKE 'B%';
 cs

 

 

 

 

  • employees 테이블에서 last_name 값이 B를 포함하는 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE last_name LIKE '%B%';
 cs

 

 

 

  • employees 테이블에서 first_name 값이 첫글자 뒤에 d가 나오는 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE first_name LIKE '_d%';
 cs

 

 

 

  • employees 테이블에서 first_name 값의 세번째 문자가 s가 나오는 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE first_name LIKE '__s%';
 cs

 

 

 

 

 

 

 

<LIKE 예제>

 

  • employees 테이블에서 job_id 값이 AD를 포함하는 모든 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE job_id LIKE '%AD%';
 cs

 

 

 

 

  • employees 테이블에서 job_id 가 AD를 포함하면서 AD뒤에 따라오는 문자열이 3자리인 데이터를 조회

1
2
3
SELECT *
FROM employees
WHERE job_id LIKE '%AD___';
 cs

 

 

 

 

 

  • employees 테이블에서 전화번호 뒷자리가 1234로 끝나는 직원 정보를 조회

1
2
3
SELECT *
FROM employees 
WHERE phone_number LIKE '%1234';
 cs

 

 

 

 

  • employees 테이블에서 전화번호에 3이 들어가지 않으면서 전화번호 끝자리가 9로 끝나는 직원 정보를 조회

1
2
3
SELECT *
FROM employees
WHERE phone_number NOT LIKE '%3%' AND phone_number LIKE '%9';
 cs

 

 

 

 

  • employees 테이블에서 job_id에 MGR을 포함하거나, ASST를 포함하는 직원 정보를 조회

1
2
3
SELECT *
FROM employees
WHERE job_id LIKE '%MGR%' OR job_id LIKE '%ASST%';
 cs

 

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

문자형 함수  (0) 2020.06.15
IS NULL 연산자, ORDER BY  (0) 2020.06.15
BETWEEN  (0) 2020.06.12
IN, NOT IN  (0) 2020.06.12
AND, OR  (0) 2020.06.12

<SELECT-WHRE절의 BETWEEN 연산자>

 

  • employees테이블에서 salary가 9000이상 10000이하인 직원정보를 출력

1
2
3
SELECT *
FROM employees
WHERE salary >= 9000 AND salary <=10000;
 cs

 

 

  • BETWEEN 연산자

1
2
3
SELECT *
FROM employees
WHERE salary BETWEEN 9000 AND 10000;
 cs

 

출력결과는 같다

 

 

 

 

 

<BETWEEN 예제>

 

  • employees테이블에서 salary가 10000이상이고 20000이하인 직원정보를 출력

1
2
3
SELECT *
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
 cs

 

 

 

 

  • employees테이블에서 hire_date가 2004년 1월 1일 부터 2004년 12월 30일 사이인 직원정보를 출력

1
2
3
SELECT *
FROM employees
WHERE hire_date BETWEEN '04/01/01' AND '04/12/30';
 cs

 

 

 

 

 

  • employees테이블에서 salary가 7000 미만이거나 17000보다 많은 직원정보를 출력

 

1
2
3
SELECT *
FROM employees
WHERE salary NOT BETWEEN 7000 AND 17000;
 cs

 

salary가 7000이 안되거나 17000보다 큰 직원정보가 출력된다.

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Learning > SQL' 카테고리의 다른 글

IS NULL 연산자, ORDER BY  (0) 2020.06.15
LIKE 연산자  (0) 2020.06.15
IN, NOT IN  (0) 2020.06.12
AND, OR  (0) 2020.06.12
SELECT-WHERE 절  (0) 2020.06.12

+ Recent posts

티스토리툴바