Interview Q&A / SQL & DBMS

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

   All three number rows in a given order. ROW_NUMBER always gives unique numbers 1, 2, 3, even for ties. RANK gives tied rows the same number and then skips ahead (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

   IntermediateIn-depth4 min read
2. Can a foreign key be NULL, and can it reference the same table?

   Yes to both. A nullable foreign key means the relationship is optional for that row. A foreign key can also reference the same table's primary key, which is how hierarchies like employee and manager are stored.

   IntermediateQuick answer1 min read
3. Can you INSERT, UPDATE or DELETE through a view?

   Only if the view is simple, based on one table without DISTINCT, GROUP BY, aggregates or joins that make the target row ambiguous. Otherwise the database cannot map the change to base rows. WITH CHECK OPTION blocks rows leaving the view.

   IntermediateQuick answer1 min read
4. Can you use HAVING without GROUP BY?

   Yes. Without GROUP BY, the whole table is treated as one group, so HAVING can filter that single aggregate row. If the condition is false, the query returns no rows.

   IntermediateQuick answer1 min read
5. How do you find rows in one table that have no matching row in another?

   Use LEFT JOIN and keep rows where the right table's key IS NULL, or use NOT EXISTS. Both are safer than NOT IN, which fails when the subquery returns a NULL.

   IntermediateQuick answerCoding1 min read
6. What do LAG and LEAD do in SQL?

   LAG returns a value from a previous row and LEAD from a following row, within the window order. They compare a row to its neighbour without a self join.

   IntermediateQuick answer1 min read
7. What do ON DELETE CASCADE, SET NULL and RESTRICT do in a foreign key?

   They define what happens to child rows when the parent row is deleted. CASCADE deletes the children, SET NULL clears their foreign key, and RESTRICT or NO ACTION blocks the delete.

   IntermediateQuick answer1 min read
8. What do PARTITION BY and ORDER BY do inside OVER()?

   PARTITION BY splits rows into independent groups, and ORDER BY sorts rows within each group. For aggregates, adding ORDER BY also turns the result into a running calculation.

   IntermediateQuick answer1 min read
9. What does GROUP BY ROLLUP do?

   ROLLUP adds subtotal and grand-total rows to a grouped result. For GROUP BY ROLLUP(a, b) you get rows for (a, b), then a subtotal per a, then one grand total, with NULL marking the rolled-up columns.

   IntermediateQuick answer1 min read
10. What does NTILE do and where is it used?

    NTILE(n) divides the ordered rows into n roughly equal buckets and returns the bucket number for each row. It is used for quartiles, percentiles and splitting data into groups.

    IntermediateQuick answer1 min read
11. What is a composite index and what is the leftmost prefix rule?

    A composite index covers several columns in a fixed order. It can be used for queries filtering on its leading columns, such as (a) or (a, b), but generally not for a filter on b alone.

    IntermediateQuick answer1 min read
12. What is a covering index?

    A covering index contains every column a query needs, so the database answers from the index alone without visiting the table. This is called an index-only scan and avoids extra lookups.

    IntermediateQuick answer1 min read
13. What is a CTE and how is it different from a subquery?

    A CTE (WITH clause) names a query at the top of a statement so it can be read like a step and reused several times. Results are the same as a subquery, but CTEs read better and also support recursion.

    IntermediateQuick answer1 min read
14. What is a FULL OUTER JOIN and how can you simulate it in MySQL?

    FULL OUTER JOIN returns all rows from both tables, matching where possible and filling NULLs where not. MySQL has no FULL JOIN, so combine a LEFT JOIN and a RIGHT JOIN with UNION.

    IntermediateQuick answer1 min read
15. What is a NATURAL JOIN and why do many developers avoid it?

    NATURAL JOIN automatically joins on all columns with the same name in both tables. It is risky because adding or renaming a column silently changes the join. Explicit ON or USING is safer.

    IntermediateQuick answer1 min read
16. What is a trigger in SQL and what types exist?

    A trigger is code that the database runs automatically when INSERT, UPDATE or DELETE happens on a table. Types are BEFORE, AFTER and INSTEAD OF, and row-level or statement-level.

    IntermediateQuick answer1 min read
17. What is a view, and how is a materialized view different?

    A view is a saved query that runs each time you select from it and stores no data. A materialized view stores the query result physically, so reads are fast but the data is stale until refreshed.

    IntermediateQuick answer1 min read
18. What is the difference between a correlated and a non-correlated subquery?

    A non-correlated subquery runs once and does not refer to the outer query. A correlated subquery refers to a column of the outer row, so it is evaluated once for each outer row, at least logically.

    IntermediateQuick answer1 min read
19. What is the difference between a stored procedure and a function in SQL?

    A function must return a value and can be used inside queries, usually without side effects. A procedure is called with CALL or EXEC, can return nothing or several outputs, and can change data and manage transactions.

    IntermediateQuick answer1 min read
20. What is the difference between IN and EXISTS?

    IN compares a value against a list of values returned by a subquery. EXISTS only checks whether the subquery returns any row and stops at the first match. EXISTS handles NULLs more safely, especially with NOT.

    IntermediateQuick answer1 min read
21. What is the difference between putting a filter in ON versus WHERE in a LEFT JOIN?

    A filter on the right table in ON limits which rows match while still keeping every left row. The same filter in WHERE runs after the join and removes the NULL-extended rows, turning the LEFT JOIN into an INNER JOIN.

    IntermediateQuick answer1 min read
22. Why might the database ignore an index on a column?

    Common reasons are wrapping the column in a function, a leading wildcard in LIKE, a type mismatch, a condition that matches most rows, or outdated statistics. The optimizer then prefers a full scan.

    IntermediateQuick answer1 min read
23. Write a query to calculate a running total of daily sales.

    Use SUM(amount) OVER (ORDER BY day) to get a cumulative sum. Add PARTITION BY to restart the total per group, and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.

    IntermediateQuick answerCoding1 min read
24. Write a query to find the highest-paid employee in each department.

    Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.

    IntermediateQuick answerCoding1 min read