Top 20 Common SQL Interview Questions

Whether you’re applying for a role as a Backend Developer, Data Analyst, or Data Engineer, SQL is the great equalizer. Interviewers don’t just want to know if you can write a query; they want to know if you understand how the database engine processes your commands.

We analyzed hundreds of technical interviews and distilled them into the Top 20 Common SQL Interview Questions. We’ve provided not just the answers, but the deep technical context that interviewers are actually looking for.

---

Part 1: Foundational Concepts

1. What is the logical execution order of a SQL query?

Answer: While you write a query from top to bottom (SELECT first), the database engine executes it in a different order:

1. FROM and JOIN (Choose tables)
2. WHERE (Filter rows)
3. GROUP BY (Aggregate)
4. HAVING (Filter aggregates)
5. SELECT (Return columns)
6. ORDER BY (Sort)
7. LIMIT (Paginate) Why it matters: This explains why you cannot use an alias defined in the SELECT clause within a WHERE clause—the WHERE clause is evaluated before SELECT even happens!

2. What is the difference between WHERE and HAVING?

Answer: Both are used to filter data, but at different stages.

• WHERE filters individual rows before any grouping or aggregation takes place.
• HAVING filters aggregated groups after the GROUP BY clause has been applied. You cannot use aggregate functions like SUM() in a WHERE clause.

3. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

• DELETE (DML): Removes rows one-by-one. It is logged in the transaction log, meaning it can be rolled back. Slower for massive data deletion.
• TRUNCATE (DDL): Deallocates the data pages for the table. It is incredibly fast, removes all rows, and typically cannot be rolled back easily. The table structure remains intact.
• DROP (DDL): Completely destroys the table structure and its data from the database.

4. Explain the difference between UNION and UNION ALL.

Answer: Both combine the result sets of two or more SELECT statements.

• UNION removes duplicate rows from the combined result set. To do this, the database must perform a costly distinct sort under the hood.
• UNION ALL retains all duplicates. It simply appends the result sets together, making it significantly faster. Pro-tip: Always use UNION ALL unless you explicitly need to deduplicate.

5. What are Primary Keys vs. Foreign Keys?

Answer:

• Primary Key (PK): A column (or set of columns) that uniquely identifies a row in a table. It must be UNIQUE and cannot be NULL.
• Foreign Key (FK): A column in a table that references the Primary Key of another table. It enforces Referential Integrity, preventing you from deleting a user if they still have active orders linked to their ID.

---

Part 2: The Art of Joining

6. Explain the core Join types (INNER, LEFT, RIGHT, FULL OUTER).

Answer:

• INNER JOIN: Returns only the rows where there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table. Unmatched rows from the right table will return as NULL.
• RIGHT JOIN: Returns all rows from the right table, and matched rows from the left.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table.

7. What is a Self-Join and when would you use it?

Answer: A self-join is a regular join where a table is joined to itself. This requires you to use table aliases to distinguish the two instances. Use Case: Organizational hierarchies. E.g., finding an employee’s manager when both are stored in the same Employees table.

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

8. What is a Cross Join?

Answer: A CROSS JOIN returns the Cartesian product of two tables. If Table A has 5 rows and Table B has 10 rows, the result will have 50 rows. Every row in Table A is paired with every row in Table B.

---

Part 3: Data Aggregation & Manipulation

9. What is the difference between COUNT(*) and COUNT(column_name)?

Answer:

• COUNT(*) counts the total number of rows in the result set, including rows that contain NULL values.
• COUNT(column_name) counts the number of rows where column_name is NOT NULL.

10. How do you find duplicate records in a table?

Answer: You use GROUP BY and HAVING.

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

11. How do you delete duplicate records, keeping only one?

Answer: Using a Common Table Expression (CTE) and a Window Function.

WITH CTE AS (
  SELECT id, email,
  ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) as rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM CTE WHERE rn > 1);

12. Write a query to find the Second Highest Salary.

Answer: The most robust way, which handles ties correctly, is using the DENSE_RANK() window function.

WITH RankedSalaries AS (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as rank
    FROM Employee
)
SELECT DISTINCT Salary FROM RankedSalaries WHERE rank = 2;

---

Part 4: Window Functions & CTEs

13. What is a Window Function?

Answer: Window functions perform calculations across a set of table rows related to the current row, similar to an aggregate function. However, unlike GROUP BY, window functions do not cause rows to become grouped into a single output row. The rows retain their separate identities.

14. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Answer: Assume salaries of 100k, 100k, 90k, 80k.

• ROW_NUMBER(): Strictly sequential. (1, 2, 3, 4)
• RANK(): Assigns the same rank to ties, but leaves a gap. (1, 1, 3, 4)
• DENSE_RANK(): Assigns the same rank to ties, leaving NO gap. (1, 1, 2, 3)

15. How do you calculate a Running Total / Cumulative Sum?

Answer: Use the SUM() aggregate function as a window function.

SELECT 
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

16. What is a CTE (Common Table Expression)?

Answer: A CTE is a temporary named result set created via the WITH clause. It makes complex queries significantly more readable than nested subqueries and can be referenced multiple times within the main query. It only exists for the duration of the query execution.

17. How do you find the first or last record per group? (e.g., the most recent order for each user)

Answer:

WITH RankedOrders AS (
    SELECT order_id, user_id, order_date,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date DESC) as rn
    FROM orders
)
SELECT * FROM RankedOrders WHERE rn = 1;

---

Part 5: Advanced Mechanics & Performance

18. What is a Correlated Subquery?

Answer: A standard subquery can be executed independently of the outer query. A Correlated Subquery, however, references columns from the outer query. This means it must be evaluated once for every single row processed by the outer query, making it notoriously slow for large datasets.

19. What is an Index and how does it work?

Answer: An index is a data structure (typically a B-Tree) that improves the speed of data retrieval operations. Instead of performing a “Full Table Scan” (checking every row from start to finish), the database traverses the B-Tree to find the exact location of the data almost instantly. Trade-off: While indexes speed up SELECT queries, they slow down INSERT, UPDATE, and DELETE queries because the B-Tree must be rebalanced.

20. What is the difference between a Clustered and Non-Clustered Index?

Answer:

• Clustered Index: Dictates the physical sorting order of the data on the disk. Because data can only be physically sorted one way, a table can only have one clustered index (usually the Primary Key).
• Non-Clustered Index: A separate structure from the data rows. It contains the indexed columns and a pointer (row locator) to the actual data row. A table can have many non-clustered indexes.

---

Ready to Test Your Knowledge?

Reading the answers is the easy part. Writing the queries under the pressure of a ticking clock is where candidates fail.

👉 Master the SQL Interview with our interactive Sandbox. Try out real-world schemas, write complex window functions, and test your query speed directly in your browser.