Session 03 · Phase 1: Foundations & SQL

SQL Functions — String, Date, Numeric & CASE

Functions let you transform data inside a query — cleaning text, rounding numbers, extracting dates, and creating conditional categories with CASE WHEN.

⏱ ~2 hrs 📚 Core content 🎯 High priority

Learning Objectives

Transform text with UPPER, LOWER, CONCAT, LENGTH, TRIM, and SUBSTRING.
Round and adjust numbers with ROUND, FLOOR, CEIL, and ABS.
Extract and format date parts with YEAR, MONTH, DATE_TRUNC, and DATE_FORMAT.
Create conditional logic with CASE WHEN (and the simple CASE form).
Replace or handle NULL values with COALESCE and NULLIF.

0. The Sample Table (recap)

We reuse the same customers table from Session 02.

customer_id	customer_name	city	account_type	balance	join_date
1	Aarav Sharma	Mumbai	Savings	25000	2023-01-15
2	Priya Verma	Delhi	Current	120000	2022-06-30
3	Rahul Mehta	Mumbai	Savings	8000	2024-03-10
4	Sneha Iyer	Bengaluru	Salary	95000	2021-11-22
5	Vikram Singh	Delhi	Current	45000	2023-09-05
6	Ananya Das	Chennai	Savings	15000	2022-02-14

1. String Functions

String functions clean and reshape text — vital for standardizing names, joining text, and preparing data for reports.

Function	Purpose	Example	Result
`UPPER(text)`	to uppercase	`UPPER('abc')`	`ABC`
`LOWER(text)`	to lowercase	`LOWER('ABC')`	`abc`
`LENGTH(text)`	number of characters	`LENGTH('abc')`	`3`
`TRIM(text)`	remove surrounding spaces	`TRIM(' hi ')`	`hi`
`CONCAT(a, b)`	join strings	`CONCAT('a','b')`	`ab`
`SUBSTRING(text, s, n)`	extract part of text	`SUBSTRING('hello',1,2)`	`he`

-- Show names in uppercase
SELECT UPPER(customer_name) AS name_upper
FROM customers;

-- Combine name and city into one column
SELECT CONCAT(customer_name, ' - ', city) AS customer_location
FROM customers;

-- Find customers whose name is longer than 11 characters
SELECT customer_name, LENGTH(customer_name) AS name_length
FROM customers
WHERE LENGTH(customer_name) > 11;

📝

Note (dialect differences): LENGTH is LEN in SQL Server; SUBSTRING is SUBSTR in Oracle; and SQL Server concatenates with + instead of CONCAT. Interviewers like to check you know these differ by database.

2. Numeric Functions

Numeric functions round and shape numbers for cleaner reporting.

Function	Purpose	Example	Result
`ROUND(n, d)`	round to d decimals	`ROUND(4.567, 2)`	`4.57`
`FLOOR(n)`	round down	`FLOOR(4.9)`	`4`
`CEIL(n)`	round up	`CEIL(4.1)`	`5`
`ABS(n)`	absolute value	`ABS(-10)`	`10`

-- Round balance to the nearest thousand
SELECT customer_name, ROUND(balance, -3) AS balance_rounded
FROM customers;

-- Absolute value of a negative balance (e.g. for overdrafts)
SELECT customer_name, ABS(balance) AS abs_balance
FROM customers;

💡

Pro Tip: ROUND(balance, -3) uses a negative precision to round to the nearest thousand — a neat trick interviewers rarely expect a fresher to know.

3. Date Functions

Dates power almost every business report — monthly sales, signups per year, day-of-week analysis. The exact syntax varies the most between databases.

Extract a part of a date

-- MySQL / SQLite style
SELECT customer_name, YEAR(join_date) AS join_year, MONTH(join_date) AS join_month
FROM customers;

-- PostgreSQL style (uses EXTRACT)
SELECT customer_name, EXTRACT(YEAR FROM join_date) AS join_year
FROM customers;

Group by month (for monthly reports)

-- PostgreSQL: DATE_TRUNC
SELECT DATE_TRUNC('month', join_date) AS join_month, COUNT(*) AS customers
FROM customers
GROUP BY DATE_TRUNC('month', join_date)
ORDER BY join_month;

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(join_date, '%Y-%m') AS join_month, COUNT(*) AS customers
FROM customers
GROUP BY DATE_FORMAT(join_date, '%Y-%m')
ORDER BY join_month;

⚠️

Warning: Date functions are the least portable part of SQL. DATE_TRUNC is PostgreSQL, DATE_FORMAT is MySQL, DATETRUNC/FORMAT is SQL Server. In an interview, name the database you're assuming before you write the query.

4. CASE WHEN — Conditional Logic

CASE is SQL's if/else. It evaluates conditions in order and returns a value from the first one that matches. It's how you create categories and segments.

Searched CASE (most common)

-- Categorize customers by balance
SELECT customer_name,
       balance,
       CASE
           WHEN balance >= 100000 THEN 'High'
           WHEN balance >= 30000  THEN 'Medium'
           ELSE 'Low'
       END AS balance_category
FROM customers;

Simple CASE (comparing one column to fixed values)

-- Abbreviate account types
SELECT customer_name,
       account_type,
       CASE account_type
           WHEN 'Savings' THEN 'S'
           WHEN 'Current' THEN 'C'
           WHEN 'Salary'  THEN 'SA'
           ELSE 'Other'
       END AS type_code
FROM customers;

🌐

Real World: "Segment our customers into High / Medium / Low value" is one of the most common real analyst tasks — and it's almost always done with a CASE WHEN.

📝

Note: CASE stops at the first matching condition, so the order of your WHEN clauses matters. Put the most specific (or highest) threshold first.

5. Handling NULL — COALESCE and NULLIF

COALESCE — return the first non-NULL value

-- Replace missing city with 'Unknown'
SELECT customer_name, COALESCE(city, 'Unknown') AS city
FROM customers;

COALESCE(a, b, c) returns the first argument that is not NULL.

NULLIF — return NULL when two values are equal

-- Avoid division-by-zero: NULLIF returns NULL if denominator is 0
SELECT customer_name,
       balance / NULLIF(0, 0) AS safe_ratio
FROM customers;

NULLIF(x, y) returns NULL if x = y, otherwise returns x. It's commonly used to prevent divide-by-zero errors.

📋 Stable content — Reviewed: August 2026

6. Interview Questions (with Model Answers)

The most commonly asked SQL functions questions for Data Analyst freshers. Answer each one yourself before opening the reveal.

IQ1. What is the CASE statement, and when would you use it?

Why they ask: It tests your ability to create conditional logic — a core analyst skill.

Model answer: "CASE is SQL's way of writing if/else logic. It checks conditions in order and returns a value from the first one that matches. I use it to categorize data — for example, classifying customers into High, Medium, or Low value based on their balance."

IQ2. How do you handle NULL values in SQL?

Why they ask: Missing data is everywhere in real analysis — they want to know you can deal with it.

Model answer: "First, I'd understand why values are missing before changing anything. Then I can check for them with IS NULL, and either replace them with a default using COALESCE(column, 'Unknown'), or filter them out with IS NOT NULL."

IQ3. What string functions have you used? Give an example.

Why they ask: Data cleaning — names, addresses, categories — depends on string manipulation.

Model answer: "I commonly use UPPER/LOWER to standardize text, TRIM to remove stray spaces, CONCAT to combine columns, LENGTH to check text size, and SUBSTRING to extract parts. For example, I'd use UPPER(TRIM(customer_name)) to make names consistent before matching."

IQ4. What is the difference between ROUND, FLOOR, and CEIL?

Why they ask: A quick check of your numeric-function vocabulary.

Model answer: "ROUND rounds to the nearest value, FLOOR always rounds down, and CEIL always rounds up. So ROUND(4.5)=5, FLOOR(4.9)=4, and CEIL(4.1)=5."

IQ5. How do you calculate monthly sales using SQL?

Why they ask: Monthly/period reporting is a bread-and-butter analyst task.

Model answer: "I'd group by the month extracted from the date. The exact function depends on the database — in PostgreSQL I'd use DATE_TRUNC('month', order_date), and in MySQL DATE_FORMAT(order_date, '%Y-%m'), then SUM the sales for each month."

IQ6. What is the difference between COALESCE and NULLIF?

Why they ask: Both handle NULL but in opposite directions — a good conceptual test.

Model answer: "COALESCE returns the first non-NULL value from a list, so it's used to fill in missing values. NULLIF returns NULL when two values are equal — I use it to avoid divide-by-zero errors."

IQ7. What is the difference between CHAR and VARCHAR?

Why they ask: A fundamental data-type question that shows you understand storage.

Model answer: "CHAR(n) is fixed-length and pads shorter values with spaces, while VARCHAR(n) is variable-length and only uses the space it needs. For most text columns like names or cities, VARCHAR is the better choice."

IQ8. How would you categorize customers into segments?

Why they ask: A practical, scenario-based question — the interviewer wants to see you apply CASE.

Model answer: "I'd use a CASE WHEN on a metric like balance or total spend. For example, CASE WHEN balance >= 100000 THEN 'High' WHEN balance >= 30000 THEN 'Medium' ELSE 'Low' END. The result becomes a new column I can group and visualize."

💡

Remember: When a question involves a function, always state the database you're assuming. Saying "in MySQL I'd use X, in PostgreSQL Y" instantly signals you've used SQL in real projects.

Hands-On Project: Transform and Segment the Customers

Using the customers table, write a query for each task. Predict the output before you run it.

Steps

List all customer names in lowercase.
Return a single column combining name and city, e.g. Aarav Sharma - Mumbai.
Show each customer's join year and join month.
Add a column balance_group that labels balances ≥ 100000 as "High", ≥ 30000 as "Medium", else "Low".
Show each customer's balance rounded down to the nearest thousand.

View Solution / Walkthrough

-- 1. Names in lowercase
SELECT LOWER(customer_name) AS name_lower
FROM customers;

-- 2. Combine name and city
SELECT CONCAT(customer_name, ' - ', city) AS customer_location
FROM customers;

-- 3. Join year and month (MySQL style)
SELECT customer_name, YEAR(join_date) AS yr, MONTH(join_date) AS mth
FROM customers;

-- 4. Balance group
SELECT customer_name, balance,
       CASE
           WHEN balance >= 100000 THEN 'High'
           WHEN balance >= 30000  THEN 'Medium'
           ELSE 'Low'
       END AS balance_group
FROM customers;
-- → Priya Verma: High | Sneha Iyer: Medium | Vikram Singh: Medium | rest: Low

-- 5. Balance rounded down to nearest thousand
SELECT customer_name, balance, FLOOR(balance / 1000) * 1000 AS balance_floor
FROM customers;

Key Takeaways

String functions (UPPER, TRIM, CONCAT, SUBSTRING) clean and reshape text.

ROUND rounds to nearest, FLOOR down, CEIL up, ABS gives magnitude.

Date functions are the least portable part of SQL — always name your database.

CASE WHEN is your if/else — order matters; the first match wins.

COALESCE fills NULLs; NULLIF creates them (e.g. to avoid divide-by-zero).

Learning Objectives

0. The Sample Table (recap)

1. String Functions

2. Numeric Functions

3. Date Functions

Extract a part of a date

Group by month (for monthly reports)

4. CASE WHEN — Conditional Logic

Searched CASE (most common)

Simple CASE (comparing one column to fixed values)

5. Handling NULL — COALESCE and NULLIF

COALESCE — return the first non-NULL value

NULLIF — return NULL when two values are equal

6. Interview Questions (with Model Answers)

Hands-On Project: Transform and Segment the Customers

Steps

Key Takeaways

Objective Questions — Test Your Understanding