(오라클) join

WE use DB for enterprise decision.



select sum(salary) as total from employees; 

select max(salary) from employees;

max->최대

select count(employee_id) from employees;

select count(commission_pct) from employees;

select count(*), department_id from employees group by department_id;

select- 고르다
count(*) - 모두다 센다
department_id를 
어디서? employees 에서
department_id 부서별로.

select avg(salary),department_id,count(*) 인원수 from employees group by department_id;

avg(salary) -> 평균 월급
department_id -> 부서 아이디

를 출력하고,

count(*) 인원수 -> 인원수를 다 세고

group by department_id-> 부서별로 나눈다

select round(avg(salary)),department_id,count(*)인원수 from employees group by department_id;

real number -> integer
we use round()

'

select * from departments where department_id = 90;

join

join type -> inner/outer/

inner join 

inner  join,

The method to join the same data in the two different table.

Let's say we have two table which are employees and departments

employees consists of first_name, last_name, email, phone_number etc expect for the department_id that is in the departments table.

and we which to print department_id as well as we print employees.

So, we combine it.

SELECT employees.first_name, employees.last_name, employees.email,

departments.department_name,

department_id //error! You have to clearly define where department_id come from

FROM employees, departments de // departments as de, to give a short nickname

WHERE employees.department_id = departments.department_id;

근데 null인 값은 테이블에서 안뜰수있다.

관계형 테이블을 만들었다!

select employees.*, department_name from employees, departments 

    where employees.department_id = DEPARTMENTS.DEPARTMENT_ID

    order by employee_id desc;

null값 확인하기, 누락되었음 ㅠ

select first_name, employee_id from employees where department_id is null;

select em.employee_id as 사번, em.first_name ,em.last_name,  em.department_id, de.department_name from employees em,departments de 
            where em.department_id = de.department_id
            order by employee_id desc;

select em.*,de.*,jo.*
from employees em, departments de, jobs jo
where em.department_id =de.department_id
    and em.job_id  = jo.job_id;
    

select emp.first_name, emp.last_name, emp.email,

emp.department_id, dep.department_name,

emp.job_id, job.job_title,

loc.city

from employees emp,

departments dep,

jobs job , locations loc

where emp.department_id = dep.department_id

and emp.job_id = job.job_id

and dep.location_id = loc.location_id;