Interview Q&A / SQL & DBMS

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

    Both enforce uniqueness and create an index. A primary key also forbids NULL, there is only one per table, and it identifies each row. A table can have many unique indexes, and they usually allow NULLs.

    BeginnerQuick answer1 min read
32. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

    COUNT(*) counts all rows, COUNT(column) counts rows where that column is not NULL, and COUNT(DISTINCT column) counts the different non-NULL values.

    BeginnerQuick answer1 min read
33. What is the difference between LEFT JOIN and RIGHT JOIN?

    LEFT JOIN keeps every row of the left table and fills NULLs where the right has no match. RIGHT JOIN does the opposite. They are mirror images, and any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables.

    BeginnerQuick answer1 min read
34. Why can't you use a column alias in the WHERE clause?

    WHERE runs before SELECT, so the alias does not exist yet when the filter is evaluated. Repeat the expression, or put the query in a subquery or CTE and filter outside.

    BeginnerQuick answer1 min read
35. Why does SELECT with GROUP BY fail when you pick a column that is not grouped?

    After grouping, each group becomes one output row, so a column that is neither in GROUP BY nor inside an aggregate has many possible values and the database cannot pick one. Group by it or aggregate it.

    BeginnerQuick answer1 min read
36. Write a query to list departments that have more than 5 employees.

    Group by department and filter groups with HAVING COUNT(*) > 5. WHERE cannot be used because the count exists only after grouping.

    BeginnerQuick answerCoding1 min read
37. 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
38. 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
39. 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
40. 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
41. What are dirty reads, non-repeatable reads and phantom reads?

    A dirty read sees another transaction's uncommitted data. A non-repeatable read gets a different value for the same row on a second read. A phantom read gets a different set of rows for the same query.

    IntermediateQuick answer1 min read
42. What are the four transaction isolation levels in SQL?

    From weakest to strongest they are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more anomalies such as dirty reads, non-repeatable reads and phantoms, at the cost of concurrency.

    IntermediateQuick answer1 min read
43. 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
44. 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
45. 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
46. 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
47. 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
48. 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
49. 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
50. 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
51. What is a deadlock in a database and how is it handled?

    A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction as the victim, and the application should retry it.

    IntermediateQuick answer1 min read
52. 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
53. 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
54. What is a partial dependency and how does 2NF remove it?

    A partial dependency is when a non-key column depends on only part of a composite primary key. 2NF requires 1NF plus no partial dependencies, which you achieve by moving that column to a table keyed by that part.

    IntermediateQuick answer1 min read
55. 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
56. 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
57. What is denormalization and when would you use it?

    Denormalization deliberately adds redundant data, such as copied columns or precomputed totals, to avoid joins and speed up reads. It trades storage and write complexity for read performance, and suits read-heavy reporting.

    IntermediateQuick answer1 min read
58. 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
59. 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
60. What is the difference between a surrogate key and a natural key?

    A natural key is a real-world attribute that is unique, like email or Aadhaar number. A surrogate key is a meaningless generated value, like an auto-increment id. Surrogate keys stay stable when business data changes.

    IntermediateQuick answer1 min read