SQL Exercises: Salary earn by the employee which is maximum
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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | ...... +---------------+----------------------+------------+-------------+
Sample table: locations
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | ........ +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
Sample Solution:
-- Selecting specific columns (a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city) from the 'employees' table (aliased as a), 'departments' table (aliased as b), and 'locations' table (aliased as c)
SELECT a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city
-- Filtering rows based on the condition that the 'salary' of employee a is equal to the maximum salary within a specified date range
FROM employees a, departments b, locations c
-- Subquery to find the maximum 'salary' within the date range '01/01/2002' to '12/31/2003' in the 'employees' table
WHERE a.salary =
(SELECT MAX(salary)
FROM employees
WHERE hire_date BETWEEN '01/01/2002' AND '12/31/2003')
-- Additional conditions to match 'department_id' between a and b, and 'location_id' between b and c
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:
Alternative Statement:
Using Subquery with JOIN:
SELECT a.employee_id, a.first_name, a.last_name, a.salary, b.department_name, c.city
FROM employees a
JOIN departments b ON a.department_id = b.department_id
JOIN locations c ON b.location_id = c.location_id
WHERE a.salary =
(SELECT MAX(salary)
FROM employees
WHERE hire_date BETWEEN '2002-01-01' AND '2003-12-31');
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.