SELECT문 활용(대체 변수,함수,형변환)

대체변수

대체변수는 사용하고자 하는 값을 입력하고자 할때 쓰는 변수이다

사용 형태는 '&변수이름'형식으로 사용한다. 자세한 사용방법은 EX)로 나타낸다

EX)

SELECT employee_id, last_name, salary,department_id

 

FROM employees

WHERE employee_id=&employee_num

//여기서 &employee_num값이 임의로 입력하고자 하는 대체 변수이다

EX)와 같이 입력을 하고 실행을 시킬경우

'ENTER value for 변수이름: '식의 문장이 생인가

여기서 입력을 하게되면 &로 입력했던 문장의 자리에 입력한 값이 들어가서 실행이 되게 된다.

EX1)

SELECT employee_id, last_name, job_id, &column_name

FROM employees

WHERE &condition

ORDER BY &order_column

// 첫번재 & 값은 출력하고자 하는 컬럼의 값이고

두번쩨는 조건을 입력하는 곳이다

세번째는 정렬을 하고자 하는 기준을 입력하는 곳이다.

단일행함수

단일행 함수란 출력하는 문자에 적용되는 함수이다.

이름	사용형태	사용예)
CONCAT	두가지문자를 한곳에 출력하는 함수	CONCAT('hello','world') = helloworld
SUBSTR	문자에서 정해진 위치의 3가지 문자를 출력하는 삼수	SUBSTR('helloworld',1,5) = hello
LENGTH	문자의 길이를 출력하는 함수	LENGTH('helloworld') = 10 // 문자의 길이 수
INSTR	문자가 있는 위치를 출력하는 함수	INSR('helloworld','w') = 6 // 문자가 있는 위치
LPAD	공간을 정의하고 남은 공간에 임의의 문자를 입력하는 함수(왼쪽에)	LPAD(salary,10,'*') = *****24000
RPAD	공간을 정의하고 남은 공간에 임의의 문자를 입력하는 함수(오른쪽에)	RPAD(salary,10,'*') = 24000*****
REPLACE	임의의 문자를 지정하는 문자로 바꾸는 함수	REPLACE('JACK and JUE','J','BL') = BLACK and BLUE
TRIM	지정한 문자를 문장에서 제외하고자 하는 함수	TRIM('h' FROM 'helloworld' ) = elloworld
UPPER	컬럼이나, 문자를 모두 대문자로 나타내는 함수	UPPER('hello')=HELLO
LOWER	컬럼이나,문자를 모두 소문자로 나타내는 함수	LOWER('HELLO')=hello

Q1)employees테이블에서 last_name이 4글자인 사원의 모든 정보를 출력하시오

SELECT *

FROM employees

WHERE LENGTH(last_name)=4

Q2) employees테이블에서 last_name에 k를 포함한 사원의 풀네임을 출력하시오.(last_name+first_name)

SELECT CONCAT(last_name,first_name)

FROM employees

WHERE INSTR(last_name,'k')>=1

숫자관련 함수

숫자에 관련되어 사용하는 함수

ROUND(45.926,2) = 45,93 // 반올림

TRUNC(45.926,2) = 45.92 // 버림

MOD(1600,300) = 100 // 나머지

숫자	1	2	3	.	4	5	6
반올림/버림 지정 숫자	-3	-2	-1		0	1	2
자리수	100의자리	10의자리	1의자리		소수점1자리	소수점2자리	소수점3자리

날짜관련 함수

함수이름	기능	활용방법
SYSDATE	현재의 날짜를 출력
MONTHES BETWEEN	두가지 날짜의 기간을 출력
NEXT_DAY	다음년도를 출력
LAST_DAY	전년도를 출력
ROUND	날짜를 반올림하여 나타냄 (반올림기준 : 6월이상,15일 이상)	ROUND (SYSDATE,'MONTH') = /현재의 달를 일기준 반올림 (15일전이면 현재의 달,이후는 다음달) ROUND ( SYSDATE,'YEAR') = /현재의 날짜를 달도기준으로 반올림 (6월전이면 현재년도 1월1일, 이후는 내년 1월1일 출력)
TRUNC	날짜를 버림하여 나타냄	위와 비슷하지만 선자를 따름

형변환

데이터 베이스 저장 시 저장하는 데이터의 유형을 조정하는 것

함수이름	기능
to_NUMBER()	문자였던 것을 숫자로 빠꿔준다.
to_VARCHAR2()=to_CHAR()	숫자였던것을 문자로 바꿔준다.
to_DATE()	문자였던 것을 날짜로 변경한다.
NUMBER()	()안에 숫자의 크기를 입력한다(입력함수)
VARCHAR2(20)	20글자 이상은 넘지않게 입력한다.
char(20)	무조건 20글자를 채워서 입력
YYYY	Y의 갯수만큼 년도 표현
YEAR	4자리를 모두 표현
MONTH	날짜 이름 (풀네임)
MON	월을 3자리로 표현
MM	월을 숫자로 표현
DY	3자리 요일
DAY	요일 이름
DD	숫자료 표현

Q1) employees 테이블에서 입사일이 1990-12-31 이전에 입사한 사원의 last_name과 hire_date를 출력

(단, last_name = 대문자 , hire_date = 1990-12-31형식)

SELECT UPPER(last_name), TO_CHAR(hire_date,'YYYY-MM-DD')
FROM employees
WHERE hire_date<TO_DATE('1990-12-31','YYYY-MM-DD')

Q2) employees테이블에서 근무년수가 15년 이상인 사원의 성명(last_name || first_name)을 출력하세요

SELECT CONCAT(last_name,first_name)

FROM employees

WHERE (sysdate-hire_date)/365>=15

Q3) employees테이블에서 사번, 연봉(salary*12)을 천의자리에서 반올림한 값을(&200,000.00)형태로 출력하되 사번순으로 오름차순 하시오

SELECT employee_id

TO_CHAR(ROUND(salary*12,-4),'$99,999,00'

FROM employees

ORDER BY employee_id;

Q4) employees테이블에서 last_name과first_name의 길이가 같은 사원의 department_id,last_name 길이

first_name길이, last_name 길이 + first_name길이를 1의 자리에서 반올림한 값, job_id를 department_id 순으로 내림차순 하여 정렬하여라

SELECT department_id,LENGTH(last_name),LENGTH(first_name),

ROUND(LENGTH(last_name)+LENGTH(first_name),-1

FROM employees

WHERE LENGTH(last_name)=LENGTH(first_name)

ORDER BY departement_id DESC

GENERAL FUNCTION

함수이름	활용형태	기능
NVL	NVL(컬럼값,바꿀값)	지정된 컬럼에서 null값을 지정하는 숫자로 바꾸는 함수 NVL(컬럼,변경하고자하는 숫자)
NVL2	NVL2(컬럼값,값1,값2)	컬럼이 NULL이 아니면 값1로, NULL이면 값2로 바꾸어 활용
NULLIF	NULLIF(값1,값2)	값1과 값2가 같으면 null값을 주고, 다르면 값1을 준다
COALESCE	COLESCE(값1,값2,값3...값N)	값들을 확인하여 NULL값이 안나오면 그값을 출력한다.

Q1)employees 테이블에서 employee_id, 연봉을 출력하시오(연봉 = salary*12+salary*12*commission_pct / 연봉표현방식 $1,500.00)

SELECT employee_id,
TO_CHAR(salary*12+salary*12*NVL(commission_pct,0),'$99,999.00') AS "연봉"
FROM employees

NVL2 활용

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'SAL+COMM','SAL') income
FROM employees
WHERE department_id IN(50,80);

 

해설

employees테이블에서 부서번호가 50,80인 사원의 Last_name,salary,commission_pct와

(commission_pct가 null이아니면 sal+comm을 null이면 sal)을 imcome테이블에 출력하라

NULLIF 활용

SELECT first_name,LENGTH(first_name)"expr1",
last_name, LEGTH(last_name)"expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employee

 

해설 

employees테이블에서 사원의 first_name,first_name의 길이,last_name,last_name의 길이와

last_name의 길이와first_name의 길이가 같으면 null을 다르면 first_name을 출력하라