6월 24일_SQL(WHERE조건절, 연산자, 문자, 숫자, 날짜 함수)

# WHERE 조건절 사용하기

• WHERE 조건은 SELECT문에서 마지막에 쓸 수 있는데 원하는 레코드만 검색하고자 할 때
• 조건절의 구성은 컬럼, 연산자, 비교대상값

연산자 종류

비교연산자	조건을 비교	= < > 등
SQL연산자	조건 비교를 확장	BETWEEN, IN 등
논리연산자	조건 논리를 연결	AND, OR등

 

SHOW USER;
-- 비교연산자 = 사용, employee_id가 100인사람 가져오기
SELECT * 
FROM employees
WHERE employee_id = 100;

SELECT * 
FROM employees
WHERE job_id = 'IT_PROG';

SELECT * 
FROM employees
WHERE first_name = 'David' AND salary > 4800;

SELECT * 
FROM employees
WHERE employee_id >= 105;

-- BETWEEN a AND b = a와 b사이의 값
SELECT * 
FROM employees
WHERE employee_id BETWEEN 100 AND 200;

SELECT * 
FROM employees
WHERE salary BETWEEN 10000 AND 20000;

-- IN (a, b, c) a, b, c 값만 찾기
SELECT * 
FROM employees
WHERE salary IN (10000, 17000, 24000);

SELECT * 
FROM employees
WHERE job_id IN ('AD_VP', 'IT_PROG');

-- LIKE 연산자 ~와 같다, % '모든문자' _ '한글자'
SELECT * 
FROM employees
-- 조건절에서 AD를 포함한 모든 문자를 찾아서 출력
WHERE job_id LIKE 'AD%';

SELECT * 
FROM employees
-- 조건절에서 K를 포함한 모든 문자를 찾아서 출력
WHERE last_name LIKE 'K%';

SELECT * 
FROM employees
-- 조건절에서 AD 뒤에 따라오는 문자가 3자리인 데이터 찾아서 출력
WHERE job_id LIKE 'AD___';

SELECT * 
FROM employees
-- Le% = Le~ , Le로 시작하는 
WHERE first_name LIKE 'Le%';

SELECT * 
FROM employees
-- %ame% = ~ame~ , ame를 포함하는 
WHERE first_name LIKE '%ame%';

SELECT * 
FROM employees
-- %in = ~in , in으로 끝나는 
WHERE first_name LIKE '%in';

SELECT * 
FROM employees
-- 1% = 1~ , 1로 시작하는 
WHERE employee_id LIKE '1%';

SELECT * 
FROM employees
WHERE employee_id LIKE '1%' AND job_id = 'AD_VP' OR job_id = 'ST_MAN';

-- AD_VP 를 제외한 데이터 찾아서 출력하는 방법
SELECT * 
FROM employees
WHERE job_id != 'AD_VP';

SELECT * 
FROM employees
WHERE job_id <> 'AD_VP';

SELECT * 
FROM employees
WHERE NOT (job_id = 'AD_VP');

-- IS NULL 연산자 : 데이터 값이 null인 경우를 조회
SELECT * 
FROM employees
WHERE manager_id IS NULL;

SELECT * 
FROM employees
WHERE commission_pct IS NULL;

SELECT * 
FROM employees
WHERE salary > 4000
AND job_id = 'IT_PROG';

SELECT * 
FROM employees
WHERE salary > 4000
AND job_id = 'IT_PROG'
OR job_id = 'FI_ACCOUNT';

-- AND 조건이 2개 이상일 때 정확한 데이터 추출을 위해 괄호로 묶어줌 
SELECT * 
FROM employees
WHERE salary > 4000
AND (job_id = 'IT_PROG'
OR job_id = 'FI_ACCOUNT')
AND first_name LIKE 'D%';

SELECT *
FROM employees
WHERE employee_id <> 105;

SELECT *
FROM employees
WHERE employee_id IS NOT NULL;

 

---

 

# 함수 사용하기

• x 값을 넣으면 함수를 사용해 y값을 출력
• 오라클 DB 함수를 이용해 문자, 숫자, 날짜 값 등을 조작, 데이터타입끼리 변환할 수도 있음 (단일행 함수 기능)
• 복수행을 조합해 그룹당 하나의 결과로도 출력가능 (그룹 함수 기능)

# 숫자함수

 

ABS

• 절대값을 구하는 함수이다.

FLOOR

• 소수점 아래를 버리는 함수이다.

ROUND

• 특정 자릿수에서 반올림하는 함수이다.

TRUNC

• 특정 자릿수에서 잘라내는 함수이다.
  • 두번째 인자값이 2인 경우 : 소수점 이하 세번째 자리에서 버림 연산을 수행한다.
  • 두번째 인자값이 0인 경우 : 소수점 자리에서 버림 연산을 수행한다.
  • 두번째 인자값이 -1인 경우 : 일의 자리에서 버림 연산을 수행한다.
  • 두번째 인자값이 없는 경우 : 소수점 자리에서 버림 연산을 수행한다.

MOD

• 나누기 연산을 한 후 나머지를 결과로 되돌려주는 함수이다.

---

 

# 문자함수

 

UPPER

• 대문자로 변환하는 함수이다. 

LOWER

• 소문자로 변환하는 함수이다.

INITCAP

• 이니셜만 대문자로 변환하는 함수이다.

LENGTH

• 문자 길이를 구하는 함수이다.

LENGTHB

• 바이트 수를 알려주는 함수이다.

INSTR

• 특정 문자의 위치를 구하는 함수이다.

SUBSTR/SUBSTRB

• SUBSTR : 대상 문자열이나 컬럼의 자료에서 시작 위치부터 선택 개수만큼의 문자를 추출한다. 

LPAD/RPAD

• 특정 기호로 채우는 함수이다.
  • LPAD(LEFT PADDING) : 오른쪽 정렬 후 왼쪽에 생긴 빈 공백에 특정 문자를 채우는 함수이다.
  • RPAD(RIGHT PADDING) : 왼쪽 정렬 후 오른쪽에 생긴 빈 공백에 특정 문자를 채우는 함수이다.

LTRIM/RTRIM

• 공백 문자를 삭제하는 함수이다.
  • LTRIM : 문자열 왼쪽(앞)의 공백 문자들을 삭제한다.
  • RTRIM : 문자열 오른쪽(뒤)의 공백 문자들을 삭제한다.

TRIM

• 특정 문자를 잘라내는 함수이다.

 

---

SHOW USER;

SELECT last_name,
-- 소문자로 변경
lower(last_name),
-- 대문자로 변경
upper(last_name),
email,
-- 첫글자만 대문자로 변경
INITCAP(email)
FROM employees;

-- 특정 자리 문자열 추출 (가져오려는 대상, 자리수, 가져오려는 갯수)
SELECT job_id, SUBSTR(job_id, 1, 2) AS 적용결과 
FROM employees;

-- DUAL : 단일 행 테이블, DUMMY.
-- 하나의 열과 행이 X라는 값을 가짐, 특정 테이블을 참조 안함
SELECT SUBSTR('999010-1231245', 1, 2) AS 연도
FROM DUAL;

SELECT SUBSTR('999010-1231245', 8, 2) AS 연도
FROM DUAL;

-- 특정 문자 찾아 바꾸기 : REPLACE
SELECT JOB_ID,
REPLACE(JOB_ID, 'ACCOUNT', 'ACCNT') AS 적용결과
FROM EMPLOYEES;

SELECT PHONE_NUMBER,
REPLACE (PHONE_NUMBER, '011', '+82-011')
FROM EMPLOYEES;

-- 특정 문자로 자릿수 채우기 : LPAD(왼쪽부터) , RPAD(오른쪽부터) 
SELECT first_name,
LPAD (first_name, '12', '*') AS LPAD적용결과
FROM EMPLOYEES;

SELECT first_name,
RPAD (first_name, '12', '-') AS RPAD적용결과
FROM EMPLOYEES;

-- 특정 문자 삭제하기 : LTRIM(왼쪽부터) , RTRIM(오른쪽부터) *첫글자만 가능
SELECT JOB_ID,
LTRIM (JOB_ID, 'F') AS LTRIM적용결과,
RTRIM (JOB_ID, 'T') AS RTRIM적용결과
FROM EMPLOYEES;

SELECT JOB_ID,
LTRIM (JOB_ID, 'A') AS LTRIM적용결과
FROM EMPLOYEES;

-- 문자공백 제거 : TRIM (문자열 바로 앞 뒤 공백만 제거)
SELECT TRIM(' 홍 길동 안녕하세요 ') AS 공백제거
FROM DUAL;

-- 문자열 중간에 포함된 공백 제거시 REPLACE 사용
SELECT REPLACE(' 홍 길 동 안 녕 하 세 요 ',' ', '') AS 공백제거
FROM DUAL;

-- 특정 자릿수에서 반올림 : ROUND
SELECT SALARY,
SALARY/30 AS 일급,
ROUND(SALARY/30, 0) AS 적용결과0,
ROUND(SALARY/30, 1) AS 적용결과1,
ROUND(SALARY/30, -1) AS 적용결과MINUS1
FROM EMPLOYEES;

-- 숫자 절삭(자르기) : TRUNC
SELECT SALARY,
SALARY/30 AS 일급,
TRUNC(SALARY/30, 0) AS 적용결과0,
TRUNC(SALARY/30, 1) AS 적용결과1,
TRUNC(SALARY/30, -1) AS 적용결과MINUS1
FROM EMPLOYEES;

-- 날짜 타입 함수
-- 현재 날짜를 반환 : SYSDATE
SELECT TO_CHAR (SYSDATE, 'YY/MM/DD/HH24:MI') AS 오늘날짜,
SYSDATE + 1 AS 더하기1,
SYSDATE - 1 AS 빼기1,
TO_DATE ('20240624') - TO_DATE('20240623')  AS 날짜빼기,
SYSDATE + 13/24 시간더하기
FROM DUAL;

-- 두 날짜 사이의 개월수 계산 : MONTHS_BETWEEN
SELECT SYSDATE, HIRE_DATE,MONTHS_BETWEEN(SYSDATE, HIRE_DATE) AS 적용결과
FROM employees
WHERE department_id = 100;

SELECT HIRE_DATE,
ADD_MONTHS(HIRE_DATE, 3) AS 더하기_적용결과,
ADD_MONTHS(HIRE_DATE, -3) AS 빼기_적용결과
FROM employees
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

-- 돌아오는 요일의 날짜 계산 : NEXT_DAY
-- 일 월 화 수 목 금 토 /1 2 3 4 5 6 7
SELECT HIRE_DATE,
NEXT_DAY(HIRE_DATE, '금요일') AS 적용결과_문자지정,
NEXT_DAY(HIRE_DATE, 3) AS 적용결과_숫자지정
FROM employees
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

SELECT SYSDATE,
NEXT_DAY(SYSDATE, '일요일') AS 적용결과_문자지정,
NEXT_DAY(SYSDATE, 1) AS 적용결과_숫자지정
FROM employees
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

-- 돌아오는 월의 마지막 날짜 : LAST_DAY
SELECT HIRE_DATE,
LAST_DAY(HIRE_DATE) AS 적용결과
FROM employees
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

SELECT HIRE_DATE,
ROUND(HIRE_DATE, 'MONTH') AS 적용결과_ROUND_M,
ROUND(HIRE_DATE, 'YEAR') AS 적용결과_ROUND_Y,
TRUNC(HIRE_DATE, 'MONTH') AS 적용결과_TRUNC_M,
TRUNC(HIRE_DATE, 'YEAR') AS 적용결과_TRUNC_Y
FROM employees
WHERE EMPLOYEE_ID BETWEEN 100 AND 106;

-- 데이터 형변환
SELECT 1 +'2'
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YY'),
TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'MM'),
TO_CHAR(SYSDATE, 'MON'),
TO_CHAR(SYSDATE, 'DY'),
TO_CHAR(SYSDATE, 'YYYYMMDD') AS 응용적용1,
TO_CHAR(TO_DATE('20171008'), 'YYYYMMDD') AS 응용적용2
FROM DUAL;

SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') AS 응용적용
FROM EMPLOYEES;

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS PM') AS 시간형식,
TO_CHAR(SYSDATE, 'HH12:MI:SS PM') AS 시간형식12,
TO_CHAR(SYSDATE, 'HH24:MI:SS PM') AS 시간형식24,
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS PM') AS 날짜와시간조합
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'HH-MI-SS PM') AS 시간형식,
TO_CHAR(SYSDATE, '"날짜:" YYYY/MM/DD "시각:" HH12:MI:SS PM "입니다"') AS 날짜와시각표현12,
TO_CHAR(SYSDATE, '"날짜:" YYYY/MM/DD "시각:" HH24:MI:SS PM "입니다"') AS 날짜와시각표현24
FROM DUAL;

 

---

 

# 오라클 문제풀이1

SHOW USER;

-- 1번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES;

---- 1번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER 
--FROM employees;

-- 2번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE JOB_ID IN 'SH_CLERK';

---- 2번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER 
--FROM employees 
--WHERE JOB_ID = 'SH_CLERK';

-- 3번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
ORDER BY SALARY DESC;

---- 3번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 
--FROM employees 
--WHERE DEPARTMENT_ID = 50 
--ORDER BY SALARY DESC;

-- 4번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (80,90);

---- 4번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 
--FROM employees 
--WHERE DEPARTMENT_ID IN (80, 90);

-- 5번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

---- 5번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, COMMISSION_PCT 
--FROM employees 
--WHERE COMMISSION_PCT IS NOT NULL;

-- 6번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 10000 
AND DEPARTMENT_ID = 80;

---- 6번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 
--FROM employees 
--WHERE DEPARTMENT_ID = 80 AND SALARY >= 10000;

-- 7번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > '2007-02-07';

---- 7번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE 
--FROM employees 
--WHERE HIRE_DATE > '2007-02-07';

-- 8번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID IN ('MK_REP','PU_CLERK');

---- 8번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID 
--FROM employees 
--WHERE JOB_ID IN ('SA_REP', 'ST_CLERK');

-- 9번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE SALARY BETWEEN 5000 AND 10000;

---- 9번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT 
--FROM employees 
--WHERE SALARY BETWEEN 5000 AND 10000;

-- 10번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20, 50, 80)
ORDER BY FIRST_NAME ASC;

---- 10번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID 
--FROM employees 
--WHERE DEPARTMENT_ID IN (20, 50, 80) 
--ORDER BY FIRST_NAME ASC;

-- 11번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID != 'SA_REP';

---- 11번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID 
--FROM employees 
--WHERE JOB_ID <> 'SA_REP';

-- 12번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID 
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

---- 12번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID 
--FROM employees 
--WHERE MANAGER_ID IS NULL;

-- 13번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY >= 10000
AND DEPARTMENT_ID = 80;

---- 13번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID 
--FROM employees 
--WHERE SALARY >= 10000
--AND DEPARTMENT_ID = 80;

-- 14번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE (JOB_ID = 'SA_REP'
OR JOB_ID = 'ST_CLERK')
AND SALARY >= 10000;

---- 14번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY 
--FROM employees 
--WHERE (JOB_ID = 'SA_REP' OR JOB_ID = 'ST_CLERK') AND SALARY >= 10000;

-- 15번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%a%';

---- 15번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID 
--FROM employees 
--WHERE FIRST_NAME LIKE '%a%';

-- 16번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%e%e%';

---- 16번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY 
--FROM employees 
--WHERE FIRST_NAME LIKE '%e%e%';

-- 17번
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE JOB_ID LIKE '%CLERK%';

---- 17번 정답
--SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID 
--FROM employees 
--WHERE JOB_ID LIKE '%CLERK%';

 

# 오라클 문제풀이2

SHOW USER;

-- 1번 EMPLOYEE FIRST_NAME 열의 모든 문자를 대문자로 변환하는 쿼리를 작성하시오.
SELECT FIRST_NAME,
    UPPER(first_name)
    FROM employees;

-- 2번 FIRST_NAME과 LAST_NAME 열을 합쳐서 FULL_NAME 열로 출력하는 쿼리를 작성하시오.
SELECT FIRST_NAME||' '||LAST_NAME AS FULL_NAME
    FROM employees;

-- 3번 DEPARTMENT_NAME 열의 문자열 길이를 구하는 쿼리를 작성하시오.
SELECT DEPARTMENT_NAME,
    LENGTH (DEPARTMENT_NAME)
    FROM DEPARTMENTS;

-- 4번 JOB_TITLE 열에서 'Manager'라는 단어가 포함된 행을 찾는 쿼리를 작성하시오.
SELECT JOB_TITLE
    FROM JOBS
    WHERE JOB_TITLE LIKE '%Manager%';

-- 5번 PHONE_NUMBER 열의 값에서 마지막 4자리 숫자만 추출하는 쿼리를 작성하시오.
SELECT PHONE_NUMBER,
    SUBSTR (PHONE_NUMBER, -4)
    FROM employees;

-- 6번 EMAIL 열의 값에서 첫 번째 문자만 대문자로 변환하는 쿼리를 작성하시오.
SELECT EMAIL,
    INITCAP(EMAIL)
    FROM employees;

-- 7번 HIRE_DATE 열의 값에서 년, 월, 일을 각각 추출하는 쿼리를 작성하시오.
SELECT HIRE_DATE, 
    TO_CHAR(HIRE_DATE, 'YYYY') AS 년,
    TO_CHAR(HIRE_DATE, 'MM') AS 월,
    TO_CHAR(HIRE_DATE, 'DD') AS 일
    FROM EMPLOYEES;