w3resource

Pandas SQL Query: Display employees who is either Sales Representative or Sales Man


Write a Pandas program to display the first and last name and date of joining of the employees who is either Sales Representative or Sales Man.

EMPLOYEES.csv

Sample Solution:

Python Code :

import pandas as pd
employees = pd.read_csv(r"EMPLOYEES.csv")
departments = pd.read_csv(r"DEPARTMENTS.csv")
job_history = pd.read_csv(r"JOB_HISTORY.csv")
jobs = pd.read_csv(r"JOBS.csv")
countries = pd.read_csv(r"COUNTRIES.csv")
regions = pd.read_csv(r"REGIONS.csv")
locations = pd.read_csv(r"LOCATIONS.csv")
print("First name      Last name       Job ID          Hire Date")
result = employees[employees['job_id'].isin(['SA_REP', 'SA_MAN'])]
for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['job_id']).ljust(15),str(row['hire_date']).ljust(10))

Sample Output:

First name      Last name       Job ID          Hire Date
John            Russell         SA_MAN          2004-10-01
Karen           Partners        SA_MAN          2005-01-05
Alberto         Errazuriz       SA_MAN          2005-03-10
Gerald          Cambrault       SA_MAN          2007-10-15
Eleni           Zlotkey         SA_MAN          2008-01-29
Peter           Tucker          SA_REP          2005-01-30
David           Bernstein       SA_REP          2005-03-24
Peter           Hall            SA_REP          2005-08-20
Christopher     Olsen           SA_REP          2006-03-30
Nanette         Cambrault       SA_REP          2006-12-09
Oliver          Tuvault         SA_REP          2007-11-23
Janette         King            SA_REP          2004-01-30
Patrick         Sully           SA_REP          2004-03-04
Allan           McEwen          SA_REP          2004-08-01
Lindsey         Smith           SA_REP          2005-03-10
Louise          Doran           SA_REP          2005-12-15
Sarath          Sewall          SA_REP          2006-11-03
Clara           Vishney         SA_REP          2005-11-11
Danielle        Greene          SA_REP          2007-03-19
Mattea          Marvins         SA_REP          2008-01-24
David           Lee             SA_REP          2008-02-23
Sundar          Ande            SA_REP          2008-03-24
Amit            Banda           SA_REP          2008-04-21
Lisa            Ozer            SA_REP          2005-03-11
Harrison        Bloom           SA_REP          2006-03-23
Tayler          Fox             SA_REP          2006-01-24
William         Smith           SA_REP          2007-02-23
Elizabeth       Bates           SA_REP          2007-03-24
Sundita         Kumar           SA_REP          2008-04-21
Ellen           Abel            SA_REP          2004-05-11
Alyssa          Hutton          SA_REP          2005-03-19
Jonathon        Taylor          SA_REP          2006-03-24
Jack            Livingston      SA_REP          2006-04-23
Kimberely       Grant           SA_REP          2007-05-24
Charles         Johnson         SA_REP          2008-01-04

Equivalent SQL Syntax:

SELECT first_name, last_name, hire_date
	FROM employees 
		WHERE job_id IN ('SA_REP', 'SA_MAN');

Click to view the table contain:

Employees Table

Departments Table

Countries Table

Job_History Table

Jobs Table

Locations Table

Regions Table

Python Code Editor:

Structure of HR database :

HR database

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to display the details of jobs in descending sequence on job title.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.