Oracle DB 문제풀이

SQL 문제풀이

문제 1

• 100번 부서를 제외한 각 부서별 평균 급여가 7,000 이상인 부서별 평균 급여를 출력하시오.

  1  select department_id, avg(salary) as avg
  2  from employees
  3  where department_id != 100		// notin(100) or not like 도 가능하나 성능이 안 좋음...
  4  group by department_id
  5* having avg(salary) >= 7000

SQL> // result

DEPARTMENT_ID        AVG
------------- ----------
           90 19333.3333
           20       9500
           70      10000
          110      10154
           80 8955.88235

문제 2

• 50번 부서에 근무하는 매니저별 평균 급여를 출력하시오.

  1  select manager_id, avg(salary) as avg
  2  from employees
  3  where department_id = 50	// 행에 대한 조건
  4* group by manager_id	// 매니저 별로 그룹화
  
SQL> // result

MANAGER_ID        AVG
---------- ----------
       100       7280
       123     3237.5
       120     2762.5
       121       3175
       124       2875
       122       2950

6 rows selected.

문제 3

• 동일한 직업을 가진 사원들의 총 수를 출력하시오.

  1  select count(*) as num, job_id	// count(employee_id) 가 더 좋다.
  2  from employees		// 왜냐면 결과값에 보이는 column heading을 보고 판단을 하는데 *를 사용하면
  3* group by job_id	// 동일한 직업을 가진 어떤 수인지를 알 수 없기 때문이다.

SQL> // result

       NUM JOB_ID
---------- --------------------
         1 AC_ACCOUNT
         1 AC_MGR
         1 AD_ASST
         1 AD_PRES
         2 AD_VP
         5 FI_ACCOUNT
         1 FI_MGR
         1 HR_REP
         5 IT_PROG
         1 MK_MAN
         1 MK_REP

       NUM JOB_ID
---------- --------------------
         1 PR_REP
         5 PU_CLERK
         1 PU_MAN
         5 SA_MAN
        30 SA_REP
        20 SH_CLERK
        20 ST_CLERK
         5 ST_MAN

19 rows selected.

문제 4

• 직원이 4명 이상인 부서의 부서번호와 인원을 출력하시오.

  1  select count(employee_id), department_id		// 인원수를 셀꺼임. 
  2  from employees	
  3  group by department_id		// "부서의 "의 조건의 그룹화
  4* having count(employee_id)>=4		// 단 인원수가 4명 이상인,
  
// 만약 count(department_id)가 된다면 부서번호 개수가 4개 이상인? 이상한 쿼리가 완성됨.

SQL> // result

COUNT(EMPLOYEE_ID) DEPARTMENT_ID
------------------ -------------
                 6           100
                 6            30
                45            50
                34            80
                 5            60

28. 매니저로 근무하는 사원들의 총 수를 출력하시오.

  1  select count(distinct manager_id) "Manager count"
  2* from employees
SQL> /

Manager count
-------------
           18

문제 5

• 29. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력하시오.
• 조건
  1.매니저가 없는 사람들은 제외한다.
  2.최소 급여가 5000미만인 경우는 제외한다.
  3.금여 기준 역순으로 조회한다.

  1  select manager_id, min(salary)		
  2  from employees
  3  where manager_id is not null		// 조건 1
  4  group by manager_id		// 매니저로 그룹화
  5  having min(salary) >= 5000		// 조건 2
  6* order by min(salary) desc				// 조건 3 그런데
// order by 절은 항상 select 절에 있는 애들 중에 갖고 오기때문에 반드시 select list중에서 하나를 갖고와야한다

SQL> // result

MANAGER_ID MIN(SALARY)
---------- -----------
       102        9000
       205        8300
       145        7000
       146        7000
       108        6900
       147        6200
       149        6200
       148        6100
       201        6000
       100        5800

10 rows selected.

문제 6

• 부서 명, 부서위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 급여를 출력하되, 부서위치를 오름차순으로 출력하시오.

  1  select d.department_name, d.location_id, count(e.employee_id), avg(e.salary) as avg_salary
  2  from employees e, departments d
  3  where e.department_id = d.department_id		// department_id로 table 연결
  4  group by d.department_name, d.location_id		// 그룹함수가 없는 2개의 column으로 그룹화
  5* order by d.location_id

SQL> // result

DEPARTMENT_NAME      LOCATION_ID COUNT(E.EMPLOYEE_ID) AVG_SALARY
-------------------- ----------- -------------------- ----------
IT                          1400                    5       5760
Shipping                    1500                   45 3475.55556
Accounting                  1700                    2      10154
Administration              1700                    1       4400
Executive                   1700                    3 19333.3333
Finance                     1700                    6 8601.33333
Purchasing                  1700                    6       4150
Marketing                   1800                    2       9500
Human Resources             2400                    1       6500
Sales                       2500                   34 8955.88235
Public Relations            2700                    1      10000

11 rows selected.