[SQL] WHERE절과 정렬, 단일 행 함수 사용

지수·2023년 4월 2일

TIL sql

플레이데이터

목록 보기

12/50

[ 실습 내용 ]

1. SELECT 문 작성

WHERE절 키워드

LIKE 비교 : WHERE name LIKE '_K%'; 검색 조건을 부분적으로만 알고 있을 때 사용
- % : 0개 이상의 문자 (문자의 내용과 개수는 상관 없음)
- _ : 1개의 문자 (문자의 내용은 상관 없음)
LIKE와 ESCAPE : LIKE 검색 조건에 % 나 _ 가 포함되어 있을 경우
LIKE '#_%' ESCAPE '#' ESCAPE에 구분자 명시, 구분자 뒤 특수문자 문자로 인식
NULL 비교 : IS NULL, IS NOT NULL
WHERE절의 의미 :
- WHERE 1 = 1 = TRUE 조건 = 모든 행 출력
- WHERE 1 = 0 = FALSE 조건 = 출력 없음
논리연산자(AND, OR, NOT) : AND, OR, NOT(NOT IN, NOT BETWEEN,NOT LIKE,NOT+산술연산자)
논리연산자 우선순위를 명확하게 하기 위해 괄호로 묶는 것 권장

ORDER BY 정렬

SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate;

오름차순 정렬 : ORDER BY ____ ASC;, 디폴트
내림차순 정렬 : ORDER BY ____ DESC;
다중 컬럼 정렬 : 여러 컬럼을 정렬할 때는 우선 정렬부터 차례로 명시
ORDER BY hiredate ASC, empno DESC;
정렬 시 NULL 값 처리 : DESC 정렬 시 NULL 값이 최상단에 옴
▶ 뒤에 NULL LAST;를 붙이면, NULL 값이 최하단으로 감
컬럼 별칭(alias)으로 정렬 : 별칭 사용은 ORDER BY에서만 가능
행 제한절 : 행을 제한적으로 출력
- FETCH FIRST 5 ROWS ONLY
- WHERE ROWNUM <= 5

2. 단일 행 함수 사용

문자 함수

UPPER() : 값 전체를 대문자로 변환
LOWER() : 값 전체를 소문자로 변환
INITCAP() : 맨 앞글자는 대문자 이후는 소문자로 변환

TRIM() : 문자열 양쪽 공백 제거
LTRIM() : 문자열 좌측 공백 제거 / 좌측 문자 제거(두 번째 인자로 제거할 문자열 받음) LTRIM(기존 값, 기존 값에 있으면 왼쪽에서 지울 값)
RTRIM() : 문자열 우측 공백 제거 / 우측 문자 제거(두 번째 인자로 제거할 문자열 받음) RTRIM(기존 값, 기존 값에 있으면 오른쪽에서 지울 값)

LPAD() : 왼쪽부터 특정 문자로 자릿수 채움
LPAD(기존 값, 총 길이, 왼쪽에 총 길이 채울 때까지 넣을 값)
RPAD() : 오른쪽부터 특정 문자로 자릿수 채움
RPAD(기존 값, 총 길이, 오른쪽에 총 길이 채울 때까지 넣을 값)

LENGTH() : 문자열 길이 반환
LENGTHB() : 저장 바이트 길이 반환

SUBSTR() : 문자열을 부분적으로 반환
- SUBSTR(phone, 3) : phone 값을 3번째 자리부터 반환
- SUBSTR(phone, 2, 3) : phone 값을 2번째 자리부터 3번째 자리까지 반환
INSTR() : 문자열 안에 특정 값이 몇번째 자리에 있는지 반환
INSTR(email, '@') email 안에 @ 값이 몇번째 자리에 있는지 반환
값이 없으면 0, 2개 이상이면 첫번째 자리 반환
REPLACE() : 특정 값을 다른 값으로 바꾸어 반환
REPLACE(email, '_', '.') email 안에 '_' 값을 '.'로 바꾸어 반환

CONCAT() : 연결 연산자(||)와 같은 역할, 2개 인자만 받을 수 있음, 중첩 사용 가능

UPPER()과 LOWER()의 활용

✅ 숫자값은 두 함수에 영향을 받지 않음
UPPER(number) = LOWER(number)조건으로 숫자값으로만 이루어진 데이터 검색 가능

LTRIM()의 활용

✅ LTRIM(a, b) IS NULL을 통해 특정값을 갖지 않는 데이터만 검색 가능

숫자 함수

ROUND() : 첫번째 인자로 받은 값을 특정 자리(두번째 인자)에서 반올림
TRUNC() : 첫번째 인자로 받은 값을 특정 자리(두번째 인자)에서 버림

FLOOR() : 소수점 첫째 자리에서 버림, 주어진 값과 가장 근접하게 작은 정수를 구함
CEIL() : 소수점 첫째 자리에서 올림, 주어진 값과 가장 근접하게 큰 정수를 구함

날짜에 ROUND(), TRUNC() 함수 활용

✅ ROUND() 함수로 날짜 값 반올림

SELECT '2021/01/25',
	ROUND(TO_DATE('2021/01/25')),		>> '2021/01/25'
	ROUND(TO_DATE('2021/01/25'), 'month'),	>> '2021/02/01'
	ROUND(TO_DATE('2021/01/25'), 'year')	>> '2021/01/01'
FROM dual;

✅ TRUNC() 함수로 날짜 값 버림

SELECT '2021/01/25',
	TRUNC(TO_DATE('2021/01/25')),		>> '2021/01/25'
	TRUNC(TO_DATE('2021/01/25'), 'month'),	>> '2021/01/01'
	TRUNC(TO_DATE('2021/01/25'), 'year')	>> '2021/01/01'
FROM dual;

날짜 함수

ADD_MONTHS() : 기존 값에 몇 개월을 더함 ADD_MONTHS(SYSDATE, 2)
MONTHS_BETWEEN() : 두 날짜 값 사이 개월 수 반환 MONTHS_BETWEEN(SYSDATE, hiredate)
NEXT_DAY() : 기준 날짜 이후 첫번째 요일이 언젠지 반환
NEXT_DAY(order_date, '금요일') order_date 이후 첫번째 금요일 날짜 반환
LAST_DAY() : 기준 날짜 값이 속한 달의 마지막 날 반환
LAST_DAY(order_date) order_date가 속한 달의 마지막 날 반환

형 변환 함수

TO_CHAR() : 문자가 아닌 값을 문자로 변환, 형태 명시
TO_CHAR(SYSDATE, 'YYYY/MM') 날짜 값을 해당 형태의 문자로 변환
TO_CHAR(salary, 'fm$9999') fm 뒤에 결과 형태 명시,
명시한 자릿수가 출력하려는 데이터의 자릿수보다 커야함

SELECT empno, ename
	, TO_CHAR(hiredate,'"입사년도는 " yyyy "년 입니다."'
	, TO_CHAR(hiredate,'"입사 분기는 " q "분기 입니다."'
	, TO_CHAR(hiredate,'"일주일 중 " d "번째 날에 입사했습니다."'
	, TO_CHAR(hiredate,'"일주일 중 " dy "요일에 입사했습니다."'
FROM emp;

TO_NUMBER() : 숫자가 아닌 값을 숫자로 변환, 형태 명시
TO_NUMBER('$15,000', '$99,999') 앞 인자 값을 뒤 인자 값 형태인 숫자로 변환

TO_DATE() : 날짜가 아닌 값을 날짜로 변환, 형태 명시
DUMP() : 인자로 받은 값의 데이터 타입 확인 DUMP(order_date)

NVL() : NVL(comm, 0) comm 값이 NULL이면 0으로 변환
NVL2() : NVL2(comm, 0, 1) comm 값이 NULL이 아니면 0, NULL이면 1로 변환
NULLIF() : NULLIF(a,b) a=b이면 NULL, 그렇지 않으면 a 반환
COALESCE() : NVL()의 확장판
COALESCE(a, b, c, d) a가 NULL이면 b로, b도 NULL이면 c로, c도 NULL이면 d로 변환

조건문

CASE 문

1) CASE ___ WHEN ___ THEN ... ELSE ... END

SELECT last_name, job_id, salary,
	CASE job_id WHEN 'AD_PRES' THEN salary * 1.1
		    WHEN 'AD_VP' THEN salary * 1.15
		    WHEN 'IT_PROG' THEN salary * 1.2
		    ELSE salary * 1.05 END AS new_salary
FROM employees;

2) CASE WHEN ___ THEN ... ELSE ... END

SELECT last_name, job_id, salary,
	CASE WHEN salary <= 5000 THEN '평균 이하'
	     WHEN salary <= 10000 THEN '평균'
	     WHEN salary <= 20000 THEN '평균 이상'
	     ELSE '최상급' END AS grade
FROM employees;

- `CASE WHEN ___ THEN`문은 = 연산자 외에 다양한 연산자(>,<,IN,BETWEEN AND,...) 사용 가능
- `IS NULL`/`IS NOT NULL`도 이 형태에서만 사용 가능

DECODE 함수 : DECODE(__기준 값__, __비교 값__, __수행문__)

SELECT last_name, job_id, salary,
	DECODE(job_id, 'AD_PRES', salary * 1.1
		     , 'AD_VP'  , salary * 1.15
		     , 'IT_PROG', salary * 1.2
		    		, salary * 1.05)
        AS new_salary
FROM employees;

✅ CASE문으로 만들 수 있는 조건은 DECODE로도 만들 수 있음
✅ 꼭 정해진 것은 없음, 더 편한 것을 사용하면 됨
(다양한 연산자, NULL값 비교가 필요하다면 CASE WHEN___THEN)

[ 복습 문제 풀이 ]

문제 3-3) 이름의 첫글자가 S로 시작하는 사원의 이름을 출력 하는데 LIKE 사용하지 말고 SUBSTR 함수로 수행하시오

SELECT ename
FROM emp
WHERE SUBSTR(ename,1,1) = 'S';

SUBSTR(name,n1,n2) name 값의 n1번째 문자부터 n2개 출력

문제 3-4) 이름의 두번째 철자가 M인 사원들의 이름을 출력하시오 (LIKE 쓰지 말고 SUBSTR 함수로)

SELECT ename
FROM emp
WHERE SUBSTR(ename,2,1) = 'M';

문제 3-8) 아래의 SQL로 출력되는 결과를 INITCAP 사용하지 말고 SUBSTR, UPPER, LOWER, || 를 사용해서 출력되게하시오

SELECT INITCAP(ename)
FROM emp;

INITCAP(ename)
-------------------------------
Smith
Allen
...
14 rows selected.

SELECT UPPER(SUBSTR(ename,1,1))||LOWER(SUBSTR(ename,2))
FROM emp;

UPPER() 인자 값을 대문자로 변환, LOWER() 인자 값을 소문자로 변환

문제 3-9) 이름의 첫번째 철자가 S인 사원들의 이름을 출력 하는데 INSTR을 이용해서 출력하시오

SELECT ename
FROM emp
WHERE INSTR(ename,'S') = 1;

INSTR(name, 'a') name 값 안에 'a' 문자의 위치 반환
없으면 0, 2개 이상이면 첫번째 값의 위치 반환

문제 3-12) 이름, 입사한 날짜부터 오늘까지 총 몇달 근무했는지 출력하시오

SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate)
FROM emp;

MONTHS_BETWEEN(date1, date2) date1과 date2 사이 개월 수 반환

문제 3-13) 오늘부터 100달 뒤에 날짜가 어떻게 되는지 출력하시오

SELECT ADD_MONTHS(SYSDATE, 100)
FROM dual;

ADD_MONTHS(date1, n) date1 날짜로부터 n개월 후 날짜 반환

문제 3-15) 오늘부터 앞으로 돌아올 월요일의 날짜를 출력하시오

SELECT NEXT_DAY(SYSDATE,'월요일')
FROM dual;

NEXT_DAY(date1, day) date1으로부터 가장 가까운 돌아오는 요일 날짜 반환

문제 3-18) 오늘 부터 100달 뒤에 돌아오는 날짜의 요일을 출력하시오

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 100), 'day')
FROM dual;

🔍 TO_CHAR() 함수에서 자주 사용하는 형식

'YYYY/MM/DD', 'YY/MM/DD', RR/MM/DD

'Q' : 분기

'MM' : 월 숫자, 'MONTH' : 월 문자(full), 'MON' : 월 문자(3문자)

'DD' : 일 숫자, 'DAY' : 요일(full), 'DY' : 요일(3문자), 'D' : 주중 몇번째 일인지
[참고] 그 외 더 많은 종류 - 자르르님 블로그

문제 3-24) 81년도에 입사한 사원들의 이름과 입사일을 출력하는데 NLS_DATE_FORMAT과 상관 없이 무조건 결과가 출력되도록 SQL을 작성하시오

SELECT ename, hiredate
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') = '1981';

-- 혹은

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN TO_DATE('1981/01/01','YYYY/MM/DD') AND TO_DATE('1981/12/31','YYYY/MM/DD');

문제 3-26) 이름, 입사한 년도(4자리), 월급, 보너스를 출력하는데 보너스가 입사한 년도가 1981년도면 5000을 출력하고 나머지 사원들은 0을 출력하시오

SELECT ename, NVL(TO_CHAR(comm), 'no comm')
FROM emp;

🔍 NVL() 함수 주의사항
인자 값 안의 NULL 값을 특정 값으로 바꿔주는 NVL 함수에서
NULL이 포함된 기존 attri의 데이터 타입과 새로운 값의 데이터 값이 같아야 함
= 원래 숫자값이 들어있던 attri에는 대체값으로 숫자를 넣어줘야 함
= 다른 데이터 타입을 넣어줘야하는 경우, 위처럼 미리 형 변환 후 NVL() 함수 사용

문제 3-29) 이름, 커미션, 보너스를 출력하는데 보너스가 커미션이 NULL이면 7000 을 출력하고 커미션이 NULL이 아니면 0을 출력하시오

SELECT ename, comm, 
    CASE WHEN comm IS NULL THEN 7000
    ELSE 0 END AS bonus
FROM emp;

=(equal) 연산자 외에 다른 연산자를 사용하거나 IS NULL/IS NOT NULL을 사용해야하는 경우에는 CASE WHEN ___ THEN ___ 문 활용

지수

사부작 사부작

이전 포스트

[SQL] SELECT문 작성

다음 포스트