w3resource

Pandas SQL Query: Exercises, Practice, Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

Pandas HR database Query [24 exercises with solution]

Click to see Structure of HR database

1. Write a Pandas program to display all the records of REGIONS file. Go to the editor

REGION.csv

Click me to see the sample solution

2. Write a Pandas program to display all the location id from locations file. Go to the editor

LOCATIONS.csv

Click me to see the sample solution

3. Write a Pandas program to extract first 7 records from employees file. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

4. Write a Pandas program to select distinct department id from employees file. Go to the editor

DEPARTMENTS.csv

Click me to see the sample solution

5. Write a Pandas program to display the first and last name, and department number for all employees whose last name is "McEwen". Go to the editor

EMPLOYEES.csv

DEPARTMENTS.csv

Click me to see the sample solution

6. 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'. Go to the editor

EMPLOYEES.csv

DEPARTMENTS.csv

Click me to see the sample solution

7. 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'. Go to the editor

DEPARTMENTS.csv

EMPLOYEES.csv

Click me to see the sample solution

8. Write a Pandas program to display the first name, last name, salary and department number in ascending order by department number. Go to the editor

DEPARTMENTS.csv

EMPLOYEES.csv

Click me to see the sample solution

9. Write a Pandas program to display the first name, last name, salary and department number in descending order by first name. Go to the editor

DEPARTMENTS.csv

EMPLOYEES.csv

Click me to see the sample solution

10. Write a Pandas program to display the first name, last name, salary and manger id where manager ids are null. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

11. Write a Pandas program to display the first name, last name, salary and manger id where manager ids are not null. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

12. Write a Pandas program to create and display a boolean series, where True for not null and False for null values or missing values in state_province column of locations file. Go to the editor

LOCATIONS.csv

Click me to see the sample solution

13. Write a Pandas program to create a boolean series selecting rows with one or more nulls from locations file. Go to the editor

LOCATIONS.csv

Click me to see the sample solution

14. Write a Pandas program to count the NaN values of all the columns of locations file. Go to the editor

LOCATIONS.csv

Click me to see the sample solution

15. 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'. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

16. 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. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

17. 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. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

18. 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. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

19. 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. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

20. Write a Pandas program to display the first name, job id, salary and department for those employees not working in the departments 50,30 and 80. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

21. Write a Pandas program to display the ID for those employees who did two or more jobs in the past. Go to the editor

JOB_HISTORY.csv

Click me to see the sample solution

22. Write a Pandas program to calculate minimum, maximum and mean salary from employees file. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

23. Write a Pandas program to display the details of jobs in descending sequence on job title. Go to the editor

JOBS.csv

Click me to see the sample solution

24. 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. Go to the editor

EMPLOYEES.csv

Click me to see the sample solution

Python Code Editor:


Structure of HR database :

HR database

[ Want to contribute to Python Pandas exercises? Send your code (attached with a .zip file) to us at w3resource[at]yahoo[dot]com. Please avoid copyrighted materials.]

Test your Python skills with w3resource's quiz



Python: Tips of the Day

Python: Annotated Assignment Statement

This might not seem as impressive as some other tricks but it's a new syntax that was introduced to Python in recent years and just good to be aware of.

Annotated assignments allow the coder to leave type hints in the code. These don't have any enforcing power at least not yet. It's still nice to be able to imply some type hints and definitely offers more options than only being able to comment regarding expected types of variables.

day: str = 'Monday'
print(day)
lst: list = [1,2,3,4]
print(lst)

Output:

Monday
[1, 2, 3, 4]

Or the same thing in a shorter way:

day= 'Monday' #str
print(day)
lst= [1,2,3,4] # list
print(lst)

Output:

Monday
[1, 2, 3, 4]