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.csvClick 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.csvClick 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.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
Python: Tips of the Day
Understanding slice notation:
It's pretty simple really:
a[start:stop] # items start through stop-1 a[start:] # items start through the rest of the array a[:stop] # items from the beginning through stop-1 a[:] # a copy of the whole array
There is also the step value, which can be used with any of the above:
a[start:stop:step] # start through not past stop, by step
The key point to remember is that the :stop value represents the first value that is not in the selected slice. So, the difference between stop and start is the number of elements selected (if step is 1, the default).
The other feature is that start or stop may be a negative number, which means it counts from the end of the array instead of the beginning. So:
a[-1] # last item in the array a[-2:] # last two items in the array a[:-2] # everything except the last two items
Similarly, step may be a negative number:
a[::-1] # all items in the array, reversed a[1::-1] # the first two items, reversed a[:-3:-1] # the last two items, reversed a[-3::-1] # everything except the last two items, reversed
Python is kind to the programmer if there are fewer items than you ask for. For example, if you ask for a[:-2] and a only contains one element, you get an empty list instead of an error. Sometimes you would prefer the error, so you have to be aware that this may happen.
Relation to slice() object
The slicing operator [] is actually being used in the above code with a slice() object using the : notation (which is only valid within []), i.e.:
a[start:stop:step]
is equivalent to:
a[slice(start, stop, step)]
Slice objects also behave slightly differently depending on the number of arguments, similarly to range(), i.e. both slice(stop) and slice(start, stop[, step]) are supported. To skip specifying a given argument, one might use None, so that e.g. a[start:] is equivalent to a[slice(start, None)] or a[::-1] is equivalent to a[slice(None, None, -1)].
While the : -based notation is very helpful for simple slicing, the explicit use of slice() objects simplifies the programmatic generation of slicing.
Ref: https://bit.ly/2MHaTp7
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework