[새싹] 현대IT&E 231027 기록 - Oracle 02~04

최정윤·2023년 10월 27일

oracle sql

새싹

목록 보기

9/67

오류 발생

https://skylarcoding.tistory.com/112#google_vignette

02. SQL의 기본

2.2 특정 조건의 데이터만 조회하는 WHERE절

2.2.5 like 연산자와 와일드카드

2.2.5.1 와일드카드(%) 사용하기

이씨 성을 가진 사원을 찾기에 실패한 예제

select *
from emp
where ename = '이';

이씨 성을 가진 사원 찾기

select *
from emp
where ename like '%성%';

2.2.5.2 와일드카드(_) 사용하기

이름이 두 번째 글자가 "성"이고, 그 뒤는 무엇이 오든 관계없는 사원 출력하기

SELECT *
FROM emp
WHERE ename LIKE '_성%';

2.2.5.3 NOT LIKE 연산자

이름에 "성"을 포함하지 않은 사원 출력하기

SELECT *
FROM emp
WHERE ename NOT LIKE '%성%';

2.3 정렬을 위한 ORDER BY절

2.3.1 오름차순 정렬을 위한 ASC

급여를 적게 받는 사원부터 많이 받는 사원 순으로 출력하기

select *
from emp
order by sal asc;

asc를 생략한 채 order by 사용하기

select *
from emp
order by sal;

2.3.2 내림차순 정려을 위한 desc

급여를 많이 받는 사원부터 적게 받는 사원 순으로 순차적으로 출력하기

select * from emp
order by sal desc;

사원의 이름을 기준으로, 사전에 먼저 실린 순서대로 출력하기

select *
from emp
order by ename;

가장 최근에 입사한 사람부터 출력하기

select *
from emp
order by hiredate desc;

2.3.3 정렬 방식에 여러 가지 조건을 제시하기

selct *
from emp
order by hiredate desc, ename asc;

연습문제

ace/ace에서
1.
salary가 10000달러 이상이며 job_id에 문자열 'MAN'이 포함된 사원의 last_name, salary, job_id 출력

salary가 10000달러 이상이거나 job_id에 문자열 'MAN'이 포함된 사원의 lst_name, salary, job_id 출력
job_id가 IT_PROG, ST_CLERK, SA_REP에 속하지 않은 사원의 last_name, job_id 출력
commission_pct가 null이 아닌 사원들의 lastname, commission_pct 출력
job_id가 ST_CLERK 또는 SA_REP이면서 급여가 2500, 3500, 7000이 아닌 모든 사원의 last_name, job_id, salary 출력

2.4 집합 연산자

그룹 활동과 싱글 활동을 하는 가수 이름 출력하기

select *
from group_star
union
select *
from single_star;

그룹 활동과 싱글 활동을 하는 가수 이름을 중복하여 출력하기

select *
from group_star
union all
select *
from single_star

연습문제

1. set operator를 이용하여 job ID가 ST_CLERK을 포함하지 않는 부서의 ID를 출력하세요.

DEPARTMENT_ID
10
20
60
80
90
110
190
7 rows selected.
풀이코드

select department_id
from employees
where job_id not like '%st_clerk%';

정답코드

select department_id from departments
minus
select distinct department_id from employees
where job_id = '%st_clerk%';

2. set operator를 이용하여 부서가 없는 지역의 Country ID와 이름을 출력하세요.

@결과
COUNTR COUNTRY_NAME
DE Germany

3. 부서가 10, 50 그리고 20의 순서로 부서의 업무리스트를 정렬하고 set operators를 이용하여 job ID와 department ID를 출력하세요.

JOB_ID DEPARTMENT_ID A_DUMMY
AD_ASST 10 1
ST_CLERK 50 2
ST_MAN 50 2
MK_MAN 20 3
MK_REP 20 3
풀이코드

select job_id, department_id
from job_history
where department_id = 10 or department_id = 50 or department_id = 20
order by (
          CASE department_id
          WHEN 10 THEN 1
          WHEN 50 THEN 2
          WHEN 20 THEN 3
          ELSE 4
          END
         );

정답코드

select job_id, department_id, 1 a_dummy
from employees
where department_id = 10
union
select job_id, department_id, 2 a_dummy
from employees
where department_id = 50
union
select job_id, department_id, 3 a_dummy
from employees
where department_id = 20
order by a_dummy;

https://tjrghks0110.tistory.com/entry/Order-By-%EC%9E%84%EC%9D%98%EB%A1%9C-%EC%A7%80%EC%A0%95%ED%95%98%EA%B8%B0

4. 입사후 현재 업무와 같은 업무를 담당한 적이 있는 사원의 employee ID와 job ID를 출력하세요.

EMPLOYEE_ID JOB_ID
176 SA_REP
200 AD_ASST
정답코드

select last_name, department_id, nvl(null,' ') as department_name
from employees
union
select department_id, department_name, nvl(null,' ') as department_name
from departments;

03. 오라클 주요 함수

3.1 DUAL 테이블

DUAL 테이블로 1일이 몇 분인지 환산하기

SELECT 24*60
FROM DUAL;

DUAL 테이블 조회하기

SELECT *
FROM DUAL;

현재 날짜 조회하기

SELECT SYSDATE
FROM DUAL;

3.2 숫자 함수

3.2.1 절대값을 구하는 ABS 함수

-10에 대한 절대값을 구하기

select -10, abs(-10)
from dual;

3.2.2 소수점 아래를 버리는 FLOOR함수

소수점 이하 반올림하기

select 34.5678, floor(34.5678)
from dual;

3.2.3 특정 자릿수에서 반올림하는 ROUND 함수

소수점 이하 반올림하기

select 34.5678, ROUND(34.5678)
from dual;

소수점 이하 두 번째 자리에서 반올림하기

select 34.5678, round(34.5678, 2)
from dual;

일의 자리에서 반올림하기

select 34.5678, round(34.5678, -1)
from dual;

3.2.4 특정 자릿수에서 잘라내는 trunc 함수

특정 자릿수에서 잘라내기

select trunc(34.5678, 2), trunc(34.5678, -1), trunc(34.5678)
from dual;

3.2.5 나머지를 구하는 MOD함수

select mod (27, 2), mod (27, 5), mod (27, 7)
from dual;

3.3 문자 처리 함수

3.3.1 대소문자 변환함수

UPPER: 대문자로 변환한다.
LOWER: 소문자로 변환한다.
INTCAP: 첫 글자만 대문자로, 나머지 글자는 소문자로 변환한다.

3.3.2 문자 길이를 구하는 함수

LENGTH: 문자의 길이를 반환한다.
LENGTHB: 문자의 길이를 반환한다.

3.3.3 문자 조작 함수

CONCAT: 문자의 값을 연결한다.
SUBSTR: 문자를 잘라 추출한다.
SUBSTRB: 문자를 잘라 추출한다.
INSTR: 특정 문자의 위치 값을 반환한다.
INSTRB: 특정 문자의 위치 값을 반환한다.
LPAD, RPAD: 입력 받을 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다.

3.3.3.1 문자열 일부만 추출하는 SUBSTR 함수

4부터 시작해서 문자 3개를 추출하기

select substr('welcome to oracle', 4, 3)
from dual;

3.3.3.2 바이트 수를 기준으로 문자열 일부만 추출하는 SUBSTRB 함수

영문자의 문자 수와 바이트 수 구하기

select substr('welcome to oracle', 3, 4),
	substrb('welcome to oracle', 3, 4)
from dual;

한글의 문자 수와 바이트 수 구하기

select substr('웰컴투오라클', 4, 3), substrb('웰컴투오라클', 4, 3)
from dual;

3.3.3.3 특정 문자의 위치를 구하는 INSTR 함수

문자열 'welcome to oracle'에 'o'의 위치 찾기

select instr('welcome to oracle', 'o')
from dual;

영문자에서 시작 위치와 발견 위치를 지정하여, 문자열에서 문자 한 개의 위치 찾기

select instr('welcome to oracle', 'o', 6, 2)
from daul;

3.3.3.4 바이트 수를 기준으로 문자의 위치를 구하는 instrb 함수

한글에서 시작 위치와 발견 위치를 지정하여 문자열에서 문자 한 개의 위치 찾기

select instr('데이터베이스', '이', 4, 1), instrb('데이터베이스', '이', 4, 1)
from dual;

3.3.3.5 특정 기호로 채우는 LPAD/RPAD 함수

왼쪽에 특정 기호로 채우기

select lpad('oracle', 20, '#')
from dual;

오른쪽에 특정 기호로 채우기

select rpad('oracle', 20, '#')
from dual;

3.4 형 변환 함수

3.4.1 문자형으로 변환하는 TO_CHAR 함수

3.4.1.1 날짜형을 문자형으로 변환하기

YYYY: 년도 표현
YY: 년도 표현
MM: 월을 숫자로 표현
MON: 월을 알파벳으로 표현
DAY: 요일 표현
DY: 요일을 약어로 표현
현재 날짜와 시간을 출력하기

SELECT to_char(sysdate, 'YYYY/MM/DD, AM HH:MI:SS')
FROM DUAL;

3.4.1.2 숫자형을 문자형으로 변환하기

0: 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.
9: 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.
L: 각 지역별 통화 기호를 앞에 표시한다.
.: 소수점
,: 천 단위 자리 구분

3.4.2 날짜형으로 변환하는 TO_DATE 함수

TO_DATE('문자', 'format')

3.4.3 숫자형으로 변환하는 TO_NUMBER 함수

수치 형태의 문자 값의 차를 구하려다 오류가 발생하는 예

SELECT '20,000' - '10,000'
FROM DUAL;

수치 형태의 문자 값의 차 알아보기

SELECT TO_NUMBER('20,000', '99,999')
	- TO_NUMBER('10,000', '99,999')
FROM DUAL;

3.5 날짜 함수

SYSDATE: 시스템에 저장된 현재 날짜를 반환한다.
MONTHS_BETWEEN: 두 날짜 사이가 몇 개월인지를 반환한다.
ADD_MONTHS: 특정 날짜에 개월 수를 더한다.
NEXT_DAY: 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다.
LAST_DAY: 해당 달의 마지막 날짜를 반환한다.
ROUND: 인자로 받은 날짜를 특정 기준으로 반올림한다.
TRUNC: 인자로 받은 날짜를 특정 기준으로 버린다.

3.5.1 현재 날짜를 반환하는 SYSDATE 함수

**현재 날짜 알아보기

SELECT to_char(SYSDATE, 'YYYY/MM/DD')
FROM DUAL;

현재 날짜를 기준으로 어제와 내일 날짜 계산하기

SELECT 	to_char(SYSDATE-1, 'YYYY/MM/DD') 어제,
        to_char(SYSDATE, 'YYYY/MM/DD') 오늘,
        to_char(SYSDATE+1, 'YYYY/MM/DD') 내일,
FROM DUAL;

3.5.2 특정 조건을 기준으로 반올림하는 ROUND 함수

CC, SCC: 4자리 연도의 끝 두 글자를 기준으로 반올림
SYYY, YYYY, YEAR / SYEAR, YYY, YY, Y: 년
DDD, D, J: 일을 기준으로 반올림
HH, HH12, HH24: 시를 기준으로 반올림
Q: 한 분기의 두 번째 달의 16일을 기준으로 반올림
MONTH, MON, MM, RM: 월
DAY, DY, D: 한 주가 시작되는 날자를 기준으로 반올림
MI: 분을 기준으로 반올림

3.5.3 특정 기준으로 버리는 TRUNC 함수

특정 날짜를 달을 기준으로 버리기

SELECT to_char(hiredate, 'YYYY/MM/DD') 입사일,
		to_char(TRUNC(hiredate, 'MONTH'), 'YYYY/MM/DD') 입사일
FROM emp;

3.5.4 두 날짜 사이의 간격을 구하는 MONTHS_BETWEEN 함수

날짜 사이의 개월 수 구하기

SELECT ename, SYSDATE 오늘, to_char(hiredate, 'YYYY/MM/DD') 입사일,
	TUNC(MONTH_BETWEEN (SYSDATE, hiredate)) 근무일수
FROM emp;

3.5.5 개월 수를 더하는 ADD_MONTHS 함수

입사 날짜에 6개월을 추가하기

select ename, to_char(hiredate, 'YYYY/MM/DD')	입사일,
				to_char(ADD_MONTHS(hiredate, 6), 'YYYY/MM/DD') "입사 6개월 후"
from emp;

3.5.6 해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수

next_day (date, 요일)

3.5.7 해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수

입사한 달의 마지막 날을 구하기

select ename, to_char(hiredate, 'YYYY/MM/DD') 입사일,
			to_char(LAST_DAY(hiredate), 'YYYY/MM/DD') "마지막 날짜"
from emp;

3.6 NULL을 다른 값으로 변환하는 NVL 함수

NVL(expr1, expr2)
NVL(comm, 0)
NVL(hiredate, to_date('2014/5/10', 'YYYY/MM/DD'))
NVL(job, '매니저')
NVL2(expr1, expr2, expr3)
NULLIF(expr1, expr2)
COALESCE(expr-1, expr-2, ..., expr-n)

3.7 선택을 위한 DECODE 함수

decode (표현식, 조건1, 결과1,
				조건2, 결과2,
                조건3, 결과3,
                기본결과n
        )

연습문제

select grade
from salgrade;

select ename, decode(mod(empno, 2), 0, '백군'
                                    , 1, '청군'
                                    , '예외') as "팀"
from emp;

select ename, deptno, sal as 올해연봉,
    decode(deptno, 10, sal*1.2
                , 20, sal*1.1
                    , sal) as 내년연봉
from emp;

select ename, sal,
decode(ceil(sal/300)-1, 0, 'LOW',
1, 'MID',
2, 'HIGH',
'TOP') as grade
from emp;

select ename, sal,
    case ceil(sal/300)-1 when 0 then 'LOW'
                        when 1 then 'MID'
                        when 2 then 'HIGH'
                        else 'TOP'
                        end as grade
from emp;

select ename, sal,
    case when sal <= 300 then 'LOW'
     when sal <= 600 then 'MID'
     when sal <= 900 then 'HIGH'
    else 'TOP'
    end as grade
from emp;

3.8 조건에 따라 서로 다른 처리가 가능한 CASE 함수

case when 조건1 then 결과1
	when 조건2 then 결과2
    when 조건3 then 결과3
    else 결과n
end

04. 그룹 함수

4.1 그룹 함수

SUM: 그룹의 누적 합계를 반환합니다.
AVG: 그룹의 평균을 반환합니다.
COUNT: 그룹의 총 개수를 반환합니다.
MAX: 그룹의 최대값을 반환합니다.
MIN: 그룹의 최소값을 반환합니다.
STDDEV: 그룹의 표준편차를 반환합니다.
VARIANCE: 그룹의 분산을 반환합니다.

4.1.1 그룹 함수와 NULL 값

사원들의 커미션 총액 출력하기

select sum(comm) as "커미션 총액"
from emp;

4.1.2 그룹 함수와 단순 컬럼

최대 급여 구하기

select max(sal)
from emp;

최대 급여와 최대 급여를 받는 사원의 이름 출력하기

select ename, max(sal)
from emp;

커미션을 받은 사원의 수를 구하기

select count(comm) as "커미션 받는 사원의 수"
from emp;

전체 사원의 수와 커미션을 받는 사원의 수를 구하기

select count(*) as "전체 사원의 수",
		count(comm) as "커미션 받는 사원 수"
from emp

4.2 데이터 그룹: group by 절

select 컬럼명, 그룹 함수
from 테이블명
where 조건 (연산자)
group by 컬럼명;

group by 절에 명시하지 않은 컬럼을 select 절에 잘못 사용한 예제

select deptno, ename, avg(sal)
from emp
group by deptno;

4.3 그룹 결과 제한: HAVING 절

부서별 급여 평균이 500 이상인 부서번호와 급여 평균 구하기

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >= 500;

최정윤

개발 기록장

이전 포스트

[새싹] 현대IT&E 231026 기록 - Oracle 1~2장

다음 포스트

[새싹] 현대IT&E 231027 기록 - Oracle 02~04

새싹

오류 발생

02. SQL의 기본

2.2 특정 조건의 데이터만 조회하는 WHERE절

2.2.5 like 연산자와 와일드카드

2.2.5.1 와일드카드(%) 사용하기

2.2.5.2 와일드카드(_) 사용하기

2.2.5.3 NOT LIKE 연산자

2.3 정렬을 위한 ORDER BY절

2.3.1 오름차순 정렬을 위한 ASC

2.3.2 내림차순 정려을 위한 desc

2.3.3 정렬 방식에 여러 가지 조건을 제시하기

연습문제

2.4 집합 연산자

연습문제

1. set operator를 이용하여 job ID가 ST_CLERK을 포함하지 않는 부서의 ID를 출력하세요.

2. set operator를 이용하여 부서가 없는 지역의 Country ID와 이름을 출력하세요.

3. 부서가 10, 50 그리고 20의 순서로 부서의 업무리스트를 정렬하고 set operators를 이용하여 job ID와 department ID를 출력하세요.

4. 입사후 현재 업무와 같은 업무를 담당한 적이 있는 사원의 employee ID와 job ID를 출력하세요.

03. 오라클 주요 함수

3.1 DUAL 테이블

3.2 숫자 함수

3.2.1 절대값을 구하는 ABS 함수

3.2.2 소수점 아래를 버리는 FLOOR함수

3.2.3 특정 자릿수에서 반올림하는 ROUND 함수

3.2.4 특정 자릿수에서 잘라내는 trunc 함수

3.2.5 나머지를 구하는 MOD함수

3.3 문자 처리 함수

3.3.1 대소문자 변환함수

3.3.2 문자 길이를 구하는 함수

3.3.3 문자 조작 함수

3.3.3.1 문자열 일부만 추출하는 SUBSTR 함수

3.3.3.2 바이트 수를 기준으로 문자열 일부만 추출하는 SUBSTRB 함수

3.3.3.3 특정 문자의 위치를 구하는 INSTR 함수

3.3.3.4 바이트 수를 기준으로 문자의 위치를 구하는 instrb 함수

3.3.3.5 특정 기호로 채우는 LPAD/RPAD 함수

3.4 형 변환 함수

3.4.1 문자형으로 변환하는 TO_CHAR 함수

3.4.1.1 날짜형을 문자형으로 변환하기

3.4.1.2 숫자형을 문자형으로 변환하기

3.4.2 날짜형으로 변환하는 TO_DATE 함수

3.4.3 숫자형으로 변환하는 TO_NUMBER 함수

3.5 날짜 함수

3.5.1 현재 날짜를 반환하는 SYSDATE 함수

3.5.2 특정 조건을 기준으로 반올림하는 ROUND 함수

3.5.3 특정 기준으로 버리는 TRUNC 함수

3.5.4 두 날짜 사이의 간격을 구하는 MONTHS_BETWEEN 함수

3.5.5 개월 수를 더하는 ADD_MONTHS 함수

3.5.6 해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수

3.5.7 해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수

3.6 NULL을 다른 값으로 변환하는 NVL 함수

3.7 선택을 위한 DECODE 함수

연습문제

3.8 조건에 따라 서로 다른 처리가 가능한 CASE 함수

04. 그룹 함수

4.1 그룹 함수

4.1.1 그룹 함수와 NULL 값

4.1.2 그룹 함수와 단순 컬럼

4.2 데이터 그룹: group by 절

4.3 그룹 결과 제한: HAVING 절

[새싹] 현대IT&E 231026 기록 - Oracle 1~2장

[새싹] 현대IT&E 231030 기록 - Oracle 10~11

0개의 댓글