Pandas SQL Query: Display the name, salary and department number for those employees whose first name starts with the letter 'S'

Pandas HR database Queries: Exercise-6 with Solution

Write a Pandas program to display the first, last name, salary and department number for those employees whose first name starts with the letter 'S'.

EMPLOYEES.csv

DEPARTMENTS.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]=='S']
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
Shelli          Baida           2900      30.0
Sigal           Tobias          2800      30.0
Shanta          Vollman         6500      50.0
Steven          Markle          2200      50.0
Stephen         Stiles          3200      50.0
Sarath          Sewall          7000      80.0
Sundar          Ande            6400      80.0
Sundita         Kumar           6100      80.0
Sarah           Bell            4000      50.0
Samuel          McCain          3200      50.0
Susan           Mavris          6500      40.0
Shelley         Higgins         12000     110.0

Equivalent SQL Syntax:

SELECT first_name, last_name, salary,  department_id
  FROM employees
   WHERE first_name  LIKE 'S%';

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 and last name, and department number for all employees whose last name is "McEwen".
Next: Write a Pandas program to display the first, last name, salary and department number for those employees whose first name does not contain the letter ‘M’.