[21일] Database (5) - 문제

테이블 문제

문제 세팅

문1) emp 사원테이블을 생성하시오
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) emp사원테이블에 행을 추가하시오
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);

 

문제

문3) 전체 행의 갯수를 조회하시오

	SELECT COUNT(*) FROM emp;

문4) 테이블의 칼럼(필드) 목록을 확인하시오

    SELECT * FROM emp;
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; --자바 등에서 사용하기 위해서는 열을 다 나열해줘야 한다.

문5) 사원이름순(오름차순) 정렬하시오

    SELECT *
    FROM emp
    ORDER BY ename ASC; --ASC 생략가능

문6) 사원이름순(내림차순) 정렬하시오

    SELECT *
    FROM emp
    ORDER BY ename DESC;

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

    SELECT ename, sal, deptno
    FROM emp
    ORDER BY sal DESC;

문8) 부서코드 오름차순, 급여 내림차순으로
     사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오
     
     SELECT ename, sal, deptno
     FROM emp
     ORDER BY deptno, sal DESC;

문9) 이름(ename)/입사일자(hiredate)/부서코드(deptno)를
      부서코드 오름차순, 입사일자(hiredate) 오름차순으로 조회하시오
      
      SELECT ename, hiredate, deptno
      FROM emp
      ORDER BY deptno, hiredate;
      
문10) 직급(job) 칼럼의 중복데이터를 하나씩만 조회하시오

    SELECT DISTINCT job FROM emp;

    SELECT job FROM emp GROUP BY job;

문11) emp테이블의 job 오름차순, sal 내림차순으로 정렬해서
      ename 이름, job 직급, sal 급여 칼럼명으로 별칭을 바꿔서 조회하시오
      
      SELECT ename 이름, job 직급, sal 급여
      FROM emp
      ORDER BY job, sal DESC;
      
문12) 급여가 100이상 400이하보다 작은 직원 조회하기(급여 내림차순)

    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;
    
문14) 직급(job)이 과장 또는 부장인 직원들을 이름순으로 조회하시오

    SELECT job, ename
    FROM emp
    WHERE job='과장' OR job='부장'
    ORDER BY ename;
    
    SELECT job, ename
    FROM emp
    WHERE job IN ('과장', '부장')
    ORDER BY ename;

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

    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) 부서코드가 10 또는 20 또는 30인 직원을 조회하시오
      (or, in연산자 각각 활용해서 조회)
      
      SELECT deptno, ename
      FROM emp
      WHERE deptno=10 OR deptno=20 OR deptno=30;
      
      SELECT *
      FROM emp
      WHERE deptno IN (10, 20, 30);

문18) 급여가 300~500인 직원을 급여순으로 조회하시오
      (and, between연산자 각각 활용해서 조회)
      
      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;

문19) 이름이 무궁화 조회하시오

    SELECT ename
    FROM emp
    WHERE ename='무궁화';

문20) 김씨성을 조회하시오

    SELECT ename
    FROM emp
    WHERE ename LIKE '김%';

문21) 이름에 '화' 포함된 줄을 조회하시오

    SELECT ename
    FROM emp
    WHERE ename LIKE '%화%';

문22) 부서코드가 20인 레코드에 대해서
      급여의 1%를 커미션으로 책정하시오
      
      UPDATE emp
      SET comm=sal*0.01
      WHERE deptno=20;
      
      SELECT * FROM emp;

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

문24) 커미션이 null이면 0으로 바꾼후 연봉을 다시 계산해서
      이름, 급여, 커미션, 연봉을 조회하시오
      
      --comm이 null값인 행을 조회
      SELECT ename, comm FROM emp WHERE comm IS NULL;
      
      --comm칼럼에 null값을 0으로 바꾸기
      SELECT ename, NVL(comm, 0)
      FROM emp
      WHERE comm IS NULL;
      
      --comm칼럼에 null값을 0으로 바꾼 후, 연봉 계산하기
      SELECT ename, deptno, sal, sal*12+NVL(comm, 0) AS 연봉
      FROM emp;
      
      --연봉을 반올림해서 소수점없이 조회하기
      SELECT ename, deptno, sal, ROUND(sal*12+NVL(comm, 0)) AS 연봉
      FROM emp;
      
문25) 각 사람의 급여를 검색해서 '누구누구의 급여는 얼마입니다'로 조회하시오
      (|| 결합연산자)
      
      SELECT ename || '의 급여는 ' || sal || '입니다'
      FROM emp;
      
      SELECT ename || '의 급여는 ' || sal || '입니다' AS pay
      FROM emp;
      
      SELECT CONCAT(ename, '의 급여는 ') CONCAT(sal, '입니다')
      FROM emp;
      
문26) emp테이블에서 입사일(hiredate)이 2005년 1월 1일 이전인 사원에 대해
      사원의 이름(ename), 입사일, 부서번호(deptno)를 입사일순으로 조회하시오
      
      SELECT ename, hiredate, deptno
      FROM emp
      WHERE hiredate<'2005-01-01'
      ORDER BY hiredate;

문27) emp테이블에서 부서번호가 20번이나 30번인 부서에 속한
     사원들에 대하여 이름, 직업코드(job), 부서번호를 이름순으로 조회하시오
     (or, in연산자 각각 활용해서 모두 조회)
     
     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) 사번, 이름, 부서를 조회하시오
      (단, 부서코드가 10이면 관리부
                      20이면 영업부
                      30이면 교육부로 바꿔서 출력)
                      
    SELECT empno, ename, deptno, CASE WHEN deptno=10 THEN '관리부'
                                      WHEN deptno=20 THEN '영업부'
                                      WHEN deptno=30 THEN '교육부'
                                 END AS deptname
    FROM emp;
    
문29) 다음의 SQL문을 분석하시오
      select empno, sal 
      from emp
      where not(sal>200 and sal<300)  --not(201~299) 급여가 201~299가 아닌 행 조회
      order by sal;
      
      급여가 200 초과 300 미만이 아닌(200이하 또는 300이상) 사람의 사번, 급여를 급여순으로 조회하시오

문30) 다음의 SQL문을 분석하시오
      select empno, sal 
      from emp
      where not sal>200 and sal<300   --200이하이면서 300미만. -> 200이하 조회
      order by sal;
      
      급여가 200 초과가 아니면서 300 미만인 사람의 사번, 급여를 급여순으로 조회하시오

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

    SELECT deptno, SUM(sal)
    FROM emp
    GROUP BY deptno;

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

    SELECT deptno, SUM(sal)       --         4)
    FROM emp                      --실행순서 1)
    GROUP BY deptno               --         2)
    HAVING sum(sal)>=1500;        --         3)
    
문33) 부서코드별 급여평균이 300이상 조회하시오

    SELECT deptno, AVG(sal)
    FROM emp
    GROUP BY deptno
    HAVING AVG(sal)>=300;
    
문34) 급여가 300이상 데이터중에서 부서코드별 급여평균을 구해서 급여평균순으로 조회하시오

    SELECT deptno, AVG(sal)
    FROM emp
    WHERE sal>=300
    GROUP BY deptno
    ORDER BY AVG(sal);
    
문35) 급여가 300이상 데이터중에서 부서코드별 급여평균이 400이상을 급여순으로 조회하시오

    SELECT deptno, AVG(sal)
    FROM emp
    WHERE sal>=300
    GROUP BY deptno
    HAVING AVG(sal)>=400
    ORDER BY AVG(sal);
    
문36) hiredate칼럼을 사용하여 월별로 입사한 인원수를 구하시오
    -- 변환함수 : to_char(원래날짜, '원하는모양')
        
    --입사일 조회
    SELECT hiredate FROM emp;
    
    --입사일에서 월 가져오기
    SELECT to_char(hiredate, 'mm') FROM emp;
    SELECT to_char(hiredate, 'mm') AS 월 FROM emp;
    
    SELECT to_char(hiredate, 'yy') AS 입사년도 FROM emp;
    SELECT to_char(hiredate, 'dd') AS 일 FROM emp;
    
    --입사월을 정렬해서 조회
    SELECT to_char(hiredate, 'mm')
    FROM emp
    ORDER BY TO_CHAR(hiredate, 'mm');
    
    --입사월별로 그룹화 하기
    SELECT to_char(hiredate, 'mm')
    FROM emp
    GROUP BY TO_CHAR(hiredate, 'mm');
    
    --입사월별로 그룹화하고, 행 갯수 구하기(인원수) & 인원수 순으로 정렬    
    SELECT to_char(hiredate, 'mm') AS 입사월, COUNT(*) AS 인원수
    FROM emp
    GROUP BY to_char(hiredate, 'mm')
    ORDER BY COUNT(*);
    
문37) 매니저별 담당인원수를 조회하시오

    SELECT mgr, COUNT(*)
    FROM emp
    GROUP BY mgr;
    
문38) 사원번호 7654의 급여보다 적은 행을 조회하시오

    SELECT sal FROM emp WHERE empno=7654;  --400
    
    SELECT empno, ename, sal
    FROM emp
    WHERE sal<(SELECT sal FROM emp WHERE empno=7654);
    
문39) 부서별로 급여+커미션를 구했을때  최대값, 최소값, 평균값(반올림 해서)을 부서순으로 조회하시오

    SELECT deptno, sal, comm, sal+nvl(comm, 0) FROM emp ORDER BY deptno;
    
    SELECT deptno, MAX(sal+nvl(comm, 0)) 최대값, MIN(sal+nvl(comm, 0)) 최소값, ROUND(AVG(sal+nvl(comm, 0))) 평균값
    FROM emp
    GROUP BY deptno
    ORDER BY deptno;
    
문40) 각 직원들에 대해서 직원의 이름과 근속년수를 구하시오
      단, 근속년수는 연단위를 버림하여 나타내시오
      --한 번에 조회하려고 하지 말고 순차적으로 데이터를 보면서 하자
      
      --이름, 입사일을 입사일 순으로 조회
      SELECT ename, hiredate
      FROM emp
      ORDER BY hiredate;
      
      --근속일 : 현재 날짜-입사일
      SELECT ename, hiredate, sysdate-hiredate FROM emp;
      
      --(현재 날짜-입사일)/365
      SELECT ename, hiredate, (sysdate-hiredate)/365 FROM emp;
      
      --(현재 날짜-입사일)/365 -> 소수점 버림
      SELECT ename, hiredate 입사일, TRUNC((sysdate-hiredate)/365) 근속년수 FROM emp;
      
      --근속년도 순으로 내림차순 정렬
      SELECT ename, hiredate 입사일, TRUNC((sysdate-hiredate)/365) 근속년수
      FROM emp
      ORDER BY 근속년수 DESC;
      --------------------------------------------------------------------------------------      
      SELECT ename, sysdate, hiredate, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) 근속년수
      FROM emp;
      
문41) 아래와 같이 출력 하시오
      예) 박지성의 근속년수 : 20년
      
      SELECT ename, hiredate FROM emp ORDER BY hiredate;
      SELECT ename, sysdate-hiredate FROM emp ORDER BY hiredate;
      SELECT ename, (sysdate-hiredate)/365 FROM emp ORDER BY hiredate;
      SELECT ename, trunc((sysdate-hiredate)/365) FROM emp ORDER BY hiredate;
      SELECT ename || '의 근속년수 ' || trunc((sysdate-hiredate)/365) || '년' FROM emp ORDER BY hiredate;
      SELECT ename || '의 근속년수 ' || trunc((sysdate-hiredate)/365) || '년' AS 근속년수 FROM emp ORDER BY hiredate;
      -----------------------------------------------------------------------------------------------------------
      SELECT ename || '의 근속년수 : ' || TRUNC((sysdate-hiredate)/365) || '년'
      FROM emp;
      ----------------------------------------------------------------------------------------
      SELECT ename || '의 근속년수 : ' || TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) || '년'
      FROM emp;
      
문42) 손흥민의 근속년수와 동일한 행을 조회(이름, 근속년수)하시오

      --손흥민 조회하기
      SELECT * FROM emp WHERE ename='손흥민';
      
      --손흥민 근속년수 구하기
      SELECT TRUNC((sysdate-hiredate)/365)
      FROM emp
      WHERE ename='손흥민';          --20
      
      SELECT ename, TRUNC((sysdate-hiredate)/365) 근속년수
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365)=(SELECT TRUNC((sysdate-hiredate)/365)
                                                         FROM emp
                                                         WHERE ename='손흥민');
                                                         
문43) 입사한지 만15년 이상된 사람에 한해 현재연봉에서 10% 인상시켰을 때 
      사번, 이름, 입사일, 현재연봉, 인상후연봉, 인상된금액으로 고액연봉순으로 조회하시오
      연봉구하는 식 : 급여(sal)*12개월+보너스(comm)
      
      --입사한지 만15년 이상된 사람 조회하기
      SELECT ename, hiredate, sal
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15;
      
      --현재 연봉 구하기
      SELECT ename, hiredate, sal, sal*12+comm
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15;
      
      --현재 연봉 구하기(단, 커미션이 없으면 0으로 바꿔서 계산)
      SELECT ename, hiredate, sal, sal*12+nvl(comm, 0) AS 현재연봉
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15;
      
      --현재 연봉에서 10% 인상된 금액
      SELECT ename, hiredate, sal
      , sal*12+nvl(comm, 0) AS 현재연봉
      , (sal*12+nvl(comm, 0))*0.1 AS 인상된금액
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15;
      
      --현재 연봉 + 인상된 금액 = 최종 연봉(인상 후 연봉)
      SELECT ename, hiredate, sal
      , 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 인상후연봉
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15;
      
      --최종연봉 소수점없이 반올림
      SELECT ename, hiredate, sal
      , 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) AS 인상후연봉
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15; 
      
      --최종연봉 내림차순 정렬하기
      SELECT ename, hiredate, sal
      , 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) AS 인상후연봉
      FROM emp
      WHERE TRUNC((sysdate-hiredate)/365) >= 15
      ORDER BY 인상후연봉 DESC; 
      -------------------------------------------------------------------------------------------------------------
      SELECT empno, ename, hiredate, sal, sal*12+NVL(comm, 0) AS 현재연봉, (sal*12+NVL(comm, 0))*1.1 AS 인상후연봉, (sal*12+NVL(comm, 0))*0.1 AS 인상된금액
      FROM emp
      WHERE TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12)>=15
      ORDER BY 인상된금액 DESC;
      
      select * from emp;
      
문44) 입사년도가 짝수인 직원들의 급여의 평균을 job별로 출력하시오

    --직급, 입사일 조회하기
    SELECT JOB, hiredate FROM emp;
    
    --입사일에서 년도 추출하기
    SELECT hiredate, EXTRACT(YEAR FROM hiredate) FROM emp;
    
    --짝수년도 추출하기
    SELECT job, hiredate, EXTRACT(YEAR FROM hiredate)
    FROM emp
    WHERE MOD(EXTRACT(YEAR FROM hiredate), 2)=0;     --mod() 나머지 연산
    
    --직급별 급여의 평균 구하기    
    SELECT job, AVG(sal)
    FROM emp
    WHERE MOD(EXTRACT(YEAR FROM hiredate), 2)=0
    GROUP BY job;

 

연산자 우선순위

() 괄호 : 연산자 우선순위보다 우선함
1순위 : 비교연산자, SQL연산자, 산술연산자
2순위 : not
3순위 : and
4순위 : or
5순위 : 결합연산자