SQL Developer Interview Questions &amp; Answers

Show answer

Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.

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.

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.

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?

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 NULL in SQL and how do you compare it?

SQL BasicsEasy

Show answer

NULL means a missing or unknown value, not zero or an empty string. Any comparison with NULL using = or <> returns unknown, so rows are not returned. Use IS NULL or IS NOT NULL to test it, and COALESCE or IFNULL to replace it. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts all rows and COUNT(column) skips NULLs.

How do you read an EXPLAIN ANALYZE output in PostgreSQL?

Show answer

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101; Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.

Tell me about a time you significantly improved query performance.

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.

Why is NOT IN dangerous when the subquery contains NULL?

Show answer

If the subquery returns any NULL, value NOT IN (list) becomes unknown for every row, because comparing with NULL is unknown, so the query returns no rows. For example, NOT IN (SELECT manager_id FROM employees) fails when a manager_id is NULL. Use NOT EXISTS or filter out NULLs in the subquery to get correct results.

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.

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.

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.

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 a stored procedure and a function?

Show answer

A function must return a value or table and can be used inside SELECT or WHERE, but in most databases cannot change data or manage transactions. A stored procedure is executed independently, can return multiple result sets or output parameters, can modify data and control transactions. Use functions for reusable calculations and procedures for workflows such as month-end processing.

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.

An interviewer asks how you would find why a query is slow. What steps do you describe?

Show answer

First reproduce the issue and measure time. Then check the execution plan for scans, expensive sorts, row estimate errors and join types. Look at indexes on filter and join columns, whether predicates are SARGable, and statistics freshness. Check for blocking or locks from other sessions. Change one thing at a time, measure again, and document the improvement.

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.

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.

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.

When should you not create an index?

Show answer

Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.

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.

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.

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.

How do you insert, update and delete a row?

SQL BasicsEasy

Show answer

INSERT INTO students (student_id, name, city) VALUES (101, 'Anita', 'Noida'); UPDATE students SET city = 'Gurugram' WHERE student_id = 101; DELETE FROM students WHERE student_id = 101; Always use WHERE with UPDATE and DELETE; without it every row changes.

Create an audit trigger that logs salary changes.

Transactions and Advanced SQLHard

Show answer

PostgreSQL: CREATE FUNCTION log_salary() RETURNS trigger AS $$ BEGIN IF NEW.salary <> OLD.salary THEN INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at) VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_salary AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_salary();

What is a materialised view?

Show answer

A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.

Why should we hire you for a SQL or data role as a fresher?

SQL BasicsEasy

Show answer

Situation: in college I built a library management project with a MySQL backend. Task: make queries fast and correct. Action: I designed normalised tables, wrote queries with joins and group by, and practised over 150 interview problems online. Result: I can write clean, tested SQL today. I learn quickly, ask good questions and want to grow into a strong data professional with your team.

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.

Describe a time you reviewed someone else's SQL and suggested improvements.

Show answer

Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.

What are dirty reads, non-repeatable reads and phantom reads?

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.

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.

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.

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

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.

What is MVCC and how does it improve concurrency?

Transactions and Advanced SQLHard

Show answer

Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.

What is a deadlock and how do databases handle it?

Show answer

A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.

What is a SARGable query and why does it matter?

Show answer

A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.

Find departments where every employee earns above 40000.

Show answer

SELECT d.department_id, d.name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary <= 40000 ) AND EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); Double negation expresses for all; the EXISTS excludes empty departments.

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.

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