SQL Interview Questions in Chennai

Show answer

This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.

How do you get the top 3 rows in different databases?

SQL BasicsMedium

Show answer

MySQL and PostgreSQL: SELECT * FROM products ORDER BY price DESC LIMIT 3; SQL Server: SELECT TOP 3 * FROM products ORDER BY price DESC; Standard SQL: SELECT * FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY; Always use ORDER BY, otherwise the top rows are not guaranteed.

You need customers who bought both product A and product B. How would you write it?

JoinsMedium

Show answer

Group by customer and count distinct matching products: SELECT customer_id FROM order_items WHERE product_id IN ('A', 'B') GROUP BY customer_id HAVING COUNT(DISTINCT product_id) = 2. Alternatively use INTERSECT of two queries, or two EXISTS conditions. The HAVING approach is concise, scans the table once and is easy to extend to three or more products.

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.

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.

Create an index to speed up searching orders by customer and date.

Indexes and PerformanceEasy

Show answer

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date); This helps queries like: SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2025-01-01'; Check with EXPLAIN that the index is used, and avoid creating duplicate indexes on the same columns.

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.

Tell me about a time you significantly improved query performance.

Indexes and PerformanceMedium

Show answer

Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.

Find duplicate email addresses in a users table.

Show answer

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; To see full duplicate rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email; Normalise case with LOWER(email) if needed.

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

Indexes and PerformanceMedium

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.

Rewrite a nested query using CTEs for readability.

Show answer

WITH order_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), ranked AS ( SELECT customer_id, total, NTILE(10) OVER (ORDER BY total DESC) AS decile FROM order_totals ) SELECT * FROM ranked WHERE decile = 1; Each CTE is a named step, making top 10 percent customer logic easy to read.

What happens when you join on a column that has NULL values?

JoinsEasy

Show answer

NULL never equals NULL in a join condition, so rows with NULL in the join column do not match each other in an inner join. In a LEFT JOIN, left rows with NULL keys are kept but get NULLs for right columns. If you really need NULLs to match, use a condition such as a.key IS NOT DISTINCT FROM b.key, or COALESCE both sides carefully.

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.

Your query WHERE manager_id <> 5 is not returning employees who have no manager. Why?

SQL BasicsMedium

Show answer

Employees with no manager have manager_id as NULL, and NULL <> 5 evaluates to unknown, not true, so those rows are excluded. Rewrite it as WHERE manager_id <> 5 OR manager_id IS NULL, or use COALESCE(manager_id, 0) <> 5. This three-valued logic with true, false and unknown causes many subtle bugs in reports and NOT IN subqueries.

Can you use a column alias in GROUP BY or HAVING?

Show answer

In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.

A join between two large tables takes minutes. How would you speed it up?

JoinsHard

Show answer

Check the execution plan for scans, join type and row estimates. Ensure join keys have indexes and matching data types, because implicit conversion blocks index use. Filter both sides early, select only needed columns, update statistics, and avoid functions on join columns. For very large data, consider partitioning, pre-aggregated tables, or batch processing outside peak hours.

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.

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Database DesignMedium

Show answer

The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.

You need each customer's latest address from a history table. How do you query it?

Window FunctionsMedium

Show answer

Use ROW_NUMBER partitioned by customer and ordered by updated date descending, then keep rn = 1: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM address_history) SELECT * FROM r WHERE rn = 1. Add a tiebreaker such as id DESC in case two updates share the same timestamp.

What is the difference between a unique index and a normal index?

Indexes and PerformanceEasy

Show answer

A unique index enforces that no two rows have the same value in the indexed columns, and it is created automatically for primary keys and unique constraints. A normal index only speeds up lookups and allows duplicates. Both speed up searches, but a unique index also protects data integrity, for example preventing two users from registering with the same email.

Create a simple stored procedure that returns orders for a customer.

Show answer

MySQL: DELIMITER // CREATE PROCEDURE get_orders(IN p_customer INT) BEGIN SELECT order_id, order_date, amount FROM orders WHERE customer_id = p_customer ORDER BY order_date DESC; END // DELIMITER ; CALL get_orders(101); Parameters prevent SQL injection compared with building query strings in code.

What are ROLLUP, CUBE and GROUPING SETS?

Show answer

They produce multiple grouping levels in one query. ROLLUP(region, city) gives city totals, region subtotals and a grand total. CUBE(region, product) gives all combinations of subtotals. GROUPING SETS lets you list exact combinations, such as (region), (product) and (). The GROUPING function identifies subtotal rows, which is useful for labelling them in reports.

Tell me about a time you had to deliver a report under a tight deadline.

Show answer

Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.

Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?

Show answer

Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.

What is normalisation and why is it needed?

Database DesignEasy

Show answer

Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.

Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?

Transactions and Advanced SQLHard

Show answer

Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.

How do you explain a technical database issue to a non-technical manager?

Show answer

Situation: my manager asked why a report was late every Monday. Task: explain and get approval for a fix. Action: I compared the database to a busy shop counter where reports and customers queue together, showed the timing data, and proposed a separate reporting copy. Result: he understood the trade-off in five minutes, approved the read replica, and Monday reports became on time.

An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?

Database DesignHard

Show answer

Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.

Are CTEs materialised or inlined?

Show answer

It depends on the database. SQL Server and MySQL usually inline CTEs like subqueries. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since version 12 non-recursive CTEs referenced once are inlined, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. Referencing a heavy CTE many times may cause repeated work, so check the plan.

How do you keep improving your SQL skills?

JoinsEasy

Show answer

Situation: after college I knew only basic SELECT queries. Task: become interview-ready for data roles. Action: I solved two practice problems daily, read execution plans for my own queries, revised window functions weekly and built a small project analysing public sales data in PostgreSQL. Result: within three months I could solve medium-level problems confidently, and I continue practising regularly.

Your running total jumps by large amounts on some dates instead of row by row. Why?

Window FunctionsMedium

Show answer

With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.

How do you prevent SQL injection in applications?

Show answer

Never build SQL by concatenating user input. Use parameterised queries or prepared statements, where input is sent separately from the SQL text, or ORMs that do this automatically. Validate and whitelist inputs such as sort column names, give application users least privilege, and avoid dynamic SQL in procedures unless parameters are bound. Input like ' OR 1=1 -- then cannot change the query logic.

What are surrogate keys and natural keys?

Database DesignMedium

Show answer

A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.

An interviewer asks for the Nth highest salary in a reusable way. How do you answer?

Show answer

Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.

Companies with offices in Chennai (8)

TCS

SQL interview questions

30 questions
4 rounds

Mumbai, Chennai, Pune +2

Infosys

SQL interview questions

30 questions
4 rounds

Bengaluru, Mysuru, Pune +2

Wipro

SQL interview questions

30 questions
4 rounds

Bengaluru, Hyderabad, Pune +2

Cognizant

SQL interview questions

30 questions
4 rounds

Chennai, Bengaluru, Pune +2

Capgemini

SQL interview questions

30 questions
4 rounds

Mumbai, Bengaluru, Pune +2

HCLTech

SQL interview questions

30 questions
4 rounds

Noida, Chennai, Bengaluru +2

Tech Mahindra

SQL interview questions

30 questions
4 rounds

Pune, Hyderabad, Bengaluru +2

Product

Amazon

SQL interview questions

30 questions
5 rounds

Bengaluru, Hyderabad, Chennai +2