Query to Find Employees Who Earn More Than the Average Salary

Explanation

This query identifies employees whose salaries are above the average salary of all employees in the organization. Here's how it works:

1. First, it calculates the average salary across all employees
2. Then it compares each employee's salary against this average
3. Finally, it returns only those employees whose salary is higher than the average

Dummy Data Table: Employees

Let's create a sample table with employee data:

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

INSERT INTO employees VALUES
(1, 'John', 'Doe', 'IT', 75000.00),
(2, 'Jane', 'Smith', 'HR', 65000.00),
(3, 'Michael', 'Johnson', 'Finance', 82000.00),
(4, 'Emily', 'Williams', 'Marketing', 58000.00),
(5, 'David', 'Brown', 'IT', 90000.00),
(6, 'Sarah', 'Davis', 'Finance', 78000.00),
(7, 'Robert', 'Miller', 'HR', 62000.00),
(8, 'Jennifer', 'Wilson', 'Marketing', 71000.00);

The Query

SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

How the Query Works

1. The subquery (SELECT AVG(salary) FROM employees) calculates the average salary of all employees
2. The main query then filters employees whose salary is greater than this average
3. Results are ordered by salary in descending order (highest to lowest)

Expected Output

Based on our dummy data, the average salary is (75000+65000+82000+58000+90000+78000+62000+71000)/8 = 72625.00

The output would show employees earning more than $72,625:

employee_id | first_name | last_name | department | salary
------------|------------|-----------|------------|--------
5           | David      | Brown     | IT         | 90000.00
3           | Michael    | Johnson   | Finance    | 82000.00
6           | Sarah      | Davis     | Finance    | 78000.00
1           | John       | Doe       | IT         | 75000.00
8           | Jennifer   | Wilson    | Marketing  | 71000.00

Alternative Query with Average Displayed

If you want to include the average salary in the output for reference:

SELECT e.employee_id, e.first_name, e.last_name, e.department, e.salary, 
       (SELECT ROUND(AVG(salary), 2) FROM employees) AS avg_salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.salary DESC;

This would add an extra column showing the average salary (72625.00) for each row in the result set.