SQL Interview Questions and Answers for Freshers and Experienced

At the driving test in Indore, the examiner does not ask you to recite the rulebook. You reverse into a parking spot, stop on a slope and explain a road sign. Each test is short, but together they show whether you can drive.

SQL interviews work the same way. Each question is one manoeuvre: a definition, a small query, or a "what happens if" about NULLs or duplicates. The examiner is listening for whether you know why, not just what.

SQL interview questions test whether you can explain core relational database concepts precisely and write correct queries for everyday analysis tasks, including edge cases such as NULLs, ties and duplicates.

The answers below use the Malwa Mart sample tables from this series, and each names the tutorial in this series that covers it in depth. For hands-on rehearsal, solve the SQL practice problems first. Broader analyst topics such as Excel and dashboards are covered in data analyst interview questions.

SQL interview questions for freshers

1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates such as COUNT(*). "Stores with more than two UPI orders" needs both: WHERE payment_mode = 'UPI' first, then HAVING COUNT(*) > 2.

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

SQLite has no TRUNCATE; DELETE FROM t without WHERE is optimised to do the same job.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INTEGER PRIMARY KEY, amount INT)")
con.executemany("INSERT INTO orders VALUES (?, ?)", [(101, 1040), (102, 330), (103, 275), (104, 180)])
con.commit()

print("DELETE removed", con.execute("DELETE FROM orders WHERE amount < 300").rowcount, "rows")
con.rollback()
print("After rollback:", con.execute("SELECT COUNT(*) FROM orders").fetchone()[0], "rows")
try:
    con.execute("TRUNCATE TABLE orders")
except sqlite3.OperationalError as err:
    print("TRUNCATE in SQLite:", err)
con.execute("DROP TABLE orders")
print("Tables left:", con.execute("SELECT name FROM sqlite_master WHERE type = 'table'").fetchall())

3. What is the difference between a primary key and a unique key?

Both reject duplicate values. A table has one primary key, which identifies each row and cannot be NULL. It can have many UNIQUE constraints, and a UNIQUE column may hold NULLs (several, in SQLite, MySQL and PostgreSQL).

4. What are the different types of joins in SQL?

INNER JOIN keeps only matching rows. LEFT JOIN keeps every left-table row, filling NULLs where nothing matches; RIGHT JOIN mirrors it. FULL OUTER JOIN keeps unmatched rows from both sides, and CROSS JOIN pairs every row with every row. Customers LEFT JOIN orders returns Neha D'Souza with NULL order columns. Full guide: SQL joins.

5. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(amount) counts rows where amount is not NULL. COUNT(DISTINCT payment_mode) counts different non-NULL values. On the 11 Malwa Mart orders they return 11, 10 and 3. The block below also answers question 1.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (order_id INT, store_id INT, amount INT, payment_mode TEXT)")
con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    (101,1,1040,'UPI'), (102,2,330,'Cash'), (103,1,275,'UPI'), (104,3,180,'Card'), (105,2,680,'UPI'), (106,4,120,'Cash'),
    (107,3,1560,'Card'), (108,1,220,'UPI'), (109,4,340,None), (110,2,465,'Cash'), (111,3,None,'UPI')])
print(con.execute("SELECT COUNT(*), COUNT(amount), COUNT(DISTINCT payment_mode) FROM orders").fetchone())
print(con.execute("""SELECT store_id, COUNT(*) AS upi_orders FROM orders
                     WHERE payment_mode = 'UPI'            -- rows first
                     GROUP BY store_id HAVING COUNT(*) > 1 -- then groups""").fetchall())

Only Vijay Nagar (store 1) has more than one UPI order.

Intermediate SQL interview questions

6. What is the difference between UNION and UNION ALL?

Both stack the results of two queries with matching columns. UNION removes duplicate rows, which costs a sort or hash step; UNION ALL keeps every row and is faster. Use UNION ALL unless duplicates are possible and unwanted.

7. In what order does SQL execute a SELECT query?

Logically: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. That is why a column alias defined in SELECT cannot be used in WHERE in MySQL or PostgreSQL, but can be used in ORDER BY. SQLite is lenient and accepts the alias in WHERE, so test portable SQL on your target database.

8. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows in a window. ROW_NUMBER gives unique numbers even for ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3).

Ties are where the three functions differ. RANK leaves gaps after a tie; DENSE_RANK does not.

9. How do you find the Nth highest value in SQL?

Rank distinct values with DENSE_RANK and filter on N. This handles ties and any N. LIMIT 1 OFFSET N-1 on SELECT DISTINCT also works, and the classic MAX(x) WHERE x < (SELECT MAX(x) ...) works only for the second highest.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE units_sold (product_name TEXT, units INT)")
con.executemany("INSERT INTO units_sold VALUES (?, ?)", [("Poha 1kg", 7), ("Basmati Rice 5kg", 4), ("Detergent 2kg", 4),
    ("Sev 500g", 3), ("Toor Dal 1kg", 3), ("Namkeen Mix 400g", 2), ("Paneer 200g", 2), ("Butter 500g", 1)])
ranked = """SELECT product_name, units,
    ROW_NUMBER() OVER (ORDER BY units DESC, product_name) AS row_num,
    RANK()       OVER (ORDER BY units DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY units DESC) AS dense_rnk
FROM units_sold ORDER BY row_num"""
for row in con.execute(ranked):
    print(row)
n = 3
third = con.execute(f"SELECT DISTINCT units FROM ({ranked}) WHERE dense_rnk = ?", (n,)).fetchall()
print(f"{n}rd highest units value:", third)

The third-highest distinct value is 3 units. A RANK-based filter for 3 would return nothing, because RANK jumps from 2 to 4. More in window functions.

10. How do you find and remove duplicate rows?

Group by the columns that define a duplicate and keep groups with COUNT(*) > 1. To delete, keep one row per group, usually the lowest id, and remove the rest.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, phone TEXT)")
con.executemany("INSERT INTO customers (name, phone) VALUES (?, ?)", [("Asha Verma", "9826011234"),
    ("Ravi Patel", "9826022345"), ("Asha Verma", "9826011234"), ("Sana Qureshi", "9826055678"), ("Asha Verma", "9826011234")])

dupes = "SELECT name, phone, COUNT(*) FROM customers GROUP BY name, phone HAVING COUNT(*) > 1"
print("Duplicates:", con.execute(dupes).fetchall())
removed = con.execute("""DELETE FROM customers WHERE customer_id NOT IN
                         (SELECT MIN(customer_id) FROM customers GROUP BY name, phone)""").rowcount
print("Removed", removed, "->", con.execute("SELECT * FROM customers").fetchall())

Then add a UNIQUE constraint on phone so the duplicates cannot return. In MySQL, the subquery must be wrapped in a derived table, because MySQL does not let a DELETE read the same table directly in a subquery.

SQL interview questions for experienced candidates

11. What is a correlated subquery, and when is it slow?

A correlated subquery refers to a column of the outer query, so it may run once per outer row: "orders above their store's average" recalculates the average for every order. On large tables, rewrite it as a pre-aggregated CTE joined back, or a window function. Query optimization times both.

12. How does an index work, and when is it not used?

Most indexes are B-trees: sorted copies of columns with pointers to rows, turning full scans into quick searches. A clustered index decides the physical row order (InnoDB's primary key); non-clustered indexes are separate structures. An index is usually ignored for functions on the column, leading-wildcard LIKE, low-variety columns or queries reading most of the table.

13. Explain ACID and transaction isolation levels.

ACID means atomicity (all or nothing), consistency (rules hold), isolation (no peeking at unfinished work) and durability (commits survive crashes). Isolation levels, from READ UNCOMMITTED to SERIALIZABLE, decide whether dirty, non-repeatable and phantom reads can occur. MySQL InnoDB defaults to REPEATABLE READ, PostgreSQL to READ COMMITTED. See transactions and ACID.

14. What is the difference between a view, a materialized view, a stored procedure and a trigger?

Each has its own tutorial earlier in this series.

15. A report query has become slow in production. How do you approach it?

Measure first: capture the query, its parameters and its timing. Read the plan with EXPLAIN or EXPLAIN ANALYZE, looking for full scans, large sorts and per-row subqueries. Then change one thing, such as an index, a sargable filter or a pre-aggregation, and re-measure, confirming the result is unchanged. Mention checking statistics and N+1 calls from the application.

Common mistakes in SQL interviews

• Giving a definition with no example. Add a one-line query or a Malwa Mart-style case to every answer.
• Forgetting NULLs. Many follow-up questions are "what if the amount is NULL?"
• Ignoring ties. "Top customer" questions often hide a tie; say how you would handle it.
• Writing SQL for one database only. Mention when syntax differs, such as LIMIT vs TOP or TRUNCATE in SQLite.
• Silence while thinking. Talk through grain, joins and filters as you write. Process is graded too.

For timed rehearsal, SQL interview-style MCQs on Debugshala Test cover these topics. If you would rather prepare with mock interviews and mentors, the Data Analytics course at Debugshala's Old Palasia campus includes SQL interview preparation, and Python rounds are covered in Python interview questions.

Key takeaways

• Fresher rounds test precise basics: WHERE vs HAVING, keys, joins, COUNT and DELETE vs TRUNCATE vs DROP.
• Intermediate rounds test execution order, ranking functions, Nth highest and duplicates.
• Experienced rounds test performance, indexes, transactions and database objects.
• Every strong answer has a definition, an example and an edge case.