SQL Exercises: Salary earn by the employee which is maximum
SQL SUBQUERY: Exercise-34 with Solution
From the following table, write a SQL query to search for employees who receive such a salary, which is the maximum salary for salaried employees, hired between January 1st, 2002 and December 31st, 2003. Return employee ID, first name, last name, salary, department name and city.
Sample table: employees
Sample table: departments
Sample table: locations
Sample Solution:
SELECT a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city
FROM employees a, departments b, locations c
WHERE a.salary =
(SELECT MAX(salary)
FROM employees
WHERE hire_date BETWEEN '01/01/2002' AND '12/31/2003')
AND a.department_id=b.department_id
AND b.location_id=c.location_id;
Sample Output:
employee_id first_name last_name salary department_name city 100 Steven King 24000.00 Executive Seattle
Code Explanation:
The said query in SQL that retrieves information from multiple tables, 'employees', 'departments', and 'locations'. The query returns the employee ID, first name, last name, salary, department name, and city of the employee with the highest salary among those hired between January 1, 2002 and December 31, 2003.
The information is obtained by joining the three tables based on the relationships between them, as specified in the "WHERE" clause of the query. In the main query, the subquery finds the highest salary among employees hired between January 1, 2002 and December 31, 2003, and the main query returns the details of that employee.
Visual Presentation:

Practice Online
Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Details of employees who manage a department.
Next SQL Exercise: Departments which located in the city London.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to request a random row in SQL?
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND() LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table ORDER BY RANDOM() LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID()
Select a random row with IBM DB2:
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1
Database: SQL Server, PostgreSQL Server, MySQL
Ref: https://bit.ly/39n35HP
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook