SQL Deep Dive Interview Questions Hindi mein

SQL Deep Dive कोर्स के 25 सवाल, हर सवाल के सही जवाब और explanation के साथ। जवाब देखने से पहले ख़ुद सोचें।

How SQL Thinks

1. What order do rows come back in without ORDER BY?

  1. Primary key order
  2. Insertion order
  3. No guaranteed order
  4. Alphabetical order
जवाब देखें

जवाब: No guaranteed order

Always add ORDER BY when order matters.

पाठ: Thinking in Sets

2. Where do you filter on an aggregate such as count(*) > 2?

  1. HAVING
  2. WHERE
  3. ON
  4. ORDER BY
जवाब देखें

जवाब: HAVING

HAVING runs after GROUP BY.

पाठ: Logical Order of Evaluation

3. What does NULL = NULL evaluate to?

  1. An error
  2. TRUE
  3. FALSE
  4. UNKNOWN (NULL)
जवाब देखें

जवाब: UNKNOWN (NULL)

Use IS NULL or IS NOT DISTINCT FROM.

पाठ: NULL and Three-Valued Logic

Joins Without Surprises

4. What happens when you filter a LEFT JOIN's right table in WHERE?

  1. Nothing changes
  2. Unmatched left rows are removed, like an inner join
  3. All rows are duplicated
  4. The query fails
जवाब देखें

जवाब: Unmatched left rows are removed, like an inner join

NULL fails the WHERE condition.

पाठ: Inner and Outer Joins

5. Why can NOT IN (subquery) return no rows at all?

  1. NOT IN is not supported
  2. If the subquery returns a NULL, every NOT IN comparison is unknown
  3. It only works on numbers
  4. It requires an index
जवाब देखें

जवाब: If the subquery returns a NULL, every NOT IN comparison is unknown

Use NOT EXISTS instead.

पाठ: Semi-Joins and Anti-Joins

6. How do you fix revenue doubled by joining order items?

  1. Switch to a LEFT JOIN
  2. Use DISTINCT on the revenue sum
  3. Add ORDER BY
  4. Aggregate at the order grain before joining, or avoid the unneeded join
जवाब देखें

जवाब: Aggregate at the order grain before joining, or avoid the unneeded join

Match the grain to the metric.

पाठ: Fan-Out and Double Counting

Aggregation and Reporting

7. What does count(*) FILTER (WHERE hired >= '2022-01-01') compute?

  1. The number of rows in each group hired since 2022
  2. All rows ignoring the filter
  3. The rows removed by WHERE
  4. A boolean
जवाब देखें

जवाब: The number of rows in each group hired since 2022

Conditional aggregation.

पाठ: GROUP BY, HAVING and FILTER

8. What does GROUPING(col) return for a subtotal row where col is rolled up?

  1. NULL
  2. 0
  3. 1
  4. The column value
जवाब देखें

जवाब: 1

It identifies aggregated levels.

पाठ: Subtotals With ROLLUP and GROUPING SETS

9. Why use >= start and < next_start for date ranges?

  1. BETWEEN is not supported
  2. They are faster to type
  3. Half-open ranges avoid gaps and double counting at boundaries
  4. They ignore time zones
जवाब देखें

जवाब: Half-open ranges avoid gaps and double counting at boundaries

Works for dates and timestamps.

पाठ: Pivoting With Conditional Aggregation

Window Functions

10. Which function gives tied rows the same number and then skips numbers?

  1. RANK
  2. ROW_NUMBER
  3. DENSE_RANK
  4. NTILE
जवाब देखें

जवाब: RANK

1, 2, 2, 4.

पाठ: Ranking and Top-N per Group

11. Which function returns the previous row's value in the window order?

  1. NTILE
  2. LEAD
  3. FIRST_VALUE
  4. LAG
जवाब देखें

जवाब: LAG

LEAD looks forward.

पाठ: Running Totals, Moving Averages and LAG

12. What does sum(salary) OVER (PARTITION BY dept_id) return on each row?

  1. A running total
  2. The total salary of that row's department
  3. The overall total
  4. The row's own salary
जवाब देखें

जवाब: The total salary of that row's department

No ORDER BY means the whole partition.

पाठ: Shares, Comparisons and Bands

CTEs, LATERAL and Set Operations

13. What are the two parts of a recursive CTE?

  1. A SELECT and an ORDER BY
  2. An anchor query and a recursive query joined to the previous results
  3. Two tables of equal size
  4. A trigger and a function
जवाब देखें

जवाब: An anchor query and a recursive query joined to the previous results

Combined with UNION ALL.

पाठ: CTEs and Recursive Queries

14. How do you keep outer rows with no LATERAL results?

  1. Use UNION
  2. CROSS JOIN LATERAL
  3. Add DISTINCT
  4. LEFT JOIN LATERAL (...) ON true
जवाब देखें

जवाब: LEFT JOIN LATERAL (...) ON true

Like any left join.

पाठ: LATERAL Joins

15. Which operation returns rows in the first query but not the second?

  1. EXCEPT
  2. INTERSECT
  3. UNION
  4. UNION ALL
जवाब देखें

जवाब: EXCEPT

Set difference.

पाठ: UNION, INTERSECT and EXCEPT

Analytical Patterns

16. How do you show days with no orders as zero?

  1. Use INNER JOIN
  2. GROUP BY ordered_on only
  3. LEFT JOIN orders to a generated list of days and COALESCE the sum to 0
  4. Add ORDER BY day
जवाब देखें

जवाब: LEFT JOIN orders to a generated list of days and COALESCE the sum to 0

Start from the complete calendar.

पाठ: Filling Gaps in Time Series

17. In gaps-and-islands, what stays constant within a run of consecutive days?

  1. The date
  2. The row number
  3. The date minus its row number
  4. The user count
जवाब देखें

जवाब: The date minus its row number

That difference is the island key.

पाठ: Gaps and Islands

18. What prevents duplicates from reappearing after cleanup?

  1. A unique constraint or unique index on the key
  2. An ORDER BY clause
  3. A view
  4. Running the cleanup weekly only
जवाब देखें

जवाब: A unique constraint or unique index on the key

Let the database enforce it.

पाठ: Finding and Removing Duplicates

Changing Data Safely

19. Which PostgreSQL feature is simplest for a concurrent single-row upsert?

  1. CREATE VIEW
  2. A SELECT then INSERT in application code
  3. TRUNCATE
  4. INSERT ... ON CONFLICT DO UPDATE
जवाब देखें

जवाब: INSERT ... ON CONFLICT DO UPDATE

Atomic with respect to concurrent inserts.

पाठ: MERGE and Upserts

20. What does RETURNING do on an UPDATE?

  1. Rolls back the update
  2. Returns the modified rows, like a SELECT
  3. Returns only a count
  4. Locks the whole table
जवाब देखें

जवाब: Returns the modified rows, like a SELECT

Great for audits and verification.

पाठ: UPDATE ... FROM and DELETE ... USING

21. What does ROLLBACK do after an UPDATE inside BEGIN?

  1. Commits the changes
  2. Undoes all changes made since BEGIN
  3. Deletes the table
  4. Repeats the update
जवाब देखें

जवाब: Undoes all changes made since BEGIN

Nothing is permanent until COMMIT.

पाठ: A Safe Workflow for Data Changes

Writing Fast Queries

22. Why can WHERE date(created_at) = '2026-03-01' miss an index on created_at?

  1. PostgreSQL ignores indexes on timestamps
  2. Dates cannot be indexed
  3. The index is corrupted
  4. The function changes the column, so the index order on raw values cannot be used directly
जवाब देखें

जवाब: The function changes the column, so the index order on raw values cannot be used directly

Use a range or an expression index.

पाठ: Index-Friendly (Sargable) Predicates

23. An index on (user_id, kind) is least useful for which filter?

  1. WHERE kind = 'buy'
  2. WHERE user_id = 42
  3. WHERE user_id = 42 AND kind = 'buy'
  4. WHERE user_id = 42 ORDER BY kind
जवाब देखें

जवाब: WHERE kind = 'buy'

Leftmost prefix rule.

पाठ: Composite Index Column Order

24. What does EXPLAIN ANALYZE add compared with EXPLAIN?

  1. It creates missing indexes
  2. It rewrites the query
  3. It executes the query and reports actual row counts
  4. It only formats the SQL
जवाब देखें

जवाब: It executes the query and reports actual row counts

Estimated versus actual.

पाठ: Reading EXPLAIN ANALYZE

25. Which belongs on an SQL review checklist?

  1. Put LEFT JOIN filters in WHERE
  2. Always use SELECT *
  3. Use NOT EXISTS rather than NOT IN with nullable subqueries
  4. Rely on default row order
जवाब देखें

जवाब: Use NOT EXISTS rather than NOT IN with nullable subqueries

NULL-safe anti-joins.

पाठ: An SQL Review Checklist

← कोर्स पर वापस जाएँ