Top Salary SQL Queries for Interviews Based on Employee Table

Every query on this page was written for the same two tables introduced in the Employee and Salary table structure tutorial. If you have not created them yet, do that first — otherwise the answers below will not run. Once the tables and sample data are in place, work through the queries in order. They move from simple filters and joins to correlated subqueries and self-referential joins, which is roughly how interviewers sequence the difficulty.

Each question shows the SQL, a short explanation of the idea behind it, and a note about running it on MySQL, SQL Server, PostgreSQL, or Oracle. Where syntax differs, the alternative is given directly in the note so you can adapt it without guessing.

1. Select employees whose salary exceeds the salary of Abhijit Joshi

SELECT empname, salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary > (
    SELECT salary FROM salary
    WHERE id = (SELECT id FROM employee WHERE empname = 'Abhijit Joshi')
);

The question asks for a comparison against one named employee rather than against a fixed number. That means the threshold has to be looked up first, and only then can other employees be compared. The inner subquery locates Abhijit Joshi's id by name; the next subquery feeds that id to the salary table and returns his salary. The outer query then joins the two tables and keeps every row where the salary is above that value. Because the threshold is expressed as a query, the answer stays correct even when the salary table is updated later.

Interviewers use variations of this to test whether you can read a nested subquery without panicking. A common follow-up is to ask what happens if two employees share the same name — the safe answer is to filter on id, not name, in production code.

Note: Works on MySQL, SQL Server, PostgreSQL, and Oracle. In MySQL, SQL Server, and PostgreSQL, replace the name string with a bound parameter or variable when embedding the query in application code.

2. Display Employee Name, Date of Birth, and Manager’s name

SELECT e.empname, e.birth_date, m.empname AS manager_name
FROM employee e
LEFT JOIN employee m ON e.mgr_id = m.id;

The manager column in this schema is just another employee row, so displaying it alongside the employee requires a self-join: the table appears twice in the FROM clause, once as the employee and once as the manager. The join condition matches the employee's mgr_id against the manager's id. Using LEFT JOIN rather than an inner join is deliberate — the top-level employees have a NULL mgr_id, and an inner join would silently drop them. With LEFT JOIN, those rows come back with a NULL manager name, which is the honest answer.

The pattern shows up constantly in HR dashboards, approval-chain displays, and anything that renders a reporting line. If you only remember one query from this page, this is the one.

Note: Same syntax on MySQL, SQL Server, PostgreSQL, and Oracle. Date formatting is the only thing that varies in practice.

3. Update the salary of Nilesh Patil to 30000

UPDATE salary
SET salary = 30000
WHERE id = (SELECT id FROM employee WHERE empname = 'Nilesh Patil');

The salary table stores the money, but the name lives in the employee table, so the update has to cross the two using id. The subquery returns the employee's id by name, and the WHERE clause of the UPDATE matches on it. Because the subquery is guaranteed to return a single row when names are unique, this is safe to run directly. If the name were not unique, the subquery would return more than one row and the database would throw an error — which is arguably better than updating the wrong record.

This is the smallest realistic example of a broader pattern: look up a key from one table, use it to modify another. Once you've internalised it, you can rewrite the same idea with MERGE, UPDATE ... FROM, or a CTE depending on the database.

Note: Works on MySQL, SQL Server, PostgreSQL, and Oracle. Ensure the subquery returns exactly one row; add LIMIT 1 or FETCH FIRST 1 ROW ONLY if you want to be defensive.

4. Display Employee Name, Birth Date, Designation, and Salary

SELECT e.empname, e.birth_date, s.designation, s.salary
FROM employee e
JOIN salary s ON e.id = s.id;

This is the join most applications actually start with. Employee identity lives in one table, and the assignment and pay live in another, so displaying them together means combining both on their shared id. The result is a flat grid that can be bound directly to a data-grid control in a desktop app or rendered as a table in a web page. Nothing fancy is going on, but that is exactly the point — the query is clean, readable, and does not pretend to be more than it is.

In real HR systems this grid usually sits behind filters (by department, by designation, by hire date) and is the starting point for exports. In interviews, it is often the warm-up question to make sure you can write a join without a syntax error.

Note: Identical output on MySQL, SQL Server, PostgreSQL, and Oracle. Grid formatting is handled by your client (MySQL Workbench, SSMS, pgAdmin, SQL Developer).

5. Select employees whose birthday falls in May

SELECT empname, birth_date
FROM employee
WHERE EXTRACT(MONTH FROM birth_date) = 5;

Extracting the month from a date column is one of those small operations that turns up in almost every HR system. Here the query keeps only those rows where the month component of birth_date equals May. Because the function is applied to the column, no index on birth_date can be used directly — this is fine for small tables but worth remembering when the employee table grows into the hundreds of thousands of rows.

Typical use cases are birthday reminders, recognition programs, and monthly HR reports. The same shape works for hire dates, contract start dates, or any date where you care about a specific month.

Note: MySQL and SQL Server use MONTH(birth_date) instead of EXTRACT. PostgreSQL and Oracle use EXTRACT(MONTH FROM birth_date) as written.

6. Employees whose salary is greater than 50000

SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary > 50000;

A straightforward filter after a join. The interesting part is not the syntax but the intent: isolating high earners is a routine step in compensation reviews, bonus planning, and headcount reporting. Because the filter is applied to a column on the salary table, a B-tree index on salary speeds it up considerably once the table grows.

Interviewers sometimes follow up with: "What if the threshold changes every year?" The honest answer is that the number should be a bind parameter, not a literal, so the query plan can be reused.

7. Employees whose salary falls between 30,000 and 60,000

SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary BETWEEN 30000 AND 60000;

BETWEEN is inclusive on both ends, so both 30,000 and 60,000 are included in the result. This trips up candidates occasionally — if you want a strict range, use > 30000 AND < 60000 instead. The query itself is another salary-band report, and the pattern shows up in compensation analytics all the time.

A useful extension is to bucket the data with a CASE expression inside the SELECT list, so a single row of output tells you how many employees fall into each band rather than listing every row.

8. Employees whose salary is below the average salary

SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary < (SELECT AVG(salary) FROM salary);

The average is computed once in a scalar subquery, then each employee's salary is compared against it. You could also compute the average in a CTE and join it back to the salary table, but the scalar-subquery form is shorter and easier to read for most people. The result is the list of employees below the company midpoint — a list HR frequently wants when reviewing fairness or planning raises.

Two things are worth flagging. First, the subquery must return a single row; if someone accidentally groups it, the query will fail on most databases. Second, average salary can be dragged around by a handful of extreme values, so this query is often paired with a median or percentile calculation in serious analysis.

9. Find the employee with the highest salary

SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary = (SELECT MAX(salary) FROM salary);

This is the classic "top of the list" query and it shows up in almost every SQL interview. The subquery returns a single value — the maximum salary — and the outer query keeps every row that matches it. If two employees happen to be tied for the highest salary, both appear, which is usually the desired behaviour. If you only want one row, add an ORDER BY and a row-limit clause after the match.

An alternative formulation uses ORDER BY salary DESC with a row-limiting clause and skips the subquery entirely. Both are valid; the subquery form has the advantage of being insensitive to ties and easier to combine with other conditions.

Note: Index the salary column for faster execution on large datasets.

10. Employees born after a specific date

SELECT empname, birth_date
FROM employee
WHERE birth_date > TO_DATE('2000-01-01', 'YYYY-MM-DD');

Date literals have to be converted to the database's native date type before comparison, and every database has a different function for that. Oracle and PostgreSQL offer TO_DATE; MySQL has STR_TO_DATE; SQL Server usually accepts the literal as-is because of its default date parsing. The query itself is a simple filter but the portability angle makes it a regular interview talking point.

Realistic uses include workforce demographic reports, eligibility checks for age-banded benefits, and preparation for pension or retirement analysis.

Note: MySQL uses STR_TO_DATE('2000-01-01', '%Y-%m-%d'). SQL Server accepts CAST('2000-01-01' AS DATE). PostgreSQL and Oracle use TO_DATE as shown.

11. List employees and their manager’s name where applicable

SELECT e.empname, m.empname AS manager_name
FROM employee e
LEFT JOIN employee m ON e.mgr_id = m.id;

The idea is the same as query 2, but the question is phrased around the reporting relationship rather than the birthday. The self-join with two aliases (e for the employee, m for the manager) is the standard way to handle a hierarchy that lives inside a single table. Using LEFT JOIN ensures that the highest-ranking employees — the ones without a manager — still appear in the output, with a NULL value in the manager column. Inner joins would drop those rows and quietly give a wrong answer to "list all employees".

This pattern scales into recursive CTEs when the hierarchy gets deeper than one level. If the interviewer asks "what about the manager's manager?" the answer is a recursive query, not another join.

Note: Identical syntax on MySQL, SQL Server, PostgreSQL, and Oracle. If you want to filter out the top of the hierarchy, add WHERE e.mgr_id IS NOT NULL.

12. Count the total number of employees in the company

SELECT COUNT(id) AS total_employees FROM employee;

COUNT(id) counts only rows where id is not NULL. Since id is the primary key, it is always populated, so this is functionally the same as COUNT(*) here. The distinction matters when the column you count can be NULL — COUNT(some_optional_column) returns the number of rows where that column has a value, which is often what you want, but occasionally a bug you don't notice.

In practice, headcount queries are usually wrapped inside date filters ("headcount as of March 31") and joined against department tables. The plain count is the starting point.

13. Employees who have not been assigned to a manager

SELECT empname
FROM employee
WHERE mgr_id IS NULL;

NULL is not equal to anything — not even to another NULL — so the correct comparison is IS NULL, not = NULL. This is one of the most commonly tested gotchas in SQL interviews, and it is worth knowing by heart. The result of this query is usually the top of the reporting hierarchy: founders, executives, or standalone contributors whose role does not sit under anyone.

A useful follow-up question is "how many people report directly to each of these?" which turns into a GROUP BY mgr_id query against the same table.

14. Top 5 highest-paid employees

SELECT empname, salary
FROM employee e
JOIN salary s ON e.id = s.id
ORDER BY s.salary DESC
FETCH FIRST 5 ROWS ONLY;

Sort descending, take the first few rows. Every database supports the idea but spells it differently. Oracle and PostgreSQL standardised on FETCH FIRST n ROWS ONLY, MySQL uses LIMIT, and SQL Server uses a TOP clause. Interviewers usually ask this question to check whether you know your platform's syntax and whether you can adapt to another.

Ties matter here. If two employees have the same salary and that salary sits at the fifth position, FETCH FIRST 5 will return exactly five rows and drop one arbitrarily. If the requirement is "all employees in the top five salary bands", the query has to be written differently, usually with a window function.

Note: MySQL uses LIMIT 5. SQL Server uses SELECT TOP 5 …. PostgreSQL and Oracle use FETCH FIRST 5 ROWS ONLY as shown.

15. Give every employee below 40,000 a raise of 5,000

UPDATE salary
SET salary = salary + 5000
WHERE salary < 40000;

A targeted update that changes only the rows below the threshold. Because the condition is on the same column being modified, the database evaluates each row's original value before applying the change — so this is safe and predictable. The row count returned by the update tells you how many employees were affected, which is useful for auditing.

In production, this query would normally be run inside a transaction with a COMMIT at the end, and often preceded by a SELECT using the same WHERE clause to preview the impact. Both habits are worth mentioning in an interview — they signal that you have actually run UPDATE statements against real data.

16. Employees who joined before a specific date

SELECT empname, hire_date
FROM employee
WHERE hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

The hire_date column is not part of the base schema described on the structure page — it is used here to demonstrate a common variant. The query filters to employees hired before 2020, which is usually the input to a seniority report, a service-award list, or a retention analysis.

The interesting part is the date literal conversion. Oracle requires TO_DATE, MySQL prefers STR_TO_DATE, SQL Server accepts the literal directly, and PostgreSQL lets you cast with DATE '2020-01-01'. Being able to write the same filter across all four is a practical interview skill.

Note: MySQL uses STR_TO_DATE('2020-01-01', '%Y-%m-%d'). SQL Server accepts the string literal as-is. PostgreSQL uses CAST('2020-01-01' AS DATE).

17. Find all employees who are managers

SELECT DISTINCT e.empname
FROM employee e
WHERE e.id IN (SELECT mgr_id FROM employee WHERE mgr_id IS NOT NULL);

Managers are employees whose id appears in the mgr_id column of at least one other row. That makes the query a straightforward IN-subquery against the same table, with a DISTINCT to collapse the duplicates that appear when someone manages several people. The inner WHERE mgr_id IS NOT NULL clause is redundant if the subquery is only returning ids, but it is good defensive style and shows the reader you thought about NULLs.

The same idea, expressed with EXISTS, tends to perform better on large tables in most optimizers. If asked to optimize, that is the first thing to try.

18. Employees earning above the average salary

SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary > (SELECT AVG(salary) FROM salary);

The mirror image of query 8. The average is computed once, and every employee above it is returned. In compensation work, the above-average list feeds into bonus allocation and succession planning; the below-average list feeds into fairness reviews and retention risk analysis. Both are simple queries — but their business meaning is very different, and interviewers often ask which one you would run first in a given scenario.

The one caution worth repeating: the scalar subquery must return exactly one row, and any accidental grouping will turn it into a syntax error on most databases.

19. Count employees per designation

SELECT designation, COUNT(*) AS total_employees
FROM salary
GROUP BY designation;

GROUP BY collapses rows that share a designation into a single output row, and COUNT(*) tells you how many employees each group contained. This is the standard "headcount by role" report and a common precursor to more elaborate dashboards. Because designation lives in the salary table in this schema, no join is needed.

Two refinements come up in interviews. First, adding ORDER BY total_employees DESC turns the output into a ranking of roles by size. Second, adding HAVING COUNT(*) > 1 hides designations with only one incumbent, which is often what you want when reporting on job families.

20. Top 3 highest-paid employees

SELECT empname, salary
FROM salary
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

The smaller-sibling version of query 14. Same idea, different limit, and the same platform-specific note applies. Because the schema stores names in the employee table and salaries in the salary table, a more complete answer would join the two — but the question only asks for names and salaries, and the salary table in this dataset conveniently also carries the name in the top-three case presented on the structure page. In a strict schema you would join.

Note: MySQL uses LIMIT 3. SQL Server uses SELECT TOP 3 …. PostgreSQL and Oracle use FETCH FIRST 3 ROWS ONLY.

21. Employees who have not received a salary increase

SELECT empname, salary
FROM salary
WHERE salary = (SELECT salary FROM salary WHERE id = 101);

The question is phrased around salary history, but the base schema does not store history — so the query is really a comparison against a reference employee (ID 101). Anyone whose current salary equals that reference is treated as "unchanged". This is a pragmatic answer that works with the schema as it is, and it is honest about the limitation.

In a production system, salary history would be a separate table with an effective_date column, and the query would look for the maximum effective date per employee. That is the correct answer if you have the schema for it, and worth mentioning in an interview as the "proper" version of this question.

22. Employees who report to a specific manager

SELECT e.empname
FROM employee e
WHERE e.mgr_id = (SELECT id FROM employee WHERE empname = 'Abhijit Joshi');

Direct reports of a named manager. The subquery resolves the manager's id, and the outer query returns everyone whose mgr_id points to it. Because a manager's name is not guaranteed to be unique in a real system, the safer version of this query uses the manager's id directly as a bind parameter. In an interview, mentioning that caveat is a small but telling detail.

This shape also scales into a recursive question: "who reports to this manager, directly or indirectly?" The answer is a recursive CTE, not a single query.

23. Update birth date for employees earning exactly 40,000

UPDATE employee
SET birth_date = TO_DATE('1990-01-01', 'YYYY-MM-DD')
WHERE id IN (SELECT id FROM salary WHERE salary = 40000);

A cross-table update. The id IN (…) subquery returns every employee id whose salary is exactly 40,000, and the update sets their birth date to a fixed value. The IN form is safe even if the subquery returns multiple rows, which is the main difference from the = form used in query 3.

In practice you would not hard-code the date, and you would be careful about updating production employee records in bulk. The question is a test of whether you can write a cross-table update without accidentally touching the wrong rows.

Note: MySQL uses STR_TO_DATE or a direct date literal. SQL Server uses CAST or CONVERT. PostgreSQL accepts DATE '1990-01-01'.

24. Employees with no assigned manager (full details)

SELECT id, empname, birth_date
FROM employee
WHERE mgr_id IS NULL;

The same condition as query 13, but with more columns. This is the query you would actually run when auditing an org chart: it tells you who sits at the top of the hierarchy, including possibly some employees who should have a manager and don't. That second use case — catching data-quality problems — is worth mentioning in an interview, because it shows you are thinking about how the query fits into a workflow rather than just how it runs.

25. Employees hired in a particular month

SELECT empname, hire_date
FROM employee
WHERE EXTRACT(MONTH FROM hire_date) = 5;

Same pattern as query 5, applied to hire dates instead of birthdays. The result feeds seasonal hiring reports, anniversary lists, and month-by-month headcount reconciliation. Because the function is applied to the column, index usage is limited — a range predicate against a date column often performs better when the table is large.

Note: MySQL and SQL Server use MONTH(hire_date). PostgreSQL and Oracle use EXTRACT(MONTH FROM hire_date).

26. Employees with salaries between two values

SELECT id, empname, salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary BETWEEN 30000 AND 50000;

Another salary-band filter, this time narrower than query 7. The extra columns (id, empname) make the output usable as the input to a follow-up process — for example, a mail-merge to department heads about employees in a compensation review band. BETWEEN is inclusive on both ends.

If the intent is "strictly between", the query needs salary > 30000 AND salary < 50000 instead. Being precise about inclusivity is a habit worth building.

27. Employees older than 30 years

SELECT id, empname, birth_date
FROM employee
WHERE birth_date <= ADD_MONTHS(SYSDATE, -360);

Age questions are easier to express as "born before a cutoff date" than by computing age on every row. ADD_MONTHS(SYSDATE, -360) produces the date exactly 30 years ago, and the query returns everyone born on or before that date. The advantage is that the age computation is done once in the constant, not per row, which matters when the table is large.

Using 360 months instead of 30 years is a choice inherited from Oracle's date arithmetic. On other databases the equivalent is a year subtraction, or the interval syntax shown below.

Note: MySQL uses DATE_SUB(CURDATE(), INTERVAL 30 YEAR). SQL Server uses DATEADD(YEAR, -30, GETDATE()). PostgreSQL uses CURRENT_DATE - INTERVAL '30 years'.

28. Highest-paid employee for each designation

SELECT s.designation, e.empname, s.salary
FROM salary s
JOIN employee e ON s.id = e.id
WHERE s.salary = (
    SELECT MAX(salary)
    FROM salary
    WHERE designation = s.designation
)
ORDER BY s.designation;

This is a correlated subquery: the inner query references the outer query's designation and is re-evaluated for each row. For each designation, the subquery returns the maximum salary in that designation, and the outer query keeps the employee whose salary matches. The result is one row per designation — the top earner in each role.

The correlated form is the most portable, but it is not the fastest on large tables. A window function (ROW_NUMBER() OVER (PARTITION BY designation ORDER BY salary DESC)) is the modern alternative, and mentioning it in an interview signals that you know both the classic and the modern approach.

29. Employees whose name starts with a specific letter

SELECT empname
FROM employee
WHERE empname LIKE 'A%';

The LIKE operator with a trailing wildcard is the standard prefix search. Because it is anchored at the start of the string, an index on empname can still be used — unlike a LIKE '%A' pattern, which would force a full scan. This distinction between leading and trailing wildcards is one of the most common performance questions in SQL interviews.

Realistic uses include directory listings and type-ahead suggestions in employee lookup tools.

30. Employees with a specific job designation

SELECT e.id, e.empname, s.designation
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.designation = 'Manager';

A single-value filter on designation. In practice you would parameterise the value so the same query can be reused for any job title, but the shape is the same. The query is deliberately similar to query 4 — same join, different filter — which is exactly how interviewers often sequence their questions.

31. Employees eligible for retirement (age 60 or older)

SELECT empname, birth_date
FROM employee
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birth_date) >= 60;

Extracting the year from both dates and subtracting gives a rough age in years. It is accurate to within one day, which is fine for retirement planning but not for anything requiring precise age. If precision matters, use month/day comparisons or a proper age function.

The result feeds succession planning, benefits eligibility reviews, and pension analysis. It is one of those queries where the business context matters as much as the SQL — the cutoff age and eligibility rules vary by country and employer.

Note: MySQL uses YEAR(CURDATE()). SQL Server uses YEAR(GETDATE()). PostgreSQL uses EXTRACT(YEAR FROM CURRENT_DATE).

32. Total salary of the top 3 highest-paid employees

SELECT SUM(salary) AS total_top_3_salaries
FROM (
    SELECT salary
    FROM salary
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) AS top_salaries;

A subquery in the FROM clause (a derived table) selects the top three salaries, and the outer query sums them. This is the kind of query that appears in executive dashboards — "how much do our three highest-paid people cost us?" — and it is a useful demonstration that ORDER BY inside a subquery is meaningful when combined with a row-limiting clause.

Note the alias AS top_salaries after the closing parenthesis. Every database requires the derived table to be named, even if you never use the name again.

Note: MySQL uses LIMIT 3 instead of FETCH FIRST 3 ROWS ONLY. SQL Server uses SELECT TOP 3 ….

33. Second highest paid employee in the company

SELECT empname, salary
FROM salary s
JOIN employee e ON s.id = e.id
WHERE s.salary = (
    SELECT MAX(salary)
    FROM salary
    WHERE salary < (
        SELECT MAX(salary) FROM salary
    )
);

The trick here is nesting: the innermost subquery returns the maximum salary, the next level returns the maximum salary below that, and the outer query matches the employee whose salary equals the second-highest value. It avoids ranking functions entirely, which makes it portable across every major database — including older versions that do not support window functions.

A common alternative uses DENSE_RANK() OVER (ORDER BY salary DESC) and filters on rank = 2, which handles ties more gracefully. Both answers are valid, and being able to present the second one as the "modern" version is a good interview move. If the question asks for the second-highest *distinct* salary, the nested subquery above already handles it correctly.