SQL Programming Resources

🚀 SQL Roadmap 2026 — Part 18 SQL Indexes & Query Performance Writing a correct SQL query is important. But in real-world data analytics, especially when working with millions or billions of rows, another question matters: «How efficiently does the database find the data?» This is where SQL indexes become important. Indexes can dramatically improve data retrieval when used appropriately, but they also come with storage and write-performance costs. 1️⃣ What Is a SQL Index? An index is a database structure that helps the database find rows more efficiently. Think about a book. Without an index: Search for a topic ↓ Read page 1 ↓ Read page 2 ↓ Read page 3 ↓ ... ↓ Eventually find the topic With an index: Search topic ↓ Check index ↓ Find page ↓ Go directly to the relevant section A database index works on a similar principle. Instead of scanning every row, the database may use an index to locate relevant rows more efficiently. 2️⃣ Why Do We Need Indexes? Imagine a table containing: 10 million customers You run: SELECT * FROM customers WHERE customer_id = 100245; Without a suitable index, the database may need to inspect many rows. With an appropriate index: CREATE INDEX idx_customers_customer_id ON customers(customer_id); the database may be able to locate the requested row much more efficiently. The exact execution strategy is chosen by the database optimizer. 3️⃣ Creating an Index Basic syntax: CREATE INDEX index_name ON table_name(column_name); Example: CREATE INDEX idx_customers_email ON customers(email); Now the database has an index on: customers.email 4️⃣ Querying an Indexed Column You don't need to change your SQL query after creating the index. You still write: SELECT * FROM customers WHERE email = '[email protected]'; The database optimizer decides whether using the index is beneficial. Important: «Creating an index does not guarantee that the database will use it.» 5️⃣ Indexes and WHERE Conditions

SELECT region, COUNT(*) FROM orders GROUP BY region; An index on "region" may sometimes help, depending on the database and execution plan. But indexes don't automatically make every aggregation faster. For large analytical workloads, the database may choose another strategy. 9️⃣ Primary Keys and Indexes Primary keys are commonly backed by an index or equivalent structure. For example: CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100) ); The database generally creates an index-like structure to enforce primary-key uniqueness and support efficient lookups. The exact implementation varies by database system. 🔟 Unique Index A unique index prevents duplicate values in the indexed key. Example: CREATE UNIQUE INDEX idx_customers_email ON customers(email); Now duplicate email values are not allowed, subject to the database's NULL semantics. For example: • [email protected] • [email protected] can exist. But two identical non-NULL values generally cannot. A "UNIQUE" constraint is another way to enforce uniqueness and may be implemented using a unique index depending on the database. 1️⃣1️⃣ Single-Column Index An index can contain one column. CREATE INDEX idx_orders_customer ON orders(customer_id); This is called a single-column index. Useful when queries frequently search using: WHERE customer_id = ... 1️⃣2️⃣ Composite Index An index can also contain multiple columns. CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date); This is called a composite index or multi-column index. It can be useful for queries such as: SELECT * FROM orders WHERE customer_id = 101 AND order_date >= DATE '2026-01-01'; 1️⃣3️⃣ Column Order Matters This is one of the most important concepts with composite indexes. Suppose: CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date); The index starts with: customer_id and then: order_date This means the index is particularl

SELECT * FROM customers WHERE UPPER(customer_name) = 'RAHUL'; If you have a normal index on: customer_name the database may not be able to use that index efficiently because the query applies a function to the column. Depending on the database, an expression/function-based index may help: CREATE INDEX idx_customer_upper_name ON customers(UPPER(customer_name)); The exact syntax and availability depend on the database. 1️⃣7️⃣ Indexes and NULL Indexes can have database-specific behavior regarding NULL values. For example: SELECT * FROM customers WHERE email IS NULL; Whether and how an index can help depends on the database's index implementation. Don't assume every database handles NULL indexing identically. 1️⃣8️⃣ Why Not Create an Index on Every Column? This is a common beginner mistake. You might think: More indexes Faster database But that's not true. Indexes have costs. When data changes: • INSERT • UPDATE • DELETE the database may also need to maintain the relevant indexes. Therefore: More indexes ↓ More storage ↓ More maintenance ↓ Potentially slower writes Indexes should be created based on actual query patterns and workload requirements. 1️⃣9️⃣ Indexes Have a Storage Cost Suppose your table contains: 100 million rows and you create several large indexes. The indexes themselves can consume significant storage. So database design involves a trade-off: Read performance ↕ Write performance ↕ Storage A good indexing strategy balances all three. 20️⃣ Query Performance Indexes are only one part of SQL performance. Other factors include: • Query structure • JOIN strategy • Filtering • Data volume • Table design • Statistics • Partitioning • Database engine • Execution plan • Network transfer • Aggregations • Sorting • Data types A slow query isn't automatically an "index problem." 21️⃣ SELECT * and Performance Consider: SELECT * FROM orders WHERE customer_id = 101; If you only need

Don't retrieve every transaction and filter it later in Python or Excel if the database can efficiently perform the filtering. A good principle is: «Let the database do the data filtering and aggregation whenever practical.» 24️⃣ Execution Plans One of the most important tools for understanding SQL performance is the execution plan. An execution plan shows how the database intends to execute your query. It can reveal things such as: • Table Scan • Index Scan • Index Seek • Join Strategy • Sort • Aggregation • Estimated Rows • Actual Rows • Cost Different databases use different terminology. 25️⃣ EXPLAIN Many SQL databases support "EXPLAIN". For example: EXPLAIN SELECT * FROM orders WHERE customer_id = 101; This lets you inspect the planned execution strategy. Some databases support: EXPLAIN ANALYZE which can provide information about actual execution as well. Exact syntax and output vary by database. 26️⃣ Table Scan vs Index Access Imagine a table with: 10,000,000 rows A table scan may mean the database reads a large portion of the table to find matching records. Conceptually: 10 million rows ↓ Check rows ↓ Find matching rows An index-based access path may instead look more like: Index ↓ Locate matching keys ↓ Fetch relevant rows For highly selective queries, the second approach can be much more efficient. But if a query needs a large percentage of the table, scanning the table may actually be more efficient. This is why the optimizer chooses the execution strategy. 27️⃣ Selectivity Selectivity describes how effectively a condition narrows down the data. Consider: WHERE customer_id = 100245 If customer IDs are unique, this may return one row. Highly selective. Now consider: WHERE country = 'India' If 60% of the table contains Indian customers, the condition is much less selective. The database may decide that scanning the table is cheaper than using an index. Therefore: «An index isn't automatically useful

Why? Because the query filters on: customer_id • order_date The database can then evaluate the index as part of its execution strategy. But you should verify the impact using an execution plan and real workload data. 30️⃣ Query Optimization Checklist When you have a slow SQL query, ask: Step 1 Do I actually need all columns? SELECT * may be unnecessary. Step 2 Can I filter earlier? WHERE can reduce the amount of data processed. Step 3 Are JOIN conditions correct? Check: ON a.id = b.id Step 4 Could a suitable index help? Look at frequently used WHERE, JOIN, ORDER BY columns. Step 5 Is the index being used? Check the execution plan. Step 6 Am I processing unnecessary rows? Look at the data volume. Step 7 Are functions preventing efficient access? For example: WHERE UPPER(name) = ... Step 8 Am I creating too many indexes? Indexes also have costs. 💼 Data Analyst Example Imagine a dashboard queries: SELECT customer_id, SUM(order_amount) AS total_sales FROM orders WHERE order_date >= DATE '2026-01-01' GROUP BY customer_id; The table contains: 100 million orders Potential performance considerations include: 1. Is order_date indexed? 2. How selective is the date filter? 3. How many rows are processed? 4. Is partitioning available? 5. What does EXPLAIN show? 6. Is the aggregation expensive? 7. Is the dashboard requesting this data repeatedly? A strong analyst doesn't immediately say: «"Create an index."» Instead, they investigate the execution plan and workload first. 🎯 Interview Questions Q1. What is an index? An index is a database structure that can help locate rows more efficiently. Q2. Why are indexes useful? They can improve read performance for suitable queries, especially when searching or joining on indexed columns. Q3. Can indexes slow down INSERT operations? Yes. The database may need to maintain indexes when rows are inserted. Q4. Can indexes slow down UPDATE and DELETE? Yes, depending on which indexed c

🚀 SQL Roadmap 2026 — Part 19 SQL Transactions — COMMIT, ROLLBACK & SAVEPOINT SQL isn't only about retrieving data. In real-world systems, SQL is also used to: • Insert records • Update records • Delete records • Process financial transactions • Move money between accounts • Update multiple related tables • Maintain data consistency But what happens when a process involves multiple SQL statements and one of them fails? That's where SQL transactions become important. 1️⃣ What Is a Transaction? A transaction is a group of one or more SQL operations that are treated as a logical unit of work. For example, transferring money between two accounts may involve: • Account A → Deduct ₹1,000 • Account B → Add ₹1,000 These two operations should normally be treated as one transaction. You don't want this situation: • Account A → ₹1,000 deducted ✅ • Account B → ₹1,000 not credited ❌ The transaction mechanism helps maintain consistency. 2️⃣ Basic Transaction Flow BEGIN TRANSACTION ↓ SQL Statement 1 ↓ SQL Statement 2 ↓ SQL Statement 3 ↓ COMMIT If something goes wrong: BEGIN TRANSACTION ↓ SQL Statement 1 ↓ SQL Statement 2 ❌ ↓ ROLLBACK ↓ Changes undone 3️⃣ BEGIN TRANSACTION Depending on the database, you may see: BEGIN TRANSACTION; -- or BEGIN; Some database systems handle transaction boundaries differently, so exact syntax varies. For example: BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101; The transaction has started. 4️⃣ COMMIT "COMMIT" permanently saves the changes made during the transaction. Example: BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202; COMMIT; After the transaction is successfully committed, the changes become durable according to the database's transaction rules. 5️⃣ ROLLBACK "ROLLBACK" reverses uncommitted changes within the tr

The transaction can continue from there. Conceptually: Transaction starts ↓ Update 101 ↓ SAVEPOINT ↓ Update 102 ↓ ROLLBACK TO SAVEPOINT ↓ Update 101 remains, Update 102 is undone Exact savepoint syntax varies by database. 8️⃣ Why Are Transactions Important? Imagine an order-processing system. Creating an order might require: 1. Create order 2. Create order items 3. Reduce inventory 4. Record payment 5. Update customer balance If step 4 fails after steps 1–3 succeed, you could end up with inconsistent data. A transaction can group these operations together. BEGIN ↓ Create order ↓ Create order items ↓ Reduce inventory ↓ Record payment ↓ Update balance ↓ COMMIT If a critical operation fails: ROLLBACK This helps keep the system consistent. 9️⃣ The ACID Properties Transactions are commonly explained using the ACID properties: • A → Atomicity • C → Consistency • I → Isolation • D → Durability These are fundamental database concepts. 🔟 Atomicity Atomicity means a transaction is treated as a logical unit. Either the required transaction changes are committed, or the transaction can be rolled back. Example: • Transfer ₹1,000 • Debit account A + Credit account B You don't want only one side of the transfer to succeed. Conceptually: • Both succeed → COMMIT • Critical failure → ROLLBACK 1️⃣1️⃣ Consistency Consistency means a successful transaction should leave the database in a state that satisfies its defined rules and constraints. For example: • Account balance must not violate business/database constraints Suppose a database has: CHECK (balance >= 0) An operation that violates the constraint may fail rather than leaving the database in an invalid state. 1️⃣2️⃣ Isolation Isolation deals with how concurrent transactions interact with each other. Imagine Transaction A + Transaction B both accessing the same data at the same time. The database needs rules governing what each transaction can see while the other is running. This becomes

ROLLBACK; If everything is correct: COMMIT; This can be useful when performing potentially dangerous data modifications. 1️⃣8️⃣ A Safe Pattern for Data Changes Before executing a large UPDATE or DELETE, analysts often first run a SELECT using the same condition. Instead of immediately doing: UPDATE customers SET status = 'Inactive' WHERE last_order_date < DATE '2024-01-01'; first check: SELECT * FROM customers WHERE last_order_date < DATE '2024-01-01'; Then, where transaction support and operational rules permit: BEGIN; UPDATE customers SET status = 'Inactive' WHERE last_order_date < DATE '2024-01-01'; -- Verify the affected rows COMMIT; If something looks wrong: ROLLBACK; This is a valuable habit when working with production data. 1️⃣9️⃣ Transactions and Autocommit Many database clients use an autocommit mode. When autocommit is enabled, individual statements may be committed automatically. For example: UPDATE customers SET status = 'Active' WHERE customer_id = 101; may be committed immediately. That means you may not be able to simply run: ROLLBACK; after the statement has already been committed. The exact behavior depends on: • Database system • Client/tool • Connection settings • Transaction configuration Always understand the transaction mode before modifying production data. 20️⃣ Transactions and DDL Statements such as: • CREATE • ALTER • DROP are DDL statements. Their transaction behavior varies significantly across database systems. Some databases implicitly commit certain DDL operations. Therefore, don't assume: BEGIN; DROP TABLE test_table; ROLLBACK;

will behave identically across every database. Always check the behavior of your specific database. 21️⃣ Transaction Isolation Levels Isolation is one of the deeper transaction concepts. Common isolation levels include: • READ UNCOMMITTED • READ COMMITTED • REPEATABLE READ • SERIALIZABLE Some databases also support additional modes or implement these differently. The general idea is: • More isolation ↓ Stronger guarantees between concurrent transactions ↓ Potentially more locking/contention or reduced concurrency The exact behavior is database-specific. 22️⃣ READ UNCOMMITTED This is the weakest commonly described isolation level. A transaction may potentially see changes that another transaction has not committed. This can lead to phenomena such as: • Dirty reads It is not appropriate for every workload. 23️⃣ READ COMMITTED A transaction generally sees committed data rather than another transaction's uncommitted changes. This is a common default isolation level in some database systems. However, behavior across databases can differ. 24️⃣ REPEATABLE READ The goal is to ensure that repeated reads within a transaction provide a stable view of previously read data under the database's isolation model. It provides stronger guarantees than READ COMMITTED. The exact implementation differs between database systems. 25️⃣ SERIALIZABLE This provides the strongest standard isolation level among these four. The goal is to make concurrent transactions behave as if they were executed serially. Conceptually: Transaction A ↓ Transaction B rather than allowing certain conflicting operations to interact concurrently. The trade-off can be reduced concurrency or increased contention. 26️⃣ Common Transaction Problems When transactions run concurrently, several phenomena can occur depending on the isolation level and database. Dirty Read • Transaction A reads data changed by Transaction B before B commits. • B → UPDATE ↓ A → reads uncommitted value ↓ B

The exact workflow should follow your organization's production-change and approval procedures. 29️⃣ COMMIT vs SAVEPOINT vs ROLLBACK Remember: • COMMIT → Save the transaction • ROLLBACK → Undo uncommitted transaction changes • SAVEPOINT → Mark a point inside a transaction • ROLLBACK TO SAVEPOINT → Undo changes after that point Example: BEGIN; UPDATE customers SET status = 'Active' WHERE customer_id = 101; SAVEPOINT s1; UPDATE customers SET status = 'Inactive' WHERE customer_id = 102; ROLLBACK TO SAVEPOINT s1; COMMIT; The first update can remain while the second update is rolled back, subject to the database's transaction semantics. 30️⃣ Common Mistakes ❌ Mistake 1: Forgetting COMMIT — You may make changes but not persist them as intended. ❌ Mistake 2: Assuming ROLLBACK always works — If the changes have already been committed, a normal rollback cannot undo them. ❌ Mistake 3: Running UPDATE without checking the WHERE condition — Dangerous: UPDATE customers SET status = 'Inactive'; Safer workflow: SELECT * FROM customers WHERE ...; UPDATE customers SET status = 'Inactive' WHERE ...; ❌ Mistake 4: Assuming transaction behavior is identical everywhere — Database systems differ in areas such as: - Autocommit - DDL transactions - Isolation - Locking - Savepoints - Error handling 🎯 Interview Questions • Q1. What is a transaction? A transaction is a logical unit of one or more database operations that are handled together. • Q2. What does COMMIT do? It commits the transaction's changes. • Q3. What does ROLLBACK do? It reverses uncommitted changes in the transaction. • Q4. What is SAVEPOINT? A savepoint marks a point inside a transaction to which you can potentially roll back without undoing the entire transaction. • Q5. What does ACID stand for? A → Atomicity, C → Consistency, I → Isolation, D → Durability • Q6. What is atomicity? It treats the transaction as a logical unit so that its operations