Pandas SQL Query: Exercises, Practice, Solution
This resource offers a total of 120 Pandas HR database Query problems for practice. It includes 24 main exercises, each accompanied by solutions, detailed explanations, and four related problems.
[An Editor is available at the bottom of the page to write and execute the scripts.]
Click to see Structure of HR database and Download from here.
1. Display All Records of REGIONS File
Write a Pandas program to display all the records of REGIONS file.
REGION.csv
Click me to see the sample solution
2. Display All Location IDs from LOCATIONS File
Write a Pandas program to display all the location id from locations file.
LOCATIONS.csv
Click me to see the sample solution
3. Extract First 7 Records from EMPLOYEES File
Write a Pandas program to extract first 7 records from employees file.
EMPLOYEES.csv
Click me to see the sample solution
4. Select Distinct Department IDs from DEPARTMENTS File
Write a Pandas program to select distinct department id from employees file.
DEPARTMENTS.csv
Click me to see the sample solution
5. Employee Names & Dept No. for Last Name "McEwen"
Write a Pandas program to display the first and last name, and department number for all employees whose last name is "McEwen".
EMPLOYEES.csv
DEPARTMENTS.csv
Click me to see the sample solution
6. Display Employees Whose First Name Starts with 'S'
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
Click me to see the sample solution
7. Display Employees Whose First Name Does Not Contain 'M'
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'.
DEPARTMENTS.csv
EMPLOYEES.csv
Click me to see the sample solution
8. Display Records in Ascending Order by Department Number
Write a Pandas program to display the first name, last name, salary and department number in ascending order by department number.
DEPARTMENTS.csv
EMPLOYEES.csv
Click me to see the sample solution
9. Display Records in Descending Order by First Name
Write a Pandas program to display the first name, last name, salary and department number in descending order by first name.
DEPARTMENTS.csv
EMPLOYEES.csv
Click me to see the sample solution
10. Display Employees with Null Manager IDs
Write a Pandas program to display the first name, last name, salary and manger id where manager ids are null.
EMPLOYEES.csv
Click me to see the sample solution
11. Display Employees with Non-Null Manager IDs
Write a Pandas program to display the first name, last name, salary and manger id where manager ids are not null.
EMPLOYEES.csv
Click me to see the sample solution
12. Boolean Series for Non-Null state_province Values
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.
LOCATIONS.csv
Click me to see the sample solution
13. Boolean Series for Rows with Any Null in LOCATIONS
Write a Pandas program to create a boolean series selecting rows with one or more nulls from locations file.
LOCATIONS.csv
Click me to see the sample solution
14. Count NaN Values of All Columns in LOCATIONS File
Write a Pandas program to count the NaN values of all the columns of locations file.
LOCATIONS.csv
Click me to see the sample solution
15. Display Employees Whose First Name Ends with 'm'
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'.
EMPLOYEES.csvClick me to see the sample solution
16. Employees with First Name Ending in d, n, or s (Desc)
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.csvClick me to see the sample solution
17. Display Employees Working in Department 70 or 90
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
Click me to see the sample solution
18. Display Employees Whose Managers Have IDs 120, 103, or 145
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
Click me to see the sample solution
19. Employees with 'n' as 3rd Char in First Name
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.
EMPLOYEES.csv
Click me to see the sample solution
20. Display Employees Not Working in Departments 50, 30, and 80
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.
EMPLOYEES.csv
Click me to see the sample solution
21. Display IDs for Employees Who Had Two or More Past Jobs
Write a Pandas program to display the ID for those employees who did two or more jobs in the past.
JOB_HISTORY.csv
Click me to see the sample solution
22. Calculate Minimum, Maximum, and Mean Salary
Write a Pandas program to calculate minimum, maximum and mean salary from employees file.
EMPLOYEES.csv
Click me to see the sample solution
23. Display Job Details in Descending Order by Job Title
Write a Pandas program to display the details of jobs in descending sequence on job title.
JOBS.csv
Click me to see the sample solution
24. Employee Name & DOJ for Sales Reps or Sales Men
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.
EMPLOYEES.csvClick me to see the sample solution
Python Code Editor:
Structure of HR database :
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
[ 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