SQL - DAY 2

BUMSOO·2024년 6월 11일

SELECT 컬럼,컬럼...
FROM 테이블
WHERE 기준컬럼 비교연산자 비교값
AND 기준컬럼 비교연산자 비교값;

WHERE 절

행을제한하는 절

조건절

기준컬럼이 문자열, 날짜열이면 비교값은 작은 따음표로 묶어야 한다.

영문자는 대소문자를 구분한다.

날짜형식은 지역에 따라 기본 날짜 형식이 다르다.
한국(RR/MM/DD), 미국(DD-MON-RR)

비교연산자

=(같다), >(크다), >=(크거나같다), <(작다), <=(작거나같다), !=,^=,<>(같지않다)

논리연산자

AND : 두 조건이 모두 참일 경우 TRUE

OR : 두 조건중에 하나가 참일 경우 TRUE

NOT : FALSE 경우TRUE, TRUE 경우 FALSE

우선순위 : NOT > AND > OR
SELECT *
FROM hr.employees
WHERE hire_date = '04/01/30'; -- 한국지역(RR/MM/DD)

SELECT *
FROM hr.employees
WHERE hire_date = '30-JAN-04'; -- 미국지역(DD-MON-RR)

SELECT *
FROM hr.employees
WHERE department_id = 60;

SELECT *
FROM hr.employees
WHERE department_id != 60;

SELECT *
FROM hr.employees
WHERE department_id = 60
AND salary >= 6000;

BETWEEN AND 절

예시 구문_

SELECT last_name, salary
FROM hr.employees
WHERE salary between 2500 and 3500
ORDER BY 1;

SELECT last_name, salary
FROM hr.employees
WHERE last_name between 'King' and 'Smith'
ORDER BY 1;

SELECT last_name, salary, hire_date
FROM hr.employees
WHERE hire_date BETWEEN '01/01/01' AND '02/12/31'
ORDER BY 3;

IN연산자

예시구문_

SELECT *
FROM hr.employees
WHERE employee_id IN (100,101)

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (100,101)

SELECT *
FROM hr.employees
WEHRE department_id IN (30,50,60)
AND salary >= 5000;

IS NULL, IS NOT NULL

NULL을 체크하는 연산자

SELECT *
FROM hr.employees
WHERE commission_pct IS NULL;

SELECT *
FROM hr.employees
WHERE commission_pct IS NOT NULL;

LIKE 연산자

문자 패턴을 찾는 연산자.

% : 0개 이상의 문자를 찾을 때 사용

_ : 1개 문자를 찾을 때 사용

ESCAPE('\') : LIKE 연산자 사용시에 % 또는 _를 순수한 문자로 인식할 수 있는 방법
SELECT *
FROM hr.employees
WHERE last_name LIKE 'K%'

SELECT *
FROM hr.employees
WHERE last_name LIKE '_i%'

SELECT *
FROM hr.employees
WHERE job_id LIKE 'HR\%%'

[문제6]

employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고
salary 값은 10000이상 받는 사원들의 정보를 출력해주세요.
SELECT *
FROM hr.employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000;

[문제7]

last_name의 세번째 문자가 'a' 또는 'e' 글자가 포함된 사원들의 정보를 출력해주세요.
SELECT *
FROM hr.employees
WHERE last_name LIKE '__a%'
OR last_name LIKE '__e%';

[문제8]

employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고 salary 값은 10000이상 받고 2005년도에 입사한(hire_date)
사원들의 정보를 출력해주세요.
SELECT *
FROM hr.employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000
AND hire_date BETWEEN '05/01/01' AND '05/12/31'
ORDER BY 6;

SORT(정렬)

ORDER BY 절을 이용해서 정렬한다.

ORDER BY 절은 SELECT문의 가장 마지막절에 기술한다.

기본값으로 오름차순(ASC Ascending) 정렬된다.
SELECT employee_id, salary
FROM hr.employees
ORDER BY salary asc;
내림차순(DESC Descending) 정렬
SELECT employee_id, salary
FROM hr.employees
ORDER BY salary desc;
열 별칭 표기법
SELECT employee_id, (salary*12)+(salary*12*coalesce(commission_pct,0)) as "annual_salary"
FROM hr.employees
ORDER BY  "annual_salary" asc;
위치 표기법
SELECT employee_id, (salary*12)+(salary*12*coalesce(commission_pct,0)) as "annual_salary"
FROM hr.employees
ORDER BY  2 asc;

[문제9]

2006년도 입사한 사원의 employee_id, last_name, hire_date를 출력해주세요 단 last_name을 기준으로 오름차순정렬 해주세요.
SELECT employee_id, last_name, hire_date
FROM hr.employees
WHERE hire_date BETWEEN '2006/01/01' AND '2006/12/31'
ORDER BY 2 asc;

[문제10]

80번 department_id 사원중에 commission_pct 값이 0.2 이고 job_id는 SA_MAN인 사원의 employee_id, last_name, salary를 출력해주세요.
단 last_name을 기준으로 오름차순정렬해 주세요.
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE department_id = 80
AND commission_pct = 0.2
AND job_id = 'SA_MAN'
ORDER BY 2 asc;

[문제11]

salary가 5000 ~ 12000의 범위에 속하지 않는 모든 사원의 last_name 및 salary를 출력해주세요. 단 salary을 기준으로 내림차순 정렬하세요
SELECT last_name, salary
FROM hr.employees
WHERE salary < 5000
OR salary > 12000
ORDER BY 2 desc;

함수(function)

기능의 프로그램

단일행 함수

행당 조작하는 함수, 입력값으로 한 행의 필드값이 들어가서 출력값은 하나를 리턴한다.

문자함수, 숫자함수,날짜함수,형변환함수,일반함수

그룹(여러행)함수

행 그룹당 하나의 결과를 산출하도록 행 그룹을 조작하는 함수.

여러행의 값이 들어가서 출력값은 하나를 리턴한다.

문자함수

lower

소문자로 변환하는 함수

SELECT lower(last_name) FROM hr.employees;

upper

대문자로 변환하는 함수

SELECT upper(last_name) FROM hr.employees;

initcap

첫글자 대문자 나머지 글자는 소문자로 변환하는 함수

SELECT initcap(last_name) FROM hr.employees;

WHERE절에 함수를 함부로 사용하면 풀테이블 스캔을 하기 때문에 신중하게 사용해야한다. (조회속도 느려짐)

concat

연결연산자(||)와 동일한 기능의 함수,
인수값이 2개만 들어가야 한다.

SELECT
	last_name || first_name,
    concat(last_name,first_name),
    upper(last_name || first_name || job_id),
    lower(concat(concat(last_name,first_name),job_id)))
FROM hr.employees;

length

문자의 길이를 리턴하는 함수

SELECT last_name, length(last_name) FROM hr.employees;

lengthb

문자의 바이트값을 리턴하는 함수
- 전세계 출판되는 글자는 char, varchar2 타입의 컬럼에 입력할 수 있다.
- 영어는 1글자당 1byte, 한글은 1글자당 3byte
- NLS_CHARACTERSET AL32UTF8
- 한글,한자,영어,일어 글자를 char,varchar2 타입의 컬럼에 입력할 수 있다. 2byte
- NLS_CHARACTERSET KO16MSWIN949

SELECT last_name,lengthb(last_name) FROM hr.employees;

instr

문자의 위치를 리턴하는 함수
(컬럼, 찾는문자열, 시작위치(1), 몇번째로 찾는 위치(1))

SELECT last_name, 
       instr(last_name,'a'),
       instr(last_name,'a',1,1),
       instr(last_name,'a',1,2)
FROM hr.employees
WHERE instr(last_name,'a',1,2)>0;