SQL JOINS on HR Database: Display the job title, full name of employee, and the difference between maximum salary for the job and salary of the employee
SQL JOINS on HR Database: Exercise-14 with Solution
14. From the following tables, write a SQL query to find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.
Sample table: employees
Sample table: jobs
Sample Solution:
SELECT job_title, first_name || ' ' || last_name AS Employee_name,
max_salary-salary AS salary_difference
FROM employees
NATURAL JOIN jobs;
Sample Output:
job_title employee_name salary_difference President Steven King 16000.00 Administration Vice President Neena Kochhar 13000.00 Administration Vice President Lex De Haan 13000.00 Programmer Alexander Hunold 1000.00 Programmer Bruce Ernst 4000.00 Programmer David Austin 5200.00 Programmer Valli Pataballa 5200.00 Programmer Diana Lorentz 5800.00 Finance Manager Nancy Greenberg 4000.00 Accountant Daniel Faviet 0.00 Accountant John Chen 800.00 Accountant Ismael Sciarra 1300.00 Accountant Jose Manuel Urman 1200.00 Accountant Luis Popp 2100.00 Purchasing Manager Den Raphaely 4000.00 Purchasing Clerk Alexander Khoo 2400.00 Purchasing Clerk Shelli Baida 2600.00 Purchasing Clerk Sigal Tobias 2700.00 Purchasing Clerk Guy Himuro 2900.00 Purchasing Clerk Karen Colmenares 3000.00 Stock Manager Matthew Weiss 500.00 Stock Manager Adam Fripp 300.00 Stock Manager Payam Kaufling 600.00 Stock Manager Shanta Vollman 2000.00 Stock Manager Kevin Mourgos 2700.00 Stock Clerk Julia Nayer 1800.00 Stock Clerk Irene Mikkilineni 2300.00 Stock Clerk James Landry 2600.00 Stock Clerk Steven Markle 2800.00 Stock Clerk Laura Bissot 1700.00 Stock Clerk Mozhe Atkinson 2200.00 Stock Clerk James Marlow 2500.00 Stock Clerk TJ Olson 2900.00 Stock Clerk Jason Mallin 1700.00 Stock Clerk Michael Rogers 2100.00 Stock Clerk Ki Gee 2600.00 Stock Clerk Hazel Philtanker 2800.00 Stock Clerk Renske Ladwig 1400.00 Stock Clerk Stephen Stiles 1800.00 Stock Clerk John Seo 2300.00 Stock Clerk Joshua Patel 2500.00 Stock Clerk Trenna Rajs 1500.00 Stock Clerk Curtis Davies 1900.00 Stock Clerk Randall Matos 2400.00 Stock Clerk Peter Vargas 2500.00 Sales Manager John Russell 6000.00 Sales Manager Karen Partners 6500.00 Sales Manager Alberto Errazuriz 8000.00 Sales Manager Gerald Cambrault 9000.00 Sales Manager Eleni Zlotkey 9500.00 Sales Representative Peter Tucker 2000.00 Sales Representative David Bernstein 2500.00 Sales Representative Peter Hall 3000.00 Sales Representative Christopher Olsen 4000.00 Sales Representative Nanette Cambrault 4500.00 Sales Representative Oliver Tuvault 5000.00 Sales Representative Janette King 2000.00 Sales Representative Patrick Sully 2500.00 Sales Representative Allan McEwen 3000.00 Sales Representative Lindsey Smith 4000.00 Sales Representative Louise Doran 4500.00 Sales Representative Sarath Sewall 5000.00 Sales Representative Clara Vishney 1500.00 Sales Representative Danielle Greene 2500.00 Sales Representative Mattea Marvins 4800.00 Sales Representative David Lee 5200.00 Sales Representative Sundar Ande 5600.00 Sales Representative Amit Banda 5800.00 Sales Representative Lisa Ozer 500.00 Sales Representative Harrison Bloom 2000.00 Sales Representative Tayler Fox 2400.00 Sales Representative William Smith 4600.00 Sales Representative Elizabeth Bates 4700.00 Sales Representative Sundita Kumar 5900.00 Sales Representative Ellen Abel 1000.00 Sales Representative Alyssa Hutton 3200.00 Sales Representative Jonathon Taylor 3400.00 Sales Representative Jack Livingston 3600.00 Sales Representative Kimberely Grant 5000.00 Sales Representative Charles Johnson 5800.00 Shipping Clerk Winston Taylor 2300.00 Shipping Clerk Jean Fleaur 2400.00 Shipping Clerk Martha Sullivan 3000.00 Shipping Clerk Girard Geoni 2700.00 Shipping Clerk Nandita Sarchand 1300.00 Shipping Clerk Alexis Bull 1400.00 Shipping Clerk Julia Dellinger 2100.00 Shipping Clerk Anthony Cabrio 2500.00 Shipping Clerk Kelly Chung 1700.00 Shipping Clerk Jennifer Dilly 1900.00 Shipping Clerk Timothy Gates 2600.00 Shipping Clerk Randall Perkins 3000.00 Shipping Clerk Sarah Bell 1500.00 Shipping Clerk Britney Everett 1600.00 Shipping Clerk Samuel McCain 2300.00 Shipping Clerk Vance Jones 2700.00 Shipping Clerk Alana Walsh 2400.00 Shipping Clerk Kevin Feeney 2500.00 Shipping Clerk Donald OConnell 2900.00 Shipping Clerk Douglas Grant 2900.00 Administration Assistant Jennifer Whalen 1600.00 Marketing Manager Michael Hartstein 2000.00 Marketing Representative Pat Fay 3000.00 Human Resources Representative Susan Mavrs 2500.00 Public Relations Representative Hermann Bae 500.00 Accounting Manager Shelley Higgins 4000.00 Public Accountant William Gietz 700.00
Pictorial Presentation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: From the following tables, write a SQL query to find those employees who joined on 1st January 1993 and leave on or before 31 August 1997. Return job title, department name, employee name, and joining date of the job.
Next: From the following table, write a SQL query to compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- 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