SQL Interview Questions and Answers

Most SQL interview rounds for freshers reuse the same dozen query patterns. If you can write the second highest salary query, deduplicate a table, and explain why you used a JOIN over a subquery, you have covered the ground that decides most interviews. This guide walks through those patterns as runnable queries you can practice, not as facts to memorize.

Every example uses one schema so you can build it once and try everything. Set it up locally and type each query yourself — reading is not the same as being able to produce the answer under pressure. For deeper prep, pair this with the SQL learning hub and the dedicated second highest salary walkthrough.

The practice schema

CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(100),
    department VARCHAR(50),
    salary     DECIMAL(10, 2),
    manager_id INT
);

CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    emp_id     INT REFERENCES employees(emp_id),
    order_date DATE,
    amount     DECIMAL(10, 2)
);

Load a handful of rows across two or three departments with a couple of employees sharing the same salary. Ties are where interview questions get interesting, so make sure your test data has some.

Finding the second highest salary

This is the single most asked SQL interview question. The naive answer is to sort descending and skip one row, but that breaks when the top salary is shared by two people. The robust answer uses DENSE_RANK, which gives tied values the same rank without leaving gaps:

SELECT DISTINCT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

An equivalent without window functions uses a correlated subquery that counts how many distinct salaries are strictly greater:

SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary >= e1.salary
);

Both return the correct answer even with ties. Being able to write it two ways, and explain which you prefer and why, is what impresses the interviewer.

Interview note: The follow-up is always "now make it the Nth highest." With DENSE_RANK you just change WHERE rnk = 2 to WHERE rnk = N. If your only solution was LIMIT 1 OFFSET 1, you have nothing to generalize — which is exactly the trap.

GROUP BY, HAVING, and the WHERE distinction

Aggregation questions test whether you understand the order operations run in. WHERE filters rows before grouping; HAVING filters groups after. This query finds departments whose average salary exceeds 60,000, counting only permanent-style employees with a salary set:

SELECT department,
       COUNT(*)      AS headcount,
       AVG(salary)   AS avg_salary
FROM employees
WHERE salary IS NOT NULL      -- row filter, before grouping
GROUP BY department
HAVING AVG(salary) > 60000    -- group filter, after grouping
ORDER BY avg_salary DESC;

The key insight to state out loud: you cannot put AVG(salary) > 60000 in the WHERE clause because aggregates do not exist until after grouping. That single sentence answers a question interviewers ask constantly. Practice more of this pattern in the GROUP BY tutorial.

Joins: the difference that trips people up

Join questions separate people who have used SQL from people who have read about it. Given employees and orders, an INNER JOIN returns only employees who have at least one order:

SELECT e.name, COUNT(o.order_id) AS order_count
FROM employees e
INNER JOIN orders o ON o.emp_id = e.emp_id
GROUP BY e.name;

A LEFT JOIN keeps every employee, showing zero for those with no orders:

SELECT e.name, COUNT(o.order_id) AS order_count
FROM employees e
LEFT JOIN orders o ON o.emp_id = e.emp_id
GROUP BY e.name;

Notice that COUNT(o.order_id) counts non-null order ids, so employees with no orders correctly show zero rather than one. That subtlety — why you count the join column and not COUNT(*) — is a favorite trap. The full breakdown lives in the SQL joins tutorial.

Common mistake: Using COUNT(*) with a LEFT JOIN and reporting 1 for employees with no orders. The outer join produces one row with nulls for those employees, and COUNT(*) counts that row. Always count a column from the right-hand table.

Finding and removing duplicates

"This table has duplicate rows — find them and delete all but one" is a standard task. First find them by grouping on the columns that define a duplicate:

SELECT emp_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY emp_id, order_date, amount
HAVING COUNT(*) > 1;

To delete duplicates while keeping the lowest order_id of each group, use a window function to number the copies:

DELETE FROM orders
WHERE order_id IN (
    SELECT order_id FROM (
        SELECT order_id,
               ROW_NUMBER() OVER (
                   PARTITION BY emp_id, order_date, amount
                   ORDER BY order_id
               ) AS rn
        FROM orders
    ) t
    WHERE rn > 1
);

ROW_NUMBER assigns 1 to the row you keep and 2, 3, ... to the extras, and you delete everything numbered above 1.

Top-N per group: the query that separates levels

A step up from the second highest salary is "find the highest-paid employee in each department." This tests whether you can partition data, and it is where window functions shine. ROW_NUMBER with PARTITION BY restarts the numbering for every department:

SELECT department, name, salary
FROM (
    SELECT department, name, salary,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

Change WHERE rn = 1 to WHERE rn <= 3 and you have the top three earners per department. Interviewers love this because the naive approaches — a self-join or a correlated subquery per department — are clumsy, while the window-function version is short and reads clearly. Being able to reach for PARTITION BY signals genuine comfort with SQL.

Subqueries versus joins

A frequent conceptual question is "when do you use a subquery instead of a join?" A subquery is a query nested inside another, useful when you need a single computed value or a set to test membership against:

-- Employees who earn more than the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

You cannot express "more than the average" with a plain join, because the average is an aggregate over the whole table. Joins, by contrast, are for combining columns from related rows. The honest answer interviewers want: use a join to bring columns together, use a subquery to compute a value or filter against a set, and know that many subqueries can be rewritten as joins with equal or better performance.

Conceptual questions you must answer cleanly

Not every SQL question is a query. Interviewers also test vocabulary. Be ready to explain the difference between DELETE, TRUNCATE, and DROP: DELETE removes chosen rows and is transactional, TRUNCATE wipes all rows fast and resets the table, DROP removes the table itself. Know the difference between UNION (removes duplicates) and UNION ALL (keeps them, and is faster). Be able to say what a primary key, foreign key, and unique constraint each enforce — concepts that come straight from database normalization.

Performance questions appear too. "This query is slow, what do you check?" should trigger you to mention EXPLAIN and indexes. Knowing that a filter on an unindexed column forces a full table scan turns a vague question into a confident answer.

How to prepare without wasting time

Set up the schema above and solve each pattern from a blank editor, not by copying. Then delete your solution and do it again a day later — recall under mild difficulty is what builds durable skill. Explain each query out loud as if to an interviewer, because most SQL rounds are conducted live and being right silently is not enough.

Pro tip: For every query you write, be ready for the "make it handle ties" and "make it faster" follow-ups. Those two twists cover most of what turns a medium question into a hard one, and preparing for them upgrades your whole answer set at once.

Work through the freshers-focused set in SQL interview questions for freshers, then run timed mock rounds. If you are targeting your first developer role in Hyderabad, the SQL round is one of the most learnable parts of the interview — a few focused weeks on these patterns is enough to walk in prepared. Combine this practice with the backend and database work in the Java Full Stack with AI program and you will be answering these questions from experience rather than memory.