select to_char(trunc(avg(salary)), '999,999') 사원급여평균 from employees
select department_id 부서ID, to_char(trunc(avg(salary)), '999,999') 부서별급여평균 from employees group by department_id order by 2
select job_id 업무ID, to_char(sum(salary), 'L999,999') 급여합계 from employees group by job_id order by 2
select last_name, to_char(trunc(avg(salary)), '999,999') 부서별급여평균 from employees group by department_id -- 데이터를 묶는다 -- 부서를 묶으면 사원이 안나옴
*실행이 되지 않는 코드! 부서를 묶은 데이터만 select 가능.
select department_id 부서ID, to_char(trunc(avg(salary))) "평균급여" from employees group by department_id having avg(salary)>=6000;
select department_id 부서코드, to_char(round(avg(salary)), 'L999,999') "평균급여" from employees group by department_id having avg(salary) >=5000 order by 1;
select department_id, max(salary) max_salary from employees group by department_id -- 부서를 전부 묶은다음 having department_id = 10 or department_id = 20 -- 10번, 20번을 뽑아낸다. order by department_id -- 묶는 횟수가 많을 수록 비효율적이다. 20만건이면.. - --효율적인 방법 select department_id, max(salary) max_salary from employees where department_id = 10 or department_id = 20 -- 부서가 10, 20 뽑아낸 다음 묶음 group by department_id -- 그룹화 횟수가 줄어든다. order by 1;
select department_id, max(salary) "max_salary" from employees where department_id in(10, 20) group by department_id having max(salary) >=5000 order by 1;
select department_id, min(salary) min_salary from employees group by department_id having 20<=department_id and department_id <=80 order by 1;
-- 오라클 구문 select employee_id, employees.department_id, department_name from employees, departments where employees.department_id=departments.department_id; - select employee_id, e.department_id, department_name from employees e, departments d where e.department_id=d.department_id; - --ANSI 표준 select employee_id, department_id, department_name from employees inner join departments using(department_id) -- inner 생략 가능
-- 오라클 구문 select department_id, city from departments d, locations l where d.location_id = l.location_id -- 같은 것에 대해서 조인을 해달라 order by 1 desc - -- ansi 표준 select department_id, city from departments join locations using(location_id) order by 1
select last_name, e.department_id, department_name from employees e, departments d where e.department_id = d.department_id(+); - select * from departments - select last_name, department_id, department_name from employees left join departments using(department_id) -- left outter join으로 표기 가능
select last_name, e.department_id, department_name from employees e, departments d where e.department_id(+) = d.department_id - select last_name, department_id, department_name from employees right join departments using(department_id) -- right outter join으로 표기 가능
select last_name, department_id, department_name from employees full join departments using(department_id)
select location_id, city, department_name from departments join locations using(location_id);
-- 오라클 구문
select last_name, d.department_id, d.manager_id
from departments d, employees e
where (d.department_id = e.department_id) and (d.manager_id = e.manager_id)
--ansi 표준
select last_name, department_id, manager_id
from employees
join departments using(department_id, manager_id);
--자연조인
select last_name, department_id, manager_id
from employees
natural join departments
create table locations2
as
select * from locations; -- 테이블 복사
select * from locations2;
alter table locations2
rename column location_id to loc_id;
--오라클 구문
select department_id 부서ID, department_name 부서명, city 도시
from departments d, locations2 l
where d.location_id = l.loc_id;
--ansi표준
select department_id 부서ID, department_name 부서명, city 도시
from departments
join locations2 on(location_id = loc_id) -- 컬럼명이 다른 경우 on 사용
--오라클 구문
select e.employee_id 사원번호, e.last_name 사원이름, m.last_name 관리자
from employees e, employees m
where e.manager_id = m.employee_id(+);
-- ansi 표준
select e.employee_id 사원번호, e.last_name 사원이름, m.last_name 관리자
from employees e
left join employees m on (e.manager_id = m.employee_id)
select count(*) from countries; -- 25
select count(*) from locations; -- 23
-- 오라클 구문
select * from countries, locations; -- 25*23 = 575
--ansi표준
select *
from countries
cross join locations;
create table salgrade(
salvel varchar2(2),
lowst number,
highst number);
insert into salgrade values('A',20000,29999);
insert into salgrade values('B',10000,19999);
insert into salgrade values('C',0,9999);
commit -- 우측 상단에 auto commit이 있으면 실행안해도 커밋
select * from salgrade;
select * from tab;
select last_name 사원이름,salary 급여, salvel "급여등급"
from employees
left join salgrade on (salary between lowst and highst)
order by salary desc;
select * from employees;
select * from jobs;
select * from departments;
select last_name 사원이름, department_name 부서이름, job_title 업무명
from employees
join departments using(department_id)
join jobs using(job_id)
order by 2, 3
; -- 세미콜론은 범위지정, 다른 편집기에선 컨트롤+엔터로 실행함.
select last_name 이름, department_name 부서이름, manager_id 매니저ID
from employees
join departments using (manager_id)
where department_name = 'IT'
order by 1
select department_id, city
from departments
join locations using (location_id)
where city = 'Seattle'
order by 1 desc
select last_name, department_id, department_name
from employees
join departments using(department_id)
where department_id in (30, 90)
order by 1;
select last_name 사원이름, city "도 시", department_name 부서이름
from employees
left join departments using (department_id) -- 이 결과를 바탕으로 아래와 조인,
left join locations2 on (location_id = loc_id)
where city in ('Seattle', 'Oxford')
order by 2
select employee_id 사원번호, last_name 사원이름, department_name 부서이름,
city 도시, street_address 도시주소, country_name 나라명
from employees
left join departments using(department_id)
left join locations2 on(loc_id =location_id)
left join countries using(country_id)
where street_address like '%St%' or street_address like '%Vi%'
order by 6, 4