SQL Technical Round Questions &amp; Answers

Show answer

A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.

What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?

JoinsMedium

Show answer

In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.

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 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.

How do SQL dialects differ and how do you write portable SQL?

SQL BasicsHard

Show answer

Databases differ in row limiting (LIMIT, TOP, FETCH), string concatenation (|| versus + versus CONCAT), date functions, identity columns (AUTO_INCREMENT, IDENTITY, SERIAL), upsert syntax and case sensitivity. For portable SQL, use ANSI features such as CASE, COALESCE, standard joins, window functions and FETCH FIRST where supported, keep vendor-specific logic isolated, and test against each target database.

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.

What are referential actions such as ON DELETE CASCADE?

Database DesignMedium

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 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.

A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?

Subqueries and CTEsHard

Show answer

Break it into CTEs with meaningful names, each producing one business step such as active customers, order totals and returns. Remove repeated logic, standardise filters, and add comments on business rules. Test each step's row count against the original output. If parts are reused across reports, move them into views. Finally compare results and execution plans to ensure nothing changed.

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.

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.

Match each employee's salary to a grade using a range table.

JoinsMedium

Show answer

SELECT e.name, e.salary, g.grade FROM employees e JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary; This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.

Produce region subtotals and a grand total in one query.

Aggregation and GroupingMedium

Show answer

SELECT COALESCE(region, 'All Regions') AS region, COALESCE(city, 'Subtotal') AS city, SUM(sales) AS sales FROM store_sales GROUP BY ROLLUP(region, city) ORDER BY region, city; In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.

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

Indexes and PerformanceEasy

Show answer

A clustered index defines the physical order of rows in the table, so there can be only one, and in SQL Server the primary key creates it by default. A non-clustered index is a separate structure with key values and pointers to rows, and a table can have many. Range queries on the clustered key are very fast, while non-clustered indexes suit selective lookups.

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.

Write an upsert that inserts a row or updates it if it exists.

Show answer

PostgreSQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty; MySQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty); SQL Server and others use MERGE. A unique key on sku is required.

What is an execution plan?

Indexes and PerformanceEasy

Show answer

An execution plan shows how the database will run a query: which tables are scanned or seeked, which indexes are used, the join algorithms, sort and aggregate steps, and estimated row counts and costs. Use EXPLAIN in MySQL and PostgreSQL, EXPLAIN ANALYZE for actual timing, and the estimated or actual plan in SQL Server. Reading plans is the first step in tuning slow queries.

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.

How does LIKE work and what are wildcards?

SQL BasicsMedium

Show answer

LIKE matches string patterns. The percent sign matches any sequence of characters and the underscore matches exactly one character. name LIKE 'Ra%' finds names starting with Ra, and code LIKE 'A_1' matches A01 or AB1. Leading wildcards such as LIKE '%kumar' prevent index use and are slow on large tables. Case sensitivity depends on the database collation.

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 );

The dashboard queries hit the production database and slow down the app at month end. What do you propose?

Indexes and PerformanceHard

Show answer

Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.

Get the latest 3 orders for every customer using a lateral join.

Subqueries and CTEsHard

Show answer

SELECT c.customer_id, o.order_id, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3 ) o; With an index on (customer_id, order_date), each lookup is fast. SQL Server uses CROSS APPLY with TOP 3.

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.

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.

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.

Write a query for monthly revenue for the current year.

Aggregation and GroupingMedium

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.

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.

What is a covering index?

Indexes and PerformanceMedium

Show answer

A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.

Calculate month-over-month revenue growth percentage.

Show answer

WITH m AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct FROM m ORDER BY month; NULLIF avoids division by zero.

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 SUM with CASE and filtering with WHERE?

Aggregation and GroupingMedium

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').

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.

What are transaction isolation levels?

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 all customers whose email is missing or empty.

SQL BasicsMedium

Show answer

SELECT customer_id, name FROM customers WHERE email IS NULL OR TRIM(email) = ''; NULL needs IS NULL, because email = NULL never matches. Empty strings and spaces are separate values, so TRIM handles both. Consider fixing data at entry with NOT NULL and CHECK constraints.

Find users who logged in on 3 or more consecutive days.

Window FunctionsHard

Show answer

WITH d AS ( SELECT DISTINCT user_id, login_date FROM logins ), g AS ( SELECT user_id, login_date, login_date - CAST(ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date) AS INT) AS grp FROM d ) SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).

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.

Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?

Aggregation and GroupingHard

Show answer

Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.

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.

Find departments where every employee earns above 40000.