SQL 문제 4-5일차

4일차

 

1. 사원 테이블에서 각 사원에 급여(SAL)가 높은 순서대로 상위 5명을 아래 예제처럼 출력하세요? 
ex)

 

select * from

(select EMP.* from emp order by sal desc)
where rownum between 1 and 5;

2. 사원 테이블에서 각 사원에 급여(SAL)가 높은 순서대로 순위를 부여 했을 때 6등~10등인 사람을 순위대로 아래 예제처럼 출력하세요? 
    ex)

 

select * from 
(select EMP.*, ROW_NUMBER() over (order by SAL desc) as RN from EMP)
where rn between 6 and 10;

3. 아래 SQL 실행 했을 경우 0건 출력되는 이유를 설명하세요?
ex) 
    SELECT ROWNUM, A.* 
FROM   EMP A
WHERE  ROWNUM > 5

 

--rownum은 where절을 만족하는 레코드에 붙이는 순번으로,
--rownum이 ROWNUM > 5; 으로 시작해서 6의 값을 가질때
처음 레코드는 rownum이 1이라 조건이 맞지 않으므로 버리게 된다.
그 다음 레코드도 선택 후 rownum이 6인지 비교하지만 
그전 레코드를 버렸기 때문에 또 조건이 맞지 않아 버리게 되어 
0건이 출력된다.



4. SALGRADE 테이블 데이터 세로 정보를 가로로 아래 예제처럼 출력하세요?
ex)

 

-->

    

 

select * from
(select LOSAL||'~'||HISAL AS LOHISAL, GRADE from SALGRADE)
PIVOT
(MAX(LOHISAL)
for GRADE
in ('1' GRADE_1, '2' GRADE_2, '3' GRADE_3, '4' GRADE_4, '5' GRADE_5));

5. SALGRADE_TEMP 테이블을 아래처럼 만들고,위의 예제와 반대로  데이터 가로 정보를 세로로 아래 예제처럼 출력되게 SQL을 작성하세요?
ex)

-->

테이블 생성

 

create table SALGRADE_TEMP (
GRADE_1 varchar(200) not null,
GRADE_2 varchar(200) not null,
GRADE_3 varchar(200) not null,
GRADE_4 varchar(200) not null,
GRADE_5 varchar(200) not null,
primary key(GRADE_1));

insert into SALGRADE_TEMP (GRADE_1, GRADE_2, GRADE_3, GRADE_4, GRADE_5)
values('700~1200', '1201~1400', '1401~2000', '2001~3000', '3001~9999');


select * from SALGRADE;
select * from SALGRADE_TEMP;

 

 

-->

출력

 

SELECT * from SALGRADE_TEMP
unpivot
(
LOHISAL
for GRADE 
in (GRADE_1 as '1', GRADE_2 as '2' , GRADE_3 as '3' , GRADE_4 as '4' , GRADE_5 as '5' )
);

 

 

 

---

 

5일차

 

1. 사원 테이블에서 EMPNO, MGR, SAL 세 개의 컬럼을 단순 숫자 의미로 가정 할 경우 세 개의 값 중 최대값(MAX_VALUE), 최소값(MIN_VALUE) 을 아래 예제처럼 출력하세요?

ex) EMPNO, MGR, SAL 컬럼 중 널 값이 존재할 경우 0으로 치환
       정렬은 최대값 내림차순

 

 

SELECT EMPNO, MGR, SAL,
       GREATEST(EMPNO, NVL(MGR, 0), SAL) MAX_VALUE,
       LEAST(EMPNO, NVL(MGR, 0), SAL) MIN_VALUE
FROM EMP
ORDER BY MAX_VALUE DESC;

 

 

   

2. 부서 테이블에서 DNAME 컬럼의 길이가 6자리 이상이면은 5자리까지 데이터만 보여주고 ‘..’ 뒤에 붙여준다. 아래 예제처럼 출력하세요?

ex)

 

SELECT dname, CONCAT(SUBSTR(dname, 1, 5), '..')
FROM dept;

 

 

 

3. 사원 테이블에서 고용일자(HIREDATE) 해당 년 월의 마지막 날짜(HIREDATE_MONTH_LASTDAY), 고용일자부터 2006/08/05

일자까지 근무일(WORK_DAY)을 아래 예제처럼 출력하세요?

ex)

 

SELECT empno, TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') HIREDATE,
              TO_CHAR(LAST_DAY(HIREDATE), 'YYYY/MM/DD HH24:MI:SS') HIREDATE_MONTH_LASTDAY
            , TO_DATE('2006/08/05','YYYY/MM/DD') - hiredate WORK_DAY
FROM emp;

 

 

 

4. 3번 예제에서 구한 각 사원에 근무일(WORK_DAY)이 가장 높은 사람을 한 명을 아래 예제처럼 출력하세요?

ex)

 

SELECT EMPNO,
    TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') HIREDATE,
    TO_DATE('06/08/05') - TO_DATE(HIREDATE) WORK_DAY
FROM EMP
WHERE TO_DATE('06/08/05') - TO_DATE(HIREDATE)
    = (SELECT MAX(TO_DATE('06/08/05') - TO_DATE(HIREDATE)) FROM EMP);

 

 

 

5. 3번 예제에서 구한 각 사원에 근무일(WORK_DAY)이 가장 높은 사람과 낮은 사람을 각각 한 명씩 아래 예제처럼 출력하세요?

ex)

 

SELECT EMPNO, TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') HIREDATE,
    TO_DATE('06/08/05') - TO_DATE(HIREDATE) WORK_DAY
FROM EMP
WHERE TO_DATE('06/08/05') - TO_DATE(HIREDATE)
IN ((SELECT MAX(TO_DATE('06/08/05') - TO_DATE(HIREDATE)) FROM EMP),
    (SELECT MIN(TO_DATE('06/08/05') - TO_DATE(HIREDATE)) FROM EMP));