Advanced SQL Interview Questions

by

In this authoritative article, I’ve compiled high-level SQL interview questions that separate juniors from architects.

Advanced SQL Interview Questions and Answers

Fundamentals & ACID Compliance (Questions 1–15)

  1. What are ACID properties? Atomicity (all or nothing), Consistency (valid state), Isolation (no interference), Durability (persists after crash).
  2. SQL vs. NoSQL? SQL is relational (rigid schema, vertical scaling); NoSQL is non-relational (flexible, horizontal scaling).
  3. What is a Primary Key? A unique identifier for a row; cannot be NULL.
  4. Foreign Key? A field that links to a Primary Key in another table to maintain referential integrity.
  5. Unique Constraint? Ensures all values in a column are different, but allows one NULL (unlike Primary Key).
  6. Composite Key? A primary key made of 2+ columns.
  7. What is a Schema? The logical structure or container for database objects (tables, views, etc.).
  8. DML vs. DDL vs. DCL? Data Manipulation (SELECT, INSERT), Data Definition (CREATE, DROP), Data Control (GRANT, REVOKE).
  9. What is TCL? Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT).
  10. Difference between CHAR and VARCHAR? CHAR is fixed length (padded with spaces); VARCHAR is variable length.
  11. What is a NULL value? Represents missing or unknown data; not the same as zero or an empty string.
  12. IS NULL vs. = NULL? You must use IS NULL; = NULL always evaluates to false.
  13. What is COALESCE? Returns the first non-null value in a list.
  14. What is NULLIF? Returns NULL if two arguments are equal.
  15. What is an Alias? A temporary name for a table or column (using AS).

Advanced Joins & Subqueries (Questions 16–35)

  1. INNER vs. LEFT vs. RIGHT JOIN? INNER (matches only), LEFT (all from left + matches), RIGHT (all from right + matches).
  2. What is a FULL OUTER JOIN? Returns all rows when there is a match in either left or right table.
  3. What is a CROSS JOIN? A Cartesian product (every row of A paired with every row of B).
  4. What is a Self Join? Joining a table to itself (useful for manager/employee hierarchies).
  5. Natural Join? Joins tables based on columns with the same name and data type automatically.
  6. Equi Join vs. Non-Equi Join? Equi join uses =; Non-equi uses !=, <, or >.
  7. What is a Subquery? A query nested inside another query.
  8. Correlated vs. Non-Correlated Subquery? Correlated subqueries reference the outer query and execute once per row of the outer query.
  9. When to use JOIN vs. Subquery? Joins are generally faster; subqueries are better for readability or existence checks.
  10. What is EXISTS? Returns true if the subquery returns any rows (stops searching early).
  11. IN vs. EXISTS? EXISTS is usually faster for large datasets; IN is better for small literal lists.
  12. What is ANY and ALL? Used to compare a value to a set of values from a subquery.
  13. What is an Anti-Join? Returns rows from the first table that have no match in the second.
  14. What is a Semi-Join? Returns rows from the first table that have at least one match in the second (like EXISTS).
  15. UNION vs. UNION ALL? UNION removes duplicates; UNION ALL is faster and keeps duplicates.
  16. INTERSECT vs. EXCEPT? INTERSECT returns common rows; EXCEPT returns rows in the first query not in the second.
  17. Can you join a table on a range? Yes, using BETWEEN or comparison operators in the ON clause.
  18. What is a Lateral Join (CROSS APPLY)? Allows a subquery to reference columns from a preceding table in the FROM clause.
  19. How do you join on a JSON column? Use dialect-specific functions like JSON_TABLE (MySQL) or OPENJSON (SQL Server).
  20. How to avoid “Join Explosion”? Pre-aggregate data to the join grain before performing the join.

Window Functions & Aggregates (Questions 36–50)

  1. What is a Window Function? Performs calculations across a set of rows related to the current row without collapsing them.
  2. ROW_NUMBER() vs. RANK() vs. DENSE_RANK()? ROW_NUMBER is unique; RANK skips numbers after ties; DENSE_RANK does not skip.
  3. What do LEAD() and LAG() do? Access data from the next or previous row.
  4. FIRST_VALUE() and LAST_VALUE()? Returns the first or last value in a window frame.
  5. What is the OVER() clause? Defines the window partition and order for the function.
  6. PARTITION BY vs. GROUP BY? GROUP BY collapses rows; PARTITION BY keeps original rows while aggregating.
  7. What is a Window Frame (ROWS BETWEEN)? Defines a subset of rows within the partition (e.g., 3 PRECEDING).
  8. How to calculate a Running Total? SUM(val) OVER(ORDER BY date).
  9. How to calculate a Moving Average? AVG(val) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
  10. What is NTILE? Divides rows into a specified number of ranked groups (e.g., quartiles).
  11. Difference between COUNT(*) and COUNT(col)? COUNT(*) counts all rows; COUNT(col) ignores NULLs.
  12. What is STRING_AGG / GROUP_CONCAT? Concatenates values from multiple rows into a single string.
  13. How to find the Nth highest salary? Use DENSE_RANK() in a CTE and filter for Rank = N.
  14. Can you use Window Functions in a WHERE clause? No, you must use a CTE or Subquery.
  15. What is QUALIFY? A BigQuery/Snowflake clause to filter window function results (like HAVING for GROUP BY).

CTEs & Recursive Queries (Questions 51–60)

  1. What is a CTE (Common Table Expression)? A temporary result set defined using the WITH clause.
  2. CTE vs. Temp Table? CTEs exist only for the scope of the query; Temp tables are physical objects in tempdb.
  3. What is a Recursive CTE? A CTE that references itself to traverse hierarchical data (like org charts).
  4. What is the Anchor Member in recursion? The base query that initializes the recursive process.
  5. Recursive Member? The part of the query that references the CTE name to perform the next step.
  6. How to prevent infinite loops in recursion? Use a depth counter or MAXRECURSION hint.
  7. Can a CTE be used for UPDATE/DELETE? Yes, you can update a table by joining it with a CTE.
  8. Multiple CTEs in one query? Yes, separate them by commas under one WITH keyword.
  9. Nested CTEs? A later CTE can reference an earlier one in the same WITH block.
  10. Performance of CTEs? In some engines (like older PostgreSQL), CTEs act as an optimization fence (materialize).

Optimization & Performance (Questions 61–80)

  1. What is an Execution Plan? A roadmap showing how the DB engine will execute a query (use EXPLAIN).
  2. Index Seek vs. Index Scan? Seek is fast (finding specific rows); Scan reads the whole index (slower).
  3. What is a Clustered Index? Determines the physical order of data on disk (one per table).
  4. Non-Clustered Index? A separate structure with pointers to the data (multiple allowed).
  5. What is a Covering Index? An index that contains all columns requested by a query.
  6. Composite Index? An index on multiple columns.
  7. What is SARGability? “Search ARGumentable”—writing queries so the engine can use indexes (avoiding functions on columns).
  8. What is Index Fragmentation? When the physical order of index pages doesn’t match the logical order.
  9. How to handle a slow query? Check execution plan, add indexes, remove SELECT *, or update statistics.
  10. What are Database Statistics? Metadata about data distribution used by the optimizer to choose a plan.
  11. What is a Deadlock? When two transactions block each other indefinitely.
  12. How to prevent deadlocks? Access tables in the same order, keep transactions short.
  13. What is Isolation Level? Defines how changes made by one transaction are visible to others (e.g., Read Committed).
  14. Dirty Read? Reading uncommitted data from another transaction.
  15. Phantom Read? When new rows added by another transaction appear in your range search mid-transaction.
  16. What is Partitioning? Splitting a large table into smaller physical pieces based on a key (e.g., Year).
  17. Horizontal vs. Vertical Partitioning? Horizontal splits rows; Vertical splits columns into separate tables.
  18. What is Sharding? Distributing data across multiple physical database instances.
  19. What is a Hint? An instruction to the optimizer to use a specific index or join type (e.g., /*+ INDEX(t1) */).
  20. What is Parameter Sniffing? When the engine creates an execution plan based on specific parameters that may not be optimal for others.

Programmability & Design (Questions 81–100)

  1. Stored Procedure vs. Function? Procedures can modify data and return multiple values; Functions must return a value and are “read-only.”
  2. What is a Trigger? Code that executes automatically in response to an event (INSERT/UPDATE/DELETE).
  3. What is a View? A virtual table based on a stored query.
  4. Materialized View? A view that physically stores data for performance; requires refreshing.
  5. What is Normalization? Organizing data to reduce redundancy (1NF, 2NF, 3NF).
  6. What is Denormalization? Adding redundancy to improve read performance (common in Data Warehousing).
  7. Star Schema vs. Snowflake Schema? Star has a central fact table with flat dimensions; Snowflake normalizes dimensions.
  8. What is a Surrogate Key? An artificial unique identifier (like an Identity column).
  9. What is a Cursor? A mechanism to process query results one row at a time (generally avoided for performance).
  10. What is an Upsert (MERGE)? A command that inserts a row if it doesn’t exist or updates it if it does.
  11. TRUNCATE vs. DELETE? TRUNCATE is faster, DDL, and removes all rows; DELETE is DML, can be filtered, and is logged.
  12. DROP vs. TRUNCATE? DROP removes the table structure; TRUNCATE only removes the data.
  13. What is an Identity/Sequence? Auto-generating numbers for primary keys.
  14. What is Row-Level Security (RLS)? Restricting which rows a user can see based on their identity.
  15. What is Dynamic SQL? Constructing SQL queries as strings at runtime (risk of SQL injection).
  16. How to prevent SQL Injection? Use Parameterized Queries or Prepared Statements.
  17. What is Data Redundancy? Storing the same data in multiple places leads to inconsistency.
  18. What is an OLTP system? Online Transaction Processing (fast, frequent updates, e.g., Banking).
  19. What is an OLAP system? Online Analytical Processing (complex queries, large volumes, e.g., Data Warehousing).
  20. What is a Data Warehouse? A central repository for integrated data from one or more sources for analysis.

Conclusion:

Answering advanced SQL interview questions is about demonstrating that you can protect the database’s health while delivering complex business insights. Your goal is to show that you understand the mechanics beneath the surface of T-SQL.

Master these concepts, and you won’t just pass the interview—you’ll set the standard for the entire team. Good luck!

You may also like the following articles: