[문자함수]last_name 의 세번째 문자가 'a' 또는 'e'가 포함된 모든 사원들의 last_name을 출력해주세요.

1.SQL

explain plan for 
select employee_id, last_name
from employees
where last_name like '__a%' or last_name like '__e%';
select * from table(dbms_xplan.display(null,null,'typical'));

 

-------------------------------------------------------------------------------  | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |           |    10 |   120 |     3   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| EMPLOYEES |    10 |   120 |     3   (0)| 00:00:01 |  -------------------------------------------------------------------------------     Predicate Information (identified by operation id):  ---------------------------------------------------        1 - filter("LAST_NAME" LIKE '__a%' OR "LAST_NAME" LIKE '__e%')

 

 

 

 

2. R

emp = read.csv("c:/data/employees.csv",header=TRUE,stringsAsFactors = FALSE)
1)
emp[substr(emp$LAST_NAME,3,3) %in% c('a','e'), c('EMPLOYEE_ID','LAST_NAME')]
#head(emp,5)#str(emp)
2)
subset(emp,substr(LAST_NAME,3,3) %in% c('a','e'), c(EMPLOYEE_ID, LAST_NAME))
3)
library(sqldf)
sqldf("select employee_id,last_name from emp
      where last_name like '__a%' or last_name like '__e%'") #헐 이름중에 대문자 e가 있는 놈이 있어서 출력이 1개 더 많음. 버그인듯.
4)
library(dplyr)
emp%>%
  filter(substr(LAST_NAME,3,3) %in% c('a','e'))%>%
  select(EMPLOYEE_ID, LAST_NAME)

 

 

 

3. PYTHON

1)
import csv
file = open("c:/data/employees.csv","r")
csv_emp = csv.reader(file)
titie = next(file)
res=[]
for i in csv_emp:
    if (i[2][2]=='a') or (i[2][2]=='e'):
        res += [i[0], i[2]]
print(res)
2)
import pandas as pd
df = pd.read_csv("c:/data/employees.csv")
df[['EMPLOYEE_ID','LAST_NAME']][[i[2] in ['a','e'] for i in df['LAST_NAME']]]
3)
df[['EMPLOYEE_ID','LAST_NAME']][df.iloc[:,2].apply(lambda x:x[2]).isin(['a','e'])]