Pandas SQL Query: Display name, salary and department number for those employees whose first name ends with specified letter

Pandas HR database Queries: Exercise-16 with Solution

Write a Pandas program to display the first name, last name, salary and department number for those employees whose first name ends with the letter 'd' or 'n' or 's' and also arrange the result in descending order by department id.

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      Salary    Department ID")
result = employees[employees['first_name'].str[-1].isin(['s','d','n'])]
result = result.sort_values('department_id', ascending=True)
for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['salary']).ljust(9),row['department_id'])

Sample Output:

First name       Last name      Salary    Department ID
Den             Raphaely        11000     30.0
Karen           Colmenares      2500      30.0
Susan           Mavris          6500      40.0
Jason           Mallin          3300      50.0
Alexis          Bull            4100      50.0
Kevin           Feeney          3000      50.0
Curtis          Davies          3100      50.0
John            Seo             2700      50.0
Stephen         Stiles          3200      50.0
Winston         Taylor          3200      50.0
James           Marlow          2500      50.0
Steven          Markle          2200      50.0
James           Landry          2400      50.0
Kevin           Mourgos         5800      50.0
Donald          OConnell        2600      50.0
Douglas         Grant           2600      50.0
Girard          Geoni           2800      50.0
Jean            Fleaur          3100      50.0
David           Austin          4800      60.0
Hermann         Baer            10000     70.0
Charles         Johnson         6200      80.0
Jonathon        Taylor          8600      80.0
Gerald          Cambrault       11000     80.0
Harrison        Bloom           10000     80.0
David           Lee             6800      80.0
Allan           McEwen          9000      80.0
David           Bernstein       9500      80.0
Karen           Partners        13500     80.0
John            Russell         14000     80.0
Ellen           Abel            11000     80.0
Steven          King            24000     90.0
Luis            Popp            6900      100.0
John            Chen            8200      100.0

Equivalent SQL Syntax:

SELECT first_name, last_name, salary, department_id
 FROM employees
  WHERE first_name LIKE '%D%'
   OR first_name LIKE '%S%'
   OR first_name LIKE '%N%'
    ORDER BY salary DESC;

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 :

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

Previous: Write a Pandas program to display the first name, last name, salary and department number for those employees whose first name ends with the letter 'm'.
Next: Write a Pandas program to display the first name, last name, salary and department number for employees who works either in department 70 or 90.