SQL Programming Resources

SQL Interview Series — Part 2

📌 Question 2: Find Duplicate Records

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• department

• email

❓ Find all email addresses that appear more than once in the Employee table.

Example:

employee_id | employee_name | email
------------|---------------|-------------------
1           | Amit          | amit@gmail.com
2           | Rahul         | rahul@gmail.com
3           | Priya         | amit@gmail.com
4           | Neha          | neha@gmail.com
5           | Raj           | rahul@gmail.com

Expected result:

email

amit@gmail.com
rahul@gmail.com

💡 Approach

We need to:

1️⃣ Group records by email.

2️⃣ Count how many times each email appears.

3️⃣ Keep only the emails whose count is greater than 1. 

📌 SQL Solution

SELECT email, COUNT(*) AS occurrence_count
FROM Employee
GROUP BY email
HAVING COUNT(*) > 1;

🔎 Why use HAVING instead of WHERE?

"WHERE" filters individual rows before grouping.

"HAVING" filters groups after "GROUP BY".

Since we want to filter based on "COUNT(*)", we use "HAVING".

GROUP BY email
HAVING COUNT(*) > 1

This means:

"Group employees by email and return only those groups containing more than one record."

🎯 Double Tap ❤️ For Part-3

𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗧𝗼 𝗟𝗲𝗮𝗿𝗻 𝗔𝗜 𝗶𝗻 𝟮𝟬𝟮𝟲🚀
​
Explore 6 free resources covering AI fundamentals, tools, deep learning, research and real-world applications.

✅ 100% Free Learning
✅ Beginner-Friendly
✅ AI • ML • Deep Learning
✅ Real-World Applications

🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 👇

https://pdlink.in/4AFHq5R

📢 Share this valuable opportunity with your friends and classmates!

SQL Interview Series — Part 1

Hi guys, let's start a SQL interview series covering frequently asked and important SQL interview questions.

Each part will cover 1 practical interview question with:

✅ Problem statement

✅ SQL solution

✅ Approach

✅ Interview tip

📌 Question 1: Find the Second Highest Salary

Suppose you have an Employee table:

Employee

• employee_id

• employee_name

• salary

Example data:

employee_id| employee_name| salary

1| Amit| 50000

2| Rahul| 80000

3| Priya| 70000

4| Neha| 90000

5| Raj| 80000

❓ Find the second-highest salary from the Employee table.

💡 Approach

First, we need to identify the highest salary.

Then, we need the highest salary that is less than the maximum salary.

One simple approach is to use a subquery:

SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (
    SELECT MAX(salary)
    FROM Employee
);

Output:

second_highest_salary

80000

🔎 Why does this work?

The inner query:

SELECT MAX(salary)
FROM Employee;

returns:

90000

Then the outer query considers only salaries below 90000:

50000

70000

80000

80000

Finally, MAX() returns:

80000

⚠️ Important Interview Point

If the question asks for the second-highest DISTINCT salary, this approach works because duplicate salaries are naturally treated as one value.

For example:

90000

80000

80000

70000

The second-highest distinct salary is still 80000.

💯 Double Tap ❤️ For Part-2

🧠 Real-World SQL Scenario-Based Questions & Answers

1. Get the 2nd highest salary from the Employees table

SELECT MAX(salary) AS SecondHighest  
FROM Employees  
WHERE salary < (SELECT MAX(salary) FROM Employees);

2. Find employees without assigned managers

SELECT * FROM Employees  
WHERE manager_id IS NULL;

3. Retrieve departments with more than 5 employees

SELECT department_id, COUNT(*) AS employee_count  
FROM Employees  
GROUP BY department_id  
HAVING COUNT(*) > 5;

4. List customers who made no orders

SELECT c.name  
FROM Customers c  
LEFT JOIN Orders o ON c.id = o.customer_id  
WHERE o.id IS NULL;

5. Find the top 3 highest-paid employees

SELECT * FROM Employees  
ORDER BY salary DESC  
LIMIT 3;

6. Display total sales for each product

SELECT product, SUM(amount) AS total_sales  
FROM Sales  
GROUP BY product;

7. Get employee names starting with 'A' and ending with 'n'

SELECT name FROM Employees  
WHERE name LIKE 'A%n';

8. Show employees who joined in the last 30 days

SELECT * FROM Employees  
WHERE join_date >= CURRENT_DATE - INTERVAL 30 DAY;

💬 Tap ❤️ for more!

🎓 𝗛𝗔𝗥𝗩𝗔𝗥𝗗 𝗨𝗡𝗜𝗩𝗘𝗥𝗦𝗜𝗧𝗬 𝗙𝗥𝗘𝗘 𝗢𝗡𝗟𝗜𝗡𝗘 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 😍

Dreaming of learning from one of the world’s most prestigious universities? Explore Harvard’s online courses and build valuable, career-ready skills from home!

💡 Beginner-friendly options
⏰ Learn at your own pace
🌍 Accessible online worldwide
🎯 Ideal for students, freshers and working professionals

🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 👇

https://pdlink.in/4xPUdzU

📢 Share this valuable opportunity with your friends and classmates!

Data Analytics Roadmap
|
|-- Fundamentals
|   |-- Mathematics
|   |   |-- Descriptive Statistics
|   |   |-- Inferential Statistics
|   |   |-- Probability Theory
|   |
|   |-- Programming
|   |   |-- Python (Focus on Libraries like Pandas, NumPy)
|   |   |-- R (For Statistical Analysis)
|   |   |-- SQL (For Data Extraction)
|
|-- Data Collection and Storage
|   |-- Data Sources
|   |   |-- APIs
|   |   |-- Web Scraping
|   |   |-- Databases
|   |
|   |-- Data Storage
|   |   |-- Relational Databases (MySQL, PostgreSQL)
|   |   |-- NoSQL Databases (MongoDB, Cassandra)
|   |   |-- Data Lakes and Warehousing (Snowflake, Redshift)
|
|-- Data Cleaning and Preparation
|   |-- Handling Missing Data
|   |-- Data Transformation
|   |-- Data Normalization and Standardization
|   |-- Outlier Detection
|
|-- Exploratory Data Analysis (EDA)
|   |-- Data Visualization Tools
|   |   |-- Matplotlib
|   |   |-- Seaborn
|   |   |-- ggplot2
|   |
|   |-- Identifying Trends and Patterns
|   |-- Correlation Analysis
|
|-- Advanced Analytics
|   |-- Predictive Analytics (Regression, Forecasting)
|   |-- Prescriptive Analytics (Optimization Models)
|   |-- Segmentation (Clustering Techniques)
|   |-- Sentiment Analysis (Text Data)
|
|-- Data Visualization and Reporting
|   |-- Visualization Tools
|   |   |-- Power BI
|   |   |-- Tableau
|   |   |-- Google Data Studio
|   |
|   |-- Dashboard Design
|   |-- Interactive Visualizations
|   |-- Storytelling with Data
|
|-- Business Intelligence (BI)
|   |-- KPI Design and Implementation
|   |-- Decision-Making Frameworks
|   |-- Industry-Specific Use Cases (Finance, Marketing, HR)
|
|-- Big Data Analytics
|   |-- Tools and Frameworks
|   |   |-- Hadoop
|   |   |-- Apache Spark
|   |
|   |-- Real-Time Data Processing
|   |-- Stream Analytics (Kafka, Flink)
|
|-- Domain Knowledge
|   |-- Industry Applications
|   |   |-- E-commerce
|   |   |-- Healthcare
|   |   |-- Supply Chain
|
|-- Ethical Data Usage
|   |-- Data Privacy Regulations (GDPR, CCPA)
|   |-- Bias Mitigation in Analysis
|   |-- Transparency in Reporting

Free Resources to learn Data Analytics skills👇👇

1. SQL

https://mode.com/sql-tutorial/introduction-to-sql

https://t.me/sqlspecialist/738

2. Python

https://www.learnpython.org/

https://t.me/pythondevelopersindia/873

https://bit.ly/3T7y4ta

https://www.geeksforgeeks.org/python-programming-language/learn-python-tutorial

3. R

https://datacamp.pxf.io/vPyB4L

4. Data Structures

https://leetcode.com/study-plan/data-structure/

https://www.udacity.com/course/data-structures-and-algorithms-in-python--ud513

5. Data Visualization

https://www.freecodecamp.org/learn/data-visualization/

https://t.me/Data_Visual/2

https://www.tableau.com/learn/training/20223

https://www.workout-wednesday.com/power-bi-challenges/

6. Excel

https://excel-practice-online.com/

https://t.me/excel_data

https://www.w3schools.com/EXCEL/index.php

Join @free4unow_backup for more free courses

Like for more ❤️

ENJOY LEARNING 👍👍

𝗟𝗲𝘃𝗲𝗹 𝗨𝗽 𝗬𝗼𝘂𝗿 𝗦𝗸𝗶𝗹𝗹𝘀 𝘄𝗶𝘁𝗵 𝗧𝗵𝗲𝘀𝗲 𝗚𝗮𝗺𝗲-𝗖𝗵𝗮𝗻𝗴𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀!
​
Looking to learn practical, in-demand skills? These courses cover Generative AI, Cybersecurity, AI tools and Digital Marketing.

💫 Learn at your own pace
⚡Build career-relevant skills
🔥Practical learning opportunities

𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝘁𝗵𝗲 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 :-

https://pdlink.in/4z3vOYU

Save this post and share with your friends

Answer: Both uncommitted updates are rolled back, assuming the statements executed within the same transaction and the database/session supports the shown transaction behavior.

🔥 Mini Challenge

Imagine an order-processing system. You need to:

1. Create an order.

2. Add an order item.

3. Reduce inventory.

4. Commit everything if successful.

5. Roll back if a critical operation fails.

Write a transaction structure for this workflow.

Solution:

BEGIN;

INSERT INTO orders (order_id, customer_id, order_amount)
VALUES (1001, 101, 5000);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 501, 2);

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 501
  AND stock_quantity >= 2;

COMMIT;

In a real application, you would also validate that each critical operation succeeded and handle errors according to the database/application's transaction mechanism.

If a critical operation fails:

ROLLBACK;

🎯 Key Takeaway

Remember:

• TRANSACTION → Group related operations into one unit

• COMMIT → Save changes

• ROLLBACK → Undo uncommitted changes

• SAVEPOINT → Create a rollback point

• ACID → Atomicity → Consistency → Isolation → Durability

The most important practical lesson:

«Before making large UPDATE or DELETE changes, first run the corresponding SELECT and verify exactly which rows will be affected.»

Transactions help protect data, but safe SQL also depends on careful query design, validation, permissions, and understanding your database's transaction behavior.

Double Tap ❤️ For More

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 are committed together or rolled back as appropriate.

•

Q7. What is isolation? It controls how concurrent transactions interact and what changes they can see.

•

Q8. What is durability? Committed changes are intended to survive failures according to the database's durability mechanisms.

•

Q9. What is autocommit? A mode where individual statements may be committed automatically.

•

Q10. What is a dirty read? Reading data changed by another transaction before that transaction commits.

🧠 Practice Questions

Practice 1 Write a transaction that updates a customer's status and commits it.

BEGIN;
UPDATE customers SET status = 'Active' WHERE customer_id = 101;
COMMIT;

Practice 2 Update a customer's status but roll back the change.

BEGIN;
UPDATE customers SET status = 'Inactive' WHERE customer_id = 101;
ROLLBACK;

Practice 3 Create a savepoint after the first update.

BEGIN;
UPDATE customers SET status = 'Active' WHERE customer_id = 101;
SAVEPOINT customer_update;
UPDATE customers SET status = 'Inactive' WHERE customer_id = 102;
ROLLBACK TO SAVEPOINT customer_update;
COMMIT;

Practice 4 Before executing this UPDATE:

UPDATE orders SET status = 'Cancelled' WHERE order_date < DATE '2025-01-01';

write a SELECT that lets you inspect the affected records.

SELECT * FROM orders WHERE order_date < DATE '2025-01-01';

Practice 5 Explain what happens here:

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;
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 → ROLLBACK

• A saw a value that never became permanent.

Non-Repeatable Read

• A transaction reads the same row twice and gets different values because another transaction committed a change between the reads.

• A → Read = 100

• B → Update = 200, B → COMMIT

• A → Read again = 200

Phantom Read

• A transaction repeats a query and sees additional or missing rows because another transaction inserted or deleted matching rows.

• A → SELECT ... WHERE amount > 1000 → 5 rows

• B → INSERT another matching row, B → COMMIT

• A → same query → 6 rows

The exact handling depends on the database and isolation level.

27️⃣ Transaction vs Query

Don't confuse these concepts.

A query is a SQL statement such as:

SELECT * FROM customers;

A transaction is a logical unit containing one or more operations.

For example:

Transaction

│

├── INSERT

├── UPDATE

├── UPDATE

└── COMMIT

So:

• Query → Individual SQL operation

• Transaction → Unit of work containing one or more operations

28️⃣ Real-World Analytics Example

Suppose an operations team needs to correct payment statuses.

There are 10,000 affected records.

Instead of blindly running:

UPDATE payments
SET status = 'Completed'
WHERE payment_date IS NOT NULL;

first inspect:

SELECT payment_id, status, payment_date
FROM payments
WHERE payment_date IS NOT NULL;

Then, if the correction is confirmed:

BEGIN;

UPDATE payments
SET status = 'Completed'
WHERE payment_date IS NOT NULL;

SELECT COUNT(*) AS updated_rows
FROM payments
WHERE payment_date IS NOT NULL
  AND status = 'Completed';

COMMIT;

If the validation reveals an unexpected result:

ROLLBACK;

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;

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 especially important in:

• Banking

• Payments

• Order processing

• Inventory systems

• Financial systems

• High-volume applications

1️⃣3️⃣ Durability

Once a transaction has successfully committed, its changes are intended to survive subsequent failures according to the database's durability guarantees.

Conceptually:

COMMIT ↓ Data saved ↓ System failure ↓ Committed changes remain

Durability is supported by database mechanisms such as transaction logs and recovery systems.

1️⃣4️⃣ Transaction Example — Bank Transfer

Suppose:

• Account 101 → ₹50,000

• Account 202 → ₹30,000

• Transfer: ₹5,000

SQL:

BEGIN;

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 202;

COMMIT;

After success:

• Account 101 → ₹45,000

• Account 202 → ₹35,000

• The total money remains: ₹80,000

1️⃣5️⃣ What If Something Fails?

Suppose the second update fails.

Without appropriate transaction handling:

• Account 101 ₹50,000 → ₹45,000

• Account 202 Still ₹30,000

The system has become inconsistent.

With a transaction:

BEGIN ↓ Debit Account 101 ↓ Credit Account 202 ❌ ↓ ROLLBACK

The debit can be rolled back along with the other uncommitted changes.

1️⃣6️⃣ Transactions with INSERT

Transactions aren't limited to UPDATE.

Example:

BEGIN;

INSERT INTO orders (order_id, customer_id, order_amount)
VALUES (1001, 101, 5000);

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 501, 2);

COMMIT;

Both operations belong to the transaction.

1️⃣7️⃣ Transactions with DELETE

You can also use transactions before deleting records.

For example:

BEGIN;

DELETE FROM orders
WHERE order_date < DATE '2020-01-01';

Before committing, you can inspect the result.

If it isn't what you expected:

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

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 202;

ROLLBACK;

The changes made in that transaction are undone.

6️⃣ COMMIT vs ROLLBACK

This is a common interview question.

• COMMIT → Save the transaction

• ROLLBACK → Undo uncommitted changes

Example:

BEGIN;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 101;
COMMIT;

The change is committed.

Whereas:

BEGIN;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 101;
ROLLBACK;

The uncommitted update is rolled back.

7️⃣ SAVEPOINT

Sometimes you don't want to roll back the entire transaction.

You can create a "SAVEPOINT".

Example:

BEGIN;

UPDATE customers
SET status = 'Active'
WHERE customer_id = 101;

SAVEPOINT customer_update;

UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 102;

Now suppose you don't want the second update.

You can roll back to the savepoint:

ROLLBACK TO SAVEPOINT customer_update;

🚀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗣𝗿𝗼𝗳𝗲𝘀𝘀𝗶𝗼𝗻𝗮𝗹 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗲𝘀 𝗶𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 & 𝗔𝗜! 📊

Explore these 4 Google learning programs and develop practical, career-relevant skills.

🎓 Explore the programs:
1️⃣ Google Data Analytics Professional Certificate
2️⃣ Google Business Intelligence Professional Certificate
3️⃣ Google AI Essentials
4️⃣ Google Advanced Data Analytics Professional Certificate

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4htgIEW

📌 Save this post and share it with someone interested in Data Analytics or AI!

SQL Interview Questions with Answers

1. What is a primary key and why is it important in a database?
   - A primary key is a unique identifier for each record in a database table. It is important because it ensures that each record can be uniquely identified and helps maintain data integrity by preventing duplicate or null values.

2. Can you explain the difference between INNER JOIN and OUTER JOIN in SQL?
   - INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns all rows from one table and the matched rows from the other table (or null values if there is no match).

3. How do you optimize a SQL query for better performance?
   - To optimize a SQL query, you can use indexes, avoid using SELECT *, limit the number of columns selected, use appropriate data types, and avoid using functions in WHERE clauses.

4. What is normalization and why is it important in database design?
   - Normalization is the process of organizing data in a database to reduce redundancy and dependency. It is important because it helps improve data integrity, reduce storage space, and make data maintenance easier.

5. How do you handle missing data in SQL queries?
   - You can handle missing data in SQL queries by using functions like COALESCE or IFNULL to replace null values with a default value, or by using the IS NULL or IS NOT NULL operators to filter out records with missing data.

6. Can you explain the difference between GROUP BY and HAVING clauses in SQL?
   - GROUP BY is used to group rows that have the same values into summary rows, while HAVING is used to filter groups based on specified conditions after the GROUP BY clause has been applied.

7. How do you identify and remove duplicate records from a database table?
   - You can identify duplicate records by using the DISTINCT keyword or by using the GROUP BY clause with COUNT() function. To remove duplicate records, you can use the DELETE statement with a subquery that identifies the duplicates.

8. How do you write a subquery in SQL?
   - A subquery is a query nested within another query. You can write a subquery by enclosing the inner query within parentheses and using it as a part of the outer query's WHERE, FROM, or SELECT clause.

9. What is the difference between a view and a table in SQL?
   - A table stores actual data in a database, while a view is a virtual table that displays data from one or more tables based on a predefined query. Views do not store data themselves but provide a way to present data in a specific format.

10. How do you use indexes to improve query performance in SQL?
    - Indexes are used to speed up data retrieval in SQL queries by creating an ordered list of values for one or more columns in a table. You can create indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses to improve query performance.

Hope it helps :)

🚀 𝐁𝐞𝐜𝐨𝐦𝐞 𝐚𝐧 𝐀𝐈 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫 𝐢𝐧 𝟐𝟎𝟐𝟔

🎯 Choose Your Learning Track:

💻 Java Full Stack + AI Engineering
🌐 MERN Full Stack + AI Engineering

Placement Highlights: ₹41 LPA highest package | ₹7.4 LPA average package | 2,000+ students placed | 500+ hiring partners

🔗 𝗕𝗼𝗼𝗸 𝗙𝗥𝗘𝗘 𝗗𝗲𝗺𝗼 𝗖𝗹𝗮𝘀𝘀 :- https://pdlink.in/4fWJVID

⚡ AI is creating new career opportunities—start building the skills companies need in 2026!

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 101;

If supported by your database:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 101;

🔥 Mini Challenge

You have an "orders" table containing 50 million

rows:

• order_id

• customer_id

• order_date

• status

• region

• order_amount

The following query is running slowly:

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 5001
  AND order_date >= DATE '2026-01-01'
  AND status = 'Completed';

Think about:

1. Which columns are being filtered?

2. Would a composite index be worth investigating?

3. Which column should come first?

4. Should you use "SELECT *"?

5. How would you inspect the execution plan?

6. Would the index always be used?

7. What happens to performance when the table receives millions of new rows?

Possible candidate to investigate:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Then inspect the query:

EXPLAIN
SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 5001
  AND order_date >= DATE '2026-01-01'
  AND status = 'Completed';

Don't automatically assume this is the optimal index.

Use the execution plan, data distribution, workload, and database-specific behavior to determine whether it actually helps.

🎯 Key Takeaway

Remember:

INDEX → Helps the database find data efficiently

COMPOSITE INDEX → Index on multiple columns

EXPLAIN → Inspect how the database plans to execute a query

SELECTIVITY → How much a filter narrows the data

And the most important principle:

More indexes ≠ Always faster

Good indexing + Good query design + Execution-plan analysis = Better SQL performance

A strong Data Analyst doesn't just write SQL that produces the correct answer.

They also understand how that SQL behaves when the data grows from thousands of rows to millions or billions. 🚀

🎯 Double Tap ❤️ For More

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 columns are affected and the database implementation.

Q5. What is a composite index?

An index containing multiple columns.

CREATE INDEX idx_customer_date
ON orders(customer_id, order_date);

Q6. Does the order of columns in a composite index matter?

Yes. The leading columns strongly influence which queries can efficiently use the index.

Q7. Does every query use an index if one exists?

No. The optimizer decides whether using an index is beneficial.

Q8. What is EXPLAIN?

It is a command or feature used to inspect a query's execution plan, with syntax varying by database.

Q9. What is selectivity?

It describes how effectively a condition narrows the number of matching rows.

Q10. Why shouldn't you index every column?

Indexes consume storage and require maintenance during data modifications, so excessive indexing can hurt write performance.

🧠 Practice Questions

Practice 1

Create an index on "customer_id":

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Practice 2

Create a composite index using customer and date:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Practice 3

Create a unique index on email:

CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

Practice 4

Write a query that could potentially benefit from an index on "customer_id":

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 101;

Practice 5

Inspect the execution plan:

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 simply because the column appears in WHERE.»

28️⃣ Indexes on Low-Cardinality Columns

Suppose:

status

contains only:

• Active

• Inactive

That's a low-cardinality column.

An index may not always provide a large benefit if most rows match the condition.

For example:

WHERE status = 'Active'

If 95% of rows are Active, reading the index and then retrieving almost the entire table may be less efficient than scanning the table.

Again, the optimizer makes the decision.

29️⃣ Real-World Example

Suppose an orders table contains:

50 million rows

Analysts frequently run:

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 12345
  AND order_date >= DATE '2026-01-01';

A possible candidate is:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

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:

• order_id

• order_date

• order_amount

prefer:

SELECT
    order_id,
    order_date,
    order_amount
FROM orders
WHERE customer_id = 101;

Why?

Because retrieving unnecessary columns can:

• Increase data transfer

• Increase memory usage

• Increase I/O

• Make downstream processing heavier

It also makes your SQL less explicit.

22️⃣ Filter Early

Suppose you need sales for 2026:

SELECT
    customer_id,
    SUM(order_amount) AS total_sales
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id;

Filtering before aggregation can significantly reduce the amount of data that needs to be processed.

Conceptually:

10 million rows

↓

Filter

↓

2 million rows

↓

Aggregate

instead of:

10 million rows

↓

Aggregate everything

↓

Filter later

The optimizer may transform queries internally, but writing clear predicates is still important.

23️⃣ Avoid Unnecessary Data Processing

Suppose you need only completed transactions:

SELECT
    transaction_id,
    amount
FROM transactions
WHERE status = 'Completed';