SQL Interview Questions in Bangalore

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.

Write a query to pivot order counts by status into columns.

SQL BasicsMedium

Show answer

SELECT customer_id, SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled, SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned FROM orders GROUP BY customer_id; Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.

Rewrite a non-SARGable date filter to use an index.

Indexes and PerformanceMedium

Show answer

Slow: SELECT * FROM orders WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 3; Fast: SELECT * FROM orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'; The second form compares the raw column, so an index on order_date can be used with a range seek.

Calculate a 7-day moving average of daily active users.

Show answer

SELECT activity_date, dau, ROUND(AVG(dau) OVER ( ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ), 1) AS dau_7d_avg FROM daily_users ORDER BY activity_date; This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.

Compare this month's and last month's sales per product using a full outer join.

JoinsMedium

Show answer

SELECT COALESCE(c.product_id, p.product_id) AS product_id, COALESCE(c.sales, 0) AS this_month, COALESCE(p.sales, 0) AS last_month FROM cur_month c FULL OUTER JOIN prev_month p ON c.product_id = p.product_id; Products sold in only one of the months still appear, with zero for the other month.

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.

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.

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.

Write a transaction to transfer money between two accounts.

Transactions and Advanced SQLEasy

Show answer

BEGIN; UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1 AND balance >= 5000; UPDATE accounts SET balance = balance + 5000 WHERE account_id = 2; COMMIT; If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.

Find cities with more than 100 customers.

Aggregation and GroupingEasy

Show answer

SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) > 100 ORDER BY customers DESC; HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.

What are ACID properties?

Transactions and Advanced SQLEasy

Show answer

Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not interfere and behave as if run in some order, depending on isolation level. Durability: once committed, changes survive crashes because they are written to logs. Banking and payment systems depend on ACID.

Write a query for monthly revenue for the current year.

Show answer

SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS revenue, COUNT(DISTINCT customer_id) AS buyers FROM orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY EXTRACT(MONTH FROM order_date) ORDER BY month; A date range in WHERE keeps an index on order_date usable.

Calculate a running total of daily sales.

Window FunctionsEasy

Show answer

SELECT sale_date, amount, SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM daily_sales ORDER BY sale_date; Add PARTITION BY store_id to restart the running total for each store.

What is the difference between CHAR and VARCHAR?

SQL BasicsEasy

Show answer

CHAR(n) stores fixed-length strings and pads shorter values with spaces, so every value uses n characters. VARCHAR(n) stores variable-length strings up to n and uses only the needed space plus a small length overhead. CHAR suits fixed codes such as country code or PIN format, while VARCHAR suits names, emails and addresses whose length varies.

How do you concatenate values within a group?

Show answer

Use string aggregation: STRING_AGG(name, ', ') in PostgreSQL and SQL Server, GROUP_CONCAT(name SEPARATOR ', ') in MySQL, and LISTAGG in some other databases. Most support ordering inside, for example STRING_AGG(name, ', ' ORDER BY name). It is used to list products in an order or skills of an employee in one row, but watch length limits.

What is GROUP BY and what is the rule for selected columns?

Aggregation and GroupingEasy

Show answer

GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.

Can you use window functions with aggregate functions in the same query?

Show answer

Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.

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

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 is a window frame and what is the default frame?

Show answer

A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.

What are anti joins and semi joins?

JoinsMedium

Show answer

A semi join returns rows from the first table that have at least one match in the second, without duplicating them, usually written with EXISTS or IN. An anti join returns rows with no match, written with NOT EXISTS or LEFT JOIN with WHERE right.key IS NULL. For example, customers who never ordered is an anti join. They avoid duplicates that a normal join could create.

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

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.

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

Database DesignMedium

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.

What are FIRST_VALUE, LAST_VALUE and NTH_VALUE?

Show answer

FIRST_VALUE returns the first value in the window frame, LAST_VALUE the last, and NTH_VALUE the nth. A common trap is LAST_VALUE with the default frame, which ends at the current row, so it returns the current row's value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value of the partition.

Someone ran an UPDATE without WHERE on production. What would you do?

SQL BasicsMedium

Show answer

Stop further changes and inform the lead and DBA immediately. If the session is still in an open transaction, roll back. Otherwise, restore affected rows from the latest backup or point-in-time recovery to a separate database and update the production table from it, or use audit or history tables if available. Afterwards, add safeguards such as safe-update mode, reviews and restricted write access.

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.

You need to delete duplicate rows but keep one copy of each. How do you do it?

Show answer

Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.

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

Indexes and PerformanceMedium

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 is the difference between SUM with CASE and filtering with WHERE?

Show answer

WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').

You need to update 50 million rows in production. How do you do it safely?

Show answer

Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.

Two users book the last seat at the same time and both succeed. How do you fix it?

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.

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.

A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?

Show answer

Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.

What does DISTINCT do and how is it different from GROUP BY?

SQL BasicsMedium

Show answer

DISTINCT removes duplicate rows from the result based on all selected columns. GROUP BY groups rows so you can apply aggregates per group. SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city give the same output, but GROUP BY is needed when you also want counts or sums. Overusing DISTINCT to hide duplicate join rows is a common mistake.

Write SQL to create a students table with a primary key and constraints.

SQL BasicsEasy

Show answer

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE, age INT CHECK (age >= 17), city VARCHAR(50) DEFAULT 'Delhi', joined_on DATE ); PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.

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.

Why is NOT IN dangerous when the subquery contains NULL?