-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12
-
13
-
14
-
15
-
16
-
17
-
18
-
19
-
20
-
21
-
22
-
23
-
24
-
25
-
26
-
27
-
28
-
29
-
30
-
31
-
32
-
33
-
34
-
35
-
36
-
37
-
38
-
39
본문내용
sal and j.highest_sal
4.10
select last_name, hire_date
from employees
where hire_date > (select hire_date from employees where last_name = 'Davies')
4.11
select e.last_name, e.hire_date, m.last_name, m.hire_date
from employees e , employees m
where e.manager_id = m.employee_id
and (e.hire_date < m.hire_date)
5장 그룹 함수를 사용한 데이터 집계
5.1 그룹 함수는 여러 행에 적용되어 그룹 당 하나의 결과를 출력합니다.
True
5.2 그룹 함수는 계산에 널을 포함합니다.
False
5.3 where절은 그룹 계산에 행을 포함시키기 전에 행을 제한합니다.
True
5.4
select max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", avg(salary) as "Average"
from employees
5.5
select job_id, max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", avg(salary) as "Average"
from employees
group by job_id
5.6
select job_id, count(*)
from employees
group by job_id
5.7
select count(distinct manager_id ) as "Number of Managers"
from employees
5.8
select max(salary)-min(salary) difference
from employees
5.9
select manager_id, min(salary)
from employees
having min(salary) > 6000
and manager_id is not null
group by manager_id
order by min(salary) desc
5.10
select b.department_name name, b.location_id location, count(employee_id) NumbeR_Of_people, round(avg(a.salary),2) salary
from employees a,departments b
where a.department_id = b.department_id
group by b.department_name,b.location_id
5.11
select count(employee_id) total, sum(decode(to_char(hire_date,'yyyy'),'1995',1,0)) "1995", sum(decode(to_char(hire_date,'yyyy'),'1996',1,0)) "1996", sum(decode(to_char(hire_date,'yyyy'),'1997',1,0)) "1997", sum(decode(to_char(hire_date,'yyyy'),'1998',1,0)) "1998" from employees
5.12
select job_id as "job"
,sum(decode(department_id, 20, salary)) "dept20"
,sum(decode(department_id, 50, salary)) "dept50"
,sum(decode(department_id, 80, salary)) "dept80"
,sum(decode(department_id, 90, salary)) "dept90"
,sum(salary) as "Total"
from employees
group by job_id;
6장 서브쿼리
6.1
select last_name, hire_date
from employees
where department_id in(select department_id
from employees where last_name='Zlotkey')
and last_name <> 'Zlotkey'
order by last_name;
6.2
select employee_id, last_name, salary
from employees
where salary >(select avg(salary)from employees)
order by salary asc
6.3
select employee_id, last_name
from employees
where department_id in(select department_id
from employees
where last_name like '%u%')
order by department_id
6.4
select last_name, department_id, job_id from employees
where department_id in (select department_id from departments
where location_id = '1700')
6.5
select last_name, salary
from employees
where manager_id in (select manager_id from employees where manager_id = '100')
6.6
select department_id, last_name, job_id
from employees
where department_id in(select department_id from departments where department_name = 'Executive')
6.7
select employee_id, last_name,salary
from employees
where salary > (select avg(salary) from employees)
and department_id in(select department_id
from employees
where last_name like '%u%')
order by department_id
4.10
select last_name, hire_date
from employees
where hire_date > (select hire_date from employees where last_name = 'Davies')
4.11
select e.last_name, e.hire_date, m.last_name, m.hire_date
from employees e , employees m
where e.manager_id = m.employee_id
and (e.hire_date < m.hire_date)
5장 그룹 함수를 사용한 데이터 집계
5.1 그룹 함수는 여러 행에 적용되어 그룹 당 하나의 결과를 출력합니다.
True
5.2 그룹 함수는 계산에 널을 포함합니다.
False
5.3 where절은 그룹 계산에 행을 포함시키기 전에 행을 제한합니다.
True
5.4
select max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", avg(salary) as "Average"
from employees
5.5
select job_id, max(salary) as "Maximum", min(salary) as "Minimum", sum(salary) as "Sum", avg(salary) as "Average"
from employees
group by job_id
5.6
select job_id, count(*)
from employees
group by job_id
5.7
select count(distinct manager_id ) as "Number of Managers"
from employees
5.8
select max(salary)-min(salary) difference
from employees
5.9
select manager_id, min(salary)
from employees
having min(salary) > 6000
and manager_id is not null
group by manager_id
order by min(salary) desc
5.10
select b.department_name name, b.location_id location, count(employee_id) NumbeR_Of_people, round(avg(a.salary),2) salary
from employees a,departments b
where a.department_id = b.department_id
group by b.department_name,b.location_id
5.11
select count(employee_id) total, sum(decode(to_char(hire_date,'yyyy'),'1995',1,0)) "1995", sum(decode(to_char(hire_date,'yyyy'),'1996',1,0)) "1996", sum(decode(to_char(hire_date,'yyyy'),'1997',1,0)) "1997", sum(decode(to_char(hire_date,'yyyy'),'1998',1,0)) "1998" from employees
5.12
select job_id as "job"
,sum(decode(department_id, 20, salary)) "dept20"
,sum(decode(department_id, 50, salary)) "dept50"
,sum(decode(department_id, 80, salary)) "dept80"
,sum(decode(department_id, 90, salary)) "dept90"
,sum(salary) as "Total"
from employees
group by job_id;
6장 서브쿼리
6.1
select last_name, hire_date
from employees
where department_id in(select department_id
from employees where last_name='Zlotkey')
and last_name <> 'Zlotkey'
order by last_name;
6.2
select employee_id, last_name, salary
from employees
where salary >(select avg(salary)from employees)
order by salary asc
6.3
select employee_id, last_name
from employees
where department_id in(select department_id
from employees
where last_name like '%u%')
order by department_id
6.4
select last_name, department_id, job_id from employees
where department_id in (select department_id from departments
where location_id = '1700')
6.5
select last_name, salary
from employees
where manager_id in (select manager_id from employees where manager_id = '100')
6.6
select department_id, last_name, job_id
from employees
where department_id in(select department_id from departments where department_name = 'Executive')
6.7
select employee_id, last_name,salary
from employees
where salary > (select avg(salary) from employees)
and department_id in(select department_id
from employees
where last_name like '%u%')
order by department_id
소개글