SQL Interview Questions in Noida

Show answer

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.

A query with many OR conditions across different columns is slow. How can you optimise it?

Indexes and PerformanceHard

Show answer

OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.

Your NOT IN query returns zero rows even though you expect results. What do you check?

Show answer

Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.

What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?

JoinsMedium

Show answer

A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.

What is a star schema and a snowflake schema?

Show answer

A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.

A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?

Transactions and Advanced SQLMedium

Show answer

A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.

What are transaction isolation levels?

Transactions and Advanced SQLMedium

Show answer

Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.

Find customers who have never placed an order.

JoinsEasy

Show answer

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; Alternative: SELECT customer_id, name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );

What is a trigger and when should it be used?

Transactions and Advanced SQLMedium

Show answer

A trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table. It is used for auditing changes, enforcing complex rules and maintaining derived data. Triggers are hidden from application developers, can slow writes and make debugging hard, especially when they cascade. Use them sparingly, document them well, and prefer constraints or application logic when possible.

What is the CASE expression and where can it be used?

SQL BasicsMedium

Show answer

CASE adds conditional logic in SQL, returning a value based on conditions: CASE WHEN marks >= 60 THEN 'First' WHEN marks >= 45 THEN 'Second' ELSE 'Fail' END. It can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates, for example SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) to pivot data. Conditions are checked in order and the first true one wins.

A query uses SELECT * on a table with 80 columns. Why is that a problem?

Show answer

SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.

You added an index but the query still does a full table scan. Why might that be?

Show answer

Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.

What do NTILE, PERCENT_RANK and CUME_DIST do?

Window FunctionsMedium

Show answer

NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.

What is the difference between a correlated and a non-correlated subquery?

Subqueries and CTEsEasy

Show answer

A non-correlated subquery runs independently and returns a result once, such as the overall average salary. A correlated subquery references columns from the outer query and logically runs for each outer row, such as comparing each employee's salary with the average of their own department. Correlated subqueries are powerful but may be slow without good indexes, although optimisers often rewrite them as joins.

Describe a time you found and fixed a wrong number in a report.

JoinsMedium

Show answer

Situation: a monthly dashboard showed 30 percent higher revenue than finance figures. Task: find the cause before the leadership review. Action: I traced the SQL, found a join to the shipments table that duplicated orders with split shipments, rewrote it with a pre-aggregated CTE and reconciled totals with finance. Result: numbers matched exactly and I added a row-count test to the report pipeline.

What is the difference between UNION and UNION ALL?

SQL BasicsMedium

Show answer

UNION combines the results of two queries and removes duplicate rows, which requires sorting or hashing. UNION ALL combines results and keeps all rows, so it is faster. Both need the same number of columns with compatible types in the same order. Use UNION ALL when duplicates are impossible or acceptable, such as stacking monthly tables, and UNION only when deduplication is required.

A search on name LIKE '%sharma%' is very slow on a 20 million row table. What can you do?

SQL BasicsMedium

Show answer

A leading wildcard cannot use a normal B-tree index, so the database scans the table. Options are full-text indexing, trigram indexes in PostgreSQL, a reversed or computed column if the pattern is a suffix, or a search engine for heavy text search. Also limit the result, filter on other indexed columns first, and check whether users really need contains search.

A query that ran in 1 second now takes 40 seconds after a data load. What do you check?

Show answer

Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.

What is the difference between IN and EXISTS?

Show answer

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.

What is the difference between OLTP and OLAP databases?

Show answer

OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.

What is the difference between a CTE, a subquery, a temp table and a view?

Show answer

A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.

Management wants the percentage of each category in total sales. How do you write it?

Aggregation and GroupingMedium

Show answer

Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.

How would you design a database for an online food delivery app at a high level?

Show answer

Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.

Join three tables to get total sales by product category.

JoinsMedium

Show answer

SELECT cat.category_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id GROUP BY cat.category_name ORDER BY revenue DESC; Join from the most detailed table outward and aggregate at the end.

What is the difference between an index scan and an index seek?

Show answer

An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.

What are equi joins, non-equi joins and natural joins?

JoinsMedium

Show answer

An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.

Write a query to show each employee with their manager's name.

JoinsMedium

Show answer

SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.

What are referential actions such as ON DELETE CASCADE?

Show answer

Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.

What is a subquery and where can it be used?

Subqueries and CTEsEasy

Show answer

A subquery is a query nested inside another statement. It can appear in WHERE, such as salary > (SELECT AVG(salary) FROM employees), in FROM as a derived table, in SELECT as a scalar value, and in HAVING. It can return a single value, a list for IN, or a table. Subqueries break complex logic into smaller steps.

A long-running report transaction is causing table bloat and blocking. What do you do?

Transactions and Advanced SQLHard

Show answer

Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.

A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?

Show answer

Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.

Calculate the time each user spent between consecutive events.

Window FunctionsHard

Show answer

SELECT user_id, event_time, event_name, event_time - LAG(event_time) OVER ( PARTITION BY user_id ORDER BY event_time ) AS gap_since_prev FROM events ORDER BY user_id, event_time; Gaps above 30 minutes can start a new session using a SUM of a flag over the same window, a common sessionisation pattern.

What is the difference between optimistic and pessimistic locking?

Transactions and Advanced SQLHard

Show answer

Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.

What are window functions in SQL?

Window FunctionsEasy

Show answer

Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.

Companies with offices in Noida (4)

IT services

HCLTech

SQL interview questions

30 questions
4 rounds

Noida, Chennai, Bengaluru +2

IT services

Tech Mahindra

SQL interview questions

30 questions
4 rounds

Pune, Hyderabad, Bengaluru +2

Product

Microsoft

SQL interview questions

30 questions
5 rounds

Hyderabad, Bengaluru, Noida +2

Fintech

Paytm

SQL interview questions

30 questions
5 rounds

Noida, Bengaluru, Mumbai