Pandas SQL Query: Display the name, salary and department number for those employees whose managers are hold the ID 120, 103 or 145

Pandas HR database Queries: Exercise-18 with Solution

Write a Pandas program to display the first name, last name, salary and department number for those employees whose managers are hold the ID 120, 103 or 145.

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    Manager ID")
result = employees[employees['manager_id'].isin([120, 103, 145])]

for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['salary']).ljust(9),row['manager_id'])

Sample Output:

First name      Last name       Salary    Manager ID
Bruce           Ernst           6000      103.0
David           Austin          4800      103.0
Valli           Pataballa       4800      103.0
Diana           Lorentz         4200      103.0
Julia           Nayer           3200      120.0
Irene           Mikkilineni     2700      120.0
James           Landry          2400      120.0
Steven          Markle          2200      120.0
Peter           Tucker          10000     145.0
David           Bernstein       9500      145.0
Peter           Hall            9000      145.0
Christopher     Olsen           8000      145.0
Nanette         Cambrault       7500      145.0
Oliver          Tuvault         7000      145.0
Winston         Taylor          3200      120.0
Jean            Fleaur          3100      120.0
Martha          Sullivan        2500      120.0
Girard          Geoni           2800      120.0

Equivalent SQL Syntax:

SELECT first_name, last_name, email, 
  salary, manager_id
    FROM employees
     WHERE manager_id IN (120 , 103 , 145);

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 employees who works either in department 70 or 90.
Next: Write a Pandas program to display the first, last name, salary and department number for those employees who holds a letter n as a 3rd character in their first name.