Step-by-step articles on SQL, part of our Backend Interviews collection.
Showing 10 articles
Short answer: WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY , so it can use aggregate functions like COUNT and SUM . -- Customers who placed more than 5 paid orders in 2026 SELECT customer_id, COUNT(*) AS paid_orders, SUM(...
Short answer: joins combine rows from two tables based on a condition. They differ in what happens to rows that have no match. -- customers: (1, Asha), (2, Ravi), (3, Meera) -- orders: (101, customer 1), (102, customer 1), (103, customer 9) INNER JOIN: only ma...
A favourite interview question: given employees(id, name, department, salary) , find the Nth highest salary. Mention how you handle ties and missing values. 1. DISTINCT with LIMIT/OFFSET -- 3rd highest distinct salary SELECT DISTINCT salary FROM employees ORDE...
Short answer: an index is a separate, sorted data structure (usually a B-tree) that maps column values to row locations, so the database can find matching rows without scanning the whole table, much like a book's index. CREATE INDEX idx_orders_customer ON orde...
Short answer: normalisation organises tables to reduce duplicated data and prevent update anomalies. Each normal form removes a specific kind of redundancy. Starting point orders_raw order_id | customer | customer_city | products | product_prices 101 | Asha |...
Short answer: ACID describes the guarantees a database transaction gives: A tomicity, C onsistency, I solation and D urability. BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT;...
Short answer: both stack the results of two queries. UNION removes duplicate rows; UNION ALL keeps every row. Because removing duplicates needs a sort or hash step, UNION ALL is faster. -- Emails from two sources SELECT email FROM newsletter_subscribers UNION...
Short answer: DELETE removes selected rows, TRUNCATE quickly removes all rows but keeps the table, and DROP removes the table itself, including its structure. DELETE FROM sessions WHERE expires_at < NOW(); -- some rows TRUNCATE TABLE import_staging; -- all row...
Short answer: a primary key uniquely identifies each row and can't be NULL (one per table). A unique key also enforces uniqueness but a table can have many, and they may allow NULLs. A foreign key links a column to a key in another table and enforces that the...
Short answer: all three number rows in a given order. They differ only in how they handle ties. SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense F...
