Pandas SQL Query: Display the name, salary and department number for employees who works either in department 70 or 90

Pandas HR database Queries: Exercise-17 with Solution

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.

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['department_id'].isin([70, 90])]

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
Steven          King            24000     90.0
Neena           Kochhar         17000     90.0
Lex             De Haan         17000     90.0
Hermann         Baer            10000     70.0

Equivalent SQL Syntax:

SELECT first_name, last_name, salary, department_id
 FROM employees
  WHERE department_id IN (70 , 90);

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 'd' or 'n' or 's' and also arrange the result in descending order by department id.
Next: 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.