SQL JOINS on HR Database: Display the full name, job title and salary differences of those employees who is working in the department ID 80
SQL JOINS on HR Database: Exercise-16 with Solution
16. From the following tables, write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.
Sample table: employees
Sample table: jobs
SELECT job_title, first_name || ' ' || last_name AS Employee_name, max_salary-salary AS salary_difference FROM employees NATURAL JOIN jobs WHERE department_id = 80;
job_title employee_name salary_difference 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 Charles Johnson 5800.00
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: 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.
Next: From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- 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