Pandas SQL Query: Display the name, salary and department number in descending order by first name
Pandas HR database Queries: Exercise-9 with Solution
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
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.sort_values('first_name', ascending=False)
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 Winston Taylor 3200 50.0 William Gietz 8300 110.0 William Smith 7400 80.0 Vance Jones 2800 50.0 Valli Pataballa 4800 60.0 Trenna Rajs 3500 50.0 Timothy Gates 2900 50.0 Tayler Fox 9600 80.0 TJ Olson 2100 50.0 Susan Mavris 6500 40.0 Sundita Kumar 6100 80.0 Sundar Ande 6400 80.0 Steven Markle 2200 50.0 Steven King 24000 90.0 Stephen Stiles 3200 50.0 Sigal Tobias 2800 30.0 Shelli Baida 2900 30.0 Shelley Higgins 12000 110.0 Shanta Vollman 6500 50.0 Sarath Sewall 7000 80.0 Sarah Bell 4000 50.0 Samuel McCain 3200 50.0 Renske Ladwig 3600 50.0 Randall Perkins 2500 50.0 Randall Matos 2600 50.0 Peter Vargas 2500 50.0 Peter Hall 9000 80.0 Peter Tucker 10000 80.0 Payam Kaufling 7900 50.0 Patrick Sully 9500 80.0 Pat Fay 6000 20.0 Oliver Tuvault 7000 80.0 Neena Kochhar 17000 90.0 Nanette Cambrault 7500 80.0 Nandita Sarchand 4200 50.0 Nancy Greenberg 12000 100.0 Mozhe Atkinson 2800 50.0 Michael Rogers 2900 50.0 Michael Hartstein 13000 20.0 Matthew Weiss 8000 50.0 Mattea Marvins 7200 80.0 Martha Sullivan 2500 50.0 Luis Popp 6900 100.0 Louise Doran 7500 80.0 Lisa Ozer 11500 80.0 Lindsey Smith 8000 80.0 Lex De Haan 17000 90.0 Laura Bissot 3300 50.0 Kimberely Grant 7000 nan Ki Gee 2400 50.0 Kevin Feeney 3000 50.0 Kevin Mourgos 5800 50.0 Kelly Chung 3800 50.0 Karen Partners 13500 80.0 Karen Colmenares 2500 30.0 Julia Nayer 3200 50.0 Julia Dellinger 3400 50.0 Joshua Patel 2500 50.0 Jose Manuel Urman 7800 100.0 Jonathon Taylor 8600 80.0 John Seo 2700 50.0 John Russell 14000 80.0 John Chen 8200 100.0 Jennifer Whalen 4400 10.0 Jennifer Dilly 3600 50.0 Jean Fleaur 3100 50.0 Jason Mallin 3300 50.0 Janette King 10000 80.0 James Landry 2400 50.0 James Marlow 2500 50.0 Jack Livingston 8400 80.0 Ismael Sciarra 7700 100.0 Irene Mikkilineni 2700 50.0 Hermann Baer 10000 70.0 Hazel Philtanker 2200 50.0 Harrison Bloom 10000 80.0 Guy Himuro 2600 30.0 Girard Geoni 2800 50.0 Gerald Cambrault 11000 80.0 Ellen Abel 11000 80.0 Elizabeth Bates 7300 80.0 Eleni Zlotkey 10500 80.0 Douglas Grant 2600 50.0 Donald OConnell 2600 50.0 Diana Lorentz 4200 60.0 Den Raphaely 11000 30.0 David Austin 4800 60.0 David Lee 6800 80.0 David Bernstein 9500 80.0 Danielle Greene 9500 80.0 Daniel Faviet 9000 100.0 Curtis Davies 3100 50.0 Clara Vishney 10500 80.0 Christopher Olsen 8000 80.0 Charles Johnson 6200 80.0 Bruce Ernst 6000 60.0 Britney Everett 3900 50.0 Anthony Cabrio 3000 50.0 Amit Banda 6200 80.0 Alyssa Hutton 8800 80.0 Allan McEwen 9000 80.0 Alexis Bull 4100 50.0 Alexander Khoo 3100 30.0 Alexander Hunold 9000 60.0 Alberto Errazuriz 12000 80.0 Alana Walsh 3100 50.0 Adam Fripp 8200 50.0
Equivalent SQL Syntax:
SELECT first_name, last_name, salary, department_id FROM employees ORDER BY first_name;
Click to view the table contain:
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 in ascending order by department number.
Next: Write a Pandas program to display the first name, last name, salary and manger id where manager ids are null.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/python-exercises/pandas/sql/python-pandas-hr-database-queries-exercise-9.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics