Query to Fetch Top 5 Highest Paid Employees

I'll explain how to write a SQL query to fetch the top 5 highest paid employees, including creating a dummy table with sample data and showing the expected output.

Creating a Dummy Employee Table

First, let's create a table called employees with some relevant columns:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Inserting Dummy Data

Now, let's insert some sample data:

INSERT INTO employees (employee_id, first_name, last_name, department, salary, hire_date) VALUES
(1, 'John', 'Smith', 'IT', 85000.00, '2020-01-15'),
(2, 'Sarah', 'Johnson', 'HR', 92000.00, '2019-05-22'),
(3, 'Michael', 'Williams', 'Finance', 110000.00, '2018-03-10'),
(4, 'Emily', 'Brown', 'Marketing', 78000.00, '2021-02-18'),
(5, 'David', 'Jones', 'IT', 95000.00, '2019-11-05'),
(6, 'Jessica', 'Garcia', 'Finance', 125000.00, '2017-08-30'),
(7, 'Daniel', 'Miller', 'Operations', 82000.00, '2020-07-12'),
(8, 'Lisa', 'Davis', 'HR', 88000.00, '2021-01-25'),
(9, 'Robert', 'Rodriguez', 'IT', 105000.00, '2018-09-14'),
(10, 'Jennifer', 'Martinez', 'Marketing', 76000.00, '2022-03-08');

Query to Fetch Top 5 Highest Paid Employees

Here's the SQL query to get the top 5 highest paid employees:

SELECT employee_id, first_name, last_name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Alternative Syntax (for different database systems):

For SQL Server (uses TOP instead of LIMIT):

SELECT TOP 5 employee_id, first_name, last_name, department, salary
FROM employees
ORDER BY salary DESC;

For Oracle:

SELECT employee_id, first_name, last_name, department, salary
FROM (
    SELECT employee_id, first_name, last_name, department, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Expected Output

The output of the query would look like this:

Explanation of the Query

1. SELECT employee_id, first_name, last_name, department, salary - This specifies which columns we want to retrieve from the table.
2. FROM employees - This specifies the table we're querying.
3. ORDER BY salary DESC - This sorts the results by salary in descending order (highest to lowest).
4. LIMIT 5 - This restricts the output to only the first 5 rows after sorting.

Additional Variations

If you want to include ranking information:

SELECT 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
    employee_id, 
    first_name, 
    last_name, 
    department, 
    salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Output with ranking:

This query provides a clear way to identify the top earners in an organization along with their relevant details.