[데이터 선택 - AND & OR 연산자] 복수의 OR 연산자와 AND 연산자 - 11 (오라클 SQL, R, Python, SAS)

 

11. Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000.

 

* 판매직, 영업직 또는 분석직 직무를 수행하고 3000 이상의 급여를 수령하는 직원들의 이름을 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• [다중 OR / AND 연산자] 복수의 OR 연산자와 AND 연산자

Oracle : where 구문, OR/AND 연산자 파이썬 Pandas : 슬라이싱(Slicing), .loc, .query 구문, isin(), & 연산자, in 연산자 R 프로그래밍 : 슬라이싱(Slicing), subset() 구문, which 함수, %in% 연산자, & 연산자 R Dplyr Package : filter() 함수, %in% 연산자, & 연산자 R sqldf Package : where 구문, in 연산자, AND 연산자 Python pandasql Package : where 구문, in 연산자, AND 연산자 R data.table Package : 슬라이싱(Slicing), %in%, &연산자 SAS Proc SQL : where 구문, Or 연산자, and 연산자 SAS Data Step : where 구문, IF 조건문, Or 연산자, and 연산자 Python Dfply Package : filter_by, isin 연산자 파이썬 Base 프로그래밍 :

 

---

1. 오라클(Oracle)

 

Oracle Programming

select ename 
from   emp 
where  (job='CLERK' or job='SALESMAN' or job='ANALYST') and sal>1000; 

---

 

Oracle Programming

select ename 
from   emp 
where  job in ('CLERK','SALESMAN','ANALYST') and sal>1000;

 

---

2. 파이썬(Pandas)

 

Python Programming

emp[ (emp['job'].isin(['CLERK','SALESMAN','ANALYST'])) & (emp['sal'] > 1000 )][["ename"]]

 

Results

	ename
1	ALLEN
2	WARD
4	MARTIN
7	SCOTT
9	TURNER
10	ADAMS
12	FORD
13	MILLER

 

---

 

Python Programming

emp.loc[(emp.job.isin(['CLERK','SALESMAN','ANALYST'])) & (emp.sal > 1000 ) , ["ename"]]

 

Results

	ename
1	ALLEN
2	WARD
4	MARTIN
7	SCOTT
9	TURNER
10	ADAMS
12	FORD
13	MILLER

 

---

 

Python Programming

# query 구문에서 isnull() / notnull() 구문 사용 : python engine instead of numexpr engin

emp.query("job in (['CLERK','SALESMAN','ANALYST']) & sal > 1000")[["empno","ename"]]

 

Results

	empno	ename
1	7499	ALLEN
2	7521	WARD
4	7654	MARTIN
7	7788	SCOTT
9	7844	TURNER
10	7876	ADAMS
12	7902	FORD
13	7934	MILLER

 

---

3. R Programming (R Package)

 

R Programming

%%R
emp[emp$job %in% c('CLERK','SALESMAN','ANALYST') & emp$sal > 1000 , c("empno","ename") ]

 

Results

# A tibble: 8 x 2
  empno ename 
  <dbl> <chr> 
1  7499 ALLEN 
2  7521 WARD  
3  7654 MARTIN
4  7788 SCOTT 
5  7844 TURNER
6  7876 ADAMS 
7  7902 FORD  
8  7934 MILLER

 

---

 

R Programming

%%R
emp[which(emp$job %in% c('CLERK','SALESMAN','ANALYST') & emp$sal > 1000), c("empno","ename") ]

 

Results

# A tibble: 8 x 2
  empno ename 
  <dbl> <chr> 
1  7499 ALLEN 
2  7521 WARD  
3  7654 MARTIN
4  7788 SCOTT 
5  7844 TURNER
6  7876 ADAMS 
7  7902 FORD  
8  7934 MILLER

 

---

 

R Programming

%%R
subset(emp,subset= (job %in% c('CLERK','SALESMAN','ANALYST') & sal > 1000) , select=c(empno,ename) )

 

Results

# A tibble: 8 x 2
  empno ename 
  <dbl> <chr> 
1  7499 ALLEN 
2  7521 WARD  
3  7654 MARTIN
4  7788 SCOTT 
5  7844 TURNER
6  7876 ADAMS 
7  7902 FORD  
8  7934 MILLER

 

---

4. R Dplyr Package

 

R Programming

%%R
emp %>% filter(job %in% c('CLERK','SALESMAN','ANALYST') & sal > 1000 ) %>% dplyr::select(empno,ename)

 

---

5. R sqldf Package

 

R Programming

%%R

require(sqldf)
sqldf("select ename from emp where job in ('CLERK','SALESMAN','ANALYST') and sal>1000")

 

Results

   ename
1  ALLEN
2   WARD
3 MARTIN
4  SCOTT
5 TURNER
6  ADAMS
7   FORD
8 MILLER

 

---

6. Python pandasql Package

 

Python Programming

ps.sqldf("select ename from emp where job in ('CLERK','SALESMAN','ANALYST') and sal>1000")

 

Results

	ename
0	ALLEN
1	WARD
2	MARTIN
3	SCOTT
4	TURNER
5	ADAMS
6	FORD
7	MILLER

 

---

7. R data.table Package

 

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)

DT[ job %in% c('CLERK','SALESMAN','ANALYST') & sal > 1000 , .( empno, ename )]

 

Results

   empno  ename
1:  7499  ALLEN
2:  7521   WARD
3:  7654 MARTIN
4:  7788  SCOTT
5:  7844 TURNER
6:  7876  ADAMS
7:  7902   FORD
8:  7934 MILLER

 

---

8. SAS Proc SQL

 

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    SELECT ENAME
    FROM   EMP
    WHERE (job='CLERK' or job='SALESMAN' or job='ANALYST') 
      and sal>1000;
QUIT;
PROC PRINT;RUN;

 

Results

OBS	ename
1	ALLEN
2	WARD
3	MARTIN
4	SCOTT
5	TURNER
6	ADAMS
7	FORD
8	MILLER

 

---

9. SAS Data Step

 

SAS Programming

%%SAS sas

DATA STATSAS_2;
 SET EMP;
     KEEP ENAME;
     WHERE (job='CLERK' or job='SALESMAN' or job='ANALYST') 
      and sal>1000;
RUN;
PROC PRINT;RUN;

 

Results

OBS	ename
1	ALLEN
2	WARD
3	MARTIN
4	SCOTT
5	TURNER
6	ADAMS
7	FORD
8	MILLER

 

---

10. Python Dfply Package

 

Python Programming

emp >> filter_by( X.job.isin(['CLERK','SALESMAN','ANALYST']) , X.sal > 1000 ) >> select(X.empno, X.ename)

 

Results

	empno	ename
1	7499	ALLEN
2	7521	WARD
4	7654	MARTIN
7	7788	SCOTT
9	7844	TURNER
10	7876	ADAMS
12	7902	FORD
13	7934	MILLER

 

---

 

Python Programming

emp >> mask( X.job.isin(['CLERK','SALESMAN','ANALYST']) , X.sal > 1000 ) >> select(X.empno, X.ename)

 

Results

	empno	ename
1	7499	ALLEN
2	7521	WARD
4	7654	MARTIN
7	7788	SCOTT
9	7844	TURNER
10	7876	ADAMS
12	7902	FORD
13	7934	MILLER

 

---

 

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE]   SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트