Oracle Quiz5

Oracle에 sql전체적 문제

1. first_name컬럼값이 'J_ _n~~~'인 사원의 사번, 이름을 검색하시오.

select employee_id,first_name,last_name

from employees

where first_name like 'J__n%';

2. 수당을 받는 사원의 사번, 이름, 급여, 수당을 출력하시오

select employee_id,first_name,salary,commission_pct

from employees

where commission_pct is not null

3. 급여가 10000이상인 사원의 사번, 부서번호, 이름, 급여, 수당을 출력하시오.

단, 부서번호가 30번부서이거나 90번인 사원만 검색한다.

select employee_id,department_id,first_name,salary,commission_pct

from employees

where salary>=10000 and department_id between 30 and 90

4. 사원의 급여는 통화기호를 출력하고 15자리로늘려 빈칸은 *을 출력하고 천단위로,를 출력하고 소숫점1자리까지 출력한다.

[SQLPLUS로 확인해야 정확한 결과가 보임]

사번  이름    급여15자리

------------------------------

101             ******\24,000.0

:               *******\9,000.0 

:               ******\12,000.0

:               *******\9,000.0 

-------------------------------

select employee_id,first_name,lpad(ltrim(to_char(salary,'L99,999.9')),15,'*')

from employees

/*

LTRIM을 쓴이유

sql> select 24000, '24000' from dual;

      24000   |   '24000'

   -------------------

        24000 | 24000

숫자는오른쪽 정렬 문자는 왼쪽 정렬

to_char(salary,'L99,999.9')가 숫자(오정)가 문자(왼정)로 바뀌면서

공백포함 오른쪽정렬이 되어지게 된다.

그래서 ltrim으로 앞에 공백을 잘라야함.

*/

5. 30번, 90번 부서만 대상으로

각 부서의 부서번호, 인원수, 평균급여를 출력하시오.

select department_id, count(*),avg(salary)

from employees

where department_id in (30,90)

group by department_id

6. 부서평균급여가 9500이상인 부서대상으로 부서별 최대급여, 평균급여, 인원수를 출력하시오.

평균급여를 소숫점둘째자리에서 반올림한다

단, 각부서의 최대급여와 최소급여가 같은 부서는 제외한다.

select max(salary),round(avg(salary),2),count(*)

from employees

group by department_id

having avg(salary)>=9500 and max(salary)<>min(salary)

7. 년도별 입사자수를 출력하시오. 단,최근년도부터

select to_char(hire_date,'YY') 년도,count(*)

from employees

group by to_char(hire_date, 'YY')

order by 년도

8. 사원들의 사번과 이름, 매니저번호와 이름을 출력하시오.

select e.employee_id,e.first_name,e.manager_id,m.first_name

from employees e left join employees m on e.manager_id=m.employee_id

9. 사원들의 사번과 이름, 매니저번호을 출력하되, 매니저에 속한 사원들이 아래와 같이 출력되도록 한다. 

동일 매니저로 부터 관리받는 사원들은 늦은사번이 먼저 나타난다.

사번은 *기호로들여쓰기가 되도록한다.

        사번  이름                      관리자번호

        -------------------------------------------

        100 Steven King -

*201 Michael Hartstein 100

**202 Pat Fay        201

*149 Eleni Zlotkey        100

**179 Charles Johnson        149

**178 Kimberely Grant 149

**177 Jack Livingston 149

**176 Jonathon Taylor 149

  **175 Alyssa Hutton        149

**174 Ellen Abel        149

*148 Gerald Cambrault 100

**173 Sundita Kumar        148

select lpad(employee_id,2+level,'*') 사번, first_name, manager_id

from (select a.* from (select * from employees order by employee_id desc) a)

start with manager_id is null

connect by manager_id=prior employee_id

10. 'Sales'부서의 평균급여보다 많은 급여를 받는 'Sales'부서외의 사원들의 사번, 이름, 부서번호, 급여를 출력하시오.

select e.employee_id,e.first_name,e.department_id,e.salary

from employees e join departments d on e.department_id=d.department_id

where d.department_name<>'Sales' 

      and e.salary > (select avg(e.salary) from employees e join departments d on e.department_id=d.department_id

                       where d.department_name='Sales' group by e.department_id)