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
- 매니저의 사번 및 그 매니저 밑 사원들 중 최소 급여를 받는 사원의 급여를 출력하시오.
- 조건
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.