- 4 일차 SQL 과제 –

-     4 일차 SQL 과제 –

 

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

 

SELECT  e.empno

        , e.ename

        , e.job

        , e.mgr

        , e.hiredate

        , e.sal

        , e.comm

        , e.deptno 

FROM emp e RIGHT JOIN (

                       SELECT empno,RANK() OVER (ORDER BY sal asc)  aa

                        FROM emp

                        ) i

ON e.empno = i.empno     

WHERE aa<=5;

  

 

 

 

 

 

 

 

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

SELECT *

FROM (SELECT empno

             , ename

             , job

             , mgr

             , hiredate

             , sal

             , comm

             , deptno

             , RANK() OVER(ORDER BY sal asc) RN

      FROM emp

      )

WHERE RN BETWEEN 6 AND 10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

ex)

     

 

è 

 

 

SELECT *

 

FROM (SELECT (CASE grade

               WHEN 1 THEN '700~1200'

               END) AS GRADE_1

               ,(CASE grade

               WHEN 1 THEN '1201~1400'

               END) AS GRADE_2

               ,(CASE grade

               WHEN 1 THEN '1401~2000'

               END) AS GRADE_3

               ,(CASE grade

               WHEN 1 THEN '2001~3000'

               END) AS GRADE_4

               ,(CASE grade

               WHEN 1 THEN '3001~9999'

               END) AS GRADE_5

        FROM salgrade

        )

WHERE GRADE_1 IS NOT NULL  

 

 

또는

 

SELECT MAX(CASE grade

               WHEN 1 THEN '700~1200'

               END) AS GRADE_1

               ,MAX(CASE grade

               WHEN 2 THEN '1201~1400'

               END) AS GRADE_2

               ,MAX(CASE grade

               WHEN 3 THEN '1401~2000'

               END) AS GRADE_3

               ,MAX(CASE grade

               WHEN 4 THEN '2001~3000'

               END) AS GRADE_4

               ,MAX(CASE grade

               WHEN 5 THEN '3001~9999'

               END) AS GRADE_5

        FROM salgrade

 

 

이게 답이다..밑에꺼..

 

SELECT MAX( DECODE(grade,1, concat(concat(losal, '~'), hisal))) AS GRADE_1

              ,   MAX( DECODE(grade,2, concat(concat(losal, '~'), hisal))) AS GRADE_2

              ,    MAX( DECODE(grade,3, concat(concat(losal, '~'), hisal))) AS GRADE_3

              ,     MAX( DECODE(grade,4, concat(concat(losal, '~'), hisal))) AS GRADE_4

              ,      MAX( DECODE(grade,5, concat(concat(losal, '~'), hisal))) AS GRADE_5

FROM salgrade