Top 50 SQL Interview Questions & Answers (2026 Advanced Guide)
Data is the most valuable asset in the modern tech industry. Whether you are a Data Scientist, Backend Engineer, or Data Analyst, a deep mastery of SQL (Structured Query Language) is absolutely mandatory for interviews in 2026.
Interviewers are no longer satisfied with simple `SELECT *` queries. They want to test your knowledge on Query Optimization, Window Functions, CTEs, and ACID properties. Here are the Top 50 Advanced SQL Interview Questions you need to know.
💾 Part 1: Core SQL Commands (DDL/DML)
1. What are the subsets of SQL?
SQL is divided into 4 subsets: DDL (Data Definition Language - CREATE, ALTER, DROP), DML (Data Manipulation Language - SELECT, INSERT, UPDATE, DELETE), DCL (Data Control Language - GRANT, REVOKE), and TCL (Transaction Control Language - COMMIT, ROLLBACK).
2. What is a Primary Key?
A Primary Key is a column (or a set of columns) that uniquely identifies each row in a table. It must contain UNIQUE values and cannot contain NULL values. A table can only have one primary key.
3. What is a Foreign Key?
A Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It is used to establish and enforce a link between the data in the two tables, ensuring Referential Integrity.
4. What is the difference between DELETE and TRUNCATE?
DELETE is a DML command used to remove specific rows based on a WHERE clause; it logs every deleted row in the transaction log and can be rolled back. TRUNCATE is a DDL command that removes all rows instantly by deallocating the data pages, making it much faster but generally non-rollbackable.
5. What is the difference between DROP and TRUNCATE?
TRUNCATE empties the table of all data but keeps the table structure (columns, constraints) intact. DROP completely obliterates the table, its data, its indexes, and its structural definition from the database.
6. What is the UNIQUE constraint?
It ensures that all values in a column are different. Unlike a Primary Key, you can have multiple UNIQUE constraints in a table, and a UNIQUE column can accept one NULL value (in most SQL dialects).
7. What does the COALESCE() function do?
COALESCE() takes a list of arguments and returns the first non-NULL value. Example: COALESCE(phone_number, mobile_number, 'N/A'). If the phone is NULL, it checks mobile; if mobile is also NULL, it returns 'N/A'.
8. How do you select distinct (unique) values from a table?
Use the DISTINCT keyword. E.g., SELECT DISTINCT department_id FROM Employees; will return a list of departments without duplicates.
9. What is the difference between WHERE and HAVING?
The WHERE clause is used to filter rows before any grouping is done. The HAVING clause is used to filter aggregated data after the GROUP BY clause has been executed.
10. How do you find the 3rd highest salary in a table?
Using standard SQL with OFFSET: SELECT salary FROM Employees ORDER BY salary DESC LIMIT 1 OFFSET 2; (LIMIT/OFFSET syntax varies slightly between MySQL, Postgres, and SQL Server).
Ready to test your Database skills? 🚀
Practice these exact SQL queries and thousands more on the TechQuiz app. Track your progress and crush your FAANG interview.
🔗 Part 2: Joins & Relationships
11. What is a JOIN in SQL?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them (usually Primary Key and Foreign Key).
12. Explain INNER JOIN.
An INNER JOIN returns only the rows that have matching values in both tables. If a row in Table A has no match in Table B, it is completely excluded from the result set.
13. Explain LEFT JOIN (Left Outer Join).
A LEFT JOIN returns ALL rows from the left table, and the matched rows from the right table. The result is NULL from the right side if there is no match.
14. Explain RIGHT JOIN (Right Outer Join).
A RIGHT JOIN returns ALL rows from the right table, and the matched rows from the left table. Similar to Left Join, but the logic is reversed.
15. What is a FULL OUTER JOIN?
It returns ALL rows when there is a match in either left or right table. It is essentially a combination of a Left Join and a Right Join. If there is no match, the missing side will contain NULLs.
16. What is a CROSS JOIN?
A CROSS JOIN returns the Cartesian product of the two tables. Every row from the first table is paired with every row from the second table. If Table A has 10 rows and Table B has 10 rows, the result is 100 rows.
17. What is a Self Join?
A Self Join is a regular join, but the table is joined with itself. It is extremely useful for hierarchical data, such as finding the manager of an employee when both employee and manager exist in the same `Employees` table.
18. What is the difference between UNION and UNION ALL?
UNION combines the result sets of two queries and removes duplicate rows. UNION ALL combines them but keeps the duplicates. Because it doesn't spend CPU cycles looking for duplicates, UNION ALL is significantly faster.
19. Can you join more than two tables?
Yes. You simply chain the JOIN commands. E.g., SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id;
20. What is an Equi Join?
An Equi Join is any join where the join condition uses an equality operator (=). A Non-Equi Join uses other operators like <, >, or BETWEEN.
👇 Dominate System Design & Backend Logic 👇
Join thousands of engineers using TechQuiz to master Database Engineering and land jobs at top tier tech companies.
📊 Part 3: Grouping & Aggregate Functions
21. What are Aggregate Functions?
Functions that perform a calculation on a set of values and return a single aggregated value. Common examples are COUNT(), SUM(), AVG(), MIN(), and MAX().
22. How do Aggregate functions handle NULL values?
Most aggregate functions (like SUM, AVG, MAX) completely ignore NULL values in their calculations. The exception is COUNT(*), which counts every row in the table, including rows with NULLs.
23. What is the GROUP BY statement?
GROUP BY is used in collaboration with aggregate functions to group the result-set by one or more columns. E.g., SELECT department, COUNT(*) FROM Employees GROUP BY department; gives the headcount per department.
24. Can you use aliases in the GROUP BY clause?
In standard SQL, you cannot use column aliases in the GROUP BY clause because GROUP BY is evaluated before the SELECT clause. However, some RDBMS (like MySQL and Postgres) allow it as an extension.
25. What is the execution order of a SQL query?
The logical processing order is: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT/OFFSET.
26. What are Window Functions?
Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row. (e.g., OVER(), PARTITION BY).
27. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
ROW_NUMBER() assigns a unique sequential integer starting at 1. RANK() assigns the same rank to identical values, but skips the next numbers (e.g., 1, 2, 2, 4). DENSE_RANK() assigns the same rank to identical values, but does NOT skip numbers (e.g., 1, 2, 2, 3).
28. What is the LEAD() function?
LEAD() is a window function that provides access to a row at a given physical offset that follows the current row. It is incredibly useful for comparing current values with next values (like day-over-day growth).
29. What is the LAG() function?
The opposite of LEAD. LAG() accesses data from a previous row in the same result set without the need to use a self-join.
30. How do you cast or convert data types in SQL?
You can use the CAST(expression AS target_type) or CONVERT() functions depending on the RDBMS. E.g., CAST('123' AS INT).
🧠 Part 4: Subqueries, CTEs & Advanced Concepts
31. What is a Subquery?
A Subquery (or Inner Query) is a query nested inside another SQL query (like inside a SELECT, INSERT, UPDATE, or DELETE statement). The inner query executes first, and its results are used by the outer query.
32. What is a Correlated Subquery?
A Correlated Subquery is a subquery that uses values from the outer query. Unlike a standard subquery that executes once, a correlated subquery is evaluated once for *every single row* processed by the outer query. This makes them notoriously slow.
33. What is a CTE (Common Table Expression)?
A CTE (using the WITH clause) creates a temporary, named result set that exists only for the duration of a single SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries significantly more readable than nested subqueries.
34. Can a CTE be recursive?
Yes, Recursive CTEs refer to themselves. They are heavily used to traverse hierarchical or tree-structured data (like organizational charts, file directories, or bill-of-materials).
35. What is a View?
A View is a virtual table based on the result-set of an SQL statement. It doesn't store data itself (unless it's a materialized view), but allows developers to encapsulate complex queries behind a simple name for security and simplicity.
36. What is a Materialized View?
Unlike standard Views, Materialized Views physically store the results of the query on disk. This makes reading data insanely fast, but it requires periodic refreshes (or triggers) to stay updated when the underlying data changes.
37. What is a Trigger?
A Trigger is a special type of stored procedure that automatically executes (fires) when a specific event occurs in the database table, such as before or after an INSERT, UPDATE, or DELETE operation.
38. What is a Stored Procedure?
A Stored Procedure is a prepared SQL code that you can save so the code can be reused over and over again. They can accept parameters, encapsulate business logic inside the database, and reduce network traffic.
39. What is the difference between a Function and a Stored Procedure?
A Function MUST return a value and can only contain SELECT statements (no DML like INSERT/UPDATE). A Stored Procedure may or may not return a value, and can perform DML operations to modify data.
40. What is SQL Injection?
It is a security vulnerability where an attacker manipulates a query by inserting malicious SQL code into input fields. It is prevented by always using Parameterized Queries (Prepared Statements) or ORMs instead of concatenating strings.
🚀 Part 5: Performance, Indexing & ACID
41. What is an Index?
An index is a data structure (typically a B-Tree) that improves the speed of data retrieval operations on a table, at the cost of additional space and slower writes (INSERT/UPDATE/DELETE). It works like an index in a book.
42. Clustered vs Non-Clustered Index?
A Clustered Index dictates the physical order of data in the table (you can only have one per table, usually the Primary Key). A Non-Clustered Index creates a separate structure pointing to the physical rows (you can have multiple).
43. What is a Composite Index?
An index on two or more columns of a table. Order matters heavily; a composite index on (ColumnA, ColumnB) helps queries filtering by A, or A and B, but is useless for queries filtering *only* by B.
44. What is a Database Transaction?
A transaction is a single logical unit of work comprised of one or more SQL statements. If the transaction is successful, it is COMMITted. If any part of it fails, the entire transaction is ROLLBACKed.
45. What are the ACID properties?
Atomicity (All or nothing), Consistency (Valid state before and after), Isolation (Concurrent transactions don't interfere), Durability (Committed data is saved permanently, even if power is lost).
46. What is Database Normalization?
Normalization is the process of organizing data to minimize redundancy and improve data integrity. It involves splitting large tables into smaller, related ones (1NF, 2NF, 3NF).
47. What is Denormalization?
The deliberate adding of redundant data or grouping of data to optimize READ performance. While it requires more storage and makes updates harder, it avoids expensive JOIN operations in read-heavy applications.
48. What is a Deadlock?
A deadlock occurs when two or more transactions are waiting on one another to release locks on resources. Database engines usually detect this, kill one transaction (the "victim"), and let the other proceed.
49. Explain the EXPLAIN statement.
Prepending EXPLAIN (or EXPLAIN QUERY PLAN) to a SQL query tells the database to output its execution plan. It reveals if the query is doing a full table scan or using indexes, which is critical for optimization.
50. What is a Schema?
A schema is a logical container or blueprint that holds database objects like tables, views, procedures, and functions. It helps organize objects and manage user access permissions.
🔥 Explore More Interview Guides
Preparing for multiple roles? Check out our other in-depth technical interview guides:
Comments
Post a Comment