🚀 SQL Roadmap 2026 — Part 8

NULL Handling, COALESCE & NULLIF — Managing Missing Data in SQL

In real-world databases, missing data is extremely common.

Customers may not have a phone number.

Orders may not have a discount.

Employees may not have a resignation date.

Transactions may have missing reference values.

SQL uses NULL to represent an unknown or missing value.

Understanding NULL properly is essential for accurate SQL queries and data analysis.

🧠 1. What is NULL?

"NULL" means:

«The value is missing, unknown, or not available.»

Example:

customer_id | customer_name | phone
101         | Alice         | 9876543210
102         | Bob           | NULL
103         | Charlie       | 9123456780

Bob's phone number is not stored.

It does not necessarily mean:

• "0"

• empty string "''"

• "Unknown"

• "N/A"

These are different values.

⚠️ 2. NULL Is Not Equal to 0

SELECT *
FROM customers
WHERE credit_limit = 0;

This finds customers whose credit limit is actually zero.

It will not find customers whose credit limit is missing.

To find missing values:

SELECT *
FROM customers
WHERE credit_limit IS NULL;

⚠️ 3. Never Use = NULL

This is incorrect:

SELECT *
FROM customers
WHERE phone = NULL;

It won't correctly identify NULL values.

Use:

SELECT *
FROM customers
WHERE phone IS NULL;

And for non-NULL values:

SELECT *
FROM customers
WHERE phone IS NOT NULL;

Remember:

= NULL       ❌
<> NULL      ❌
IS NULL      ✅
IS NOT NULL  ✅

🔢 4. NULL in Calculations

Suppose:

order_id | price | discount
1        | 1000  | 100
2        | 800   | NULL

Now:

SELECT
    price,
    discount,
    price - discount AS final_price
FROM orders;

For order 2, the result may be:

NULL

because:

800 - NULL = NULL

SQL generally cannot determine the result when one operand is unknown.

🛠️ 5. COALESCE()

COALESCE() is one of the most important functions for handling NULL values.

It returns the first non-NULL value.

Syntax:

COALESCE(value1, value2, value3, ...)

Example:

SELECT
    customer_name,
    COALESCE(phone, 'Not Available') AS phone
FROM customers;

If "phone" is NULL:

NULL → Not Available

🎯 6. COALESCE with Multiple Values

You can provide several fallback values.

SELECT
    customer_name,
    COALESCE(phone, email, 'No Contact Information') AS contact
FROM customers;

SQL checks in order:

phone
  ↓
email
  ↓
No Contact Information

The first non-NULL value is returned.

💰 7. COALESCE for Financial Calculations

Suppose discounts can be NULL.

Instead of:

SELECT
    price - discount AS final_price
FROM orders;

Use:

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Now a missing discount is treated as zero.

Example:

Price | Discount | Final Price
1000  | 100      | 900
800   | NULL     | 800

This is extremely common in analytics.

📊 8. COALESCE with Aggregations

Suppose there are no matching transactions for a customer.

You may want to display:

0

instead of NULL.

SELECT
    customer_id,
    COALESCE(SUM(amount), 0) AS total_spending
FROM transactions
GROUP BY customer_id;

This makes reports easier to interpret.

🧮 9. NULL and COUNT()

These two queries behave differently:

SELECT COUNT(*)
FROM customers;

Counts all rows.

While:

SELECT COUNT(phone)
FROM customers;

Counts only rows where "phone" is not NULL.

Example:

customer | phone
A        | 12345
B        | NULL
C        | 67890

COUNT(*)     → 3
COUNT(phone) → 2

This difference is frequently tested in interviews.

📈 10. NULL and SUM(), AVG(), MIN(), MAX()

Most aggregate functions ignore NULL values.

Example:

Salary
50000
60000
NULL
70000

Then:

SELECT AVG(salary)
FROM employees;

The NULL salary is generally ignored.

So the average is calculated using:

50000, 60000, 70000

not four values.

Important:

• COUNT(*) counts rows.

• COUNT(column) ignores NULL.

• SUM(), AVG(), MIN(), and MAX() generally ignore NULL values.

🔄 11. NULL with CASE

NULL can be handled using CASE.

SELECT
    customer_name,
    CASE
        WHEN phone IS NULL THEN 'Missing'
        ELSE 'Available'
    END AS phone_status
FROM customers;

Result:

customer | phone_status
Alice    | Available
Bob      | Missing
Charlie  | Available

🧹 12. Handling NULL in Data Cleaning

Suppose customer cities contain missing values.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

This can make reports more readable.

But be careful:

Replacing NULL does not mean the original data wasn't missing.

For analysis, it may still be important to track missingness.

🧨 13. NULLIF()

NULLIF() returns NULL when two expressions are equal.

Syntax:

NULLIF(value1, value2)

Example:

SELECT NULLIF(10, 10);

Result:

NULL

But:

SELECT NULLIF(10, 5);

Result:

10

🚨 14. NULLIF() for Division by Zero

This is one of the most useful real-world applications.

Suppose:

SELECT
    revenue / orders AS revenue_per_order
FROM sales;

If "orders = 0", some database systems will raise a division-by-zero error.

Use:

SELECT
    revenue / NULLIF(orders, 0) AS revenue_per_order
FROM sales;

If:

orders = 0

then:

NULLIF(orders, 0)

returns:

NULL

So the calculation becomes:

revenue / NULL

and returns NULL instead of attempting division by zero.

You can then provide a fallback:

SELECT
    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order
FROM sales;

This combines:

• NULLIF → prevent invalid division

• COALESCE → provide fallback value

🔗 15. NULL in JOINs

NULL becomes especially important with joins.

Suppose:

customers

contains all customers, while:

orders

contains only customers who placed orders.

Using:

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Customers without orders may have:

order_id = NULL

You can identify them with:

WHERE o.order_id IS NULL;

This is a common technique for finding:

«Customers who have never placed an order.»

📦 16. NULL in GROUP BY

NULL values can also appear as a group.

Example:

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

If some employees have no department, the result can contain a group where:

department = NULL

You can make it more readable:

SELECT
    COALESCE(department, 'Unassigned') AS department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY COALESCE(department, 'Unassigned');

↕️ 17. NULL and ORDER BY

NULL sorting behavior can differ between database systems.

For example:

SELECT *
FROM employees
ORDER BY salary DESC;

Depending on the database, NULL values may appear at the beginning or end.

Some systems support:

ORDER BY salary DESC NULLS LAST;

Always check the SQL dialect you're using when NULL ordering matters.

🧠 18. NULL vs Empty String

These are not necessarily the same:

NULL
''

"NULL" means:

«No known value.»

An empty string means:

«A string exists but contains no characters.»

For example:

phone = ''

is different from:

phone IS NULL

This distinction matters during data cleaning.

🏢 19. Real-World Analytics Example

Imagine an e-commerce dataset:

order_id | revenue | discount | shipping_cost
1        | 2000    | 200      | 100
2        | 1500    | NULL     | 80
3        | 3000    | 300      | NULL

Calculate profit safely:

SELECT
    order_id,
    revenue,
    COALESCE(discount, 0) AS discount,
    COALESCE(shipping_cost, 0) AS shipping_cost,
    revenue
        - COALESCE(discount, 0)
        - COALESCE(shipping_cost, 0) AS net_revenue
FROM orders;

This prevents missing values from turning the entire calculation into NULL.

🎯 20. Business KPI Example — Conversion Rate

Suppose:

conversions = 50
visitors = 0

A safe calculation is:

SELECT
    COALESCE(
        conversions * 100.0 / NULLIF(visitors, 0),
        0
    ) AS conversion_rate
FROM marketing;

The logic is:

NULLIF(visitors, 0)
        ↓
Prevents division by zero
        ↓
Returns NULL if visitors = 0
        ↓
COALESCE(..., 0)
        ↓
Displays 0 instead of NULL

This pattern is highly useful for KPI dashboards.

⚠️ Common NULL Mistakes

Mistake 1:

WHERE salary = NULL;

❌ Incorrect

Use:

WHERE salary IS NULL;

Mistake 2:

Assuming NULL means zero.

NULL ≠ 0

Mistake 3:

Ignoring NULL during calculations.

price - discount

may produce NULL when discount is NULL.

Consider:

price - COALESCE(discount, 0)

when treating missing discount as zero is appropriate.

Mistake 4:

Using COALESCE blindly.

Replacing every NULL with "0" can distort analysis.

For example:

Missing salary → 0

does not mean the employee earns zero.

The correct replacement depends on the business meaning of the missing value.

🎤 SQL Interview Questions

Q1. What is NULL?

NULL represents a missing, unknown, or unavailable value.

Q2. How do you check for NULL?

WHERE column_name IS NULL;

Q3. How do you check for non-NULL values?

WHERE column_name IS NOT NULL;

Q4. Why doesn't "= NULL" work?

Because NULL represents an unknown value and comparisons with NULL do not evaluate to TRUE in the normal way. SQL provides IS NULL and IS NOT NULL specifically for this purpose.

Q5. What does COALESCE() do?

It returns the first non-NULL expression.

COALESCE(phone, email, 'No Contact')

Q6. What does NULLIF() do?

It returns NULL when two expressions are equal.

NULLIF(value1, value2)

Q7. Difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows.

COUNT(column) counts non-NULL values in that column.

Q8. How can you prevent division by zero?

revenue / NULLIF(orders, 0)

Q9. Does AVG() normally include NULL values?

No. NULL values are generally ignored when calculating the average.

Q10. What is the difference between NULL and 0?

"0" is an actual numeric value.

"NULL" represents an unknown or missing value.

📝 Practice Questions

Practice 1

Find customers whose email is missing.

SELECT *
FROM customers
WHERE email IS NULL;

Practice 2

Display "Unknown" when a customer's city is NULL.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

Practice 3

Calculate final price assuming a missing discount means zero.

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Practice 4

Calculate revenue per order without dividing by zero.

SELECT
    revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM sales;

Practice 5

Count how many customers have a phone number.

SELECT COUNT(phone) AS customers_with_phone
FROM customers;

🧪 Mini SQL Challenge

You have a table:

sales

sale_id
revenue
discount
orders

Write a query that returns:

• sale_id

• revenue

• discount, treating NULL as 0

• revenue after discount

• revenue per order

• safely handle "orders = 0"

Solution:

SELECT
    sale_id,
    revenue,
    COALESCE(discount, 0) AS discount,

    revenue - COALESCE(discount, 0)
        AS revenue_after_discount,

    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order

FROM sales;

Double Tap ❤️ For Part-9

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥

Want to upgrade your tech skills without spending money?

Here are some excellent FREE YouTube resources to learn high-demand technologies through tutorials and hands-on practice.

🔥 Learn → Practice → Build Projects → Upgrade Your Resume

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

https://pdlink.in/4x3B9hb

🎯 Perfect for Students • Freshers • Job Seekers • Working Professionals

Data analytics is not about the the tools you master but about the people you influence.

I see many debates around the best tools such as:

- Excel vs SQL
- Python vs R
- Tableau vs PowerBI
- ChatGPT vs no ChatGPT

The truth is that business doesn't care about how you come up with your insights.

All business cares about is:

- the story line
- how well they can understand it
- your communication style
- the overall feeling after a presentation

These make the difference in being perceived as a great data analyst...

not the tools you may or may not master 😅

🚀 𝗙𝗥𝗘𝗘 𝗖𝗶𝘁𝗶 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀 😍 | Boost Your Resume

Citi offers virtual experience programs designed to help students and freshers develop job-ready skills through real-world tasks.

✅ 100% FREE
✅ Self-paced learning
✅ Real-world projects
✅ Certificate on completion
✅ Add the experience to your Resume & LinkedIn

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

https://pdlink.in/4zZqJ4U

🔥 Learn → Complete Projects → Earn Certificate → Strengthen Your Resume

🚀 SQL Roadmap 2026 — Part 9

SQL String Functions — Cleaning & Transforming Text Data

In real-world databases, a huge amount of information is stored as text:

• Customer names

• Email addresses

• Phone numbers

• Product names

• Cities

• Categories

• Addresses

• Job titles

But text data is rarely perfectly clean.

You may encounter:

• ' Alice '

• 'alice@example.com'

• 'ALICE@EXAMPLE.COM'

• 'Premium Customer'

• ' Mumbai'

SQL string functions allow you to clean, search, extract, combine, and transform text directly inside your queries.

🧠 1. What Are String Functions?

String functions are SQL functions that operate on text values.

Common functions include:

• LENGTH()

• UPPER()

• LOWER()

• TRIM()

• LTRIM()

• RTRIM()

• SUBSTRING()

• LEFT()

• RIGHT()

• CONCAT()

• REPLACE()

• POSITION()

• CHAR_LENGTH()

Exact function names and syntax can vary slightly between databases such as PostgreSQL, MySQL, SQL Server, and Oracle.

🔠 2. UPPER()

Converts text to uppercase.

SELECT
    customer_name,
    UPPER(customer_name) AS uppercase_name
FROM customers;

Example:

Alice becomes ALICE

Useful for:

• Standardizing text

• Case-insensitive comparisons

• Creating reports

• Data cleaning

🔡 3. LOWER()

Converts text to lowercase.

SELECT
    LOWER(email) AS email
FROM customers;

Example:

ALICE@EXAMPLE.COM becomes alice@example.com

A common data-cleaning pattern is:

SELECT
    LOWER(TRIM(email)) AS cleaned_email
FROM customers;

This handles both unnecessary spaces and inconsistent capitalization.

🧹 4. TRIM()

Removes leading and trailing spaces.

SELECT
    TRIM(customer_name) AS cleaned_name
FROM customers;

For example ' Alice ' becomes 'Alice'

This is extremely useful when importing data from Excel, CSV files, APIs, and external systems.

↩️ 5. LTRIM() and RTRIM()

• LTRIM() removes spaces from the beginning:

  SELECT LTRIM(customer_name) FROM customers;
  

• RTRIM() removes spaces from the end:

  SELECT RTRIM(customer_name) FROM customers;
  

• While TRIM() generally handles both sides:

  SELECT TRIM(customer_name) FROM customers;
  

📏 6. LENGTH()

Returns the number of characters in a string.

SELECT
    customer_name,
    LENGTH(customer_name) AS name_length
FROM customers;

Example:

• Alice → 5

• Robert → 6

Function behavior can vary across SQL dialects, particularly with multibyte characters.

🔍 7. Finding Long or Short Values

String length can be useful for data-quality checks.

Example:

SELECT * FROM customers WHERE LENGTH(phone) < 10;

This can help identify potentially invalid phone numbers.

SELECT * FROM products WHERE LENGTH(product_name) > 100;

This can identify unusually long product descriptions.

✂️ 8. SUBSTRING()

SUBSTRING() extracts part of a string.

A common form is: SUBSTRING(column_name, start_position, length)

SELECT SUBSTRING(customer_name, 1, 3) AS first_three_characters FROM customers;

For Alexander the result would be Ale.

Syntax differs by database, so always check the dialect you're using.

👈 9. LEFT()

Returns characters from the beginning of a string.

SELECT LEFT(product_code, 3) AS category_code FROM products;

If product_code = ELE12345, Result: ELE.

This can be useful when codes contain meaningful prefixes.

👉 10. RIGHT()

Returns characters from the end of a string.

SELECT RIGHT(account_number, 4) AS last_four_digits FROM accounts;

Example: 1234567890, Result: 7890.

This is commonly useful for reporting or identifying records without displaying the complete identifier.

🔗 11. CONCAT()

Combines multiple strings.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

Example: first_name = Alice, last_name = Smith, Result: Alice Smith.

⚠️ 12. CONCAT vs + Operator

Some SQL dialects allow string concatenation using operators such as first_name + ' ' + last_name while others use first_name || ' ' || last_name.

CONCAT() provides a more portable and readable approach, although NULL behavior can still vary by database.

🔄 13. REPLACE()

Replaces one piece of text with another.

SELECT REPLACE(phone, '-', '') AS cleaned_phone FROM customers;

Example: 987-654-3210 becomes 9876543210.

SELECT REPLACE(product_name, 'Old', 'New') AS updated_name FROM products;

📧 14. Extracting Information from Email Addresses

Suppose email = 'alice@gmail.com'. You may want to identify the domain.

One approach is database-specific string manipulation.

For example, in PostgreSQL:

SELECT SPLIT_PART(email, '@', 2) AS email_domain FROM customers;

Result: gmail.com

This is useful for:

• Customer segmentation

• Domain analysis

• Corporate vs personal email analysis

• Detecting invalid domains

📊 15. Grouping Customers by Email Domain

Once you extract the domain, you can aggregate it.

SELECT
    SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS email_domain,
    COUNT(*) AS customer_count
FROM customers
WHERE email IS NOT NULL
GROUP BY SPLIT_PART(LOWER(TRIM(email)), '@', 2)
ORDER BY customer_count DESC;

This combines several concepts:

TRIM() → LOWER() → SPLIT_PART() → GROUP BY → COUNT() → ORDER BY

This is much closer to real-world analytics work.

🔎 16. POSITION()

POSITION() finds where a substring occurs.

SELECT POSITION('@' IN email) AS at_position FROM customers;

For alice@gmail.com it returns the position of @.

This can help identify whether a string contains a particular character.

🧪 17. String Functions for Data Validation

Suppose you want to identify potentially invalid emails.

SELECT * FROM customers WHERE email IS NOT NULL AND POSITION('@' IN email) = 0;

This doesn't prove an email is valid, but it can identify obviously problematic records.

For serious validation, application-level validation or dedicated data-quality tools may be more appropriate.

🏷️ 18. Standardizing Categories

Suppose your database contains Premium, premium, PREMIUM, Premium. These may represent the same business category.

You can standardize them:

SELECT UPPER(TRIM(customer_type)) AS standardized_type FROM customers;

Now they all become PREMIUM.

This is particularly useful before grouping.

📈 19. String Functions + GROUP BY

Without cleaning:

SELECT customer_type, COUNT(*) AS customer_count FROM customers GROUP BY customer_type;

You might get separate groups for Premium, premium, PREMIUM.

Instead:

SELECT UPPER(TRIM(customer_type)) AS customer_type, COUNT(*) AS customer_count 
FROM customers 
GROUP BY UPPER(TRIM(customer_type));

Now logically equivalent values can be grouped together.

🧹 20. Cleaning Product Names

Suppose product names contain unnecessary spaces and inconsistent capitalization.

SELECT UPPER(TRIM(product_name)) AS cleaned_product_name FROM products;

You can also remove unwanted characters:

SELECT REPLACE(TRIM(product_name), '-', ' ') AS cleaned_product_name FROM products;

Example: ' wireless-earbuds ' can become wireless earbuds.

💼 21. Real-World Business Example

Suppose an e-commerce company stores customer names inconsistently.

You have ' alice ', 'ALICE', 'Alice', ' alice'

You can create a normalized version:

SELECT UPPER(TRIM(customer_name)) AS normalized_name FROM customers;

This produces ALICE, ALICE, ALICE, ALICE.

The cleaned value can be used for analysis or as part of a data-matching strategy.

String normalization alone does not guarantee that two records represent the same person.

🧩 22. Combining Multiple String Functions

SQL becomes particularly powerful when functions are combined.

SELECT UPPER(TRIM(customer_name)) AS cleaned_name FROM customers;
SELECT LOWER(TRIM(email)) AS cleaned_email FROM customers;

Think of it as a pipeline:

Raw Data → TRIM() → LOWER()/UPPER() → REPLACE() → Clean Data

⚠️ 23. Common Mistakes

Mistake 1 — Ignoring spaces:

• 'Alice' and ' Alice' may behave as different values depending on the database and comparison context.

• Use TRIM(customer_name) when appropriate.

Mistake 2 — Ignoring capitalization:

• Premium, premium, PREMIUM can create inconsistent groups.

• Use UPPER(TRIM(customer_type)) when the business meaning is case-insensitive.

Mistake 3 — Assuming all databases use the same syntax:

• String functions differ between PostgreSQL, MySQL, SQL Server, and Oracle.

• Always verify the syntax for your SQL dialect.

Mistake 4 — Modifying data unnecessarily:

• There is a difference between SELECT TRIM(name) and actually updating the stored value.

• Always understand whether you're transforming data for analysis or permanently modifying the database.

🎤 SQL Interview Questions

Q1. What is the purpose of string functions?

• They are used to manipulate, clean, transform, search, and extract text data.

Q2. What does TRIM() do?

• It removes leading and trailing spaces from a string.

Q3. Difference between UPPER() and LOWER()?

• UPPER() converts text to uppercase. LOWER() converts text to lowercase.

Q4. What does CONCAT() do?

• It combines multiple strings into one value.

Q5. What does REPLACE() do?

• It replaces occurrences of one substring with another.

Q6. How can you find the length of a string?

• Commonly LENGTH(column_name) or, depending on the database, CHAR_LENGTH(column_name).

Q7. How would you standardize customer categories?

• For example UPPER(TRIM(customer_type)). This removes surrounding spaces and standardizes capitalization.

Q8. How can you extract the last four characters of a value?

• In databases supporting it: RIGHT(column_name, 4).

Q9. How can you combine first and last names?

• CONCAT(first_name, ' ', last_name)

Q10. Why are string functions important for data analysts?

• Because real-world text data often contains inconsistent capitalization, spaces, formats, prefixes, suffixes, and unwanted characters.

📝 Practice Questions

Practice 1: Convert customer names to uppercase.

SELECT UPPER(customer_name) AS customer_name FROM customers;

Practice 2: Remove unnecessary spaces from product names.

SELECT TRIM(product_name) AS product_name FROM products;

Practice 3: Create a full name from first and last name.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

Practice 4: Remove hyphens from phone numbers.

SELECT REPLACE(phone, '-', '') AS cleaned_phone FROM customers;

Practice 5: Find products whose names contain more than 50 characters.

SELECT * FROM products WHERE LENGTH(product_name) > 50;

🧪 Mini SQL Challenge

You have this table:

customers: customer_id, first_name, last_name, email, customer_type, phone

Write a query that returns: Customer ID, Cleaned full name, Cleaned lowercase email, Standardized customer type, Phone number without hyphens.

Solution:

SELECT
    customer_id,
    CONCAT(TRIM(first_name), ' ', TRIM(last_name)) AS full_name,
    LOWER(TRIM(email)) AS cleaned_email,
    UPPER(TRIM(customer_type)) AS customer_type,
    REPLACE(TRIM(phone), '-', '') AS cleaned_phone
FROM customers;

This single query demonstrates a practical data-cleaning workflow using several string functions.

📌 Double Tap ❤️ For More

🚀 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 📊

Want to build a career in Data Analytics but don’t know where to start? Learn the most important skills completely FREE with these expert YouTube resources.

🔥 Learn → Practice → Build Projects → Become Job-Ready

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

https://pdlink.in/4ysm4XS

🎯 Perfect for Students • Freshers • Job Seekers • Aspiring Data Analysts

🚨Here is a comprehensive list of #interview questions that are commonly asked in job interviews for Data Scientist, Data Analyst, and Data Engineer positions:


➡️ Data Scientist Interview Questions


Technical Questions

1) What are your preferred programming languages for data science, and why?

2) Can you write a Python script to perform data cleaning on a given dataset?

3) Explain the Central Limit Theorem.

4) How do you handle missing data in a dataset?

5) Describe the difference between supervised and unsupervised learning.

6) How do you select the right algorithm for your model?


Questions Related To Problem-Solving and Projects

7) Walk me through a data science project you have worked on.

8) How did you handle data preprocessing in your project?

9) How do you evaluate the performance of a machine learning model?

10) What techniques do you use to prevent overfitting?


➡️Data Analyst Interview Questions


Technical Questions

1) Write a SQL query to find the second highest salary from the employee table.

2) How would you optimize a slow-running query?

3) How do you use pivot tables in Excel?

4) Explain the VLOOKUP function.

5) How do you handle outliers in your data?

6) Describe the steps you take to clean a dataset.


Analytical Questions

7) How do you interpret data to make business decisions?

8) Give an example of a time when your analysis directly influenced a business decision.

9) What are your preferred tools for data analysis and why?

10) How do you ensure the accuracy of your analysis?


➡️Data Engineer Interview Questions


Technical Questions

1) What is your experience with SQL and NoSQL databases?

2) How do you design a scalable database architecture?

3) Explain the ETL process you follow in your projects.

4) How do you handle data transformation and loading efficiently?

5) What is your experience with Hadoop/Spark?

6) How do you manage and process large datasets?


Questions Related To Problem-Solving and Optimization

7) Describe a data pipeline you have built.

8) What challenges did you face, and how did you overcome them?

9) How do you ensure your data processes run efficiently?

10) Describe a time when you had to optimize a slow data pipeline.

I have curated Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Hope this helps you 😊

🚀 𝗧𝗔𝗧𝗔 𝗚𝗿𝗼𝘂𝗽 𝗙𝗥𝗘𝗘 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀 😍

Tata Group/TCS virtual job simulations let you work through industry-style tasks and strengthen your resume.

🎓 3 FREE Virtual Programs:
📊 Data Visualisation
🔐 Cybersecurity
🌱 ESG (Environmental, Social & Governance)

💻 Virtual & flexible
🎓 Free Certificate on Completion
📄 Add the experience to your Resume/LinkedIn

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

https://pdlink.in/4yoXEOI

🔥 Perfect for Students • Freshers • Job Seekers

🚀 SQL Roadmap 2026 — Part 10

SQL JOINs — Combining Data from Multiple Tables

In real-world databases, information is rarely stored in one table.

For example: customers, orders, products, payments, employees, departments

A customer may exist in one table while their orders exist in another. JOINs allow us to combine related data from multiple tables. This is one of the most important SQL concepts for a Data Analyst.

🧠 1. Why Do We Need JOINs?

Suppose we have two tables:

customers

customer_id | customer_name
101         | Alice
102         | Bob
103         | Charlie

orders

order_id | customer_id | amount
1        | 101         | 500
2        | 101         | 800
3        | 102         | 300

The customer name is stored in "customers". The order amount is stored in "orders".

To answer:

«How much did each customer spend?»

We need to combine the tables. That's where JOIN comes in.

🔗 2. Basic JOIN Structure

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Here: customers → c, orders → o. These are called table aliases.

The condition: ON c.customer_id = o.customer_id tells SQL how the tables are related.

🔑 3. The JOIN Key

A JOIN usually connects tables through a related column.

customers.customer_id ↓ orders.customer_id

Often: one table contains a primary key, another table contains the corresponding foreign key.

Example: customers.customer_id → Primary Key, orders.customer_id → Foreign Key.

🧩 4. INNER JOIN

"INNER JOIN" returns only rows that have a match in both tables.

SELECT c.customer_name, o.order_id, o.amount 
FROM customers c 
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result:

Alice | 1 | 500
Alice | 2 | 800
Bob   | 3 | 300

Charlie is missing because Charlie has no matching order.

Customers ∩ Orders - Only matching records.

👈 5. LEFT JOIN

"LEFT JOIN" returns: All rows from the left table + matching rows from the right table.

SELECT c.customer_name, o.order_id, o.amount 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result includes:

Charlie | NULL | NULL

🎯 6. Finding Customers Who Never Ordered

This is a very common interview and analytics problem.

SELECT c.customer_id, c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

This technique is often called an anti-join pattern.

👉 7. RIGHT JOIN

"RIGHT JOIN" returns: All rows from the right table + matching rows from the left table.

In practice, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN by switching table order because it is often easier to read.

🔄 8. FULL OUTER JOIN

"FULL OUTER JOIN" returns: All rows from both tables, whether they match or not.

Conceptually:

LEFT JOIN + RIGHT JOIN

It can reveal: matching records, customers without orders, orders without matching customers.

⚠️ Not every database supports "FULL OUTER JOIN" directly.

🆚 9. INNER JOIN vs LEFT JOIN

• INNER JOIN = Returns only customers with matching orders.

• LEFT JOIN = Returns all customers, including those without orders.

Simple rule:

INNER JOIN = matching records,

LEFT JOIN = keep everything from the left table.

📊 10. JOIN + Aggregation

Question:

«How much has each customer spent?»

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
INNER JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

💰 11. Include Customers with Zero Spending

SELECT c.customer_id, c.customer_name, COALESCE(SUM(o.amount), 0) AS total_spending 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

🔢 12. JOIN + COUNT()

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Why COUNT(o.order_id) instead of COUNT(*)?

Because COUNT(*) would count the LEFT JOIN row even when the customer has no matching order.

⚠️ 13. A Very Common JOIN Mistake

SELECT ... WHERE o.amount > 500; -- This removes NULLs and behaves like INNER JOIN

Correct:

LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.amount > 500;

Important concept: With an OUTER JOIN, the location of a filter can change the result.

🔗 14. Joining More Than Two Tables

SELECT c.customer_name, o.order_id, p.product_name, o.amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN products p ON o.product_id = p.product_id;

🏢 15. Real-World Business Example

SELECT p.category, SUM(o.amount) AS total_revenue 
FROM orders o 
JOIN products p ON o.product_id = p.product_id 
GROUP BY p.category 
ORDER BY total_revenue DESC;

This is a typical Data Analyst query.

📈 16. JOIN + WHERE + GROUP BY + HAVING

Question:

«Find customers who spent more than ₹50,000.»

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name 
HAVING SUM(o.amount) > 50000 
ORDER BY total_spending DESC;

Logical flow: JOIN → GROUP BY → HAVING → ORDER BY

🪞 17. SELF JOIN

A table can also be joined to itself.

SELECT e.employee_name AS employee, m.employee_name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.employee_id;

🔢 18. CROSS JOIN

"CROSS JOIN" produces every possible combination of rows.

5 products x 4 regions = 20 rows

🚨 19. The Biggest JOIN Problem: Duplicate Rows

One customer has five orders → customer appears five times. This is the natural result of a one-to-many relationship.

If you want unique customers:

SELECT COUNT(DISTINCT c.customer_id)

⚠️ 20. Double Counting in Multiple JOINs

If both "orders" and "payments" have multiple rows per customer, joining them directly can create a many-to-many multiplication.

Example: 2 orders × 3 payments = 6 joined rows. SUM() will overcount.

Understand the grain of each table before joining.

🧠 21. JOINs and Table Grain

Before writing a JOIN, identify:

Table 1 - One row = one customer,

Table 2 - One row = one order → One-to-Many relationship.

Understanding table grain helps prevent: duplicate counts, inflated revenue, incorrect averages, incorrect KPIs.

🎤 SQL Interview Questions

Q1. What is a JOIN?

Combines rows from multiple tables using a related condition.

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER returns only matching, LEFT returns all from left + matching from right.

Q3. How do you find customers who never placed an order?

LEFT JOIN + WHERE o.customer_id IS NULL

Q4. What is a SELF JOIN?

Joins a table to itself, for hierarchical relationships.

Q5. What is a CROSS JOIN?

Creates every possible combination.

Q6. Why can JOINs create duplicate rows?

Because of one-to-many or many-to-many relationships.

Q7. Why should you understand table grain?

Because grain determines how rows multiply and whether aggregations become inaccurate.

Q8. What happens when there is no match in a LEFT JOIN?

Columns from right become NULL.

Q9. How do you count unique customers after a JOIN?

COUNT(DISTINCT customer_id)

Q10. Can a query contain multiple JOINs?

Yes.

📝 Practice Questions

Practice 1: Return customer names and their orders.

SELECT c.customer_name, o.order_id 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

Practice 2: Find customers who have never ordered.

SELECT c.customer_id, c.customer_name 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
WHERE o.customer_id IS NULL;

Practice 3: Calculate total spending per customer.

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 4: Return all customers and their order counts, including zero orders.

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.customer_name;

Practice 5: Find number of unique customers who placed orders.

SELECT COUNT(DISTINCT c.customer_id) AS unique_customers 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id;

🧪 Mini SQL Challenge

Write a query that returns: Customer name, Product name, Category, Amount - Only orders > ₹1,000.

Solution:

SELECT c.customer_name, p.product_name, p.category, o.amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN products p ON o.product_id = p.product_id 
WHERE o.amount > 1000 
ORDER BY o.amount DESC;

📌 JOINs are the bridge between database tables. But writing a JOIN is only half the skill. A strong Data Analyst also understands: What each table represents → How tables are related → How rows will multiply → How that affects the KPI.

Double Tap ❤️ For More

🚀 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗟𝗮𝗻𝗱 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯𝘀 𝗶𝗻 𝟮𝟬𝟮𝟲😍

💰 Highest Salary: ₹41 LPA
📈 Average Salary: ₹7.4 LPA
🎓 2,000+ Students Placed
🏢 500+ Hiring Partners

💻 Full Stack :- https://pdlink.in/3SuUeuD

📊 Data Analytics :- https://pdlink.in/45vk5ph

💫AI Engineering :- https://pdlink.in/4fWJVID

🔥 Take the first step towards your high-paying tech career in 2026!