w3resource

SQL Subquery Exercises: Display the employee id, name, salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003

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

Pictorial Presentation:

SQL Subqueries: Display the employee id, name, salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the employee id, name, salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003 - Duration

Rows:

Query visualization of Display the employee id, name, salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003 - Rows

Cost:

Query visualization of Display the employee id, name, salary, department name and city for all the employees who gets the salary as the salary earn by the employee which is maximum within the joining person January 1st, 2002 and December 31st, 2003 - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.
Next: From the following tables, write a SQL query to find those departments, located in the city ‘London’. Return department ID, department name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

Ref: https://bit.ly/3zbLPQm