251020 TIL

문제

-- [문제9] 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 TO_DATE('20050101', 'yyyymmdd') AND TO_DATE('20060101', 'yyyymmdd') - 1/24/60/60;

-- [문제10] employees테이블에서 job_id 가 SA_REP 또는 AD_PRES 사원들 중에 salary값이 10000 초과한 사원들의 정보를 출력해주세요.
SELECT *
FROM hr.employees
WHERE job_id in ('SA_REP', 'AD_PRES')
AND salary > 10000;

SORT(정렬)

• ORDER BY 절을 이용해서 정렬
• 기본값은 오름차순으로 정렬. ASC(ASCending)
• 내림차순 DESC(DESCending)
• ORDER BY 절은 SELECT문의 가장 마지막 절에 기술해야 한다.

-- 오름차순 정렬
SELECT employee_id, last_name, salary
FROM hr.employees
ORDER BY salary;

SELECT employee_id, last_name, salary
FROM hr.employees
ORDER BY salary ASC;

-- 내림차순 정렬
SELECT employee_id, last_name, salary
FROM hr.employees
ORDER BY salary DESC;

-- 표현식 사용 가능
SELECT employee_id, last_name, salary, salary * 12
FROM hr.employees
ORDER BY salary * 12;

-- 열 별칭 사용 가능
SELECT employee_id, last_name, salary, salary * 12 annual_salary
FROM hr.employees
ORDER BY annual_salary;

-- 열 별칭 사용시 주의점 : 큰 따옴표 사용한 경우 동일하게 큰 따옴표 사용해야 함
SELECT employee_id, last_name, salary, salary * 12 "annual_salary"
FROM hr.employees
ORDER BY "annual_salary";

-- 위치 표기법 사용
SELECT employee_id, last_name, salary, salary * 12 annual_salary
FROM hr.employees
ORDER BY 4;

문제

-- [문제11] 2006년도 입사한 사원의 employee_id, last_name, hire_date를 출력해주세요 단 last_name 이름을 기준으로 오름차순정렬 해주세요.
SELECT employee_id, last_name, hire_date
FROM hr.employees
WHERE hire_date BETWEEN TO_DATE('20060101', 'yyyymmdd') AND TO_DATE('20070101', 'yyyymmdd') - 1/24/60/60
ORDER BY last_name;

-- [문제12] 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 last_name;

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

함수

• 함수는 데이터 항목을 조작하고 결과를 반환하는 프로그램
• 단일행함수
  • 행당 조작하는 함수
  • 입력 값으로 한 행의 필드 값이 들어가서 출력 값으로는 하나가 리턴된다.
  • 문자함수, 숫자함수, 날짜함수, 형변환함수, 일반함수
• 그룹함수
• 함수는 중첩 가능

문자 함수

대소문자 변환 함수

• upper : 대문자로 변환하는 함수
• lower : 소문자로 변환하는 함수
• initcap : 첫 글자 대문자 나머지 글자는 소문자로 변환하는 함수

SELECT last_name, upper(last_name), lower(last_name), initcap(last_name)
FROM hr.employees;

concat

• 연결 연산자와 동일한 함수
• concat은 인자를 2개까지 밖에 못 받기 때문에 여러 개 연결하려면 중첩해서 사용해야 함
• 여러 개를 연결하려면 || (연결 연산자)가 편리

SELECT last_name, first_name, last_name || ' ' || first_name, concat(concat(last_name,' '), first_name)
FROM hr.employees;

length

• length : 문자의 길이를 리턴하는 함수
• lengthb : 문자의 바이트 값을 리턴하는 함수
  • 한 글자 당 영어는 1byte, 한글은 3byte

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

현재 데이터베이스에 저장된 국가별 언어 지원 정보 뷰 조회

SELECT *
FROM nls_database_parameters;

• NLS_CHARACTERSET
  • AL32UTF8 ⇒ 데이터베이스에 설정된 문자 characterset, 유니코드(unicode)
    • 전세계 출판되는 글자들을 char, varchar2 타입의 컬럼에 입력 가능
    • 1byte ~ 4byte, 영어 1byte, 한글 3byte
  • K016MSWIN949 : 한글, 한자, 영어, 일어 글자들을 char, varchar2 타입의 컬럼에 입력 가능
    • 2byte
• NLS_NCHAR_CHARACTERSET
  • AL16UTF16 : 전세계 출판되는 글자들을 nchar, nvarchar2 타입의 컬럼에 입력 가능
  • 1byte ~ 4 byte, 영어 1byte, 한글 3byte
  • National Characterset

instr

• 문자의 위치를 리턴하는 함수
• 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;

SELECT last_name
FROM hr.employees
WHERE last_name LIKE '%a%a%';

SELECT last_name
FROM hr.employees
WHERE instr(last_name, 'a', 1, 2) > 0; -- 인덱스 사용 가능

substr

• substr: 문자를 추출하는 함수, substr(컬럼(문자열), 시작점, 추출개수)

SELECT last_name, substr(last_name, 1, 1), substr(last_name, 2, 1), substr(last_name, -2, 2)
FROM hr.employees;

• substrb: 문자를 바이트만큼 추출하는 함수

SELECT substr('abcdef', 1, 2), substrb('abcdef', 1, 2), substr('가나다라마바', 1, 2), substrb('가나다라마바', 1, 6)
FROM dual;

trim

• trim : 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
• ltrim : 왼쪽(접두) 부분에 연속되는 문자를 제거하는 함수
• rtrim : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수

SELECT 'aabbcaa', trim('a' from 'aabbcaa'), ltrim('aabbcaa', 'a'), rtrim('aabbcaa', 'a')
FROM dual;

• 문자 지정 안 하면 기본적으로 공백 제거

SELECT '  oracle  ', length('  oracle  '), trim('  oracle  '), length(trim('  oracle  ')), ltrim('  oracle  '), length(ltrim('  oracle  ')), rtrim('  oracle  '), length(rtrim('  oracle  '))
FROM dual;

문제

-- [문제14] employees 테이블에 last_name 컬럼의 값 중에  "J" 또는 "A" 또는 "M"으로 시작하는 
-- 사원들의 last_name, last_name의 길이를 표시하는 query(select문) 를 작성합니다.
-- 사원들의 last_name 기준으로 내림차순 정렬해 주세요.
SELECT last_name, length(last_name)
FROM hr.employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'A%'
OR last_name LIKE 'M%'
ORDER BY last_name desc;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE SUBSTR(last_name, 1, 1) = 'J'
OR SUBSTR(last_name, 1, 1) = 'A'
OR SUBSTR(last_name, 1, 1) = 'M'
ORDER BY last_name desc;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE SUBSTR(last_name, 1, 1) IN ('J', 'A', 'M')
ORDER BY last_name desc;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE INSTR(last_name, 'J', 1, 1) = 1
OR INSTR(last_name, 'A', 1, 1) = 1
OR INSTR(last_name, 'M', 1, 1) = 1
ORDER BY last_name desc;

SELECT last_name, length(last_name)
FROM hr.employees
WHERE REGEXP_LIKE(last_name, '^[AJM]')
ORDER BY last_name desc;

-- [문제15] employees테이블에서 department_id(부서코드)가 50번 사원들 중에 
-- last_name에 두번째 위치에 "a"글자가 있는 사원들을 조회하세요.
SELECT *
FROM hr.employees
WHERE department_id = 50
AND instr(last_name, 'a') = 2;

SELECT *
FROM hr.employees
WHERE department_id = 50
AND substr(last_name, 2, 1) = 'a';

SELECT *
FROM hr.employees
WHERE department_id = 50
AND last_name LIKE '_a%';

replace

• 문자를 다른 문자로 치환하는 함수
• replace(컬럼(문자열), 이전문자, 새로운문자)

SELECT 
    replace('100-001', '-', '%'),
    replace('100-001', '-', ''),
    replace('  100 001  ',' ', '')
FROM dual;

lpad, rpad

• lpad : 문자의 자리를 고정시킨 후 문자 값을 오른쪽 기준으로 채우고, 빈 왼쪽 공백을 다른 값으로 채우는 함수
• rpad : 문자의 자리를 고정시킨 후 문자 값을 왼쪽 기준으로 채우고, 빈 오른쪽 공백을 다른 값으로 채우는 함수

SELECT 
    salary, 
    lpad(salary, 10, '*'),
    rpad(salary, 10, '*')
FROM hr.employees;

문제

-- [문제16] salary에 있는 값을 1000당 * 출력해주세요.
SELECT salary, lpad('*', (salary/1000), '*') STAR
FROM hr.employees;

/*
SALARY    STAR
-------   -------
6000      ****** 
4800      **** 
4800      **** 
...
*/

숫자 함수

round

• 지정된 소수점 자릿수 값을 반올림하는 함수

SELECT 
    45.926,
    ROUND(45.926),
    ROUND(45.926, 0),
    ROUND(45.926, 1),
    ROUND(45.926, 2),
    ROUND(45.926, -1),
    ROUND(45.926, -2),
    ROUND(55.926, -2)
FROM dual;

trunc

• 지정된 소수점 자릿수 값을 truncate(버리는) 함수

SELECT 
    45.926,
    TRUNC(45.926),
    TRUNC(45.926, 0),
    TRUNC(45.926, 1),
    TRUNC(45.926, 2),
    TRUNC(45.926, -1),
    TRUNC(45.926, -2),
    TRUNC(55.926, -2)
FROM dual;

ceil

• 숫자 값을 가장 큰 정수로 반환하는 함수

SELECT 
    round(10.1),
    ceil(10.1),
    ceil(10.00000001)
FROM dual;

floor

• 숫자 값을 가장 작은 정수로 반환하는 함수

SELECT 
    trunc(10.1),
    floor(10.1),
    floor(10.00000001),
    floor(-10.00000001)
FROM dual;

몫, mod(나머지)

SELECT
    10/3,
    TRUNC(10/3),
    MOD(10,3)
FROM dual;

• mod : 어떤 값을 나눈 나머지를 반환하는 함수

power(거듭 제곱)

SELECT
    2 * 2 * 2,
    POWER(2, 3)
FROM dual;

abs(절대값)

SELECT
    abs(-100)
FROM dual;

sqrt(제곱근)

SELECT
    sqrt(9)
FROM dual;

문제

-- [문제17] employees 테이블에 있는 employee_id, last_name, salary, 
-- salary를 10% 인상된 급여를 계산하면서 계산된 급여는 소수점은 반올림해서 정수값으로 표현하고 
-- 열별칭은 New Salary로 표시하세요.
SELECT employee_id, last_name, salary, round(salary * 1.1) "New Salary"
FROM hr.employees;