231127 Python 문제

[문제] 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 strftime('%Y', date(hire_date)), count(*) 
             from emp 
             group by strftime('%Y', date(hire_date))""")
c.fetchall()

# pandas식 으로 표현

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

[문제] 요일별 입사한 인원수를 출력해주세요.

c.execute('select strftime("%w", "now")') # 0:일 - 6:토
c.fetchall()

c.execute("""select strftime('%w', date(hire_date)), count(*) 
             from emp 
             group by strftime('%w', date(hire_date))""")
c.fetchall()

 

# 문자형태로 표현

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