What SQL functions should you know for your next database development interview?

1 Aggregate functions

Aggregate functions are SQL functions that perform calculations on a set of values and return a single value. For example, you can use aggregate functions to find the sum, average, minimum, maximum, count, or standard deviation of a column or a group of rows. Aggregate functions are often used with the GROUP BY clause to group data by certain criteria and apply calculations to each group. For example, you can use the following query to find the average salary of employees by department:

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;        

Some of the most common aggregate functions are SUM, AVG, MIN, MAX, COUNT, and STDDEV.

2 String functions

String functions are SQL functions that manipulate or extract information from character data. For example, you can use string functions to concatenate, split, trim, replace, or format strings. String functions are often used to clean, transform, or validate data. For example, you can use the following query to extract the first name and last name from a full name column and concatenate them with a comma:

SELECT CONCAT(SUBSTRING(full_name, 1, CHARINDEX(' ', full_name) - 1), ', ', SUBSTRING(full_name, CHARINDEX(' ', full_name) + 1, LEN(full_name))) AS name
FROM customers;        

Some of the most common string functions are CONCAT, SUBSTRING, CHARINDEX, LEN, TRIM, REPLACE, and FORMAT.

3 Date and time functions

Date and time functions are SQL functions that manipulate or extract information from date and time data. For example, you can use date and time functions to add, subtract, or compare dates and times, or to extract parts of a date or time, such as the year, month, day, hour, minute, or second. Date and time functions are often used to filter, sort, or group data by date or time intervals. For example, you can use the following query to find the number of orders placed in each month of the year 2020:

SELECT DATEPART(month, order_date) AS month, COUNT(*) AS orders
FROM orders
WHERE DATEPART(year, order_date) = 2020
GROUP BY DATEPART(month, order_date)
ORDER BY month;        

Some of the most common date and time functions are DATEADD, DATEDIFF, DATEPART, GETDATE, and CONVERT.

5 Conditional functions

Conditional functions are SQL functions that return different values based on certain conditions. For example, you can use conditional functions to perform logical tests, case conversions, or null handling. Conditional functions are often used to control the flow of queries, create dynamic columns, or handle exceptions. For example, you can use the following query to create a column that indicates whether an employee is eligible for a bonus based on their performance rating:

SELECT name, performance_rating, CASE
  WHEN performance_rating >= 4 THEN 'Yes'
  WHEN performance_rating >= 3 THEN 'Maybe'
  ELSE 'No'
END AS bonus_eligibility
FROM employees;        

Some of the most common conditional functions are IF, CASE, COALESCE, NULLIF, and ISNULL.

6 Conversion functions

Conversion functions are SQL functions that convert data from one data type to another. For example, you can use conversion functions to convert numbers to strings, strings to dates, dates to numbers, or binary to hexadecimal. Conversion functions are often used to change the format, display, or compatibility of data. For example, you can use the following query to convert a date column to a string in the format of YYYY-MM-DD:

SELECT CONVERT(varchar(10), date_column, 120) AS date_string
FROM table;        

Some of the most common conversion functions are CAST, CONVERT, PARSE, TRY_CAST, TRY_CONVERT, and TRY_PARSE.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?