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.