DEV Community: Kailas Warade

5 Common SQL Mistakes: WHERE, ORDER BY, AND/OR, and Top 3 Queries

Kailas Warade — Thu, 29 Oct 2026 03:31:06 +0000

A SQL query can execute without errors and still produce an unexpected result.

It might return the wrong employees, sort salaries incorrectly, or select the wrong three records.

The issue often lies in how you use "WHERE", "ORDER BY", logical operators, and row-limiting clauses.

Let's explore five practical SQL examples using a simple employee table.

Sample Data

Assume we have the following "employees" table:

ID| Name| Dept| Salary
101| Amit| IT| 60000
102| Priya| HR| 45000
103| Rahul| IT| 75000
104| Neha| Fin| 55000
105| Sneha| IT| 60000

Filter Rows Using WHERE

Question: Find employees earning more than ₹50,000.

SELECT emp_id, name, salary
FROM employees
WHERE salary > 50000;

This query returns Amit, Rahul, Neha, and Sneha.

Common mistake: Using ">=" instead of ">" changes the result when an employee earns exactly ₹50,000.

">" means greater than.
">=" means greater than or equal to.

Always choose the comparison operator that matches the business requirement.

Sort Salaries Correctly

Question: Display employees from the highest salary to the lowest.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Remember:

"DESC" sorts from highest to lowest.
"ASC" sorts from lowest to highest.

Interview tip: Without "ORDER BY", SQL does not guarantee the order of returned rows.

Combine Filtering and Sorting

Question: Find IT employees earning more than ₹50,000 and display the highest salary first.

SELECT name, salary
FROM employees
WHERE dept = 'IT'
AND salary > 50000
ORDER BY salary DESC;

The query first identifies employees who satisfy both conditions and then sorts the qualifying rows by salary.

Expected result:

Name| Salary
Rahul| 75000
Amit| 60000
Sneha| 60000

Notice that Amit and Sneha have the same salary. Their relative order is not guaranteed because the query does not specify a secondary sort key.

This pattern is useful in employee reports, sales analysis, and dashboards.

Avoid AND/OR Logic Errors

Question: Find employees who work in IT or earn more than ₹60,000, provided their salary is at least ₹50,000.

SELECT name, dept, salary
FROM employees
WHERE (dept = 'IT' OR salary > 60000)
AND salary >= 50000;

The parentheses make the intended logic explicit.

In SQL, "AND" has higher precedence than "OR". Without parentheses, a condition combining both operators can return unintended results.

Interview tip: When a requirement contains both "AND" and "OR", group the conditions carefully before writing the query.

Find the Top 3 Highest-Paid Employees

Question: Return the three highest-paid employees.

SELECT emp_id, name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
LIMIT 3;

The query sorts by salary in descending order and uses "emp_id" as a secondary sort key.

Because Amit and Sneha both earn ₹60,000, the secondary sort makes their relative order predictable.

Important: "LIMIT" works in MySQL and PostgreSQL. Other database systems may use different row-limiting syntax.

Also, "LIMIT 3" returns at most three rows. If you need to include everyone tied at the third-highest salary, consider using a ranking function such as "RANK()".

One Small Change Can Affect Your Report

Compare these conditions:

WHERE salary > 50000

WHERE salary >= 50000

The difference is just one character, but the second condition also includes employees earning exactly ₹50,000.

In real reporting, small differences like this can affect totals, eligibility checks, and business decisions.

Continue Learning SQL

Want to practise more questions about filtering, sorting, and SQL interview preparation?

Explore the "SQL FAQ tutorial on restricting and sorting data" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/restricting-sorting-data-sql-faq.html) from Sankalan Data Tech for more topic-focused examples.

Final SQL Challenge

Suppose the "employees" table contains thousands of records.

How would you find the second-highest distinct salary without using "LIMIT" or "TOP"?

Try solving it with a subquery before looking up the answer. This is a useful exercise for SQL interviews.

Share your approach in the comments!

SQL SELECT Statement: Practical Examples for Interview Preparation

Kailas Warade — Wed, 28 Oct 2026 03:19:20 +0000

The "SELECT" statement is one of the first things you learn when working with SQL. But knowing the syntax is only the beginning. In real projects, you need to retrieve the right columns, filter records, remove duplicates, sort results, and calculate useful information from tables.

Let's understand how the SQL "SELECT" statement works through practical examples using an employee table.

Understanding the SELECT Statement

Suppose you have an "employees" table containing employee details.

Table: employees

id	name	dept	salary
101	Amit	IT	60000
102	Priya	HR	45000
103	Rahul	IT	75000
104	Neha	Finance	55000
105	Sneha	IT	65000

Imagine your manager asks you to prepare a list of employees working in the IT department, including their names and salaries.

You don't need every column in the table. You only need the information requested.

SQL Solution

SELECT name, salary
FROM employees
WHERE dept = 'IT';

Expected Output

name	salary
Amit	60000
Rahul	75000
Sneha	65000

The query retrieves only the required columns and returns employees whose department is IT.

Let's look at a few more situations you may encounter while working with SQL.

Retrieve All Columns from a Table

Sometimes you need to see all available columns.

SELECT *
FROM employees;

The asterisk ("*") retrieves all columns from the table.

It is useful when exploring a dataset, but in production queries, explicitly listing the required columns often makes the code easier to maintain and avoids retrieving unnecessary data.

Filter Records Using WHERE

Suppose you want to find employees earning more than ₹60,000.

SELECT name, salary
FROM employees
WHERE salary > 60000;

Expected Output

name	salary
Rahul	75000
Sneha	65000

The "WHERE" clause filters rows based on a condition. You can also combine conditions using "AND" and "OR".

For example, to find IT employees earning more than ₹60,000:

SELECT name, salary
FROM employees
WHERE dept = 'IT'
AND salary > 60000;

This returns Rahul and Sneha.

Find Unique Values Using DISTINCT

Suppose you want to identify all departments represented in the employee table.

SELECT DISTINCT dept
FROM employees;

Expected Output

dept

IT
HR
Finance

Without "DISTINCT", the IT department would appear multiple times because several employees belong to it.

Remember that "DISTINCT" applies to the combination of all selected columns, not just the first column.

Sort Records Using ORDER BY

A salary report is often easier to understand when employees are listed from highest to lowest salary.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Expected Output

name	salary
Rahul	75000
Sneha	65000
Amit	60000
Neha	55000
Priya	45000

Use "DESC" for descending order and "ASC" for ascending order.

If two employees have the same salary, you can add another sorting column to make the result order more predictable.

Calculate Department-Wise Employee Counts

The "SELECT" statement also works with aggregate functions.

Suppose the HR team wants to know how many employees work in each department.

SELECT dept, COUNT(*) AS employee_count
FROM employees
GROUP BY dept;

Expected Output

dept	employee_count
Finance	1
HR	1
IT	3

Here, "COUNT(*)" counts rows in each group, while "GROUP BY" groups employees by department.

This type of query is useful for reports, dashboards, and workforce analysis.

Filter Groups Using HAVING

Now suppose you only want departments with more than one employee.

SELECT dept, COUNT() AS employee_count
FROM employees
GROUP BY dept
HAVING COUNT() > 1;

Expected Output

dept	employee_count
IT	3

The difference between "WHERE" and "HAVING" is important:

"WHERE" filters individual rows before grouping.
"HAVING" filters groups after aggregation.

This is a common topic in SQL interviews.

Common SQL SELECT Interview Questions

Here are a few questions worth practising:

What is the difference between "SELECT *" and selecting specific columns?
How does "DISTINCT" work?
What is the difference between "WHERE" and "HAVING"?
How do you sort query results in descending order?
Can you use aggregate functions with "GROUP BY"?
How would you find the second-highest salary?
What is the difference between "COUNT(*)" and "COUNT(column_name)"?
How do you handle NULL values in SQL?
What is the logical order of SQL query processing?
How would you retrieve employees whose salaries are above the company average?

Try writing the queries yourself before looking up the answers. Understanding why a query works is more valuable than memorising its syntax.

For more questions, detailed explanations, and additional examples, you can refer to this "SQL SELECT Statement Interview Questions and Answers guide" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/retrieving-data-sql-select-statement-interview-questions-faq.html).

Final Thoughts

The "SELECT" statement is much more than a way to display table data. Combined with filtering, sorting, grouping, and aggregate functions, it becomes a practical tool for solving everyday database problems.

If you're preparing for SQL interviews or working towards a data analytics or data engineering role, practise these queries on your own tables. Start with simple questions and gradually move to joins, subqueries, CTEs, and window functions.

Your turn: Which SQL concept do you find most challenging: joins, subqueries, "GROUP BY", or window functions?

Share your experience in the comments. I'd be interested to hear what you're practising.

SQL #SQLTutorial #SQLInterviewQuestions #Database #DataAnalytics

Why Your SQL Query Returns the Wrong Rows (And How WHERE and ORDER BY Fix It)

Kailas Warade — Sun, 25 Oct 2026 08:28:57 +0000

A colleague spent twenty minutes on a query once. It was returning the top ten orders correctly. Then she added a WHERE clause and the wrong orders started coming back.

She assumed the WHERE was broken. It wasn't. She thought LIMIT 10 ran first, and the sort happened on those ten rows. The database doesn't work that way. It sorts everything, then trims. She drew it on a whiteboard and never made the same mistake again.

I've taught WHERE and ORDER BY many times. Every group has someone who gets stuck on the same three things: NULL sorting, aggregate in WHERE, missing parentheses. They're not hard ideas. They're just the three places where people's gut feeling is wrong.

Here's where the gut feeling is wrong.

The database runs your query in a different order than you wrote it
You write top to bottom. The database runs it in a completely different order.

FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.

That's the whole pipeline. Once you can picture it, half of SQL stops being confusing.

WHERE runs second. It sees one row at a time, before anything has been grouped. So when you try to write WHERE COUNT(*) > 5, the database has no idea what you mean. The count doesn't exist yet. That error message you get? It's not the database being difficult. It's saying "you asked for this too early."

ORDER BY runs sixth. By that point the database has the full result in front of it, aliases and all. So ORDER BY line_total works even when line_total is only defined in the SELECT list. It can see it. WHERE can't.

I used to memorise this order for exams and forget it a week later. What made it stick was debugging. Once you've stared at a slow query and traced it through the pipeline a few times, it stops being something you memorise and starts being something you can see.

WHERE, and the one mistake everyone makes
WHERE keeps the rows you asked for and throws away the rest.

It's also the biggest speed win you have.
If WHERE cuts the rows by 20x, everything after it has 20x less work.
If a report is slow, WHERE is the first place to look.
Not the last.

Some patterns you'll use all the time:

WHERE credit_limit > 50000
for a straight comparison.

WHERE region = 'North' AND credit_limit > 60000 when you need two conditions to be true at once.

WHERE customer_name LIKE 'A%' for pattern matching.

The % is any sequence of characters, _ is exactly one.

WHERE region IN ('North', 'South') when you have a short list and don't want to chain ORs.
And WHERE order_amount BETWEEN 5000 AND 20000 when both ends should be included.

Then there's the one that gets everyone: WHERE credit_limit IS NULL.

If you write WHERE credit_limit = NULL, you get zero rows. No error.

No warning. Just an empty result that looks like a bug.

NULL isn't equal to anything. Not even another NULL. So the comparison fails without telling you.

Always use IS NULL or IS NOT NULL.

I've made this mistake more times than I'd like to admit. I've also watched people with fifteen years of experience make it. There's no shame in it. There's just a fix.

One more thing on WHERE.
SQL checks AND before OR. So A OR B AND C is read as A OR (B AND C). Not (A OR B) AND C.

When your intent is mixed, add parentheses. I've watched this one break more production reports than any other single mistake in SQL.

ORDER BY — putting the result in the order you want
ORDER BY sorts the final result. It runs after SELECT, so it can see everything SELECT can see.

Default is ascending. ORDER BY credit_limit puts the smallest first. DESC reverses it.

Multiple sort keys work left to right. ORDER BY region ASC, credit_limit DESC sorts by region first, then by credit limit inside each region. That's the pattern behind almost every report. One column to organise by, one to rank within.

You can sort by a column you didn't select. The output can be narrower than the sort. That works because ORDER BY runs after SELECT, which still has the original columns. Clean report, correct order, no compromise.

You can sort by an alias too. ORDER BY adjusted_limit DESC works when adjusted_limit is defined in the SELECT list. You can't do the same in WHERE, because the alias doesn't exist yet. That's how you know WHERE runs before ORDER BY. Comes up in interviews a lot.

If the column you're sorting by has an index, the database reads in that order and skips the sort. On a small table you won't notice. On a table with tens of millions of rows, it's the difference between a second and a minute. When a report gets slow, ORDER BY is the first thing I check.

The NULL sorting problem
NULL isn't zero. It isn't an empty string. It's the absence of a value, so it can't be compared like a number or a word.

Which means different databases sort it differently.

Oracle and PostgreSQL put NULLs last in ascending order, first in descending.

MySQL and SQL Server do the opposite. NULLs come first in ascending, last in descending.

This is exactly the kind of bug that works on your machine and breaks on the server. It's a portability problem, not a logic problem, and it only shows up when you move code from development to production.

Oracle and PostgreSQL let you be explicit. ORDER BY credit_limit ASC NULLS LAST puts the customer with no credit limit at the bottom.

MySQL and SQL Server don't support NULLS FIRST or NULLS LAST. You sort a computed column instead, like ORDER BY ISNULL(credit_limit, 999999) or ORDER BY credit_limit IS NULL, credit_limit. Different mechanics, same result.

If a report depends on where NULLs appear, be explicit. The default is never safe across databases.

WHERE vs HAVING, and why this is the question that catches people
Both clauses filter. They're completely different in what they do.

WHERE filters rows before any grouping. One row at a time. Aggregates don't exist yet.

HAVING filters groups after GROUP BY has produced them. It sees the grouped result. So it can hold SUM, AVG, COUNT.

Try to swap them and the database stops you with an error about aggregate functions in WHERE. That error isn't the database being difficult. It's saying "you asked for this too early." Same error message as before. Same reason.

You'll often want both. Filter to delivered orders only with WHERE. Group by customer. Keep only the customers whose delivered average is above 5,000 with HAVING.

The reason to do the WHERE first is not just correctness. It's speed. Filter early means fewer rows to group. On a large table that small win adds up.

Six mistakes I keep seeing
Two of these come up in every training session I run. The other four are the ones I used to make myself.

Putting an aggregate in WHERE. WHERE COUNT(*) > 5 throws an error because the count doesn't exist when WHERE runs. Move it to HAVING.

Assuming NULL sorts the same way everywhere. It doesn't. Oracle and PostgreSQL put NULLs last in ascending. MySQL and SQL Server put them first. If the query might run on more than one database, be explicit about NULL placement.

The other four, quickly.

BETWEEN on a timestamp column doesn't cover the whole end date.
It stops at midnight. Use >= '2024-01-01' AND < '2024-02-01',

or cast the timestamp to a date first.

WHERE credit_limit = NULL returns zero rows silently. No error. Use IS NULL.

AND before OR. SQL checks AND first, so add parentheses when the intent is mixed.

ORDER BY runs before LIMIT. Not after. The pipeline sorts first, then trims.

What this article doesn't cover
Those six are the ones that break production. They aren't the full picture.

The guide on my site covers twenty-four interview questions on these two clauses. How NULL sorts in every major database. How to sort by a column you didn't select. How to make ORDER BY faster on a huge table.

How WHERE and HAVING work together when you need both. The follow-up questions interviewers actually ask after the first answer.

There are sample tables you can run against, worked examples for every clause, and a quiz at the bottom.

That full walkthrough is here:

Restricting and Sorting Data in SQL: WHERE and ORDER BY Explained(https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/restricting-sorting-data-sql-faq.html)

Six here, twenty-four there. That's what the page has that I can't fit into a Dev.to post.

A Single SQL Constraint Can Save Your Database From Bad Data — Do You Know Which One?

Kailas Warade — Thu, 22 Oct 2026 03:41:46 +0000

Imagine you are building an order management system.

Your "customers" table contains 10,000 customers. Your "orders" table contains 500,000 orders.

Everything works perfectly.

Then one day, an application inserts an order for "customer_id = 99999".

The problem? Customer 99999 does not exist.

Your database now contains an order that cannot be linked to a valid customer.

Your reports may become unreliable. Your joins may not return the expected information. Your team may spend hours investigating a data-quality problem that could have been prevented.

The solution is not another SQL query. It is a database constraint.

Let's explore how SQL developers prevent these problems and learn some common SQL interview traps along the way.

CREATE TABLE: Start With the Right Structure

First, create a customer table.

CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL
);

Notice two important constraints:

"PRIMARY KEY" ensures that every customer has a unique, non-NULL identifier.
"NOT NULL" prevents a customer record from being inserted without a name.

Now create an orders table.

CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_amount NUMBER(10,2)
);

At first glance, everything looks fine.

But there is a hidden problem.

Nothing in this table definition prevents an order from referencing a customer who does not exist.

How do we fix it?

FOREIGN KEY: Protect Table Relationships

Add a foreign key.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

Now the database enforces the relationship.

If someone tries to insert an order with a non-NULL customer ID that does not exist in "customers", the database rejects the operation.

Why is this important?

Because your database should not depend entirely on every application developer remembering to validate every relationship correctly.

Interview takeaway: A foreign key enforces referential integrity at the database level.

One important detail: a nullable foreign key can contain "NULL" unless the column is also defined as "NOT NULL".

ALTER TABLE: Change Structure Without Recreating the Table

Business requirements rarely remain the same forever.

Suppose you now need to store customer email addresses.

Do you need to drop the table and create it again?

No.

ALTER TABLE customers
ADD email VARCHAR2(150);

The new column is added to the existing table, and existing rows are retained.

You can also use "ALTER TABLE" to add or remove constraints and modify columns, subject to database-specific rules and existing data.

In production, always review dependencies before changing a schema. A column modification or removal can affect reports, stored procedures, applications, and downstream data pipelines.

DROP vs TRUNCATE vs DELETE: The SQL Interview Trap

An interviewer asks:

"You have a table containing 10 lakh records. You want to remove all records but retain the table structure. Which command would you use?"

Many candidates immediately answer "DELETE".

But let's understand the differences.

DROP — Remove the Table

DROP TABLE orders;

This removes the table itself, including its data and associated table objects such as indexes and constraints.

Use it only when the table is no longer needed.

TRUNCATE — Remove All Rows

TRUNCATE TABLE orders;

This removes all rows while retaining the table structure.

There is no "WHERE" clause, so you cannot use it to remove only selected records.

DELETE — Remove Selected Rows

DELETE FROM orders
WHERE order_amount < 1000;

This removes only rows matching the specified condition.

You can also delete all rows by omitting the "WHERE" clause.

Important Oracle interview tip: DDL statements such as "DROP" and "TRUNCATE" have implicit-commit implications. Do not assume you can roll them back like an ordinary uncommitted "DELETE".

Always verify the target table and understand the transaction behavior before running destructive commands.

COMPOSITE PRIMARY KEY: When One Column Is Not Enough

Consider an order-items table.

A single order can contain several products, and the same product can appear in different orders.

CREATE TABLE order_items (
order_id NUMBER,
product_id NUMBER,
quantity NUMBER,
unit_price NUMBER(10,2),

PRIMARY KEY (order_id, product_id)

);

Why use two columns in the primary key?

Because the combination of "order_id" and "product_id" identifies each row.

For example:

"(101, 10)" identifies product 10 in order 101.
"(101, 20)" identifies product 20 in order 101.
"(102, 10)" identifies product 10 in order 102.

These combinations are different.

But inserting "(101, 10)" a second time would violate the composite primary key.

This concept is useful when designing junction tables, order details, and many-to-many relationships.

CHECK CONSTRAINT: Prevent Invalid Values

Suppose an order amount must never be negative.

You can enforce that rule at the database level.

ALTER TABLE orders
ADD CONSTRAINT chk_order_amount
CHECK (order_amount >= 0);

Now an insert or update that violates the constraint is rejected.

One subtle detail: a "CHECK" constraint alone does not reject "NULL" values. If the amount must also be present, define the column as "NOT NULL" or add an appropriate constraint.

This is the kind of detail that separates memorizing SQL syntax from understanding how database rules actually work.

Your SQL Interview Challenge

Before you leave, try this question without looking up the answer.

You have two tables:

"customers", containing customer information.
"orders", containing customer orders.

You must prevent an order from referencing a customer who does not exist.

Which solution is best?

A. Create an index on "orders.customer_id".

B. Add a foreign key referencing "customers.customer_id".

C. Use "ORDER BY customer_id".

D. Add a "CHECK" constraint requiring "customer_id > 0".

Pause and decide before reading further.

Correct answer: B — Add a foreign key.

An index can improve access performance, but it does not enforce the relationship. Sorting rows does not enforce data integrity, and checking that an ID is positive does not establish that the customer exists.

A foreign key directly enforces the required relationship.

The key principle is simple:

Good database design makes invalid data harder to create.

Want to Master SQL DDL for Interviews?

We've covered the fundamentals, but there is much more to learn.

What happens when you need a many-to-many relationship? When should you use "ON DELETE CASCADE"? How do you modify existing constraints safely? What mistakes do interviewers expect SQL developers to avoid?

I've brought these topics together in a detailed guide on Sankalan Data Tech.

The complete tutorial covers:

"CREATE", "ALTER", "DROP", and "TRUNCATE" with practical examples
Primary keys, foreign keys, unique constraints, and check constraints
One-to-many and many-to-many relationships
Composite primary keys and junction tables
Constraints versus triggers
Common interview mistakes and follow-up questions
21 SQL interview questions with answers

👉 Continue learning with the complete SQL DDL guide:

https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/ddl-manage-tables-relationships-sql-faq.html

If you are preparing for a SQL developer, Oracle PL/SQL, or data engineering interview, work through the examples and understand the reasoning behind each constraint instead of simply memorizing syntax.

Save this post for revision, and test yourself on the interview questions in the full guide.

sql #database #tutorial #beginners

Find Departments with an Average Salary Above the Company Average

Kailas Warade — Mon, 19 Oct 2026 03:47:25 +0000

SQL Challenge.
Find Departments with an Average Salary Above the Company Average

Here’s a practical SQL interview question. Can you find which departments have an average salary higher than the company-wide average?

Table 1: employees

emp_id	dept_id	salary
101	10	80000
102	10	100000
103	10	90000
104	20	50000
105	20	60000
106	20	70000
107	30	40000
108	30	50000

Table 2: departments

id	name
10	IT
20	HR
30	Sales

Your task: Write an SQL query to find departments whose average salary is higher than the overall company average.

Solution

WITH department_averages AS (
SELECT
e.dept_id,
d.name AS dept,
AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d
ON e.dept_id = d.id
GROUP BY e.dept_id, d.name
)
SELECT
dept_id,
dept,
average_salary
FROM department_averages
WHERE average_salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY average_salary DESC;

Expected Output

dept_id	dept	average_salary
10	IT	90000

The company-wide average salary is ₹67,500. IT has an average salary of ₹90,000, while HR and Sales have lower averages.

The CTE calculates the average salary for each department, and the subquery calculates the overall company average. The final query returns only departments whose average is higher than that benchmark.

Interview Challenge

What happens if a new employee earning ₹120,000 joins HR?

Will HR also appear in the result, or will IT remain the only department?

Share your answer in the comments.

For more SQL practice involving joins and multiple tables, visit this guide:

https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/displaying-data-multiple-tables-sql-faq.html

Follow Sankalan Data Tech for more practical SQL examples and interview questions.

SQL #sqlinterview #LearnSQL #DataEngineering

SQL: Find the Top 2 Orders for Every Customer Using Window Functions

Kailas Warade — Thu, 08 Oct 2026 03:26:32 +0000

SQL: Find the Top 2 Orders for Every Customer Using Window Functions

One SQL problem I often see people get wrong is:

How do you find the top 2 orders for every customer?

Finding the top 2 orders overall is easy.

The interesting part is “for every customer.”

Let’s take a simple example.

Sample Data

CID| OID| AMT
C001| 1001| 12000
C001| 1002| 7500
C001| 1003| 9000
C002| 1004| 15000
C002| 1005| 6000
C002| 1006| 11000
C003| 1007| 5000
C003| 1008| 18000
C003| 1009| 12000

We want the top 2 orders for each customer based on order amount.

The expected result is:

CID| OID| AMT
C001| 1001| 12000
C001| 1003| 9000
C002| 1004| 15000
C002| 1006| 11000
C003| 1008| 18000
C003| 1009| 12000

Why a Simple ORDER BY Is Not Enough

A common first attempt might be:

SELECT *
FROM orders
ORDER BY amt DESC
FETCH FIRST 2 ROWS ONLY;

This returns the top 2 orders from the entire table.

But that is not what we need.

We need the ranking to start again for each customer.

That is where SQL window functions become useful.

If you want to learn more about window functions and analytic functions, I’ve also covered the topic in more detail here:

SQL Window Functions / Analytic Functions

https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/window-functions-analytic-functions-sql-faq.html

Using ROW_NUMBER()

Here is the solution:

SELECT cid,
oid,
amt
FROM (
SELECT cid,
oid,
amt,
ROW_NUMBER() OVER (
PARTITION BY cid
ORDER BY amt DESC
) AS rn
FROM orders
)
WHERE rn <= 2;

The key part is:

ROW_NUMBER() OVER (
PARTITION BY cid
ORDER BY amt DESC
)

What does it do?

PARTITION BY cid

Divides the data customer-wise.

ORDER BY amt DESC

Sorts each customer’s orders from highest amount to lowest.

ROW_NUMBER()

Assigns a sequence number to the orders within each customer.

The result before applying "WHERE rn <= 2" looks like this:

CID| OID| AMT| RN
C001| 1001| 12000| 1
C001| 1003| 9000| 2
C001| 1002| 7500| 3
C002| 1004| 15000| 1
C002| 1006| 11000| 2
C002| 1005| 6000| 3
C003| 1008| 18000| 1
C003| 1009| 12000| 2
C003| 1007| 5000| 3

Then:

WHERE rn <= 2

keeps only the first two rows for each customer.

What If Two Orders Have the Same Amount?

This is where "ROW_NUMBER()", "RANK()", and "DENSE_RANK()" behave differently.

ROW_NUMBER()

Every row gets a unique number.

For example:

OID| AMT| RN
1001| 12000| 1
1002| 12000| 2

If you need exactly 2 rows per customer, "ROW_NUMBER()" is usually the right choice.

RANK()

Rows with the same amount receive the same rank.

OID| AMT| RANK
1001| 12000| 1
1002| 12000| 1
1003| 9000| 3

Notice that rank 2 is skipped.

DENSE_RANK()

Rows with the same amount receive the same rank, but there are no gaps.

OID| AMT| RANK
1001| 12000| 1
1002| 12000| 1
1003| 9000| 2

So the function you choose depends on the requirement.

If you need:

«Exactly 2 orders for every customer»

use "ROW_NUMBER()".

If you need:

«All orders belonging to the top 2 amount levels»

"DENSE_RANK()" may be a better choice.

A Pattern Worth Remembering

Whenever you see a requirement like:

«Top N records for each customer, department, product, category, or region»

think about this pattern:

PARTITION BY
+
ORDER BY
+
ROW_NUMBER / RANK / DENSE_RANK

The same approach can be used for many practical SQL problems:

Top 3 products for each category
Highest-paid employees in each department
Latest 2 transactions for each customer
Top 5 sales for each region
Most recent record for each employee

The table and business requirement may change, but the underlying SQL pattern is often the same.

Try This Yourself

Now change the requirement:

Find the latest 2 orders for every customer.

What would you change in the query?

The answer is in the "ORDER BY" inside the window function.

That small change is worth understanding because learning the pattern is much more useful than simply memorizing one SQL query.

Sankalan Data Tech
Practical SQL, Python and Data Engineering learning.

Python Functions Explained: 10 Concepts Every Python Developer Should Know

Kailas Warade — Wed, 07 Oct 2026 12:36:54 +0000

If you are learning Python for Data Analytics, Data Engineering, automation, or backend development, functions are one of the first topics you need to get comfortable with.

But in interviews, knowing only this is not enough:

def add(a, b):
return a + b

You may be asked what happens when you change the arguments, use "*args", pass keyword arguments, use default values, return multiple values, or access variables outside the function.

Here are 10 Python function concepts worth understanding properly.

Function Definition vs Function Call

A function definition tells Python what the function should do.

def calculate_total(price, quantity):
return price * quantity

Nothing happens when Python reads the definition.

The function runs when you call it:

total = calculate_total(500, 3)

print(total)

Output:

1500

This distinction sounds basic, but it is a common interview question.

Parameters and Arguments Are Not the Same Thing

Consider this function:

def calculate_total(price, quantity):
return price * quantity

Here, "price" and "quantity" are parameters.

When we call:

calculate_total(500, 3)

"500" and "3" are arguments.

A simple way to remember it:

Parameter → variable written in the function definition
Argument → actual value passed during the function call

This becomes more important when you start working with different types of arguments.

Positional and Keyword Arguments

With positional arguments, Python matches values based on their position.

def create_user(name, age):
print(name, age)

create_user("Rahul", 28)

Here:

name = Rahul
age = 28

With keyword arguments, you explicitly mention the parameter name:

create_user(age=28, name="Rahul")

This can make function calls easier to read, especially when a function has several parameters.

You can also mix them:

create_user("Rahul", age=28)

This is valid because the positional argument comes before the keyword argument.

Default Arguments Make Functions More Flexible

Suppose most users are from India:

def create_user(name, country="India"):
print(name, country)

Now you can call:

create_user("Rahul")

Output:

Rahul India

Or override the default:

create_user("John", "USA")

Output:

John USA

Default arguments are useful when a parameter has a sensible value that will be used most of the time.

What Exactly Does "*args" Do?

Sometimes you don't know how many positional values will be passed.

For example:

def calculate_sum(*args):
return sum(args)

print(calculate_sum(10, 20))
print(calculate_sum(10, 20, 30, 40))

Output:

30
100

Inside the function, "args" is a tuple.

def show_values(*args):
print(args)

show_values(10, 20, 30)

Output:

(10, 20, 30)

The important point is that "*" tells Python to collect additional positional arguments.

What About "**kwargs"?

"**kwargs" is used when you want to accept multiple keyword arguments.

def show_user(**kwargs):
print(kwargs)

show_user(
name="Rahul",
age=28,
city="Pune"
)

Output:

{'name': 'Rahul', 'age': 28, 'city': 'Pune'}

Inside the function, "kwargs" is a dictionary.

This can be useful when the function needs to accept flexible named inputs.

For example:

def create_report(**filters):
for key, value in filters.items():
print(key, value)

create_report(
city="Pune",
department="Sales",
year=2026
)

The names "args" and "kwargs" are conventional. Python does not require those exact names.

For example, this also works:

def show_values(*values):
print(values)

And:

def show_details(**details):
print(details)

The "" and "*" are what matter.

"return" Is Different From "print()"

This is an important concept for beginners.

Consider:

def add(a, b):
print(a + b)

The function displays the result, but it does not return the result.

Now compare it with:

def add(a, b):
return a + b

You can store the returned value:

result = add(10, 20)

print(result)

This matters when functions are combined.

For example:

def calculate_discount(price, discount):
return price - (price * discount / 100)

final_price = calculate_discount(1000, 10)

print(final_price)

The returned value can be stored, passed to another function, or used in a larger calculation.

Can a Python Function Return Multiple Values?

Yes.

def get_employee():
return "Rahul", "Data Engineer", 85000

You can receive the result like this:

name, role, salary = get_employee()

print(name)
print(role)
print(salary)

Python actually returns these values as a tuple.

You can see this directly:

result = get_employee()

print(result)

Output:

('Rahul', 'Data Engineer', 85000)

This is useful when a function needs to return a small group of related values.

Understand Local and Global Variables

Consider:

name = "Rahul"

def show_name():
print(name)

show_name()

The function can read the global variable.

But a variable created inside a function normally belongs to that function:

def show_name():
name = "Rahul"
print(name)

show_name()

print(name) # NameError

The variable "name" created inside the function is local to that function.

You may also see the "global" keyword:

count = 0

def increase():
global count
count += 1

increase()

print(count)

Although "global" is valid, changing global state inside functions should generally be done carefully because it can make code harder to understand and test.

One Interview Question That Tests Your Understanding

What will this code print?

def calculate(a, b=10):
return a + b

print(calculate(5))
print(calculate(5, 20))

The answer is:

15
25

Why?

In the first call:

calculate(5)

Python uses the default value:

a = 5
b = 10

In the second call:

calculate(5, 20)

The supplied value "20" replaces the default.

Questions like this are more useful than simply memorizing the definition of a function.

What Should You Prepare for a Python Interview?

For functions, don't stop at the basic syntax.

Make sure you can explain and write examples for:

Function definition and function call
Parameters vs arguments
Positional arguments
Keyword arguments
Default arguments
"*args"
"**kwargs"
"return" vs "print"
Returning multiple values
Local and global scope
"global" keyword
Lambda functions
Nested functions
Recursion
Decorators

The important part is not just remembering the definition.

Write the code, run it, change the input, and see what Python actually does.

That is how these concepts become much easier to remember during an interview.

More Python Function Interview Questions

If you want a more detailed interview-focused reference, I have covered Python functions with examples and explanations here:

👉 "Python Functions Interview Questions and Answers" (https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-functions-tutorial.html)

The page goes deeper into Python function concepts and can be useful as a revision page before a Python interview.

Final Thought

Functions look simple when you first learn Python.

But once you understand how arguments, return values, scope, "args", "*kwargs", and nested functions work, you start writing much more reusable Python code.

And that is the real purpose of learning functions — not just answering an interview question, but being able to break a real problem into smaller pieces of code that you can reuse and test.

Python Loops and Flow Control: A Practical Guide to Repetition and Program Execution

Kailas Warade — Mon, 05 Oct 2026 01:36:55 +0000

When writing Python programs, we often need to execute the same operation repeatedly.

It could be processing records, checking values in a list, reading a file line by line, processing API responses, or performing repetitive data-processing tasks.

Writing the same code again and again is not practical.

This is where loops and flow control become important.

Python provides simple ways to repeat operations, make decisions, skip specific iterations, stop execution when required, and control the normal flow of a program.

In this post, we'll understand the important concepts behind Python loops and flow control with practical examples.

*What Is a Loop in Python?
*
A loop allows us to execute a block of code repeatedly.

For example, if we want to print numbers from 1 to 5, we could write:

print(1)
print(2)
print(3)
print(4)
print(5)

But using a loop makes the code much simpler:

for i in range(1, 6):
print(i)

Output:

1
2
3
4
5

The main advantage of loops is that they reduce repetitive code.

Loops are commonly used for:

Processing lists
Processing strings
Reading files
Processing database records
Handling API responses
Data transformation
Automation
Data analysis
ETL and data-processing tasks

The Two Main Types of Loops in Python

Python mainly provides two types of loops:

"for" loop
"while" loop

They both repeat code, but they are generally used in different situations.

for Loop

A "for" loop is commonly used to iterate through an iterable.

For example:

fruits = ["Apple", "Banana", "Mango"]

for fruit in fruits:
print(fruit)

Output:

Apple
Banana
Mango

The loop takes one item at a time from the collection.

A "for" loop can be used with lists, strings, tuples, ranges, and many other iterable objects.

For example:

name = "Python"

for character in name:
print(character)

Output:

P
y
t
h
o
n

while Loop

A "while" loop continues executing as long as its condition is true.

count = 1

while count <= 5:
print(count)
count += 1

Output:

1
2
3
4
5

The important part here is:

count += 1

The value changes during each iteration.

Eventually, "count <= 5" becomes false and the loop stops.

A "while" loop is useful when the number of iterations depends on a condition that can change during execution.

for vs while

A simple way to think about them is:

for loop

Use it when you are iterating through a sequence or iterable.

for item in items:
print(item)

while loop

Use it when a condition controls whether the loop should continue.

while condition:
# code

The choice should depend on the problem rather than simply on whether the number of iterations is known.

What Is an Iterable?

An iterable is an object whose values can be accessed one at a time.

Common examples include:

list
tuple
string
set
dictionary
range

For example:

numbers = [10, 20, 30]

for number in numbers:
print(number)

The "for" loop gets each value from the iterable.

Understanding iterables is important because Python's "for" loop is built around iteration.

Using range()

The "range()" function is commonly used with "for" loops.

There are three common forms:

range(stop)

range(start, stop)

range(start, stop, step)

For example:

for i in range(5):
print(i)

Output:

0
1
2
3
4

Notice that "5" is not included.

We can specify a starting value:

for i in range(1, 6):
print(i)

Output:

1
2
3
4
5

We can also specify a step:

for i in range(0, 11, 2):
print(i)

Output:

0
2
4
6
8
10

The "step" controls how much the value changes during each iteration.

Loop Control Variables

A loop control variable can be used to control how long a loop continues.

For example:

count = 1

while count <= 5:
print(count)
count += 1

Here, "count" starts at "1" and increases after every iteration.

When it reaches "6", the condition becomes false.

This is especially important when working with "while" loops.

Infinite Loops

An infinite loop is a loop that never reaches a condition that stops it.

For example:

while True:
print("Running...")

Because the condition is always "True", the loop continues indefinitely.

An infinite loop can also happen accidentally:

count = 1

while count <= 5:
print(count)

The problem is that "count" never changes.

A corrected version is:

count = 1

while count <= 5:
print(count)
count += 1

Whenever you write a "while" loop, ask:

What will eventually make this condition false?

break, continue and pass

Python provides three important statements for controlling loop execution:

break
continue
pass

They have completely different purposes.

break

"break" stops the entire loop.

for i in range(1, 6):

if i == 3:
    break

print(i)

Output:

1
2

When "i" becomes "3", the loop terminates.

This is useful when the required result has already been found and there is no reason to continue.

continue

"continue" skips the current iteration.

for i in range(1, 6):

if i == 3:
    continue

print(i)

Output:

1
2
4
5

The loop continues after skipping "3".

pass

"pass" does nothing.

for i in range(5):

if i == 2:
    pass

print(i)

Output:

0
1
2
3
4

So the easiest way to remember them is:

break → stop the loop
continue → skip the current iteration
pass → do nothing

Nested Loops

A loop inside another loop is called a nested loop.

For example:

for i in range(1, 4):

for j in range(1, 4):
    print(i, j)

The inner loop executes completely for each iteration of the outer loop.

Nested loops are useful for:

Tables
Grids
Matrices
Multi-dimensional data
Combinations

However, they can become expensive when working with large datasets, so unnecessary nested loops should be avoided.

enumerate()

Sometimes we need both the position and the value while iterating.

Python provides "enumerate()" for this purpose.

fruits = ["Apple", "Banana", "Mango"]

for index, fruit in enumerate(fruits):
print(index, fruit)

Output:

0 Apple
1 Banana
2 Mango

This is cleaner than maintaining a separate counter.

Loop else

Python has an interesting feature that allows an "else" block to be used with loops.

The loop "else" executes when the loop finishes normally without "break".

For example:

for i in range(5):
print(i)
else:
print("Loop completed")

The "else" block executes because the loop finishes normally.

But if "break" terminates the loop:

for i in range(5):

if i == 3:
    break

print(i)

else:
print("Loop completed")

The "else" block does not execute.

A useful way to remember it is:

Loop "else" runs when the loop completes without "break".

Simulating a do-while Loop

Python does not have a separate "do-while" statement.

However, similar behavior can be created using "while True" and "break".

while True:

value = input("Enter a positive number: ")

if int(value) > 0:
    break

The body executes first, and the loop can then be terminated when the required condition is satisfied.

This is useful when an operation needs to execute at least once before checking whether the loop should continue.

Iterating in Reverse

The "range()" function can also be used to iterate backwards.

for number in range(10, 0, -1):
print(number)

Output:

10
9
8
7
6
5
4
3
2
1

The "-1" tells Python to decrease the value after every iteration.

Printing Every Other Number

The "step" argument of "range()" can be used to skip values.

for number in range(0, 11, 2):
print(number)

Output:

0
2
4
6
8
10

The value increases by "2" each time.

Handling Exceptions Inside a Loop

When processing many values, one invalid value should not always stop the complete operation.

Python allows us to handle exceptions inside a loop.

numbers = [10, 5, 0, 3]

for number in numbers:

try:
    result = 100 / number
    print(result)

except ZeroDivisionError:
    print("Cannot divide by zero")

When the value is "0", the exception is handled and the loop continues.

This pattern is useful when processing:

Data records
Files
API responses
User input
Large datasets

It makes the program more robust because one problematic record does not necessarily stop the complete process.

How to Write Better Python Loops

Once the logic is correct, we can also think about readability and performance.

Use built-in functions

For some operations, Python's built-in functions can replace manual loops.

For example:

total = sum(numbers)

Other useful functions include:

min(numbers)
max(numbers)

Avoid unnecessary calculations

If a calculation does not change during the loop, perform it outside the loop when possible.

Avoid unnecessary nested loops

Nested loops can become expensive as the amount of data increases.

Use appropriate data structures

Sets and dictionaries can be useful for efficient lookup operations.

Stop processing when the answer is known

Use "break" when there is no reason to continue.

Consider vectorized operations for data processing

When working with libraries such as Pandas and NumPy, a Python loop is not always the best solution. Vectorized operations can often be more appropriate for large datasets.

Why Loops Matter in Data Engineering and Data Analytics

Loops are basic Python concepts, but they appear in many real-world tasks.

For example, they can be used while:

Processing records
Validating data
Transforming values
Reading files
Calling APIs
Handling errors
Automating repetitive operations
Building data-processing scripts

As your Python knowledge grows, you will also learn when not to use a loop.

For example, Pandas and NumPy provide operations that can process collections of data more efficiently than manually iterating through every row in many situations.

So the goal is not simply to learn loop syntax.

The goal is to understand how and when to control program execution effectively.

Going Deeper: Python Loops and Flow Control

The examples above give you the foundation.

But if you are preparing for technical interviews or want to understand the topic more deeply, there are many details worth exploring—such as iterable behavior, loop termination, "range()", "enumerate()", nested loops, loop "else", "break" and "continue", do-while behavior, optimization, and exception handling.

I have put those deeper topics into a separate 25-question Python Loops and Flow Control technical Q&A resource, with detailed explanations and examples.

👉 "Explore the detailed Python Loops and Flow Control questions and answers" (https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-control-structures-loops.html)

You can use this post to understand the concepts first, and then use the detailed Q&A resource when you want to test your understanding or go deeper into the technical side of Python loops.

Final Takeaway

Python loops are more than just a way to repeat code.

They are a fundamental part of controlling program execution.

The important concepts to remember are:

for → iterate over an iterable
while → repeat while a condition is true
range() → generate a sequence of numbers
enumerate → get index and value
break → stop the loop
continue → skip the current iteration
pass → do nothing
nested loop → loop inside another loop
loop else → execute when the loop completes normally

Start with small examples.

Run the code.

Change the conditions.

Predict the output.

Then move to real-world data-processing problems.

That is where Python loops and flow control become much more useful.

Python if-else: A Simple Guide with Examples

Kailas Warade — Thu, 01 Oct 2026 03:19:23 +0000

Python if-else: A Simple Guide with Examples

Programs often need to make decisions.

For example:

If the temperature is high, show a warning.
If a file exists, read it.
If marks are above 40, consider the student passed.
If a value is missing, handle it differently.

Python uses "if", "elif", and "else" to handle this kind of logic.

These are simple statements, but you will use them regularly when writing Python programs.

Python if statement

Let's start with a simple example.

age = 25

if age >= 18:
print("Adult")

The condition "age >= 18" is checked first.

If it is "True", Python runs the indented code below it.

If it is "False", Python skips that code.

Python if-else

Sometimes we have two possible results.

For example:

marks = 65

if marks >= 40:
print("Pass")
else:
print("Fail")

Here, Python does one of two things:

"marks >= 40" is true → "Pass"
"marks >= 40" is false → "Fail"

Python if-elif-else

What if there are several possible results?

We can use "elif".

marks = 78

if marks >= 75:
print("Distinction")
elif marks >= 60:
print("First Class")
elif marks >= 40:
print("Pass")
else:
print("Fail")

Python checks the conditions from top to bottom.

For "marks = 78", the first condition is true, so Python prints:

Distinction

It does not continue checking the remaining "elif" conditions.

Multiple if statements vs if-elif-else

This is an important difference.

With separate "if" statements:

num = 10

if num > 0:
print("Positive")

if num > 5:
print("Greater than 5")

Both conditions are checked independently.

With "if-elif":

num = 10

if num > 0:
print("Positive")
elif num > 5:
print("Greater than 5")

Once the first condition is true, Python skips the "elif".

A simple way to remember this:

Separate "if" statements are independent.

"if-elif-else" is one chain of conditions.

Using "and"

We can combine conditions with logical operators.

With "and", all conditions must be true.

age = 25
salary = 50000

if age >= 18 and salary >= 30000:
print("Eligible")

Both conditions need to be "True" for the message to be printed.

Using "or"

With "or", at least one condition needs to be true.

day = "Saturday"

if day == "Saturday" or day == "Sunday":
print("Weekend")

The condition is true because "day == "Saturday"" is true.

Using "not"

"not" reverses a Boolean condition.

is_active = False

if not is_active:
print("Account is inactive")

Since "is_active" is "False", "not is_active" is "True".

Nested if

An "if" statement can be placed inside another "if".

age = 25
has_id = True

if age >= 18:
if has_id:
print("Access allowed")

This is called a nested "if".

Nested conditions are sometimes useful, but too many levels of nesting can make code difficult to read.

Python conditional expression

For a simple two-way decision, Python provides a conditional expression.

age = 20

result = "Adult" if age >= 18 else "Minor"

print(result)

This is often called a ternary expression.

It can make short conditions more compact.

For complicated logic, however, a normal "if-else" statement is usually easier to understand.

Checking positive, negative, or zero

Here is another simple example:

num = -5

if num > 0:
print("Positive")
elif num < 0:
print("Negative")
else:
print("Zero")

Changing "num" to "10", "-10", or "0" will produce different results.

Checking a leap year

A slightly more interesting example is checking whether a year is a leap year.

year = 2024

if year % 400 == 0 or (year % 4 == 0 and year % 100 != 0):
print("Leap year")
else:
print("Not a leap year")

This example combines the modulo operator "%" with "and" and "or".

Try these values:

2024
2023
2000
1900

and see what happens.

Comparing two numbers

We can also use conditions to compare values.

a = 25
b = 40

if a > b:
print("a is larger")
elif b > a:
print("b is larger")
else:
print("Both are equal")

The final "else" handles the case where both values are equal.

Using if inside a function

Conditional logic is also commonly used inside functions.

def check_age(age):
if age < 18:
return "Minor"

return "Adult"

print(check_age(25))

Here, the function returns ""Minor"" when the age is below 18. Otherwise, it returns ""Adult"".

Notice that we don't need an "else" after "return".

Once "return" runs, the function ends.

Common Python if-else mistakes

Using "=" instead of "=="

Use "=" when assigning a value:

age = 18

Use "==" when comparing values:

if age == 18:
print("Age is 18")

Incorrect indentation

Python uses indentation to define code blocks.

This is incorrect:

if age >= 18:
print("Adult")

The correct version is:

if age >= 18:
print("Adult")

Using "true" and "false"

Python uses "True" and "False".

is_active = True

if is_active:
print("Active")

"true" and "false" are not the Python Boolean values.

A few things to practice

After understanding the examples above, try writing these programs yourself:

Check whether a number is even or odd.
Find the largest of three numbers.
Check whether a person is eligible based on age.
Convert marks into a grade.
Check whether a number is positive, negative, or zero.
Check whether a year is a leap year.
Check whether a password meets a few basic conditions.
Use "and", "or", and "not" in a small program.

Try solving them without looking at the examples first.

More Python if-else examples

If you want more practice, I have a separate collection of Python conditional-statement examples and questions covering "if", "elif", "else", nested conditions, logical operators, conditional expressions, and practical problems:

Python If-Else Examples and Interview Questions:
https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-control-structures-if-else.html

The examples can be useful whether you are learning Python for the first time, working with data, writing automation scripts, or preparing for a Python interview.

Python Operators Explained with Simple Examples

Kailas Warade — Sun, 27 Sep 2026 18:53:19 +0000

Python Operators Explained with Simple Examples

Python operators are symbols or keywords used to perform operations on values and variables.

For example:

a = 10
b = 3

print(a + b)
print(a > b)

Here, "+" adds two values and ">" compares them.

If you are learning Python or preparing for a Python interview, understanding operators is important because you will use them in almost every Python program.

Let's look at each type with simple examples.

1. Arithmetic Operators

Arithmetic operators are used for calculations.

a = 10
b = 3

print(a + b) # 13
print(a - b) # 7
print(a * b) # 30
print(a / b) # 3.3333333333333335
print(a // b) # 3
print(a % b) # 1
print(a ** b) # 1000

Some operators need special attention.

"/" Division

"/" returns the normal division result.

print(10 / 3)

Output:

3.3333333333333335

"//" Floor Division

"//" returns the floor value of the division.

print(10 // 3)

Output:

"%" Modulus

"%" returns the remainder.

print(10 % 3)

Output:

It is often useful for checking whether a number is even or odd.

number = 10

if number % 2 == 0:
print("Even")

"**" Exponentiation

"**" is used to raise a number to a power.

print(2 ** 3)

Output:

2. Comparison Operators

Comparison operators compare two values.

The result is always "True" or "False".

a = 10
b = 20

print(a == b) # False
print(a != b) # True
print(a > b) # False
print(a < b) # True
print(a >= b) # False
print(a <= b) # True

One common mistake is confusing "=" and "==".

"=" vs "=="

"=" is used to assign a value.

age = 25

"==" is used to compare two values.

age == 25

This difference is very important in Python.

*3. Logical Operators
*
Logical operators are mainly used when working with multiple conditions.

Python has three logical operators:

"and"
"or"
"not"

"and"

Both conditions must be "True".

age = 25
salary = 50000

if age >= 18 and salary >= 30000:
print("Eligible")

"or"

At least one condition must be "True".

age = 25
salary = 20000

if age >= 18 or salary >= 30000:
print("Eligible")

"not"

"not" reverses the result.

is_active = True

print(not is_active)

Output:

False

*4. Assignment Operators
*
Assignment operators are used to assign or update values.

salary = 50000

salary += 5000
print(salary)

Output:

55000

Here are some common assignment operators:

For example:

x = 10

x += 5
print(x) # 15

x -= 3
print(x) # 12

*5. Membership Operators
*
Membership operators check whether a value exists inside a sequence such as a list or string.

The two membership operators are:

"in"
"not in"

Example:

languages = ["Python", "Java", "SQL"]

print("Python" in languages)
print("C++" in languages)

Output:

True
False

You can also use them with strings:

name = "Python Programming"

print("Python" in name)

Output:

True

*6. Identity Operators
*
Python has two identity operators:

"is"
"is not"

They check whether two variables refer to the same object.

a = [1, 2, 3]
b = a

print(a is b)

Output:

True

Here, "b" refers to the same list object as "a".

"==" vs "is"

This is an important Python interview question.

"==" checks whether two values are equal.

"is" checks whether two variables refer to the same object.

For example:

a = [1, 2, 3]
b = [1, 2, 3]

print(a == b)
print(a is b)

Output:

True
False

The values are equal, but they are different list objects.

*7. Operator Precedence
*
When an expression contains multiple operators, Python follows operator precedence to decide which operation happens first.

For example:

result = 10 + 5 * 2

print(result)

Output:

Multiplication happens before addition.

You can use parentheses when you want to make the order clear:

result = (10 + 5) * 2

print(result)

Output:

Using parentheses also makes the code easier to read.

A Small Practical Example

Suppose we have an employee salary and want to calculate a bonus.

salary = 50000
bonus = 5000

total_salary = salary + bonus

if total_salary >= 55000:
print("Salary target reached")

This small example uses different operators together:

"+" calculates the total salary.
">=" compares the salary.
"=" assigns values.
"if" uses the result of the comparison.

This is how operators are normally used in real Python programs.

Frequently Asked Questions
**
**What is the difference between "/" and "//" in Python?

"/" performs normal division and returns a division result.

"//" performs floor division and returns the floor value.

print(10 / 3) # 3.3333333333333335
print(10 // 3) # 3

What is the difference between "==" and "is" in Python?

"==" checks whether two values are equal.

"is" checks whether two variables refer to the same object.

This is an important difference to understand when working with Python objects.

*More Python Operators Interview Questions
*
If you are preparing for a Python interview, you can find more operator-based interview questions and answers here:

*"Python Operators Interview Questions and Answers" (https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-operators-overview.html)
*

The examples cover the important Python operators and explain where they are used.

Conclusion

Python operators are simple, but they are used everywhere in Python programming.

The most important ones to understand first are arithmetic, comparison, logical, assignment, membership, and identity operators.

Once these operators are clear, writing conditions and calculations in Python becomes much easier..

Python Data Types Explained With Practical Examples

Kailas Warade — Fri, 25 Sep 2026 04:00:40 +0000

Python Data Types Explained With Practical Examples

When you write a Python program, you work with different kinds of values every day.

You might store a person's name, an age, a price, a list of products, or information returned by an API. Python needs to know what kind of value it is dealing with, and that's where data types come in.

If you're learning Python, data types are one of those topics that look simple at first but become important when you start working with functions, APIs, databases, Pandas, or data analysis.

Let's look at the most commonly used Python data types with simple examples.

Numbers: "int" and "float"

Python uses "int" for whole numbers and "float" for numbers with decimal values.

age = 45
price = 1250.50

print(type(age))
print(type(price))

Output:

For example, an age would normally be an integer, while a product price might contain decimal values.

One thing worth knowing about floats is that they don't always represent decimal numbers exactly.

print(0.1 + 0.2)

You may get:

0.30000000000000004

This isn't a Python bug. It comes from the way floating-point numbers are represented internally.

It becomes especially important when you're working with calculations where precision matters.

Strings

A string is a sequence of characters.

name = "Kailas"

print(name)
print(type(name))

Strings support indexing:

language = "Python"

print(language[0])

Output:

You can also take a part of a string using slicing:

print(language[0:3])

Output:

Pyt

One important point is that strings are immutable.

You can't change one character directly after the string has been created.

name = "Python"

name[0] = "J" # TypeError

Lists

A list is useful when you need to keep multiple values together.

languages = ["Python", "SQL", "Java"]

print(languages)

One of the biggest advantages of a list is that it can be changed.

languages[1] = "C++"

print(languages)

Output:

['Python', 'C++', 'Java']

You can also add new values:

languages.append("Go")

This is why lists are commonly used when the collection of values may change during program execution.

Tuples

Tuples also store multiple values, but they are immutable.

coordinates = (18.52, 73.85)

print(coordinates)

Once a tuple is created, you can't change one of its elements.

A simple way to remember the difference is:

List → mutable
Tuple → immutable

Neither is automatically better. The right choice depends on what you need to do with the data.

Dictionaries

A dictionary stores information using key-value pairs.

student = {
"name": "Rahul",
"age": 25,
"city": "Pune"
}

print(student["name"])

Output:

Rahul

This is useful when the meaning of each value matters.

Compare:

student = ["Rahul", 25, "Pune"]

with:

student = {
"name": "Rahul",
"age": 25,
"city": "Pune"
}

The second version makes the data much easier to understand.

Dictionaries are widely used in Python applications, APIs, configuration files, and JSON data.

Sets

A set is a collection that stores unique values.

numbers = {10, 20, 30, 10, 20}

print(numbers)

The duplicate values are removed.

Sets are particularly useful when you need operations such as union, intersection, or difference.

For example:

a = {1, 2, 3}
b = {3, 4, 5}

print(a.intersection(b))

Output:

{3}

If your main requirement is to work with unique values, a set can be very convenient.

Boolean Values

Boolean values represent either "True" or "False".

For example:

age = 25

is_adult = age >= 18

print(is_adult)

Output:

True

You'll see Boolean values everywhere in Python, especially in conditions, filtering, validation, and data processing.

For example:

if is_adult:
print("Allowed")

What is "None" in Python?

"None" is a special value that represents the absence of a value.

result = None

print(result)
print(type(result))

Output:

None

It's important not to confuse "None" with:

"0"
"False"
""""
an empty list

They represent different things.

You'll often encounter "None" when working with functions, databases, APIs, and optional values.

Type Conversion

Sometimes the value you receive isn't in the type you need.

A common example is user input.

age = input("Enter your age: ")

print(type(age))

Even if the user enters:

the result from "input()" is a string.

If you want to perform numerical calculations, you can convert it:

age = int(age)

print(age + 5)

Python provides several common conversion functions:

int()
float()
str()
list()
tuple()

But conversion isn't always possible.

For example:

int("Python")

will raise a "ValueError" because ""Python"" cannot be converted into an integer.

This is something you'll frequently deal with when processing files, APIs, databases, and user input.

Mutable vs Immutable Data Types

This is one of the Python concepts that is worth understanding properly.

Some objects can be changed after they are created. Others cannot.

Common mutable types include:

list
dict
set

Common immutable types include:

int
float
str
tuple
bool

For example, a list can be modified:

numbers = [10, 20, 30]

numbers.append(40)

print(numbers)

The same list now contains the additional value.

Strings behave differently:

name = "Python"

name[0] = "J"

The code above raises an error because strings are immutable.

This distinction becomes particularly important when you start learning about object references, copying, functions, and shallow vs deep copy.

A Practical Example

Imagine you're working on a small employee application.

You might have:

employee_id = 101
employee_name = "Amit"
salary = 55000.50
is_active = True

skills = ["Python", "SQL", "Power BI"]

employee = {
"id": employee_id,
"name": employee_name,
"salary": salary,
"active": is_active,
"skills": skills
}

Here, several Python data types are working together:

"101" → "int"
""Amit"" → "str"
"55000.50" → "float"
"True" → "bool"
"skills" → "list"
"employee" → "dict"

This is much closer to how you'll actually encounter data types in a real Python application.

Why Data Types Matter Beyond Python Basics

Data types become even more important when you move into data analytics and data engineering.

For example, an API might return:

age = "45"

while your calculation expects:

age = 45

Or a column in a dataset might contain a mixture of numbers and strings.

Before performing calculations or transformations, you need to understand what you're actually working with.

That's why Python data types are not just an interview topic. They are part of everyday Python programming.

Final Thoughts

The most important Python data types to understand first are:

int
float
str
bool
list
tuple
set
dict
None

Don't try to memorize them only for an interview.

Try small examples and pay attention to what can be changed, how values are accessed, and what happens when you convert one type to another.

Once these basics are clear, topics like functions, APIs, Pandas, data cleaning, and data analysis become much easier to understand.

Preparing for a Python Interview?

If you want to go beyond these basic examples, I've put together a detailed Python Data Types Interview Questions and Answers guide on SankalanTech.

It covers questions around:

Built-in data types
Lists vs tuples
Dictionaries
Sets
"int" vs "float"
Strings
"None"
Type conversion
Shallow vs deep copy
Dynamic typing

Read the complete Python Data Types interview guide on SankalanTech:

https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-data-types-guide.html

Use this article to understand the concepts with examples, and the detailed guide for deeper interview preparation.

Python Variables: Why y = x Sometimes Changes Both Variables

Kailas Warade — Fri, 18 Sep 2026 06:17:31 +0000

Python: Why Changing "y" Can Sometimes Change "x" Too

You write:

x = 10
y = x

Then you change "y".

Sometimes "x" stays the same.

Sometimes "x" changes too.

That sounds strange until you understand what "=" actually does in Python.

First, look at this example

x = 10
y = x

x = 20

print(x)
print(y)

Output:

20
10

Changing "x" did not change "y".

Now look at this:

x = [10, 20, 30]
y = x

y.append(40)

print(x)
print(y)

Output:

[10, 20, 30, 40]
[10, 20, 30, 40]

This time, changing "y" also changed what we see through "x".

Why?

The answer is mutation and reassignment.

"y = x" does not create a copy

When you write:

x = [10, 20, 30]
y = x

Python does not create another list containing the same values.

Both names refer to the same list object.

So when you do:

y.append(40)

you are changing that list.

Since "x" also refers to that same list, "x" now shows:

[10, 20, 30, 40]

A simple way to remember it:

Reassignment → changes what a name refers to.

Mutation → changes the object itself.

Reassignment vs mutation

Consider this:

x = 10
y = x

x = 20

Here, "x" is reassigned.

It now refers to "20".

"y" still refers to "10".

But with a list:

x = [10, 20]
y = x

y.append(30)

"y.append(30)" changes the existing list.

So both "x" and "y" show:

[10, 20, 30]

This difference is one of those small Python concepts that becomes very important when working with real programs.

Mutable and immutable objects

This is where another Python concept becomes useful.

Some objects can be changed after they are created.

These are called mutable objects.

Common examples:

"list"
"dict"
"set"

Some objects cannot be changed in place.

These are called immutable objects.

Common examples:

"int"
"float"
"str"
"tuple"

For example:

numbers = [10, 20, 30]
numbers.append(40)

The existing list is modified.

But:

x = 10
x = 20

does not modify the integer "10".

The name "x" is simply reassigned.

Here is another example that can confuse you

Predict the output before running this:

numbers = [10, 20, 30]

other_numbers = numbers

numbers = [100, 200, 300]

other_numbers.append(40)

print(numbers)
print(other_numbers)

The output is:

[100, 200, 300]
[10, 20, 30, 40]

Why?

Initially:

numbers = [10, 20, 30]
other_numbers = numbers

Both names refer to the first list.

Then:

numbers = [100, 200, 300]

This does not change the old list.

It makes "numbers" refer to a new list.

"other_numbers" is still referring to the original list.

So:

other_numbers.append(40)

changes only the original list.

One more Python variable trap: "input()"

There is another small thing that often causes confusion.

Look at this:

age = input("Enter your age: ")

print(type(age))

If you enter:

the result is:

Even though you entered a number.

"input()" returns a string.

If you actually need an integer:

age = int(input("Enter your age: "))

Now:

print(type(age))

gives:

This matters when you start doing calculations.

For example:

age = input("Enter your age: ")

print(age + 5)

This will not work as expected because "age" is a string.

Instead:

age = int(input("Enter your age: "))

print(age + 5)

Don't confuse "=" with "=="

Another basic mistake is confusing these two operators.

"=" means assignment:

age = 25

"==" means comparison:

age == 25

For example:

age = 25

if age == 25:
print("Age is 25")

A simple rule:

"=" → assign

"==" → compare

Python is dynamically typed

Python variables do not need a type declaration like some other programming languages.

You can write:

age = 25

and later:

age = "25"

The same name can refer to objects of different types at different times.

You can check the current type with:

print(type(age))

This is called dynamic typing.

Choose variable names that explain the value

Technically, this works:

a = 45000
b = 12
c = a / b

But this is easier to understand:

annual_salary = 45000
months = 12
monthly_salary = annual_salary / months

Good variable names make code easier to read, debug, and maintain.

Especially when you come back to your code after a few weeks.

Try these before you run them

Don't run these immediately.

Predict the output first.

Question 1

x = [1, 2, 3]
y = x

y.append(4)

print(x)

What will it print?

Question 2

x = 10
y = x

y = 20

print(x)
print(y)

What will it print?

Question 3

x = [1, 2, 3]
y = x

x = [10, 20, 30]

y.append(4)

print(x)
print(y)

Can you predict both outputs?

If you can explain why the answers are different, you understand the important part.

Want the Python Variable Basics in One Place?

If you're preparing for Python interviews or want a reference for Python syntax, variable naming rules, data types, dynamic typing, multiple assignment, scope, "input()", and related questions, I've collected those topics here:

Python Syntax and Variables Interview Q&A

https://www.sankalandtech.com/Tutorials/Python/interview-questions/python-syntax-variables-tutorial.html

Use it as a reference when you want to revise the broader Python syntax and variable concepts.

Final challenge

What will this program print?

items = ["SQL", "Python"]

copy_of_items = items

items = ["Python", "GCP"]

copy_of_items.append("BigQuery")

print(items)
print(copy_of_items)

Will ""BigQuery"" appear in both lists?

Don't run it immediately.

Think about these three things first:

What object does "items" refer to initially?
What happens when "items" is reassigned?
Which object does "copy_of_items" still refer to?

That's the part of Python variables that is easy to miss when you're just learning the syntax.