SQL Interview Questions and Queries (2026 Master Guide)

Q1. Explain the difference between DDL, DML, DCL, and TCL commands.

Foundation • 0-2 Yrs

DDL (Data Definition Language: CREATE, ALTER, DROP, TRUNCATE) defines database structure and auto-commits. DML (Data Manipulation Language: INSERT, UPDATE, DELETE) modifies data and requires manual commits in transactions. DCL (Data Control Language: GRANT, REVOKE) manages access permissions. TCL (Transaction Control Language: COMMIT, ROLLBACK, SAVEPOINT) manages transaction consistency.

Q2. What is the difference between WHERE and HAVING clauses in SQL?

Foundation • 0-2 Yrs

WHERE filters individual rows before any aggregations or GROUP BY operations are performed; it cannot reference aggregate functions. HAVING filters aggregated groups after GROUP BY evaluation, allowing conditions on aggregates like HAVING COUNT(*) > 5.

Q3. Differentiate between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Foundation • 0-2 Yrs

INNER JOIN returns records with matching keys in both tables. LEFT JOIN returns all records from the left table and matched records from the right table (filling NULL for unmatched right rows). RIGHT JOIN returns all rows from the right table. FULL OUTER JOIN combines LEFT and RIGHT, returning all rows from both tables with NULLs where matches do not exist.

Q4. What is the difference between UNION and UNION ALL?

Foundation • 0-2 Yrs

UNION merges the result sets of two queries and performs a distinct sort operation to eliminate duplicate rows, incurring a performance cost. UNION ALL concatenates result sets directly without deduplication, executing significantly faster when duplicates are acceptable or known not to exist.

Q5. Explain primary key, unique key, and candidate key constraints.

Foundation • 0-2 Yrs

A Candidate Key is any column or set of columns that uniquely identifies a row. The Primary Key is the chosen candidate key that uniquely identifies each record and strictly forbids NULL values (only one per table). A Unique Key also enforces uniqueness but permits NULL values (often multiple allowed depending on RDBMS).

Q6. What is the difference between DELETE, TRUNCATE, and DROP?

Foundation • 0-2 Yrs

DELETE is a logged DML operation that removes rows based on a WHERE clause, triggers row-level triggers, and can be rolled back. TRUNCATE is a minimally logged DDL operation that deallocates entire data pages, resets identity seeds, is faster, and cannot have a WHERE clause. DROP is a DDL operation that completely obliterates the table structure and data from the database.

Q7. How does NULL evaluation work with comparison operators and the COALESCE() function?

Foundation • 0-2 Yrs

NULL represents unknown or missing data and cannot be evaluated with standard comparison operators (=, !=); expressions like 'NULL = NULL' evaluate to UNKNOWN (falsy in WHERE). You must use 'IS NULL' or 'IS NOT NULL'. COALESCE(val1, val2, ...) evaluates arguments in order and returns the first non-null value.

Q8. What is a subquery and how does a correlated subquery differ from an independent subquery?

Foundation • 0-2 Yrs

An independent subquery executes once independently of the outer query, and its result is passed to the outer query. A correlated subquery references columns from the outer query, meaning it must be evaluated iteratively once for every row processed by the outer query, which can cause significant performance overhead if unindexed.

Q9. Explain the concept of database normalization from 1NF to 3NF.

Foundation • 0-2 Yrs

1NF requires atomic values in columns, unique column names, and a primary key. 2NF satisfies 1NF and removes partial dependencies (non-key columns must depend on the whole primary key). 3NF satisfies 2NF and removes transitive dependencies (non-key columns must not depend on other non-key columns).

Q10. What is the difference between clustered and non-clustered indexes?

Foundation • 0-2 Yrs

A clustered index defines the physical order of rows on disk; a table can have only one clustered index (usually on the primary key). A non-clustered index creates a separate B-Tree structure storing indexed columns alongside row locators (pointers to data pages), allowing multiple non-clustered indexes per table.

Q11. How do string pattern matching operators LIKE and ILIKE work?

Foundation • 0-2 Yrs

LIKE performs case-sensitive pattern matching using wildcard characters: '%' matches zero or more characters, and '_' matches exactly one character. In PostgreSQL, ILIKE provides case-insensitive matching. In MySQL, LIKE is case-insensitive depending on collation.

Q12. What is a Self-Join and when is it utilized in real-world databases?

Foundation • 0-2 Yrs

A Self-Join joins a table to itself using aliases. It is essential when hierarchical or recursive relationships exist within the same table, such as mapping employees to their managers where both employee_id and manager_id reside in the same 'employees' table.

Q13. What are aggregate functions in SQL and how do they handle NULL values?

Foundation • 0-2 Yrs

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) perform calculations over a set of rows and return a single value. All aggregate functions except COUNT(*) automatically ignore NULL values during computation. For instance, AVG([10, NULL, 20]) evaluates to 15, not 10.

Q14. What is a View and how does it differ from a Materialized View?

Foundation • 0-2 Yrs

A standard View is a virtual saved query that executes on-the-fly whenever queried, consuming zero disk storage beyond its definition. A Materialized View physically stores the query result on disk, providing fast read performance for expensive queries but requiring periodic refreshes (e.g., REFRESH MATERIALIZED VIEW).

Q15. What is ACID in relational database management systems?

Foundation • 0-2 Yrs

ACID guarantees transactional reliability: Atomicity (all operations succeed or all rollback), Consistency (transactions preserve integrity constraints), Isolation (concurrent transactions execute independently without interference), and Durability (committed changes survive system crashes).

Q16. Differentiate between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions.

Experienced • 3-5+ Yrs

ROW_NUMBER() assigns a unique sequential integer to each row within a partition. RANK() assigns the same rank to identical values but skips subsequent ranks (e.g., 1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties without skipping numbers (e.g., 1, 2, 2, 3), making it ideal for calculating Nth highest values.

Q17. Explain CTEs (Common Table Expressions) and recursive CTEs for hierarchical queries.

Experienced • 3-5+ Yrs

A CTE (WITH cte_name AS (...)) defines a temporary named result set within a single SELECT/INSERT/UPDATE/DELETE. Recursive CTEs consist of an Anchor member and a Recursive member combined with UNION ALL, terminating when the recursive step returns an empty set. They are used to traverse organizational charts, bill of materials, and graph networks.

Q18. How does B-Tree indexing work and what is the Leftmost Prefix Rule for composite indexes?

Experienced • 3-5+ Yrs

B-Trees maintain balanced hierarchical search trees where leaf nodes store sorted key values and row pointers, enabling O(log N) lookups. When an index spans multiple columns (A, B, C), the Leftmost Prefix Rule dictates that queries can utilize the index only if conditions filter from the leftmost column sequentially (e.g., A, or A and B). Filtering on only B and C bypasses the index.

Q19. How do you read and interpret a SQL Execution Plan (EXPLAIN ANALYZE)?

Experienced • 3-5+ Yrs

EXPLAIN shows the query planner's estimated cost, while EXPLAIN ANALYZE executes the query and reports actual runtime statistics. Key metrics to inspect include: Table Scans (Seq Scan vs Index Scan), Join algorithms (Nested Loop, Hash Join, Merge Join), Actual Rows vs Estimated Rows (identifying stale statistics), and Buffer/Cache hit ratios.

Q20. What are Transaction Isolation Levels and what concurrency anomalies do they prevent?

Experienced • 3-5+ Yrs

SQL-92 defines 4 isolation levels: Read Uncommitted (allows dirty reads), Read Committed (prevents dirty reads, allows non-repeatable reads), Repeatable Read (prevents dirty & non-repeatable reads, allows phantom reads), and Serializable (strict serial execution, prevents all anomalies). Higher isolation increases locking overhead and deadlock risks.

Q21. Explain Dirty Read, Non-Repeatable Read, and Phantom Read anomalies.

Experienced • 3-5+ Yrs

A Dirty Read occurs when Transaction A reads uncommitted data modified by Transaction B (which may rollback). A Non-Repeatable Read occurs when Transaction A re-reads a row and finds data modified by Transaction B. A Phantom Read occurs when Transaction A re-runs a range query and discovers newly inserted/deleted rows committed by Transaction B.

Q22. What causes SQL deadlocks and how do you prevent and resolve them in production?

Experienced • 3-5+ Yrs

Deadlocks occur when two or more transactions cyclically wait for locks held by each other. To prevent deadlocks: access database objects in a consistent global order across all application code, keep transactions short, use lower isolation levels when appropriate, index foreign keys, and utilize row-level locking (SELECT ... FOR UPDATE).

Q23. What is the difference between Optimistic and Pessimistic concurrency control?

Experienced • 3-5+ Yrs

Pessimistic locking assumes conflicts will occur and explicitly locks rows (SELECT FOR UPDATE) preventing others from modifying data until commit. Optimistic locking assumes conflicts are rare; transactions proceed without locks, validating record version numbers or timestamps before committing and aborting/retrying if another update occurred in between.

Q24. When should a database schema be denormalized?

Experienced • 3-5+ Yrs

Denormalization intentionally introduces redundancy into a normalized schema to optimize read performance in OLAP (Analytical) workloads or data warehouses. It avoids expensive multi-table joins, pre-aggregates metrics, and simplifies reporting queries at the cost of increased storage and complex update pipelines.

Q25. Explain table partitioning strategies (Range, List, Hash) and Partition Pruning.

Experienced • 3-5+ Yrs

Partitioning splits a massive table into smaller physical chunks while presenting a unified logical table. Range partitioning divides rows by value boundaries (e.g., dates by year). List partitioning splits by explicit category values (e.g., country codes). Hash partitioning distributes evenly across N partitions via hash algorithms. Partition Pruning allows the query optimizer to skip scanning irrelevant partitions entirely.

Q26. What is Index Fragmentation and how do you resolve it?

Experienced • 3-5+ Yrs

Fragmentation occurs when frequent INSERTs, UPDATEs, and DELETEs cause out-of-order page allocations and empty space on B-Tree index pages, degrading sequential I/O. In PostgreSQL, run VACUUM FULL or REINDEX. In SQL Server, REORGANIZE indexes when fragmentation is between 10%-30%, and REBUILD when fragmentation exceeds 30%.

Q27. How do Window Functions like LEAD() and LAG() work for time-series and delta calculations?

Experienced • 3-5+ Yrs

LEAD(col, offset) retrieves data from subsequent rows within a partition without a self-join, while LAG(col, offset) accesses data from preceding rows. They are heavily utilized in financial and time-series analytics to calculate day-over-day growth, elapsed time between events, and session activity gaps.

Q28. Explain the difference between Nested Loop, Hash Join, and Merge Join in query execution.

Experienced • 3-5+ Yrs

Nested Loop Join iterates outer rows and probes the inner table; it excels when one dataset is tiny and the other has an index. Hash Join builds an in-memory hash table of the smaller dataset and streams the larger table; it excels for large unsorted datasets. Merge Join sorts both datasets on join keys and merges simultaneously; it is fastest when inputs are already indexed or presorted.

Q29. How do you handle slow-running queries caused by parameter sniffing?

Experienced • 3-5+ Yrs

Parameter sniffing occurs when a database compiles an execution plan tailored to parameters passed on initial execution, which proves disastrous for skewed data distributions on subsequent runs. Mitigate by using query hints (OPTIMIZE FOR UNKNOWN / RECOMPILE), utilizing local variables inside stored procedures, or splitting logic into dedicated procedures.

Q30. What are the differences between Row-Store and Column-Store database architectures?

Experienced • 3-5+ Yrs

Row-store databases (Postgres, MySQL) store entire tuples contiguously on data pages, optimal for OLTP write/read of full individual records. Column-store databases (Snowflake, ClickHouse, Redshift) store individual columns contiguously, enabling high compression, vectorized CPU processing, and scanning only queried columns for massive analytical aggregations.

Q31. Write a SQL query to find the Nth highest salary in an Employee table without using subqueries.

Live Coding Challenge

Using the DENSE_RANK() window function inside a Common Table Expression (CTE) handles ties cleanly and returns exact Nth highest earners.

WITH RankedSalaries AS (
    SELECT 
        employee_id,
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rank_pos
    FROM Employee
)
SELECT employee_id, name, salary
FROM RankedSalaries
WHERE rank_pos = 2; -- Change '2' to any Nth rank

Q32. Write a SQL query to calculate a 7-day rolling average of daily revenue.

Live Coding Challenge

We use the AVG() window function specifying ROWS BETWEEN 6 PRECEDING AND CURRENT ROW over an ordered date partition.

SELECT 
    transaction_date,
    daily_revenue,
    ROUND(AVG(daily_revenue) OVER (
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_7day_avg
FROM DailySales
ORDER BY transaction_date;

Q33. Write a query to identify and delete duplicate records from a table while keeping the lowest ID.

Live Coding Challenge

We use a CTE with ROW_NUMBER() partitioned by duplicate keys, deleting all rows where row_num > 1.

WITH DuplicatesCTE AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) as row_num
    FROM Users
)
DELETE FROM Users
WHERE id IN (
    SELECT id FROM DuplicatesCTE WHERE row_num > 1
);

Q34. Write a query to find all employees whose salary is higher than their manager's salary.

Live Coding Challenge

We perform a self-join between the Employee table (representing staff) and the Employee table aliased as Manager on manager_id.

SELECT 
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM Employee e
JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Q35. Write a SQL query to identify users who logged in for 3 or more consecutive days.

Live Coding Challenge

Using the LAG() window function, we inspect preceding login dates for each user and verify date differentials.

WITH CleanLogins AS (
    SELECT DISTINCT user_id, CAST(login_time AS DATE) as login_date
    FROM UserLogins
),
LoginDiffs AS (
    SELECT 
        user_id,
        login_date,
        LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev1,
        LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS prev2
    FROM CleanLogins
)
SELECT DISTINCT user_id
FROM LoginDiffs
WHERE login_date = prev1 + INTERVAL '1 day'
  AND prev1 = prev2 + INTERVAL '1 day';

Q36. Write a SQL query to find the top 3 highest earning employees in each department.

Live Coding Challenge

We partition by department_id and rank using DENSE_RANK() ordered by salary descending.

WITH DeptRanks AS (
    SELECT 
        d.department_name,
        e.name AS employee_name,
        e.salary,
        DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_in_dept
    FROM Employee e
    JOIN Department d ON e.department_id = d.id
)
SELECT department_name, employee_name, salary
FROM DeptRanks
WHERE rank_in_dept <= 3;

Q37. Write a recursive CTE query to generate a complete organizational hierarchy reporting path.

Live Coding Challenge

The anchor member selects top-level executives (manager_id IS NULL), and the recursive member appends subordinates iteratively.

WITH RECURSIVE OrgChart AS (
    -- Anchor Member: Top Executives
    SELECT id, name, manager_id, CAST(name AS VARCHAR(255)) AS reporting_path, 1 as level
    FROM Employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive Member: Subordinates
    SELECT e.id, e.name, e.manager_id, CAST(o.reporting_path || ' -> ' || e.name AS VARCHAR(255)), o.level + 1
    FROM Employees e
    JOIN OrgChart o ON e.manager_id = o.id
)
SELECT level, reporting_path FROM OrgChart ORDER BY reporting_path;

Q38. Write a query to calculate Month-over-Month (MoM) revenue growth percentage.

Live Coding Challenge

We aggregate monthly sales and use LAG() to pull the prior month's revenue to compute percentage change.

WITH MonthlySales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(amount) AS total_revenue
    FROM Orders
    GROUP BY 1
)
SELECT 
    sales_month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY sales_month) AS prev_month_revenue,
    ROUND(100.0 * (total_revenue - LAG(total_revenue) OVER (ORDER BY sales_month)) / 
          LAG(total_revenue) OVER (ORDER BY sales_month), 2) AS mom_growth_pct
FROM MonthlySales;

Q39. Write a query to pivot monthly sales data by region without using native PIVOT syntax.

Live Coding Challenge

We utilize conditional aggregation with SUM(CASE WHEN ...) to pivot rows into columns portably across any SQL dialect.

SELECT 
    EXTRACT(YEAR FROM order_date) AS sales_year,
    SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north_sales,
    SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south_sales,
    SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS east_sales,
    SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west_sales,
    SUM(amount) AS total_annual_sales
FROM Orders
GROUP BY 1
ORDER BY 1;

Q40. Write a query to find churned customers who ordered in 2025 but placed zero orders in 2026.

Live Coding Challenge

We use a LEFT JOIN or NOT EXISTS clause between the 2025 customer cohort and 2026 customer orders.

SELECT DISTINCT o2025.customer_id
FROM Orders o2025
WHERE EXTRACT(YEAR FROM o2025.order_date) = 2025
  AND NOT EXISTS (
      SELECT 1 
      FROM Orders o2026 
      WHERE o2026.customer_id = o2025.customer_id 
        AND EXTRACT(YEAR FROM o2026.order_date) = 2026
  );