Solving tips
- Reach for a WHERE clause and combine conditions with AND when every condition must hold.
- String comparisons are case-sensitive in standard SQL, so match the department value exactly.
- Select only the columns the task asks for instead of using SELECT *.
You are given a table of company employees. Return the higher earners inside a single department so a hiring manager can review them.
Schema
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT,
salary INTEGER,
hire_date DATE
);
Sample data:
| id | name | department | salary | hire_date |
|---|
| 1 | Alice | Engineering | 95000 | 2019-03-01 |
| 2 | Bob | Engineering | 82000 | 2020-06-15 |
| 3 | Carol | Sales | 70000 | 2018-01-10 |
| 4 | Dave | Engineering | 110000 | 2017-09-23 |
| 5 | Eve | Marketing | 88000 | 2021-02-28 |
| 6 | Frank | Sales | 91000 | 2016-11-05 |
Task
Return the id, name, and salary of every employee in the Engineering department whose salary is greater than 85000. Order the rows by salary in descending order.
Expected output
| id | name | salary |
|---|
| 4 | Dave | 110000 |
| 1 | Alice | 95000 |
Approach
This is a straightforward filter. Use a WHERE clause with two conditions joined by AND: the department must equal Engineering and the salary must exceed 85000. Project only the requested columns and sort with ORDER BY salary DESC.
Query
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering'
AND salary > 85000
ORDER BY salary DESC;
Walkthrough
Scanning the sample rows, the Engineering employees are Alice (95000), Bob (82000), and Dave (110000). The salary > 85000 test keeps Alice and Dave but drops Bob at 82000. Non-Engineering rows (Carol, Eve, Frank) fail the department test even though Frank and Eve earn above the threshold. Ordering the two survivors by salary descending puts Dave (110000) first, then Alice (95000).
Complexity & notes
A single sequential scan over employees is enough; on a large table a composite index on (department, salary) would let the planner seek directly to the qualifying rows. Watch the comparison operator: > excludes an employee earning exactly 85000, whereas >= would include them. String equality is case-sensitive in PostgreSQL, so 'engineering' would match nothing; use ILIKE or lower(department) if case-insensitive matching is required.