Top SQL Functions You Must Know for Interviews With Examples

If you are learning SQL or preparing for interviews, you must know SQL functions.

These functions help you:

• Work with data easily

• Write better queries

• Solve real problems

• Answer interview questions

In this post, I will explain important SQL functions in very simple English with examples.

1. Window Functions (Very Important)

Window functions work on a group of rows but still show each row.

ROW_NUMBER()

This gives a number to each row.

SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Use this when you need ranking or pagination.

RANK()

This gives rank but skips numbers if values are the same.

Example: 1, 2, 2, 4

DENSE_RANK()

This is like RANK but does not skip numbers.

Example: 1, 2, 2, 3

LAG() and LEAD()

These help you get previous or next row values.

SELECT name, salary, LAG(salary) OVER (ORDER BY salary)
FROM employees;

Use this to compare data.

FIRST_VALUE() and LAST_VALUE()

These give the first or last value in a group.

NTILE()

This divides data into equal groups.

Example: top 25%, bottom 25%

2. Aggregate Functions

These are the most used SQL functions.

SUM()

SELECT SUM(salary) FROM employees;

AVG()

SELECT AVG(age) FROM users;

COUNT()

SELECT COUNT(*) FROM employees;

MIN() and MAX()

Find smallest and largest values.

STRING_AGG()

Combine many rows into one string.

3. String Functions

These work with text.

CONCAT()

SELECT CONCAT(first_name, ' ', last_name)
FROM users;

SUBSTRING()

Get part of a string.

REPLACE()

Replace text in a string.

TRIM()

Remove extra spaces.

UPPER() and LOWER()

Change text to uppercase or lowercase.

LENGTH()

Find the length of a string.

CHARINDEX()

Find the position of a word in text.

4. Date Functions

Very useful in real projects.

NOW()

Get current date and time.

DATEADD()

SELECT DATEADD(day, 7, order_date)
FROM orders;

Adds days, months, or years.

DATEDIFF()

Find difference between two dates.

YEAR(), MONTH(), DAY()

Get parts of a date.

5. Math Functions

ROUND()

Round numbers.

CEILING() and FLOOR()

Round up or down.

ABS()

Get positive value.

POWER()

Raise number to power.

SQRT()

Square root.

MOD()

Get remainder.

6. Conditional Functions

Very important for interviews.

CASE WHEN

SELECT name,
CASE WHEN score >= 90 THEN 'A'
ELSE 'B'
END AS grade
FROM students;

COALESCE()

Return first non-null value.

NULLIF()

Return NULL if two values are equal.

ISNULL()

Replace NULL values.

7. Data Transformation

PIVOT / UNPIVOT

Convert rows into columns.

Used in reports.

CAST() / CONVERT()

Change data type.

8. CTE (Common Table Expression)

WITH Clause

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2025-01-01'
)
SELECT * FROM recent_orders;

Recursive CTE

Used for hierarchy data.

9. Advanced Queries

Subqueries

Query inside another query.

EXISTS

Check if data exists.

Faster than IN in many cases.

EXCEPT and INTERSECT

Compare results of two queries.

10. System Functions

CURRENT_USER

Get current user name.

DB_NAME()

Get database name.

SYSTEM_USER

Get login user.

Why These Functions Matter

In interviews, companies want to see:

• Can you write SQL clearly

• Can you solve problems

• Do you understand data
  

If you know these functions, you can answer most SQL questions.

Simple Tips for Interviews

• Write clean queries

• Explain your logic

• Use window functions

• Avoid complex queries if simple works

• Practice daily