231127 Python_join, inner join, outer join, union, union all, intersect, except, over(partition by), rank() over(), dense_rank over()

# 테이블 불러오기, 이관작업 

data = pd.read_csv('c:/data/dept.csv')
data.info()
data.to_sql('dept', conn, index=False)

c.execute("select * from dept")
c.fetchall()

# 컬럼명 확인

c.execute("pragma table_info(dept)")
c.fetchall()

# join
# cartesian product(cross join)

c.execute("""select employee_id, department_name from emp, dept""")
c.fetchall()

# ANSI join

c.execute("""select employee_id, department_name from emp cross join dept""")
c.fetchall()

# equi join, simple join, inner join, 등가조인

c.execute("""select e.employee_id, d.department_name from emp e, dept d
             where e.department_id = d.department_id""")
c.fetchall()

# ANSI join

c.execute("""select e.employee_id, d.department_name 
             from emp e join dept d
             on e.department_id = d.department_id""")
c.fetchall()

# inner join

c.execute("""select e.employee_id, d.department_name 
             from emp e inner join dept d
             on e.department_id = d.department_id""")
c.fetchall()

# using

c.execute("""select e.employee_id, d.department_name 
             from emp e join dept d
             using(department_id)""")
c.fetchall()

# natual join 

c.execute("""select e.employee_id, d.department_name 
             from emp e natural join dept d""")
c.fetchall()

# self join

c.execute("select * from dept")
c.fetchall()
    
c.execute("""select e.employee_id, e.last_name, d.department_name
             from emp e, dept d
             where e.department_id = d.department_id
             and e.employee_id = d.manager_id""")
c.fetchall()

# ANSI join

c.execute("""select e.employee_id, e.last_name, d.department_name
             from emp e join dept d
             on e.department_id = d.department_id
             and e.employee_id = d.manager_id""")
c.execute("""select e.employee_id, e.last_name, d.department_name
             from emp e join dept d
             on e.department_id = d.department_id
             where e.employee_id = d.manager_id""")
c.fetchall()

# left outer join 

c.execute("""select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id""")
c.fetchall()

# right outer join

c.execute("""select e.employee_id, d.department_name
             from emp e right outer join dept d 
             on e.department_id = d.department_id""")
c.fetchall()

# full otuer join

c.execute("""select e.employee_id, d.department_name
             from emp e full outer join dept d 
             on e.department_id = d.department_id""")
c.fetchall()

 

# union 합집합

c.execute("""select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id
             union
             select e.employee_id, d.department_name
             from dept d left outer join emp e
             on e.department_id = d.department_id""")
c.fetchall()

# union all : 중복제거

c.execute("""select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id
             union all
             select e.employee_id, d.department_name
             from dept d left outer join emp e
             on e.department_id = d.department_id""")
c.fetchall()

# intersect : 교집합

c.execute("""select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id
             intersect
             select e.employee_id, d.department_name
             from dept d left outer join emp e
             on e.department_id = d.department_id""")
c.fetchall()

# except :차집합

c.execute("""select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id
             except
             select e.employee_id, d.department_name
             from dept d left outer join emp e
             on e.department_id = d.department_id""")
c.fetchall()

c.execute("""select e.employee_id, d.department_name
             from dept d left outer join emp e
             on e.department_id = d.department_id
             except
             select e.employee_id, d.department_name
             from emp e left outer join dept d 
             on e.department_id = d.department_id""")
c.fetchall()

 

---

# 사원들의 급여 등급
# non equi join, 비등가 조인

c.execute("""select e.employee_id, e.salary, j.grade_level
             from emp e, job_grades j 
             where e.salary between j.lowest_sal and j.highest_sal""")
c.fetchall()

# ANSI표준

c.execute("""select e.employee_id, e.salary, j.grade_level
             from emp e join job_grades j 
             on e.salary between j.lowest_sal and j.highest_sal""")
c.fetchall()

# self join 

c.execute("""select w.last_name, m.last_name
             from emp w, emp m 
             where w.manager_id = m.employee_id""")
c.fetchall()

# ANSI 표준

c.execute("""select w.last_name, m.last_name
             from emp w join emp m 
             on w.manager_id = m.employee_id""")
c.fetchall()

c.execute("""select w.last_name, m.last_name
             from emp w left outer join emp m 
             on w.manager_id = m.employee_id""")
c.fetchall()

# ==========================================================================================
# ■ 상호관련 서브쿼리(correlated subquery), 상관 서브쿼리 ex)존재여부 테스트 
#     a. main query(outer query) 먼저 수행
#     b. 첫 번째 행을 후보행으로 잡고 후보행 값을 서브쿼리에 전달
#     c. 후보행 값을 사용해서 서브쿼리를 수행한다.
#     d. 서브쿼리 결과값을 사용해서 후보행과 비교해서 true 이면 그 행을 결과집합(메모리)에 저장, false 면 넘어간다.
#     e. 다음 행을 후보행으로 잡고 서브쿼리에 전달 하고 table(main query) row 의 수 만큼 서브쿼리문을 반복 수행.
# ==========================================================================================

c.execute("""select * 
             from emp o 
             where salary > ( select avg(salary)
                              from emp
                              where department_id = o.department_id)""")
c.fetchall()

# 문제점을 inline view 해결 →  문제점 :동일한 테이블을 2번 이상 사용

c.execute("""select e2.* 
             from (select department_id, avg(salary) avgsal
                   from emp 
                   group by department_id) e1, emp e2
             where e1.department_id = e2.department_id 
             and e2.salary > e1.avgsal """)
c.fetchall()

# 분석함수 

c.execute("""select employee_id, salary, avg(salary) over()
             from emp""")
c.fetchall()

# 분석함수+case절

c.execute("""select employee_id, salary, avg(salary) over(),
                 case 
                     when salary >= avg(salary) over() then 'yes'
                     else 'no'
                 end case_sal
             from emp""")
c.fetchall()

# over(partition by 컬럼명) 

c.execute("""select employee_id, salary, avg(salary) over(partition by department_id)
             from emp""")
c.fetchall()

# 분석함수 + over(partition by 컬럼명)

c.execute("""select employee_id, salary, avg(salary) over(partition by department_id), 
                 case
                     when salary >= avg(salary) over(partition by department_id) then 'ok'
                     end case_sal
             from emp""")
c.fetchall()
c.execute("""select employee_id, salary, department_id
             from ( select employee_id, salary, department_id,
                       case
                           when salary >= avg(salary) over(partition by department_id) then 'ok'
                           end case_sal
                    from emp)
             where case_sal = 'ok'""")
c.fetchall()

# rank() over() 

c.execute("""select employee_id, salary, rank() over(order by salary desc) from emp """)
c.fetchall()

# dense_rank() over()

c.execute("""select employee_id, salary, dense_rank() over(order by salary desc) from emp """)
c.fetchall()

# 부서별로 순위 

c.execute("""select employee_id, salary, 
                    rank() over(partition by department_id order by salary desc) 
             from emp """)
c.fetchall()

c.execute("""select employee_id, salary, 
                                 dense_rank() over(partition by department_id order by salary desc) 
             from emp """)
c.fetchall()