SQL
oracle19c - 연습 (HR 05.06)
jomericano
2022. 5. 11. 20:27
sequence_sysdate
-- crud 예제
-- 커뮤니티 게시판을 위한 테이블 작성
-- 회원테이블 member 테이블 (회원번호, 아이디, 비밀번호, 이메일, 이름, 가입일)
create table MEMBER (
mbno number generated as identity,
userid varchar2(18) not null,
passwd varchar2(18) not null ,
email varchar2(100) not null ,
name varchar2(15) not null ,
regdate date default sysdate + 9/24,
-- regdate date default sysdate, --서버시간으로 맞지 않음
constraint mbpk primary key (mbno) );
-- 게시판 ( 글번호, 제목, 작성자, 작성일, 조회수, 본문)
create table BOARD (
-- bdno number,
-- bdno number default bd_seq.nextval, --시퀀스객체를 만들어야하는 불편함이 있음
bdno number generated as identity, -- 오라클 12 c 부터 지원
title varchar2(300) not null ,
mbno number,
regdate date default sysdate + 9/24,
reads number default 0 ,
contents clob ,
constraint bdpk primary key (bdno));
alter table BOARD
add constraint bd_fk
foreign key (mbno) references MEMBER(mbno);
-- 회원가입
insert into MEMBER (userid,passwd,email, name)
values ('abc123', '987xyz', 'abc123@987xyz.com', '혜교');
insert into BOARD (title, mbno, contents)
values ('테스트', 1, '제곧내');
-- 자동증가 확인
select *
from MEMBER;
-- 서버 시간 보완
select sysdate from dual; --서버 시스템 시간
alter session set time_zone = 'asia/seoul'; -- 시간대 변경
select current_date from dual; --현지시간
select localtimestamp from dual; --현지시간
select current_timestamp from dual; --현지시간 (!)
select sysdate from dual; -- 서버시간 반영
select sysdate +1 from dual ; -- 현재시간 날짜에 1 day 추가
select sysdate +1/24 from dual ; -- 현재시간 날짜에 1 hour 추가
select sysdate +9/24 from dual ; -- 현재시간 날짜에 9 hour 추가
-- 자동증가번호 설정 : sequence
-- 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
-- 보통 pk컬럼에 중복값이 삽입되는 것을 방지하기 위해 사용
-- create sequence 이름 ; -- 자동증가(기업체에서 자주 사용)
-- create sequence 이름 start with 시작번호 increment by 증가값 ;
-- create sequence 이름 start with 시작번호 increment by 증가값
-- nocycle nocache;
create sequence mb_seq;
create sequence bd_seq;
-- sequence를 이용한 데이터 삽입
-- pk컬럼 삽입시 '시퀀스 이름. nextval' 사용
insert into BOARD(bdno, title, mbno, contents)
values (bd_seq.nextval,'테스트테스트',1,'제곧네~');
select * from BOARD;
--오라클에서 시퀀스객체 값을 자동으로 컬럼에 입력하려면?
-- orecle 11g이하는 트리거 이용
-- orecle 12c이상은 default 제약조건에 시퀀스를 지정하거나 generated by default as identify 사용rollup,cube
--고급 분석함수 : rollup
-- 그룹함수로 집계된 결과들에서 소계, 합계를 쉽게 구하기위해 사용
-- 고객들의 등급별 마일리지 합계조회
select GRADE, sum(MILEGE)
from "Customers"
group by GRADE;
-- 사원들의 부서별, 직책별 연봉 총합 조회;
select DEPTID, JOBID, sum(sal) from emp
group by DEPTID, JOBID
order by DEPTID;
-- rollup (기준컬럼들 ... )
select DEPTID, JOBID, sum(sal) from emp
group by rollup((DEPTID, JOBID)) -- 각각 다 구해줌
order by DEPTID;
-- rollup ( (기준컬럼들 ... ) ) group by랑 별 차이없음
select DEPTID, JOBID, sum(sal) from emp
group by rollup((DEPTID, JOBID)) -- 직책의 전체 연봉합
order by DEPTID;
--고급 분석함수 : cube
--rollup 함수에 비해 좀더 다양한 집계 결과를 표시
select JOBID, sum(sal) 연봉총합 from emp
group by cube( JOBID ); -- 상단에 전체 연봉합이 표시됨.
-- 사원들의 부서별, 직책별 연봉 총합 조회;
select DEPTID, JOBID, sum(sal) 연봉총합 from emp
group by cube(DEPTID, JOBID); -- 처음에 null인 값들의 연봉합이 표시,
-- 그 다음에 직책별 연봉합이 표시
-- 마지막으로 부서별 연봉합과 부서별 직책별 연봉합이 표시
-- having절을 이용해서 부분별 소계를 표시
select DEPTID, JOBID, sum(sal) 연봉총합 from emp
group by cube(DEPTID, JOBID)
having grouping_id(DEPTID, JOBID)=1; -- 부서별 총합
-- having절을 이용해서 부분별 소계를 표시
select DEPTID, JOBID, sum(sal) 연봉총합 from emp
group by cube(DEPTID, JOBID)
having grouping_id(DEPTID, JOBID)=2; -- 직책별 총합
-- having절을 이용해서 부분별 소계를 표시
select DEPTID, JOBID, sum(sal) 연봉총합 from emp
group by cube(DEPTID, JOBID)
having grouping_id(DEPTID, JOBID)=3; -- 모든 연봉 총합
-- 사원 테이블에서 사원 이름을 소문자로 출력하세요
select lower (LAST_NAME) from EMPLOYEES;
-- 사원 테이블에서 사원 이름의 첫글자만 대문자로 출력하세요.
select substr(upper (LAST_NAME),1,1) from EMPLOYEES;
-- 사원 테이블에서 사원 이름의 길이를 출력하세요.
select length(LAST_NAME) from EMPLOYEES;
-- 사원 테이블에서 사원이름과 이름에 A가 몇번째 있는지 출력하세요.
select LAST_NAME, instr(LAST_NAME,'a',1) from EMPLOYEES;
select LAST_NAME, instr(upper(LAST_NAME),'A') from EMPLOYEES;
-- 사원 테이블에서 사원이름의 세번째 자리가 R인 사원의 정보를
-- 출력하세요.
select * from EMPLOYEES where substr(LAST_NAME, 3,1 )='r';
-- 사원 테이블에서 이름의 끝자리가
-- N으로 끝나는 사원의 정보를 출력하세요.
SELECT * FROM EMPLOYEES WHERE INSTR(LAST_NAME,'n')=LENGTH(LAST_NAME);
-- n이 처음나오는위치가 이름길이인 사람
select FIRST_NAME, substr(first_name,-1,1) from EMPLOYEES
where substr(upper(first_name),-1,1) = 'n';
-- n이 처음나오는위치가 이름길이인 사람
-- 사원들의 급여가 평균보다 큰 경우 '평균급여이상'이라 출력하고
-- 아닌 경우 '평균급여이하' 라고 출력하세요
select SALARY, round(avg(SALARY)) over ( ) ) 평균급여,
case when salary >= (select round(avg(sal)) from employee) then '평균급여이상'
when salary >= (select round(avg(sal)) from employee) then '평균급여이하' end 급여평가
from employee;
select sal,(select round(avg(sal)) from emp) 평균급여,
case when sal >=(select round(avg(sal)) from emp) then '평균급여이상'
when sal >= (select round(avg(sal)) from emp) then '평균급여이하' end 급여평가
from emp;
-- 사원의 급여에 대해 평균을 계산하고
-- 정수로 변환해서 출력하세요 (cast이용)
select round( cast( ( avg( SALARY ) ) as number) ) from EMPLOYEES;
select round( cast( ( avg( SALARY ) ) as decimal(5,0) ) )from EMPLOYEES;
select cast(avg(SALARY) as decimal) from EMPLOYEES;
-- 사원들을 입사일자가 빠른 순으로
-- 정렬한 뒤 본인보다 입사일자가 빠른
-- 사원의 급여를 본인의 급여와 함께 출력하세요 윈도우함수 : lag, lead 사용
select FIRST_NAME, HIRE_DATE, SALARY,
lag(SALARY) over (order by HIRE_DATE ) 이전사원급여,
lead(SALARY) over (order by HIRE_DATE ) 이후사원급여
from EMPLOYEES order by HIRE_DATE;
-- AND나 BETWEEN을 사용하지 않고
-- 2002 년도에 입사한 직원의 이름과
-- 월급을 출력하세요.
select * from EMPLOYEES where substr(HIRE_DATE,0,4) = '2002';
-- 사원 테이블에서 사원의 이름이
-- 5글자인 사원의 이름을 첫글자만 대문자로 출력하세요.
select initcap(LAST_NAME) from EMPLOYEES where length(LAST_NAME)=5;
-- and나 between을 사용하지않고 2002년도 입사한 직원의 이름과 월급
select FIRST_NAME, HIRE_DATE from EMPLOYEES where substr(HIRE_DATE, 3, 2) = '02';
-- 사원 테이블에서 이름과 입사일자
-- 그리고 현재날까지의 경과일을 산출하세요
-- (소숫점을 빼버리고 해당이름을 경과일로 바꾸세요) (datediff이용 mariadb)
select FIRST_NAME, HIRE_DATE,
concat(to_char(floor(sysdate - to_date(HIRE_DATE)),'9,999'), ' 일') 경과일
from EMPLOYEES;
select concat(to_char(floor(sysdate - to_date(hire_date)), '9,999'), '일' ) from EMPLOYEES;
-- 이전문제에서 경과일을 개월수로 바꿔서
-- 산출하세요. 소숫점을 빼버리고
-- 해당이름을 경과개월수로 바꾸세요 (timestampdiff이용)
select FIRST_NAME, HIRE_DATE,
concat(to_char(floor(sysdate - to_date(HIRE_DATE))*1/30,'9,999'), ' 개월') 경과개월수
from EMPLOYEES;
-- 사원 테이블에서 입사후 6개월이
-- 지난날짜 바로 다음 일요일을 구하세요.
select FIRST_NAME,HIRE_DATE, to_date(HIRE_DATE,'yyyy-mm-dd')+ 365/2 "6개월",
add_months(HIRE_DATE,6) "6개월후",
next_day(add_months(HIRE_DATE,6), '일') "6개월후다음일요일" from EMPLOYEES;
-- 교육시작일이 2020-11-02인 경우,
-- 5개월(18주)이 지난후 돌아오는 첫번째 금요일이
-- 언제인지 조회하세요 (5개월(18주) : 2021-03-08)
select '2020-11-02' 교육시작일, next_day(add_months('2020-11-02', 5), '금') ,
next_day(to_date('2020-11-02') +(18*7), '금') from dual;
-- 교육시작일이 2020-11-02인 경우,
-- 131일이 지난후 돌아오는 첫번째 금요일이
-- 언제인지 조회하세요 (131일후 : 2021-03-11)
select '2020-11-02' 교육시작일, next_day(to_date('2020-11-02') + 131,'금') from dual;
-- 사원 테이블에서 입사후
-- 첫 휴일(일요일)은 언제일지 구하세요
select FIRST_NAME,HIRE_DATE, next_day(HIRE_DATE, '일') 입사후첫휴일 from EMPLOYEES;
-- 오늘날짜를 "xx년 xx월 xx일" 형식으로 출력하세요
select to_char(sysdate, 'yyyy"년" mm"월" dd"일"') 오늘날짜 from dual;
-- 지금 현재 '몇시 몇분'인지 출력하세요.
-- AWS RDS의 시간은 미국 기준
-- 한국 기준시로 변경하고 싶다면 서버설정변경(time-zone) 필요
-- 한국은 UTC 기준 9시간 추가 : KST = UTC + 9
select to_char(sysdate + 9/24, 'am hh"시" mi"분"') 오늘시간 from dual;
-- 이번 년도 12월 31일까지 몇일이
-- 남았는지 출력하세요. (datediff이용)
select floor(to_date('2022-12-31' - 'yyyy-mm-dd') - sysdate) 연말까지남은일수 from dual;
-- 사원들의 입사일에서
-- 입사 년도와 입사 달을 조회하세요
select HIRE_DATE, to_char(to_date(HIRE_DATE), 'yyyy"년" mm"월" ') 입사년월
from EMPLOYEES;
-- 9월에 입사한 사원을 조회하세요
select FIRST_NAME, HIRE_DATE from EMPLOYEES where substr(HIRE_DATE,6,2)='09';
select FIRST_NAME, HIRE_DATE from EMPLOYEES where instr(HIRE_DATE, '09',6)=6;
select instr('2009-09-01', '09',6) from dual;
-- 사원들의 입사일을 출력하되,
-- 요일까지 함께 조회하세요
select FIRST_NAME, to_char(to_date(HIRE_DATE), 'yyyy-mm-dd day') 입사일 from EMPLOYEES;
-- 사원들의 급여를 통화 기호를 앞에 붙이고
-- 천 단위마다 콤마를 붙여서 조회하세요
-- format(값, 반올림자릿수) : 숫자에 컴마를 붙여 출력
select FIRST_NAME, SALARY,
to_char(SALARY, '$99,999') "꾸며진급여" ,
to_char(SALARY*1267, 'l999,999,999') "환율급여" from EMPLOYEES;
-- 사원들의 급여를 10자리로 출력하되
-- 자릿수가 남는 경우 빈칸으로 채워서 조회하세요
-- _____12345
select FIRST_NAME, SALARY,
to_char(SALARY, '9999999999') "꾸며진급여",
to_char(SALARY, '0000099999') "0으로채움",
to_char(SALARY, 'FM9999999999') "공백제거"
from EMPLOYEES;
-- 각 사원들의 근무한 개월 수를 현재를
-- 기준으로 구하세요 (timestampdiff이용)
-- 각 사원들의 입사일에서
-- 4개월을 추가한 날짜를 조회하세요
select HIRE_DATE, add_months(HIRE_DATE,4) "입사일+4개월" from EMPLOYEES;
-- 각 사원들의 입사한 달의 마지막 날을
-- 조회하세요 (last_day이용)
select HIRE_DATE, last_day(HIRE_DATE)
from EMPLOYEES;
-- 매니저와 그 매니저를 상사로 두고 있는 사원들의 연봉의 총합을 조회 ( 매니저와 매니저상사사원의 연봉의 합)
select coalesce(MANAGER_ID,SALARY ) from EMPLOYEES;
select FIRST_NAME, EMPLOYEE_ID, SALARY, COMMISSION_PCT,
coalesce((COMMISSION_PCT+1)*SALARY, SALARY) --null이아니면 앞, null이면 뒤
총급여 from EMPLOYEES;
select fname, EMPID, EMGRID,
-- sum(sal) over () 연봉총합,
sum(sal) over (partition by EMGRID order by SAL) 매니저와사원연봉총합
from emp;
-- 사원들의 연봉이 높은 순서와 직책별로 높은 순으로 조회
SELECT FIRST_NAME, SALARY, JOB_ID,
dense_RANK() over (order by SALARY desc ) SAL,
dense_RANK() over (partition by JOB_ID order by SALARY desc) 직책별
FROM EMPLOYEES;
-- 사원들을 입사일자가 빠른 순으로 정렬한 뒤
-- 본인과 이전/이후 사원간의 입사일의 차이를 조회하세요
select FIRST_NAME, HIRE_DATE ,
lag (HIRE_DATE) over ( order by HIRE_DATE) 이전입사일,
lead (HIRE_DATE) over ( order by HIRE_DATE) 이후입사일,
abs( to_date(HIRE_DATE) - lag(to_date(HIRE_DATE)) over ( order by HIRE_DATE) ) 이전일차이,
abs( to_date(HIRE_DATE) - lead(to_date(HIRE_DATE)) over ( order by HIRE_DATE) ) 이후입사일차이
from EMPLOYEES order by HIRE_DATE;
select name, MILEGE,
abs(MILEGE - lead(MILEGE) over ( order by USERID)) 이후마일리지차이
from "Customers";
-- HR 부서에서는 신규 프로젝트의 성공으로 해당하는 각 업무 자들에 대한 급여 인상안을 결정하고,
-- 다음과 같이 업무 별 급여 인상에 대해 적용하고자 한다.
-- 현재 107명의 사원은 19개의 업무에 소속되어 근무 중이다.
-- (Distinct job_id) 이 중 5개의 업무 자들에 대한 급여 인상이 각각 결정되었고,
-- 나머지 업무에 대해서는 인상이 동결되었다 (107행).
-- HR_REP(10%), MK_REP(12%), PR_REP(15%), SA_REP(18%), IT_PROG(20%)
select EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY,
case JOB_ID when 'HR_REP' then SALARY * 1.1
when 'MK_REP' then SALARY * 1.12
when 'PR_REP' then SALARY * 1.15
when 'SA_REP' then SALARY * 1.18
when 'IT_PROG' then SALARY * 1.20
else salary end "new salary"
from employees;
select EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY,
decode(JOB_ID, 'HR_REP' , SALARY * 1.1 , --비교연산자에는 case ''when''then''
'MK_REP' , SALARY * 1.12,
'PR_REP' , SALARY * 1.15,
'SA_REP' , SALARY * 1.18,
'IT_PROG' , SALARY * 1.20, SALARY ) newsal
from EMPLOYEES;