1차 정렬, 2차 정렬

 

 

-- employees 테이블에서 부서번호별 오름차순 정렬을 한 후 동일한 부서번호 내에서는 
     -- 월급의 내림차순으로 정렬하여 사원번호, 사원명, 월급, 부서번호를 나타내시오

 

 

	select employee_id
          , first_name
          , last_name
          , nvl( salary + (salary * commission_pct), salary ) as monthly_salary
          , department_id
     from employees
     order by 5 asc, 4 desc;
 /*         1차정렬, 2차정렬
       또는
     order by 5, 4 desc;  --- asc 는 생략가능
            1차정렬, 2차정렬 */

 

   -- employees 테이블에서 수당퍼센티지가 null 인 사원들만 
   -- 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되 
   -- 부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.

 

   select employee_id 사원번호
          , first_name || ' ' || last_name 사원명
          , nvl( salary + (salary * commission_pct), salary ) 월급
          , department_id 부서번호
   from employees
   where commission_pct is null
   order by 4, 3 desc;

 

 

       -- employees 테이블에서 수당퍼센티지가 null 이 아닌 사원들만 
   -- 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되 
   -- 부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.

  select employee_id 사원번호
          , first_name || ' ' || last_name 사원명
          , nvl( salary + (salary * commission_pct), salary ) as 월급
          , department_id 부서번호
   from employees
   where commission_pct is not null
   order by 4, 3 desc;

 

  -- employees 테이블에서 월급(기본급여+수당금액)이 10000 보다 큰 사원들만 
   -- 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되 
   -- 부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.

   select employee_id 사원번호
          , first_name || ' ' || last_name 사원명
          , nvl( salary + (salary * commission_pct), salary ) as 월급
          , department_id 부서번호
   from employees
   where nvl( salary + (salary * commission_pct), salary ) > 10000
   order by 4, 3 desc;

 

  -- employees 테이블에서 부서번호가 50번 부서가 아닌 사원들만 
   -- 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되 
   -- 부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.
  

  select employee_id
          , first_name || last_name
          , nvl( salary + (salary * commission_pct), salary ) as monthly_salary
          , department_id
   from employees
   where nvl(department_id, -9999) != 50   -- !! 주의 !! 부서번호 null 인 것도 고려해야함 
   order by 4, 3 desc;