📌 The 2026 SQL Technical Screening Standard:

In data science and business analytics hiring rounds across Indian tech hubs (Bengaluru, Hyderabad, Pune, and Noida), over 70% of candidate rejections happen during the live SQL coding round. Interviewers do not test simple SELECT queries; they evaluate Analytical Window Functions, Recursive CTEs, self-joins, query plan optimization, indexing internals, and complex multi-table aggregations.

Use this categorized 50-question master blueprint to revise high-yield SQL concepts and real-world database query patterns.

Q1. How does an INNER JOIN differ from a LEFT JOIN when handling NULL keys?

An INNER JOIN returns records only when there is a matching key in both tables; rows with NULL join keys in either table are discarded because NULL = NULL evaluates to UNKNOWN. A LEFT JOIN preserves all rows from the left table, populating right-table columns with NULL when no match is found.

Q2. What is a FULL OUTER JOIN, and how do you simulate it in MySQL?

A FULL OUTER JOIN returns all matched rows plus unmatched rows from both tables, filling missing values with NULL. Since MySQL does not natively support FULL OUTER JOIN, you simulate it by executing a LEFT JOIN and a RIGHT JOIN combined using the UNION operator (which automatically removes duplicate rows).

Q3. What is a CROSS JOIN, and when is it legitimately used in analytics?

A CROSS JOIN produces a Cartesian product, pairing every row from table A with every row from table B (yielding M * N rows). In data analytics, it is used to generate complete calendar date-range grids, product-customer matrix templates, or simulate scenarios where all combinations must be evaluated.

Q4. What is the fundamental difference between WHERE and HAVING?

WHERE filters individual records before any grouping or aggregation takes place. HAVING filters grouped records after the GROUP BY aggregation has been computed. You cannot use aggregate functions (like SUM() or AVG()) inside a WHERE clause.

Q5. How does SQL handle NULL values in boolean comparisons and aggregations?

SQL uses three-valued logic (TRUE, FALSE, UNKNOWN). Expressions like col = NULL return UNKNOWN, which evaluates as false in filter predicates; hence IS NULL is mandatory. All aggregate functions (except COUNT(*)) automatically ignore NULL values during computation.

Q6. Differentiate between UNION and UNION ALL in query performance.

UNION combines result sets and performs an expensive internal sorting and deduplication pass to eliminate duplicate rows. UNION ALL concatenates datasets directly without deduplication. Unless duplicate elimination is explicitly required, always use UNION ALL to avoid query memory exhaustion.

Q7. What is an Analytical Window Function, and how does it differ from GROUP BY?

GROUP BY collapses individual rows into a single summary record per group. Window functions (using the OVER() clause with PARTITION BY and ORDER BY) calculate aggregations across a defined window of rows while retaining individual row identities.

Q8. Explain the exact differences between ROW_NUMBER(), RANK(), and DENSE_RANK().

Given duplicate values [100, 100, 90]: ROW_NUMBER() assigns strictly sequential integers (1, 2, 3). RANK() assigns identical ranks to ties and skips subsequent numbers (1, 1, 3). DENSE_RANK() assigns identical ranks to ties without skipping subsequent ranks (1, 1, 2).

Q9. How do you find the Nth highest salary in an employee table using DENSE_RANK()?

Use a CTE with DENSE_RANK: WITH Ranked AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM Employees) SELECT DISTINCT salary FROM Ranked WHERE rnk = N; This cleanly handles duplicate salary ties without skipping numbers.

Q10. How do LEAD() and LAG() calculate Year-over-Year (YoY) revenue growth?

LAG(revenue, 1) OVER (ORDER BY year) retrieves the prior year's value into the current row. YoY growth is calculated as: ((revenue - LAG(revenue, 1) OVER (ORDER BY year)) / LAG(revenue, 1) OVER (ORDER BY year)) * 100.

Q11. What is the difference between ROWS BETWEEN and RANGE BETWEEN in rolling windows?

ROWS BETWEEN specifies a physical offset of exact preceding and following rows (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). RANGE BETWEEN specifies a logical offset based on duplicate values in the ORDER BY column.

In technical screening interviews, technical leads look beyond simple syntax to evaluate whether you have experience deploying models inside real corporate architecture. Programs backed by a Guaranteed Paid Corporate Internship like AI Campus provide learners with verifiable enterprise experience and a regular monthly stipend, bridging the gap between classroom theory and production engineering.

Q12. What is a Common Table Expression (CTE), and when is a Recursive CTE needed?

A CTE (defined via WITH) is a temporary named result set that enhances query modularity and readability. Recursive CTEs reference themselves to query hierarchical or graph structures, such as organizational management trees or category sub-hierarchies.

Q13. How does a Correlated Subquery differ from a Standard Subquery?

A standard subquery executes independently once and passes its scalar or array result to the outer query. A Correlated Subquery references columns from the outer query, forcing the database engine to re-execute the subquery once for every candidate row evaluated in the outer query, which can degrade query latency on large datasets.

Q14. How do you delete duplicate records from a table while retaining the oldest record?

Use a CTE with ROW_NUMBER(): WITH Dups AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) as rnk FROM Users) DELETE FROM Users WHERE id IN (SELECT id FROM Dups WHERE rnk > 1);

Q15. How do Clustered and Non-Clustered Indexes alter physical data storage on disk?

A Clustered Index physically sorts and stores data rows in disk blocks based on the indexed key (only one per table). A Non-Clustered Index maintains a separate B-tree holding key values and pointers to the physical data rows, enabling fast lookups without reordering table records.

Q16. Why does wrapping indexed columns in functions (e.g., WHERE YEAR(date) = 2026) cause full table scans?

Wrapping an indexed column inside a function prevents the query optimizer from traversing the B-tree index directly (it makes the predicate non-SARGable). Instead, rewrite the query to preserve the bare index: WHERE date >= '2026-01-01' AND date < '2027-01-01'.

Q17. What is an EXPLAIN plan, and what does a 'Using filesort' warning signify?

EXPLAIN details the query execution strategy: scan types, indexes used, and estimated rows read. 'Using filesort' indicates the database engine cannot use an index to satisfy an ORDER BY clause, forcing it to sort results in memory or temporary disk buffers.

From Query Questions to Enterprise Production Pipelines

While answering SQL questions prepares you for technical screening rounds, corporate hiring managers prioritize candidates who have already built scalable database pipelines inside active production sprints.

Through official partnerships with IBM SkillsBuild and Microsoft Learn, AI Campus equips learners with verifiable digital credentials and an enforceable Guaranteed 6-Month Paid Corporate Internship backed by a competitive monthly stipend.

Crack Your Next Data Analytics & SQL Technical Round

Master Advanced SQL, Power BI, and Python analytics, earn official IBM & Microsoft credentials, and launch your career with an enforceable 6-month paid corporate internship.

*Guaranteed monthly stipend corporate internship • Flexible zero-cost EMI plans available