[조인] outer join과 self join 각 1문제씩

1. oracle

1) employees와 departments를 join하세요. 단, employees의 모든 행을 출력하세요.
(1)
select e.employee_id, e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
(2)
select e.employee_id, e.last_name, d.department_id, d.department_name
from employees e
left outer join departments d on e.department_id = d.department_id;

2) employees테이블에서 사원과 사원이름, 매니저와 매니저이름을 출력하세요.
select w.employee_id, w.last_name, m.employee_id, m.last_name
from employees w, employees m
where w.manager_id = m.employee_id;

 

 

 

2. r

1) employees와 departments를 join하세요. 단, employees의 모든 행을 출력하세요. 

(1)
emp<-read.csv("c:/data/employees.csv",header=T,stringsAsFactor=F)
dept<-read.csv("c:/data/departments.csv",header=T,stringsAsFactor=F)
emp1<-emp[,c('EMPLOYEE_ID','LAST_NAME','DEPARTMENT_ID')]
dept1<-dept[,c('DEPARTMENT_ID','DEPARTMENT_NAME')]
merge(emp1,dept1,by='DEPARTMENT_ID',all.x=T)[c('EMPLOYEE_ID','LAST_NAME','DEPARTMENT_ID','DEPARTMENT_NAME')]

(2)
library(sqldf)
sqldf("select e.employee_id, e.last_name, d.department_id, d.department_name
      from emp e
      left outer join dept d on e.department_id = d.department_id")

 

2) employees테이블에서 사원과 사원이름, 매니저와 매니저이름을 출력하세요.

(1)

emp_w <- emp[,c('EMPLOYEE_ID','LAST_NAME','MANAGER_ID')]
emp_m <- emp[,c('EMPLOYEE_ID','LAST_NAME')]
merge(emp_w,emp_m,by.x='MANAGER_ID',by.y='EMPLOYEE_ID')[c('EMPLOYEE_ID','LAST_NAME.x','MANAGER_ID','LAST_NAME.y')]

 

(2)

library(sqldf) : 생략

 

 

 

3. python

1) employees와 departments를 join하세요. 단, employees의 모든 행을 출력하세요. 

(1)

import pandas as pd

mg=pd.merge(emp,dept,on='DEPARTMENT_ID', how='left'[['EMPLOYEE_ID','LAST_NAME','DEPARTMENT_ID']] 

#how='inner'/'left'/'right'
mg.info()
(2)

import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
emp.to_sql('emp',conn,index=False)
dept.to_sql('dept',conn,index=False)
c.execute("""
select e.employee_id, e.last_name, d.department_id, d.department_name 
from emp e 
left outer join dept d on e.department_id = d.department_id""")
c.fetchall()
join = pd.read_sql_query("""
select e.employee_id, e.last_name, d.department_id, d.department_name 
from emp e 
left outer join dept d on e.department_id = d.department_id""",conn)
join.info()

 

2) employees테이블에서 사원과 사원이름, 매니저와 매니저이름을 출력하세요.

(1)

mg=pd.merge(emp,emp,left_on='MANAGER_ID',right_on='EMPLOYEE_ID')
mg.info()

(2)

import sqlite3   : (생략)