35 PYTHON 예외사항, SQLite

import pandas as pd
from pandas import Series,DataFrame

emp = pd.read_csv("C:\\data\\emp.csv")
emp.info()
emp.HIRE_DATE = pd.to_datetime(emp.HIRE_DATE)   # 문자형 >> 날짜형

import matplotlib.pyplot as plt
from matplotlib import font_manager, rc
font_name = font_manager.FontProperties(fname='C:\\Windows\\Fonts\\malgun.ttf').get_name()
rc('font',family = font_name)

import numpy as np

---

■ 예외사항
실행중에 발생한 오류

def divide(x, y):     return x / y divide(10,2)    # 5.0 divide(10,3)    # 3.3333333333333335 divide(10,0)    # 오류 : ZeroDivisionError: division by zero (리셉션 이름 : 메세지) divide(10'2')   # 오류 : SyntaxError: invalid syntax. Perhaps you forgot a comma? divide(10,'2')  # 오류 :  TypeError: unsupported operand type(s) for /: 'int' and 'str'

↓

try:                                    # = bigin절     z = divide(10,2) except ZeroDivisionError as err:        # 별칭을 넣어 출력하면 원래 메세지를 같이 출력 가능     print('0으로 나눌 수 없습니다.', err) except TypeError as err:     print('인수값을 숫자로 입력해주세요.\n', err) except:                                 # 위에 걸리는게 없는 나머지 오류     print('오류가 발생했습니다.') else:                                   # 정상 작동 시 수행     print('결과 :',z) finally:                                # 프로그램 종료 시 무조건 실행     print('프로그램 종료')

except는 여러개 구성이 가능하지만 하나만 처리 후 끝난다.



# raise Exception : 사용자가 정의한 예외사항

def fun(arg):     try:         if arg < 1 or arg > 10:             raise Exception         else:             print('입력한 수는 {} 입니다.'.format(arg))     except Exception:         print('오류가 발생했습니다.') fun(11)     # 오류가 발생했습니다. fun(0)      # 오류가 발생했습니다. fun(5)      # 입력한 수는 5 입니다.

def fun(arg):     try:         if arg < 1 or arg > 10:             raise Exception('유효하지 않은 숫자입니다. {}'.format(arg))         # --- ②         else:             print('입력한 수는 {} 입니다.'.format(arg))     except Exception as error:         print('오류가 발생했습니다.', error)        # --- ① fun(11)     # 오류가 발생했습니다. 유효하지 않은 숫자입니다. 11 fun(0)      # 오류가 발생했습니다. 유효하지 않은 숫자입니다. 0 fun(5)      # 입력한 수는 5 입니다.

def positive_divide(x,y):     try:         if y < 0:             raise Exception('오류 - 음수로 나눌 수 없습니다.{}'.format(y))         return x / y     except TypeError as error:         print('인수값을 숫자로 입력해주세요.',error)     except ZeroDivisionError as error:         print('0으로 나눌 수 없습니다.',error)     except Exception as error:         print(error) positive_divide(10,2)   # 5.0 positive_divide(10,-2)  # 오류 - 음수로 나눌 수 없습니다.-2

<< 예외사항 이름을 생성하는 방법 >>
Exception, TypeError, ... -> class
클래스로 이름 구성(Exception 상속받기)

class NegativeDivisionError(Exception):     def __init__(self,value):         self.value = value def positive_divide(x,y):     if y < 0:         raise NegativeDivisionError(y)         return x / y positive_divide(10,2) positive_divide(10,-2)      # NegativeDivisionError: -2

class NegativeDivisionError(Exception):     pass def positive_divide(x,y):     if y < 0:         raise NegativeDivisionError()         return x / y positive_divide(10,2) positive_divide(10,-2)      # NegativeDivisionError

<< 예외사항 이름에 해당하는 오류 메세지 생성 >>

class NegativeDivisionError(Exception):     def __init__(self):         super().__init__('음수로 나눌 수 없습니다.') def positive_divide(x,y):     if y < 0:         raise NegativeDivisionError()         return x / y positive_divide(10,2) positive_divide(10,-2)      # NegativeDivisionError: 음수로 나눌 수 없습니다.

 

class test:     def __str__(self):         return '오늘 하루도 열심히 공부하자!!' t = test()     print(t)        # 오늘 하루도 열심히 공부하자!! t.__str__() print(test())

↓ 응용

class NegativeDivisionError(Exception):     def __str__(self):         return 'error-2000 음수로 나눌 수 없습니다.' def positive_divide(x,y):     if y < 0:         raise NegativeDivisionError()         return x / y positive_divide(10,2) positive_divide(10,-2)      # NegativeDivisionError: error-2000 음수로 나눌 수 없습니다.

---

■ SQLite
별도의 DB SERVER가 필요 없이 DB 파일에 기초하여 데이터베이스를 처리하는 엔진

import sqlite3

sqlite3.version         # '2.6.0'
sqlite3.__file__

# 임시 저장 영역 구축(메모리)

conn = sqlite3.connect(':memory:') c = conn.cursor()       # SQL문 실행 메모리 영역 c.execute('create table emp(id integer, name char, sal integer)')        # CREATE TABLE c.execute('insert into emp(id,name,sal) values(1,"홍길동",1000)')        # INSERT c.fetchone()        # 한개행 fecth, (1, '홍길동', 1000) c.execute('insert into emp(id,name,sal) values(2,"박찬호",2000)')        # INSERT c.fetchall()        # 다수 행 fetch, [(1, '홍길동', 1000), (2, '박찬호', 2000)] conn.rollback()     # transaction 종료는 Cursor가 아닌 Connect를 기준으로 해야 함 또는 conn.commit() c.close()           # cursor 종료 conn.close()        # connect 종료 ----- sqlite3 종료 # 재접속 conn = sqlite3.connect(':memory:') c = conn.cursor() c.execute('select * from emp')      # 오류, 임시메모리라서 connect를 종료하면서 전부사라짐 c.fetchone() c.close()           # cursor 종료 conn.close()        # connect 종료

# 데이터베이스에 영구히 보존하려고 할 때 사용하는 방법

conn = sqlite3.connect('C:/data/insa.db')   # 파일로 저장하면 어디서든지 사용 가능 c = conn.cursor() c.execute('create table emp(id integer, name char, sal integer)') c.execute('insert into emp(id,name,sal) values(1,"홍길동",1000)') c.execute('insert into emp(id,name,sal) values(2,"박찬호",2000)') c.execute('select * from emp') c.fetchone()            # (1, '홍길동', 1000) conn.commit() c.close() conn.close() # 재접속 conn = sqlite3.connect('C:/data/insa.db')   # 데이터파일로 연결 c = conn.cursor() c.execute('select * from emp') c.fetchall()            # [(1, '홍길동', 1000), (2, '박찬호', 2000)] ----- 영구 저장 됨

----- 접속 중 상태

# insa.db 파일안에 생성된 테이블 정보 확인 c.execute('select * from sqlite_master') c.fetchall()	[('table',   'emp',   'emp',   2,   'CREATE TABLE emp(id integer, name char, sal integer)')]
# 테이블 칼럼의 구조 확인 c.execute('pragma table_info(emp)') c.fetchall()	[(0, 'id', 'INTEGER', 0, None, 0),  (1, 'name', 'char', 0, None, 0),  (2, 'sal', 'INTEGER', 0, None, 0)]
# 테이블 삭제 c.execute('drop table emp') c.fetchall()

<< pandas DataFrame을 sqlite3 테이블로 이관 작업(data migration) >>

import pandas as pd data = pd.read_csv("C:\\data\\emp.csv") data.info() type(data)	파일 가져오기 # pandas.core.frame.DataFrame
data.to_sql('emp', conn, index=False) >> emp테이블을 conn접속대상에 인덱스 없이 복사	# 107(행)
c.execute('select * from sqlite_master') c.fetchall()	[('table',   'emp',   'emp',   2,   'CREATE TABLE "emp" (\n"EMPLOYEE_ID" INTEGER,\n  "FIRST_NAME" TEXT,\n  "LAST_NAME" TEXT,\n  "EMAIL" TEXT,\n  "PHONE_NUMBER" TEXT,\n  "HIRE_DATE" TEXT,\n  "JOB_ID" TEXT,\n  "SALARY" INTEGER,\n  "COMMISSION_PCT" REAL,\n  "MANAGER_ID" REAL,\n  "DEPARTMENT_ID" REAL\n)')]
c.execute('pragma table_info(emp)') c.fetchall()	[(0, 'EMPLOYEE_ID', 'INTEGER', 0, None, 0),  (1, 'FIRST_NAME', 'TEXT', 0, None, 0),  (2, 'LAST_NAME', 'TEXT', 0, None, 0),  (3, 'EMAIL', 'TEXT', 0, None, 0),  (4, 'PHONE_NUMBER', 'TEXT', 0, None, 0),  (5, 'HIRE_DATE', 'TEXT', 0, None, 0),  (6, 'JOB_ID', 'TEXT', 0, None, 0),  (7, 'SALARY', 'INTEGER', 0, None, 0),  (8, 'COMMISSION_PCT', 'REAL', 0, None, 0),  (9, 'MANAGER_ID', 'REAL', 0, None, 0),  (10, 'DEPARTMENT_ID', 'REAL', 0, None, 0)]
c.close() conn.close() conn = sqlite3.connect('C:/data/insa.db') c = conn.cursor() c.execute('select * from emp') c.fetchall()	# 종료 # 재접속 # 영구 저장되어 있다.

integer     int
text        char
rear        float



<< sqlite3에 있는 테이블을 pandas DataFrame으로 이관 작업 >>

emp_new = pd.read_sql_query('select * from emp where department_id = 20',conn) emp_new.info() >> pd.read_sql_query('가져올 쿼리', 접속대상)

Oracle 비슷 MYSQL
MYSQL 비슷 sqlite3

# sql 단문 c.execute('select * from emp') c.fetchall()

# sql 장문 c.execute("""select *              from emp""") c.fetchall()

- 주석 사용과 동일
- 내부에 ' ' 또는 " " 사용이 필요할 경우 외부와 겹치지 않게 사용한다.
        c.execute('select replace("오라클 분석가","분석가","엔지니어")')
        c.execute("select * from emp where lower(last_name) = 'king' ")

# SQL SELECT 1 + 2 FROM dual; >> 테스트의 경우 dual 사용

# PYTHON c.execute('select 1 + 2') >> select만 작성하면 된다.

c.execute('select * from emp where lower(last_name) = "king"')     # 따옴표 주의
c.fetchall()

c.execute('select * from emp where upper(last_name) = "KING"')
c.fetchall()

c.execute('''select last_name, substr(last_name,1,1), substr(last_name,2)
          from emp''')
c.fetchall()

c.execute('''select last_name, lower(upper(substr(last_name,1,1))||substr(last_name,2))
          from emp''')
c.fetchall()

c.execute('''select last_name, substr(last_name,-2,2)
          from emp''')
c.fetchall()

c.execute('''select last_name, length(last_name)
          from emp''')
c.fetchall()

c.execute('select replace("오라클 분석가", "분석가", "엔지니어")')
c.fetchall()

c.execute('select trim("     오라클 엔지니어     ")')
c.fetchall()

c.execute('select ltrim("     오라클 엔지니어     ")')
c.fetchall()

c.execute('select rtrim("     오라클 엔지니어     ")')
c.fetchall()

c.execute('select trim("오라클 오라클 엔지니어 오라클","오라클")')
c.fetchall()

c.execute('select trim("오라클오라클 오라클 엔지니어 오라클오라클","오라클")')
c.fetchall()

c.execute('select ltrim(" 오라클오아클 오라클 엔지니어","오라클")')
c.fetchall()

c.execute('select rtrim("오라클 엔지니어 오라클")')
c.fetchall()

c.execute("select instr('aaa@itiwill.com','0')")
c.fetchall()

# 정수 /  = 몫
c.execute('select 1+2, 2-1, 2*3, 10/3, 10%3')
c.fetchall()

# 실수 /  = 몫...나머지
c.execute('select 1+2, 2-1, 2*3, 10./3, 10%3')
c.fetchall()

c.execute('select round(45.926),round(45.926,0), round(45.926,2), round(45.926,-2)')
c.fetchall()

c.execute('select last_name, salary * 12 + commission_pct from emp')
c.fetchall()

c.execute('select last_name, salary * 12 + ifnull(commission_pct,0) from emp')
c.fetchall()

c.execute('select last_name, salary * 12 + coalesce(commission_pct,0) from emp')
c.fetchall()

c.execute('select last_name, length(last_name), nullif(length(last_name),5) from emp')
c.fetchall()

c.execute('''select department_id from emp''')
c.fetchall()

c.execute('''select distinct department_id from emp''')
c.fetchall()

c.execute('''select distinct department_id, job_id from emp''')
c.fetchall()

c.execute('''select * from emp where last_name like "_ik%"''')
c.fetchall()

c.execute('''select * from emp where department_id=10 or department_id=20''')
c.fetchall()

c.execute('''select * from emp where department_id in (10,20)''')
c.fetchall()

c.execute('''select * from emp where department_id not in (10,20)''')
c.fetchall()

c.execute('''select * from emp where salary>=10000 and salary<=15000''')
c.fetchall()

c.execute('''select * from emp where salary between 10000 and 15000''')
c.fetchall()

c.execute('''select * 
             from emp o
             where exists (select 'x' 
                           from emp 
                           where manager_id = o.employee_id)''')
c.fetchall()

c.execute('''select * 
             from emp o
             where nopt exists (select 'x' 
                                from emp 
                                where manager_id = o.employee_id)''')
c.fetchall()

c.execute('''select * 
             from emp o
             where commission_pct is not null''')
c.fetchall()


c.execute('select date("now"), datetime("now"), datetime("now","localtime")')
c.fetchall()

c.execute('select date("now","10 day"), date("now","-10 day")')    # 10일 후 , 10일 전
c.fetchall()

c.execute('select date("now","3 month"), date("now","-3 month")')
c.fetchall()

c.execute('select date("now","1 year","3 month")')    # 1년 3개월 후
c.fetchall()

c.execute('''select datetime("now","localtime","1 day","5 hour","60 minute","30 second")''')
c.fetchall()

c.execute('''select datetime("now","weekday 5")''')     # 0 일요일 ~ 6 토요일, 다음 금요일은?
c.fetchall()

c.execute('pragma table_info(emp)')
c.fetchall()

# 날짜 - 날짜 = 년수
c.execute('select date("now") - date(hire_date) from emp')
c.fetchall()

c.execute('select datetime("now","localtime") - date(hire_date) from emp')
c.fetchall()

c.execute("""select strftime('%Y %m %d %H %M %S %s',datetime('now','localtime'))""")
c.fetchall()

c.execute("""select strftime('%s','now')""")
c.fetchall()

# 초 - 초 = 초 / 86400 = 일수
c.execute('select (strftime("%s","now") - strftime("%s","2023-10-05"))/86400')
c.fetchall()

c.execute('''select (strftime("%s","now") - strftime("%s",datetime(hire_date)))/86400
             from emp''')
c.fetchall()

[문제] 2006년도에 홀수달에 입사한 사원들의 정보를 출력해주세요.

c.execute("""select *              from emp              where date(hire_date) between date('2006-01-01') and date('2006-12-31')              and strftime('%m',date(hire_date)) % 2 != 0""") c.fetchall()

c.execute("""select *              from emp              where date(hire_date) between date('2006-01-01') and date('2006-12-31')              and cast(strftime('%m',date(hire_date)) as integer) % 2 != 0""") c.fetchall() >> cast ~ as type : 형변환

c.execute("""select strftime('%m',date(hire_date)) from emp""")
c.fetchall()

c.execute("""select strftime('%m',date(hire_date)) % 2 from emp""")
c.fetchall()

c.execute("""select cast(strftime('%m',date(hire_date)) as integer) % 2 from emp""")
c.fetchall()

c.execute("""select cast(strftime('%m',date(hire_date)) as real) % 2 from emp""")
c.fetchall()

c.execute("""select cast(strftime('%m',date(hire_date)) as char) % 2 from emp""")
c.fetchall()


c.execute("""select employee_id, last_name, department_id
             from emp
             order by department_id asc""")
c.fetchall()

c.execute("""select employee_id, last_name, department_id
             from emp
             order by department_id desc""")
c.fetchall()

c.execute("""select employee_id, last_name, department_id
             from emp
             order by 3 desc""")
c.fetchall()

c.execute("""select employee_id, last_name, department_id dept_id
             from emp
             order by dept_id desc""")
c.fetchall()

c.execute("""select employee_id, last_name, department_id, salary
             from emp
             order by 3 asc, 4 desc""")
c.fetchall()


c.execute("""select last_name, salary, commission_pct,
             case
                 when commission_pct is null then salary * 12
                 else (salary * 12) + (salary * 12 * commission_pct)
             end
             from emp""")
c.fetchall()


c.execute("""select count(*), count(commission_pct) from emp""")
c.fetchall()

c.execute("""select sum(salary), total(salary), avg(salary), max(salary), min(salary)
             from emp""")
c.fetchall()


c.execute("""select department_id, sum(salary)
             from emp
             group by department_id""")
c.fetchall()


c.execute("""select department_id, sum(salary)
             from emp
             group by department_id, job_id""")
c.fetchall()


c.execute("""select department_id, sum(salary)
             from emp
             group by department_id
             having sum(salary) >= 10000""")
c.fetchall()

[문제] 년도별 입사한 인원수를 출력해주세요.
c.execute("""select strftime('%Y',date(hire_date)), count(*)              from emp              group by strftime('%Y',date(hire_date))""") c.fetchall()	[('2001', 1),  ('2002', 7),  ('2003', 6),  ('2004', 10),  ('2005', 29),  ('2006', 24),  ('2007', 19),  ('2008', 11)]
years = pd.read_sql_query("""select strftime('%Y',date(hire_date)) year, count(*) cnt                              from emp                              group by strftime('%Y',date(hire_date))""", conn) years	year  cnt 0  2001    1 1  2002    7 2  2003    6 3  2004   10 4  2005   29 5  2006   24 6  2007   19 7  2008   11

 

[문제] 요일별 입사한 인원수 출력
c.execute("""select strftime('%w','now')""")    # 0 일 ~ 6 토 c.fetchall() >> [('1',)] : 오늘 요일(월)
c.execute("""select strftime('%w',date(hire_date)) week, count(*) cnt              from emp              group by strftime('%w',date(hire_date))""") c.fetchall() >> [('0', 15), ('1', 10), ('2', 13), ('3', 15), ('4', 16), ('5', 19), ('6', 19)]
c.execute("""select case week                         when '0' then '일'                         when '1' then '월'                         when '2' then '화'                         when '3' then '수'                         when '4' then '목'                         when '5' then '금'                         when '6' then '토' end||'요일', cnt              from(select strftime('%w',date(hire_date)) week, count(*) cnt                   from emp                   group by strftime('%w',date(hire_date)))""") c.fetchall()	[('일요일', 15),  ('월요일', 10),  ('화요일', 13),  ('수요일', 15),  ('목요일', 16),  ('금요일', 19),  ('토요일', 19)]
y = pd.read_sql_query("""select case week                         when '0' then '일'                         when '1' then '월'                         when '2' then '화'                         when '3' then '수'                         when '4' then '목'                         when '5' then '금'                         when '6' then '토' end||'요일' 요일, cnt              from(select strftime('%w',date(hire_date)) week, count(*) cnt                   from emp                   group by strftime('%w',date(hire_date)))""",conn) y	요일  cnt 0  일요일   15 1  월요일   10 2  화요일   13 3  수요일   15 4  목요일   16 5  금요일   19 6  토요일   19

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()


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


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()


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


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()


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


c.execute("""select e.employee_id, d.department_name 
             from emp e natural join dept d""")
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()


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.fetchall()


# 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()


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()


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()

>> right outer join, full outer join 버전별 상이


# 합집합을 이용해서 full 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
             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()


# 합집합, 중복포함
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()


# 교집합
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()


# 차집합
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()



data = pd.read_csv("C:\\data\\job.csv")
data.info()
data.to_sql('job_grades', conn, index=False)
c.execute('select * from job_grades')
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()


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()


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



# correlated subquery(상호관련 서브쿼리)
c.execute("""select * 
             from emp o 
             where salary > (select avg(salary)
                             from emp
                             where department_id = o.department_id)""")
c.fetchall()


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(),
                  case when salary >= avg(salary) over() then 'yes'
                  else 'no' end case_sal
          from emp
          """)
c.fetchall()



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



c.execute("""
          select *
          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()



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


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()