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

최정윤·2023년 10월 27일
oraclesql
0

새싹

목록 보기
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 출력

  1. salary가 10000달러 이상이거나 job_id에 문자열 'MAN'이 포함된 사원의 lst_name, salary, job_id 출력

  2. job_id가 IT_PROG, ST_CLERK, SA_REP에 속하지 않은 사원의 last_name, job_id 출력

  3. commission_pct가 null이 아닌 사원들의 lastname, commission_pct 출력

  4. 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;
profile
최정윤
개발 기록장
이전 포스트

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

다음 포스트

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

0개의 댓글