23.01.18

학습내용

1. outer join
2. subquery
3. Single Row Subquery
4. Mulit Row Subquery
5. mulit column subquery
6. inlineview
7. sql 과제

Outer Join

INSERT INTO c_emp VALUES(201,'신입1',1500,'3429-0100',null);
INSERT INTO c_emp VALUES(202,'신입2',1500,'3429-0200',null);

select c.name 이름, s.dept_name 부서명 from c_emp c, s_dept s where c.dept_id = s.id; // null의 연산은 항상 false라 신입이 출력 안된다

select c.name 이름, s.dept_name 부서명 from c_emp c, s_dept s where c.dept_id = s.id(+); 신입사원까지 출력() //outer join

조건에 일치하는 데이터만 출력하는것이 아닌 조건에 해당하지 않는 데이터도 null로 처리하여 출력한다.

Subquery

select c.id 사번, c.name 이름
from c_emp c
where c.dept_id = ( select dept_id from c_emp c where c.name = '이순신');

sql문에 또 다른 sql문을 추가하여 원하는 결과를 가져온다.

Single Row Subquery

select
name 이름, dept_id 부서아이디
from c_emp 
where dept_id = (select id from s_dept  where dept_name IN('총무부', '영업부'));

error : single-row subquery returns more than one row
//서브쿼리의 결과가 여러개이기에 에러 발생

서브 쿼리의 결과가 한개인 쿼리를 Single Row Subquery라 한다.

Mulit Row Subquery

select
name 이름, dept_id 부서아이디
from c_emp 
where dept_id IN (select id from s_dept  where dept_name IN('총무부', '영업부'));

서브쿼리의 결과가 여러개인 쿼리를 Mulit Row Subquery라 한다

3. Multi Column Subquery

   select 
   name, salary, dept_id
   from c_emp
   where (salary, dept_id) IN (select MIN(salary), dept_id from c_emp group by c_emp.dept_id);

   여러개의 컬럼들을 검색하는 서브쿼리

4. Inline View

select MIN(salary), dept_id 
from c_emp c (select dept_id, MIN(salary) min from c_emp group by dept_id) vt
where c.dept_id = vt.dept_id and c.salary = vt.min;

from절에서 서브쿼리를 통해 사용자 임의의 가상 테이블을 만들어 사용한다.

과제

select * from tab; //내가 가지고있는 테이블을 보여줌

select empno 사번, ename 이름, mgr 관리자사번 from where emp order by empno;

select e1.empno 사번, e1.ename 이름, e1.mgr 관리자사번, e2.ename "관리자이름 조회"  
from emp e1, emp e2
where e1.mgr = e2.empno(+)
order by e1.empno;


select c2.id 사번, c2.name 이름
from c_emp c1, c_emp c2
where c1.name ='이순신'and c1.dept_id = c2.dept_id;


select c.name 이름, s.dept_name 부서명
from c_emp c, s_dept s
where c.dept_id = s.id and c.dept_id = (select c.dept_id from c_emp c where c.name = '이순신');


select *
from c_emp c, s_dept s, sal_grade sal
where c.dept_id = s.id and c.salary between sal.losal and sal.hisal; 


select c_emp.name, sal_grade.grade
from c_emp, sal_grade
where c_emp.salary between sal_grade.losal and sal_grade.hisal
and c_emp.name = '홍길동';


1장

1-1
select e.employee_id 사번, e.department_id 부서번호, e.salary*12  연봉, round(vt.maxsal) 부서평균연봉
from employees e, (select employees.department_id id, AVG(salary) maxsal from employees group by employees.department_id) vt
where e.salary > vt.maxsal and e.department_id = vt.id;


1-2
select vt.ranking, vt.last_name, vt.first_name, vt.salary
from (select RANK() OVER(order by e.salary desc) ranking ,e.last_name, e.first_name, e.salary from employees e) vt
where vt.ranking between 6 and 10;


1-3
select extract(year from vt.hire_date) 년도, AVG(vt.avg) 평균급여
from 
(select e.hire_date hire_date, AVG(e.salary) avg
from employees e
where e.job_id = 'SA_MAN'group by e.hire_date) vt
group by extract(year from vt.hire_date)
order by 1;


1-4
select e1.last_name 사원이름, NVL(e2.last_name, '<없음>') 매니저이름, d.department_name 부서이름
from employees e1, employees e2, departments d
where e1.manager_id = e2.employee_id(+) AND e1.department_id = d.department_id
AND e1.department_id IN (select d.department_id
from departments d
where d.location_id = 1700)
order by e1.last_name;


1-5
select e.last_name 사원이름, d.department_name 부서이름, vt.min 급여
from employees e, departments d,
(select e.department_id, MIN(e.salary)
 min from employees e
group by e.department_id) vt
where 
e.department_id = d.department_id AND
e.department_id IN vt.department_id
and e.salary IN vt.min
order by d.department_name, e.last_name;


1-6 
select d.department_id 
from locations l, departments d, (
		select e.department_id department_id, count(*) pepole, AVG(e.salary) salary_avg
		from employees e
		group by e.department_id
		having count(*) < 10) vt
where d.department_id = vt.department_id AND d.location_id = l.location_id;


1-7
select  j.job_title 직책, vt.salary_avg 평균급여
from jobs j, (
	select e.job_id, avg(e.salary) salary_avg
	from employees e
	where e.department_id = 30
	group by e.job_id) vt
where j.job_id = vt.job_id
order by vt.salary_avg;


1-8
select e.employee_id 사번, e.first_name ||' '|| e.last_name 이름 
from job_history jh ,employees e
where jh.job_id = (
	select j.job_id
	from jobs j
	where j.job_title = 'Public Accountant')
AND  e.employee_id = jh.employee_id;


2장

2-1
select e.employee_id, e.first_name, e.last_name, NVL(d.department_name,'<NOT-ASSIGNED>'), e.hire_date
from employees e, departments d
where e.department_id = d.department_id(+) AND e.hire_date LIKE('99%');


2-2
select e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.commission_pct, e. manager_id, e.department_id
from employees e, departments d
where e.department_id = d.department_id and d.department_name = 'Marketing';


2-3
select j.job_title, e.salary
from employees e, jobs j
where e.job_id = j.job_id and e.salary > 10000;


3장

3-1
select e1.last_name, avg(e1.salary) salary
from employees e1, (
		select e1.salary, e1.employee_id 
		from employees e1, employees e2
		where e1.employee_id = e2.manager_id) mg
where e1.manager_id = mg.employee_id and e1.salary > mg.salary
group by e1.last_name;


3-2
select e.first_name, e.last_name, e.salary
from employees e, jobs j
where e.job_id = j.job_id and
e.job_id = (
		select j.job_id
		from jobs j
		where j.job_title = 'Sales Representative')
and e.salary between 9000 and 10000;


3-3
select e.employee_id, e.first_name, e.salary
from employees e
where LOWER(e.first_name) LIKE LOWER('%al%') and e.salary > 4000;


4장

4-1
select j.job_title, sum(A.salary)
from employees A, jobs j
where j.job_id = A.job_id
group by j.job_title
having sum(A.salary) > 30000
order by 2 desc;


4-2
select e.employee_id, e.first_name, j.job_title, vt.department_name
from employees e, jobs j ,
(
		select d.department_id, d.department_name
		from departments d, locations l
		where d.location_id = l.location_id and l.city = 'Seattle'
) vt
where e.job_id = j.job_id and e.department_id = vt.department_id;



4-3
select e.employee_id, e.first_name, e.salary
from employees e, jobs j
where e.job_id = j.job_id and (j.job_title = 'Accountant' or j.job_title = 'Stock Clerk');


5장

5-1
select d.department_name, MIN(e.salary) MINSAL, MAX(e.salary) MAXSAL, AVG(e.salary) AVGSAL
from departments d, employees e
where d.department_id = e.department_id
group by department_name;


5-2
select e.employee_id, e.department_id, e.salary, round(vt.avg_salary,0)
from employees e, 
(
	select d.department_id, AVG(e.salary) avg_salary
	from departments d, employees e
	where d.department_id = e.department_id
	group by d.department_id) vt
where e.department_id = vt.department_id and e.salary > vt.avg_salary;

5-3
select e.employee_id, d.department_name, e.salary, vt.avg
from employees e, departments d,
(
	select AVG(e.salary) avg, d.department_id
	from employees e, departments d
	where e.department_id = d.department_id
	group by d.department_id
) vt
where e.department_id = d.department_id and e.department_id = vt.department_id and d.department_id = 30;


6장


6-1 
select e.hire_date, e.employee_id, e.manager_id
from employees e
where e.hire_date between '80-01-01' AND '89-12-31';


6-2
select e.first_name, e.salary, d.department_name
from employees e, departments d,
(
	select AVG(e.salary) avg
	from employees e, departments d
	where e.department_id = d.department_id and d.department_id = 100
) vt
where e.department_id = d.department_id and d.department_name = 'Sales' and e.salary < vt.avg;


6-3
select e.first_name, d.department_name
from locations l, employees e, departments d
where e.department_id = d.department_id and d.location_id = l.location_id
and l.city = 'South San Francisco' and e.salary between 7000 and 10000;


7장


7-1
select to_char(e.hire_date, 'MON') 월, count(*) 직원수
from employees e
group by to_char(e.hire_date, 'MON')
order by 1;


7-2
select d.department_name, e.first_name, e.salary
from employees e, departments d,
(
select d.department_name d_name, MAX(e.salary) max_salary
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
) vt
where e.department_id = d.department_id and e.salary = vt.max_salary and d.department_name = vt.d_name;


7-3
select d.department_name, MAX(e.salary) max, MIN(e.salary) min, AVG(e.salary) avg
from employees e, departments d 
where e.department_id = d.department_id
group by d.department_name
having AVG(e.salary) > 
(
select AVG(e.salary) avg
from employees e, departments d
where e.department_id = d.department_id and d.department_name = 'IT'
group by d.department_name)
and AVG(e.salary) < 
(
select AVG(e.salary) avg
from employees e, departments d
where e.department_id = d.department_id and d.department_name = 'Sales'
group by d.department_name
);



7-4
select NVL(d.department_name, '<신생부서>'), COUNT(*)
from employees e, departments d
where e.department_id = d.department_id(+)
group by department_name
having COUNT(*) = 1
order by d.department_name desc NULLS LAST;



7-5 
select d.department_name, vt.month, count(*) 
from departments d,
(
	select extract(month from e.hire_date) month, e.department_id department_id
	from employees e
	group by extract(month from e.hire_date), e.department_id
) vt
where d.department_id = vt.department_id
group by d.department_name, vt.month;
having 3 > 5;


select extract(year from vt.hire_date) 년도, AVG(vt.avg) 평균급여
from 
(select e.hire_date hire_date, AVG(e.salary) avg
from employees e
where e.job_id = 'SA_MAN'group by e.hire_date) vt
group by extract(year from vt.hire_date)
order by 1;



7-5 
select NVL(vt.c_name, '<부서없음>') 국가명, NVL(vt.l_city, '<부서없음>') 도시명, count(*) 직원수
from employees e, 
(
	select c.country_name c_name, l.city l_city, d.department_id as department_id
	from locations l, countries c, departments d
	where c.country_id = l.country_id and l.location_id = d.location_id(+)
) vt
where vt.department_id(+) = e.department_id
group by vt.c_name, vt.l_city
order by 1 nulls first;


7-6
select e.employee_id 직원아이디, e.first_name 이름, e.salary 급여, vtn.avg 부서평균
from employees e, 
(
select department_id, MAX(e.salary) max_salary
from employees e
group by e.department_id
) vt,
(
select d.department_id, AVG(e.salary) avg
from departments d, employees e
where d.department_id = e.department_id
group by d.department_id
) vtn
where e.department_id = vt.department_id and e.department_id = vtn.department_id and e.salary = vt.max_salary;


7-7
CREATE TABLE salgrade (
    grade number(2) primary key,
    losal number(5) not null,
    hisal number(5) not null
 );

insert into salgrade
values(1, 1000, 5000);

insert into salgrade
values(2, 5001, 10000);

insert into salgrade
values(3, 10001, 20000);

insert into salgrade
values(4, 20001, 30000);


select s.grade 급여등급, count(*) 직원수
from employees e, salgrade s
where e.salary between s.losal and s.hisal
group by s.grade 
order by s.grade;



7-8 
select round(e.commission_pct,1) 커미션, count(*) 직원수
from employees e
group by round(e.commission_pct,1);




7-9
select d.department_name 부서명, e.first_name 직원명, e.salary 급여, e.commission_pct 커미션
from employees e, departments d,
(
	select e.commission_pct, e.employee_id
	from employees e 
	where e.commission_pct is not null
	order by e.commission_pct desc
)vt
where e.department_id = d.department_id and e.employee_id = vt.employee_id and rownum <=4
order by e.commission_pct desc, e.salary desc;