[210521금] 오라클 SQL 복습 겸 연습문제, 오라클 함수(문자열, 날짜)

[SQL 연습문제] 오후 보충시간에 풀이 예정 

1. HR계정의 Lock을 풀어주는 SQL명령문 
: alter user hr identified by hr account unlock; 

 

2. 사원(employees)테이블의 모든 정보를 출력하는 SQL문을 작성하시오.
: select * from employees; 

3. 사원(employees)테이블에서 임직원의 이름(first_name)과 급여(salary)를 출력하는 SQL문을 작성하시오.
: select first_name, salary from employees; 

4. 커미션(commission_pct)이 Null이 아닌 임직원의 모든 정보를 출력하는 SQL문을 작성하시오.:

select * from employees
where commission_pct is not null;

5. 급여(salary)가 8,000보다 크고 10,000보다 작은 임직원의 사원번호(employee_id), 이름(first_name), 급여(salary)를 출력하는 SQL문을 작성하시오. :

select employee_id, first_name, salary from employees
where salary between 8000 and 10000;

6. 직업(job_id)이 ‘FI_MGR’, ‘FI_ACCOUNT’, ‘SA_REP’중 하나라도 만족하는 임직원의 사원번호(employee_id), 이름(first_name), 직업(job_id)을 출력하는 SQL문을 작성하시오. :
select employee_id, first_name, job_id from employees
where job_id like 'FI%' or job_id like 'SA%';

7. 2007년도 입사한 임직원의 사원번호(employee_id), 이름(first_name), 입사일(hire_date), 부서번호(department_id)를 출력하는 SQL문을 작성하시오. :

select employee_id, first_name, hire_date, department_id from employees
where hire_date like '07%';

8. 이름(first_name)의 첫 글자가 S로 시작하고 부서번호(department_id)가 30번인 사원의 사원번호(employee_id), 이름(first_name), 부서번호(department_id)를 출력하는 SQL문을 작성하시오. :
select employee_id, first_name, department_id from employees
where first_name like 'S%' and department_id = 30; 

 

 

[1교시] p131

[함수 Function]

: 기본적인 쿼리문을 강력하게 사용하고 두 가지의 종류가 있다. 

 

단일함수

: 각각의 행을 실행하는 함수, 각 row당 하나의 결과 리턴

: select, where(조건절에서도 함수 사용 가능), order by 

: 문자열, 숫자, 날짜, 변환함수  

 

그룹함수(집계함수)

: 전체 row를 그룹화할 수 있는 함수, 집계를 할 수 있는 함수 

: count, sum, avg... 

 

 

 [문자열 함수] 

- 잘 사용하지 않기때문에 알고만 있기

- 주로 대소문자 변환, 문자열 결합 시, 문자열 특정 위치에 어떤 값이 있는지 

 

▶ uppper,  lower : 대소문자 변환

: lower, uppper 함수 사용시 컬럼명을 ()한다는 것 잊지말기!

ex) lower(컬럼명), upper(컬럼명)

데이터의 문자열은 대소문자를 가리기 때문에 조건절에도 uppper,  lower를 사용할 수 있다. 

uppper,  lower를 써주지 않으면 위와 같이 아무값도 출력되지 않지만, 성을 대문자로 변경해주면 해당하는 값이 나온다. 

아래 사진 수정 필요! 

 

 

특정 문자열을 찾을 경우,

[이름 중간에 S가 들어가는 사람을 조회하고 싶을 경우]

select절에는 조회하고 싶은 컬럼명, from에는 해당 테이블명, where절에는 찾고자 하는 데이터에 조건을 준다. 조건절에서 이름을 대문자로 바꾸어 이름 안에 S가 들어가는 데이터를 찾는 것이다. 

 

 

[length 함수: 문자열의 길이를 구하는 함수] p132 

 

[SUBSTR 함수 : 문자열의 일부를 추출하는 함수] p134

SUBSTR((문자열)컬럼명, 시작index, 떼오고 싶은 문자열의 개수)

SQL에서는 index가 0이 아닌 1부터 시작한다. 

(0 ,2)와 (1, 2)의 출력값이 같게 나온다. 0은 1이라고 볼 수 있다. 

★자바와 다르게 인덱스가 1부터 시작하기 때문에 0을 입력하면 1로 인식해 1부터 출력한다.

 

3번째 인자를 적어주지 않으면 시작위치부터 마지막까지의 데이터를 불러온다. 

 

 

[2교시]

[INSTR 함수 : 문자열 데이터 안에서 특정 문자 위치를 찾는 함수] P137 

JAVA의 charAt()라고 할 수 있다. 거의 사용하지 않는다. 

 

[REPLACE 함수 : 특정 문자를 다른 문자로 바꾸는 함수] P139

REPLACE(문자열, 찾는 문자열, 바꿀 문자열): DUAL(DUMP TABLE명)

바꿀 문자열을 의미하는 세번째 전달인자를 주지 않으면(생략하면) 찾은 문자열을 삭제해버린다. 

 

[숫자함수: ROUND(), CEIL(), FLOOR()]

[ROUND : 특정 위치에서 반올림하는 함수] (실수)

ROUND(숫자, 출력할 자리수)

소수점 자리는 0번이다. 소수점 아래자리를 의미하는 오른쪽, 소수점 위를 의미하는 -를 착각하기 쉽다.  

소수점을 기준으로 반올림

소수점 이하 N자리까지 살리는 것

1이 의미하는 것은 소수점 아래자리 5를 살리고 6을 반올림해 1234.6이라는 데이터가 나온다. 

전달인자를 음수로도 쓸 수 있을까? 음수가 뜻하는 것은 자연수의 자리수이다.

즉 -1은 자연수 첫째자리 4를 반올림 하는 것이다. 

 

 

ROUND의 경우는 소수점이 없다고 가정하에 음수도 양수처리해 계산한 후 -를 붙이지만, CEIL과 FLOOR은 오른쪽으로 진행하며 만나는 첫번째 정수값을 데이터로 가져온다. 

 

 

P148

CEIL,  FLOOR 함수: 지정한 숫자와 가장 가까운 정수를 찾는 함수

 

[CEIL 함수] : 입력된 숫자와 가장 큰 정수를 반환하는 함수 

[FLOOR 함수] :입력된 숫자와 가장 작은 정수를 반환하는 함수

 

 

[날짜함수]

: 날짜 데이터는 연산을 할 수 있다. 

 

컴퓨터 시스템의 날짜 데이터(sysdate)를 이용해 오늘, 어제, 내일 날짜 나타내기 

 

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜 데이터 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터 - 날짜 데이터	두 날짜 간의 일수 차이
날짜 데이터 + 날짜 데이터	연산 불가, 지원하지 않음

로 날짜데이터끼리는 빼기 연산이 가능하다. 하지만 오늘 날짜를 의미하는 날짜 데이터 sysdate를 이용해 생년월일을 빼 연산을 하고 싶을 때 '93/11/06'을 문자열로 인식해 오류가 발생한다. 

 

 

▷ (날짜 데이터 - 날짜 데이터)의 경우에는 앞, 뒤 모두 날짜타입이 와야한다. 

'93/11/06'는 문자열로 인식되어 오류가 나지만 sysdate, HIRE_DATE는 날짜 타입이기 때문에 계산 가능하다.

ROUND를 이용해 소수점 이하 없애주세요. 

 

 

 

[ADD_MONTHS 함수 : N개월 이후 날짜를 구하는 함수]

ADD_MONTH(기준이 되는 날짜타입, n) 

[입사하고 10년이 된 해를 찾아보기]

N개월 이후 날짜를 구하는 함수 ADD_MONTH를 이용해 구하기 위해서는 10년 × 12개월 = 총 120개월, 입사일을 뜻하는 문자 타입 HIRE_DATE에 ADD_MONTH을 이용해 120개월 후의 데이터를 구하면 된다.  

 

 

 

[입사한지 15년이 되는 사원 조회]

[MONTHS_BETWEEN]

:  두 날짜 간의 개월 수 차이를 구하는 함수

: 1년 12개월 

: MONTHS_BETWEEN 함수는 앞에 있는 숫자를 뒤에서 빼기 때문에 음수가 나온다.

: MONTHS_BETWEEN(미래, 과거) 

 

SYSDATE(미래), HIRE_DATE(과거)의 위치를 바꿔주고 근무일수를 구해보기

 

MONTHS_BETWEEN 함수를 이용해 SYSDATE, HIRE_DATE 현재 날짜에서 입사일 날짜를 빼주어 근무개월수를 구하고, CEIL함수를 사용해 소수점 이하 반올림해준다. 여기에서는 MONTHS_BETWEEN과 CEIL 함수 2개를 사용한 것이다. 

 

+ 조건절에 추가로 넣기

SQL에서는 별칭으로 준 컬럼의 데이터를 사용할 수 없기때문에 근무개월수를 조건절에 사용할 수 없다. 

WHERE CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 120; 

 

 

조회결과 너무 많아서 15년 이상 근무한 사원으로 다시 조회하기

[15년 이상(180개월) 근무한 사람을 내림차순한 명단]

함수 그대로 where절(조건절)에서 사용해도 된다.

정렬 시에도 함수의 결과 값을 기준으로 정렬할 수 있다.

 

※ 자바에서는 날짜 타입을 쓰기 까다로워 오라클의 데이터를 문자열로 변환해서 자바로 보내면 String, 문자열로 변환해서 사용하는 편이다. 일하는 현장 마다 다르다. 

 

 

 

P155

[오라클에서 날짜 데이터를 사용할 때 기준 포맷값] 

YYYY : 연도를 숫자 4자리로 표현

MM : 달을 숫자 2자리로 표현 

DD : 일을 숫자 2자리로 표현

HH : 시간의 약자 

MI: minutes의 약자

SS: 초 단위의 약자 

 

 

P158

[TO_date 함수: 문자열 데이터('93/11/06')를 날짜, 숫자 데이터로 변환하는 함수]

1993년 11월 06일생이 오늘 날짜를 기준으로 살아온 시간 

 

to_date(날짜데이터로 바꾸고 싶은 문자열)

 

※ 자바에서는 날짜 타입을 쓰기 까다로워 오라클의 데이터를 문자열로 변환해서 자바로 보내면 String, 문자열로 변환해서 사용하는 편이다. 일하는 현장 마다 다르다. 

 

[TO_CHAR 함수: 날짜, 숫자 데이터를 문자열 데이터로 변환하는 함수]

to_char(날짜, 숫자데이터, 날짜데이터 기본 포멧값) 

 

systemtimestamp가 아니라, systimestamp이다. 

★[EMPLOYEES 테이블에서 이름, 입사일(YYYY-MM-DD) 조건 2005년 이후에 입사한 사람들만 출력]

→ 입사일(YYYY-MM-DD) 조건을 어디에 줘야할 지 고민이 되는 부분 

→ 화면에 무엇을 나타낼 지 고민해보면 될 것 같다. 

 

select first_name, to_char(hire_date, 'YYYY-MM-DD') as 입사일 

from employees

where hire_date >= '05/01/01'; 

 

내가 풀었을 때는 select절에 hire_date를 넣지 않고 sysdate를 넣음

where절에 2005년 이후에 입사한 사람들을 어떻게 표현할지 생각해보기! 

 

 

p170 

기타함수

[DECODE] 또한 함수이다. 

: DECODE는 자바의 IF문과 비슷하다. 

: DECODE(해당 컬럼명, 컬럼의 특성, 바꿀 정보)

 

DECODE를 사용하여 부서10 10%, 부서 20 5%, 부서 30 3%의 월급을 올려줄 것이다. 

DECODE(DEPARTMENT_ID, 10, SALARY * 1.1) => DEPARTMENT_ID가 10이면, SALARY * 1.1를 하겠다.

DEPARTMENT_ID는 조건에 해당하는 변수값이므로 한번만 쓴다. 

나머지 애들은 기타 등등 

 

 DECODE를 사용하기 전에 중복을 제거하는 DISTINCT를 이용해 중복을 제거한다. 

 

 

[보충시간]

[Oracle 함수 연습문제]

- 문자열 결합 부분이 익숙하지 않은 것 같다. || 

 

1. 모든 사원의 이름과 성, 전화번호 첫 3자리를 출력하세요. :

select first_name, last_name, substr(phone_number, 1, 3) phone_number
from employees; 

 

 

2. 모든 사원의 이름과 성, 그리고 (이름과 성을 합한 글자수)를 출력하시오. (Length 함수 이용) :

select first_name, last_name, length(first_name || last_name) as 글자수
from employees;

 

 

3. 모든 사원의 이름과 성의 머리글자만 출력하세요. (A.A) :

select substr(first_name, 1, 1) ||','|| substr(last_name, 1, 1)
from  employees;

 

 

4. JOB_ID의 첫 번째와 두 번째 글자가 'SA'인 사람들의 모든 정보를 출력하시오. (SUBSTR 함수 이용)

select * from employees
where substr(job_id, 1, 2)= 'SA';

 

like를 이용: 

select * from employees

where job_id like 'SA%'; 

 

 

5. 모든 사원의 이름과 성을 대문자로 출력하시오. :

select upper(first_name)first_name, upper(last_name)last_name 
from  employees;

 

 

6. 모든 사원의 전화번호의 '.' 기호를 '-'로 치환하여 이름과 함께 출력하시오.

SELECT FIRST_NAME, LAST_NAME, REPLACE(PHONE_NUMBER, '.', '-') 
FROM EMPLOYEES;

 

 

7. 사원 급여를 30으로 나눈 값을 소수점 둘째 자리에서 반올림하여 출력하시오.

 

select round(salary/30, 2) 
from employees; 

 

 

8. 모든 사원의 입사일과, 입사일 이후 100일째 되는 날을 다음과 같은 형식으로 출력하시오. (사원번호, 입사일, 백일 후): 

날짜 데이터 - 날짜 데이터: 두 날짜 간의 일수 차이 

날짜 데이터 + 날짜 데이터: 연산 불가, 지원하지 않음

 

입사일 이후 100일째 되는 날

TO_CHAR(HIRE_DATE+100, 'YYYYMMDD') AS 백일후 

입사일을 의미하는 데이터 HIRE_DATE에 100을 더하고, 날짜, 숫자데이터를 문자열 데이터로 변환하는 함수 TO_CHAR를 이용해 백일후의 값을 구한다. TO_CHAR와 날짜 데이터를 사용할 때 기준 포맷값은 자주 쓰일 일이 많다. 

SELECT 
    EMPLOYEE_ID AS 사원번호,
    HIRE_DATE AS 입사일, 
    TO_CHAR(HIRE_DATE+100, 'YYYYMMDD') AS 백일후
FROM EMPLOYEES;

 

 

9. 입사일 이후 오늘까지의 날짜수를 다음과 같이 출력하시오.(사원번호, 입사일, 근무일수/ 최근 입사한 사원순으로 정렬):

TRUNC 함수

:  TRUNCATE(숫자데이터, n)

: 지정된 자리에서 다 버리는 함수 

: +n은 음수, -n은 양수를 의미한다. 

SELECT EMPLOYEE_ID 사원번호, HIRE_DATE 입사일, TRUNC(SYSDATE - HIRE_DATE) 근무일수 
FROM EMPLOYEES 
ORDER BY HIRE_DATE DESC;

 

 

10. 입사일 이후 오늘까지의 개월 수를 다음과 같이 출력하세요. (사원번호, 입사일, 근무개월수) :

TRUNC 함수

 

MONTHS_BETWEEN 함수 

: MONTHS_BETWEEN(미래, 과거) 

 

SELECT 
    EMPLOYEE_ID 사원번호, HIRE_DATE 입사일, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) 근무개월수 
FROM EMPLOYEES;

 

 

11. 입사일을 다음과 같은 형식으로 출력하시오(YYYY년 MM월 DD일 N요일):

SELECT TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" DAY') HIRE_DATE 

FROM EMPLOYEES; 

 

 

12. 입사일이 3월인 모든 사원의 정보를 출력하시오. 

① '-'와 '%'를 이용해 출력한 것. 

 

② substr을 이용해 출력한 것 

이것도 어떤 데이터가 나오는지 오라클에 집어넣어보기, 해봤는데 안나옴

select * from employees

where substr(hire_date, 3, 2) = '03'; 

 

다시 해보니까 나온다. /도 포함되기 때문에 select * from employees where substr(hire_date, 4, 2) = '03';

 

③to_char(날짜, 숫자데이터, 날짜데이터 기본 포멧값)를 이용해 출력한 것

날짜타입인 hire_date를 to_char함수를 이용해 문자열러 변경한다. 

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MM') = '03';

 

 

13. 현재 시간을 다음과 같은 형식으로 출력하시오. (2015-06-06-11 01:28:16) 

SELECT 
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') 
FROM DUAL;

 

 

14. 사원이름과 부서명을 출력하되, 부서코드가 40이면 '인사', 60이면 '전산', 80이면 '영업' 그외는 '기타'라고 한다. (이름, 성, 부서)

SELECT 
    FIRST_NAME, LAST_NAME, 
    DECODE(DEPARTMENT_ID, 40, '인사', 60, '전산', 80, '영업', '기타') 부서 
FROM EMPLOYEES;