09. Oracle DB, 사원(emp) 테이블 연습문제 (DDL, DML 연습)

● PRACTICE 연습문제

    Q1) emp 사원테이블을 생성하시오
    Q2) emp사원테이블에 행을 추가하시오

emp 테이블

--문1) 
create table emp(
  empno       number(4)     primary key --사번(-9999~9999)
  ,ename      varchar2(50)              --이름
  ,job        varchar2(10)              --직급
  ,mgr        number(4)                 --매니저정보
  ,hiredate   date                      --입사일
  ,sal        number(7,2)               --급여(소수점 2자리)
  ,comm       number(7,2)               --커미션(보너스)
  ,deptno     number(2)                 --부서코드(-99~99)
);


-- 문2)
insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7369, '개나리', '사원', 7902, '2000-12-17', 200, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7499, '진달래', '주임', 7698, '2001-12-15', 360, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7521, '라일락', '주임', 7698, '2001-02-17', 355, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7654, '손흥민', '과장', 7839, '2002-01-11', 400, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7698, '박지성', '주임', 7698, '2000-07-12', 325, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7782, '김연아', '사원', 7698, '2001-12-17', 225, 10);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7778, '무궁화', '사원', 7839, '2005-11-14', 200, 10);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7839, '홍길동', '부장', 7566, '2006-06-17', 450, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7844, '송강호', '과장', 7566, '2018-09-17', 400, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7876, '정우성', '대표', 7839, '2004-09-09', 500, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7900, '김혜수', '사원', 7902, '2001-12-03', 200, 20);

 

    Q3) 전체 행의 갯수를 조회하시오
    Q4) 테이블의 칼럼(필드) 목록을 확인하시오

    Q5) 사원이름순(오름차순) 정렬하시오
    Q6) 사원이름순(내림차순) 정렬하시오

    Q7) 급여 내림차순으로 사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오
    Q8) 부서코드 오름차순, 급여 내림차순으로 사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오

    Q9) 이름(ename)/입사일자(hiredate)/부서코드(deptno)를

          부서코드 오름차순, 입사일자(hiredate) 오름차순으로 조회하시오

--문3)
select count(*) from emp;

--문4)
select empno, ename, job, sal from emp;

--문5)
select * from emp order by ename;

--문6)
select * from emp order by ename desc;

--문7)
select ename, sal, deptno
from emp
order by sal desc;

--문8)
select ename, sal, deptno
from emp
order by deptno asc, sal desc;

--문9)
select ename, hiredate, deptno
from emp
order by deptno asc, hiredate asc;

 

    Q10) 직급(job) 칼럼의 중복데이터를 하나씩만 조회하시오

    Q11) emp테이블을 job 오름차순, sal 내림차순으로 정렬해서
            ename 이름, job 직급, sal 급여 칼럼명으로 별칭을 바꿔서 조회하시오

    Q12) 급여가 100 이상이고 400 이하인 직원 조회하기(급여 내림차순)

    Q13) 급여가 100 이하 또는 400 이상인 직원 검색하기(급여 내림차순)

--문10)
select distinct(job) 
from emp;

select job 
from emp 
group by job;


--문11)
select ename as 이름, job as 직급, sal as 급여  --as 생략 가능 
from emp 
order by job asc, sal desc;  --asc 생략 가능


--문12
select ename, sal
from emp
where sal>=100 and sal<=400
order by sal desc;

select ename, sal
from emp
where sal between 100 and 400
order by sal desc;


--문13) 급여가 100이하 또는 400 이상의 직원 검색하기(급여 내림차순)
select ename, sal
from emp
where sal<=100 or sal>=400
order by sal desc;

 

    Q14) 직급(job)이 과장 또는 부장인 직원들을 이름순으로 조회하시오

    Q15) 부서코드(deptno)가 30인 직원을 조회하시오

    Q16) 중복된 부서코드를 한개씩만 조회하시오

    Q17) 부서코드가 10 또는 20 또는 30인 직원을 조회하시오 (or, in연산자 각각 활용해서 조회)

    Q18) 급여가 300~500인 직원을 급여순으로 조회하시오 (and, between연산자 각각 활용해서 조회)

--문14)
select ename, job
from emp
where job='과장' or job='부장'
order by ename;

select ename, job
from emp
where job in('과장', '부장')
order by ename;


--문15)
select deptno, ename 
from emp 
where deptno=30;

select deptno, ename 
from emp 
where deptno in (30);


--문16)
select distinct(deptno) 
from emp;

select deptno 
from emp 
group by deptno;


--문17)
select deptno, ename 
from emp 
where deptno=10 or deptno=20 or deptno=30;

select deptno, ename 
from emp 
where deptno in (10, 20, 30);


--문18)
select ename, sal 
from emp 
where sal>=300 and sal<=500 
order by sal;

select ename, sal 
from emp 
where sal 
between 300 and 500 
order by sal;

 

    Q19) 이름이 무궁화인 사람을 조회하시오

    Q20) 김씨성을 조회하시오

    Q21) 이름에 '화'가 포함된 사람을 조회하시오

--문19)
select ename from emp where ename='무궁화';

--문20)
select ename from emp where ename like '김%';

--문21)
select ename from emp where ename like '%화%';

 

    Q22) 부서코드가 20인 레코드에 대해서 급여의 1%를 커미션으로 책정하시오

    Q23) 연봉을 아래와 같이 구한후 이름, 부서, 급여, 커미션, 연봉을 조회하시오
            (연봉구하는 식 : 급여(sal)*12개월+보너스(comm))

    Q24) 커미션이 null이면 0으로 바꾼후 연봉을 다시 계산해서 이름, 급여, 커미션, 연봉을 조회하시오

    Q25) 각 사람의 급여를 검색해서 '누구누구의 급여는 얼마입니다'로 조회하시오 (|| 결합연산자)

--문22) 
update emp
set comm=sal*0.01
where deptno=20;


--문23) 
select ename
      ,deptno
      ,sal
      ,comm
      ,sal*12+comm as 연봉
from emp;


--문24) 
-- 1) comm이 null인 행 조회
select ename, deptno, comm from emp where comm is null;

-- 2) comm칼럼의 null값을 0으로 바꾸기
select ename, deptno, nvl(comm,0) from emp where comm is null;

-- 3) 연봉구하기
select ename, deptno, sal, comm, sal*12+nvl(comm,0) as 연봉 
from emp
order by deptno;

-- 4) 연봉을 반올림해서 소수점 없이 조회
select ename, deptno, sal, comm, round(sal*12+nvl(comm,0),0) as 연봉 
from emp
order by deptno;


--문25) 
select ename || '의 급여는 ' || sal || '입니다' from emp;
select concat(ename, '의 급여는'), concat(sal, '입니다') from emp;

 

    Q26) emp테이블에서 입사일(hiredate)이 2005년 1월 1일 이전인 사원에 대해
           사원의 이름(ename), 입사일, 부서번호(deptno)를 입사일순으로 조회하시오

    Q27) emp테이블에서 부서번호가 20번이나 30번인 부서에 속한
           사원들에 대하여 이름, 직업코드(job), 부서번호를 이름순으로 조회하시오
           (or, in연산자 각각 활용해서 모두 조회)

    Q28) 사번, 이름, 부서를 조회하시오
           (단, 부서코드가 10이면 관리부, 20이면 영업부, 30이면 교육부로 바꿔서 출력)

--문26)
select ename, hiredate, deptno
from emp
where hiredate<=to_date('2005-01-01')
order by hiredate;


--문27)
select ename, job, deptno
from emp
where deptno=20 or deptno=30
order by ename;

select ename, job, deptno
from emp
where deptno in(20, 30)
order by ename;


--문28)
select empno, ename, case when deptno=10 then '관리부'
                          when deptno=20 then '영업부'
                          when deptno=30 then '교육부'
                     end dept
from emp;

 

    Q29) 다음의 SQL문을 분석하시오

  select empno, sal 
  from emp
  where not(sal>200 and sal<300)
  order by sal;

    Q30) 다음의 SQL문을 분석하시오

  select empno, sal 
  from emp
  where not sal>200 and sal<300
  order by sal;

 

     Q31) 부서코드별 급여합계를 구하시오

     Q32) 부서코드별 급여합계를 구해서  그 합계값이 1500이상만 조회하시오

--문31)
select deptno, sum(sal)
from emp
group by deptno
order by deptno;

--문32)
select deptno, sum(sal)
from emp
group by deptno  
having sum(sal)>=1500

 

     Q33) 부서별 급여평균이 300이상 조회하시오

     Q34) 급여가 300이상 데이터중에서 부서코드별 급여평균을 구해서 급여평균순으로 조회하시오

     Q35) 급여가 300이상 데이터중에서 부서코드별 급여평균이 400이상을 급여순으로 조회하시오

--문33)
select deptno, avg(sal) 
from emp  
group by deptno
having avg(sal)>=300  
order by deptno;

--문34)
select deptno, round(avg(sal),2) --급여평균 반올림해서 소수 둘째자리까지
from emp 
where sal>=300
group by deptno 
order by avg(sal); 

--문35)
select deptno, avg(sal)    
from emp
where sal>=300
group by deptno
having avg(sal)>=400
order by avg(sal);

 

     Q36) hiredate칼럼을 사용하여 월별로 입사한 인원수를 구하시오

        - TO_CHAR() : 날짜, 숫자 등의 값을 문자열로 변환하는 함수
                           형식) to_char(원래날짜, '원하는모양')

--문36)
--1) 입사일 조회
select hiredate from emp;

--2) 입사일에서 월 가져오기
select to_char(hiredate, 'mm') from emp;
select to_char(hiredate, 'mm') from emp order by to_char(hiredate, 'mm');

--3) 월별로 그룹화하여 인원수 구하기
select to_char(hiredate, 'mm') as 입사월, count(*) as 인원수 -- alias중간에 공백주면 오류
from emp
group by to_char(hiredate, 'mm')
order by to_char(hiredate, 'mm');

--참고 : 입사일에서 년/일 가져오기    
select to_char(hiredate, 'yy') as 년 from emp;    --년
select to_char(hiredate, 'dd') as 일 from emp;    --일

 

     Q37) 매니저별 담당인원수를 조회하시오

     Q38) 사원번호 7654와 급여보다 적은 행을 조회하시오

     Q39) 부서별로 급여+커미션를 구했을때  최대값, 최소값, 평균값(반올림 해서)을 부서순으로 조회하시오

--문37)
select mgr, count(*)
from emp
group by mgr
order by count(*) desc;

--문38)
select ename, empno, sal
from emp
where sal<(select sal from emp where empno=7654);	--서브쿼리 이용

--문39)
select deptno, max(sal+nvl(comm,0)) as 최대값
             , min(sal+nvl(comm,0)) as 최소값
             , round(avg(sal+nvl(comm,0)),1) as 평균값
from emp
group by deptno
order by deptno;

 

     Q40) 각 직원들에 대해서 직원의 이름과 근속년수를 구하시오 (단, 근속년수는 연단위를 버림하여 나타내시오)

     Q41) 다음과 같이 출력 하시오 ( 예) 박지성의 근속년수 : 20년)

     Q42) 손흥민의 근속년수와 동일한 행을 조회(이름, 근속년수)하시오

--문40)
select ename as 사원명, trunc((sysdate-hiredate)/365, 0) as 근속년수
from emp 
order by 근속년수 desc;

--문41)
select ename || '의 근속년수 : ' || trunc((sysdate-hiredate)/365, 0) || '년' as 개인별근속년수
from emp
order by trunc((sysdate-hiredate)/365) desc; 

--문42)
select ename, trunc((sysdate-hiredate)/365) as 근속년수
from emp
where trunc((sysdate-hiredate)/365)=(
                                     select trunc((sysdate-hiredate)/365) 
                                     from emp 
                                     where ename='손흥민'
                                     );

 

     Q43) 입사한지 만15년 이상된 사람에 한해 현재연봉에서 10% 인상시켰을 때 
             사번, 이름, 입사일, 현재연봉, 인상후연봉, 인상된금액으로 고액연봉순으로 조회하시오
             연봉구하는 식 : 급여(sal)*12개월+보너스(comm)

--문43)
--1) 근속년수 15년 이상 조회
select ename, hiredate, sal, comm, trunc((sysdate-hiredate)/365)
from emp
where trunc((sysdate-hiredate)/365)>=15;

--2) 현재연봉구하기
select ename, hiredate, sal, comm, sal*12+comm, trunc((sysdate-hiredate)/365)
from emp
where trunc((sysdate-hiredate)/365)>=15;

--3) 현재연봉구하기 (단, comm이 없으면 0으로 바꿔서 계산)
select ename, hiredate, sal, comm, sal*12+NVL(comm,0), trunc((sysdate-hiredate)/365)
from emp
where trunc((sysdate-hiredate)/365)>=15;

--4) 연봉에서 10%인상된 금액
select ename
     , hiredate
     , sal
     , comm
     , sal*12+NVL(comm,0) as 현재연봉
     , (sal*12+NVL(comm,0))*0.1 as 인상금액
     , trunc((sysdate-hiredate)/365) as 근속연수
from emp
where trunc((sysdate-hiredate)/365)>=15;

--5) 10%인상후 최종연봉 구하기 (현재연봉+인상된금액)
select ename
     , hiredate
     , sal
     , comm
     , sal*12+NVL(comm,0) as 현재연봉
     , (sal*12+NVL(comm,0))*0.1 as 인상금액
     , (sal*12+NVL(comm,0)) + ((sal*12+NVL(comm,0))*0.1) as 최종연봉
     , trunc((sysdate-hiredate)/365) as 근속연수
from emp
where trunc((sysdate-hiredate)/365)>=15;

--6) 사번, 이름, 입사일, 현재연봉, 인상후연봉(반올림), 인상된금액으로 고액연봉순으로 조회
select empno
     , ename
     , hiredate
     , trunc((sysdate-hiredate)/365) as 근속연수
     , sal*12+NVL(comm,0) as 현재연봉
     , (sal*12+NVL(comm,0))*0.1 as 인상금액
     , round((sal*12+NVL(comm,0)) + ((sal*12+NVL(comm,0))*0.1) ,0) as 최종연봉
from emp
where trunc((sysdate-hiredate)/365)>=15
order by 최종연봉 desc;

 

     Q44) 입사년도가 짝수인 직원들의 급여의 평균을 job별로 출력하시오

--문44)

--1) 직급, 입사일 조회하기
select ename, job, hiredate from emp;

--2) 입사일에서 년도 추출하기
select ename, job, hiredate, to_char(hiredate, 'yyyy') from emp;
select ename, job, hiredate, extract(year from hiredate) from emp;     

--3) 입사년도 짝수인 직원 추출하기
select ename, sal, job, to_char(hiredate, 'yyyy')
from emp
where mod(to_char(hiredate, 'yyyy'), 2)=0;  -- mod() : 나머지 연산자    

--4) job별로 그룹화해서, 급여평균 구하기    
select job, avg(sal)
from emp
where mod(to_char(hiredate, 'yyyy'), 2)=0
group by job;

 

---

● PRACTICE 연습문제2

    - Q1~Q44 : 위의 44문제는 수업시간에 강사님이 내주시고 같이 확인한 문제들

    - Q45~Q56 : 아래 문제들은 내가 연습으로 더 찾아보거나 만들어서 풀어본 문제들인데

                      코드까지 같이 올리면 포스팅이 너무 길어질거 같아서 문제만 올리려 한다

 

        Q45) 각 직원의 이름, 직급, 급여를 나타내시오. 
               (단, 급여는 5자리로 나타내며 부족한 자리수는 '*'로 표시하고 월급이 300이상인 직원만 나타내시오.)

 

        Q46) 전체 직원에 대하여 직원의 이름과 직급, 총 근무주(week)수를 구하시오 
               (단, 근무주수가 많은 직원부터 나타내고, 근무주수가 같으면 이름에 대하여 오름차순 정렬하시오.)

 

        Q47) '진달래'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하세요.

 

        Q48) '송강호'가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일, 급여를 출력하세요.

 

        Q49) '손흥민'이나 '김혜수'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하세요.

 

        Q50) 부서번호가 30번 부서의 최고 급여보다 높거나 같은 사원의 사원번호, 이름, 급여를 출력하세요.

  

        Q51) 급여가 500이상이면, 급여+급여의 15%의 격려금을, 
               급여가 400이상이면, 급여+급여의 10%의 격려금을,
               급여가 300이상이면, 급여+급여의 5%의 격려금을, 
               그렇지 않으면 급여를 구하여, 이름, 직업, 급여,격려금을 표시하시오.

 

        Q52) 이름에 '김'를 포함하고 있는 사원들과 같은 부서에서 근무하고있는 사원의 사원번호와 이름을 출력하라.

 

        Q53) '정우성'과 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데 '정우성'은 빼고 출력하라.

 

        Q54) 자신의 급여가 평균 급여보다 많고, 
                이름에 '수'가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름, 급여를 출력하라

 

        Q55) 커미션을 받는 사원과 부서 또는 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라.

 

        Q56) 사원 중에서 입사일이 가장 빠른 사원의 사원번호, 이름, 입사일, 부서명을 출력하세요.