If you work with data, SQL functions are your toolkit for unlocking powerful insights. Whether you’re crafting detailed reports or uncovering business-changing analytics, SQL is the backbone for data doers in every industry. This guide explores 27 SQL functions that every data analyst, data engineer, or analytics enthusiast should know. These functions are grouped into five categories for easier understanding and practical use.
Table of Contents
- Essential Querying Functions
- Data Aggregation and Summarization
- Data Transformation and Type Handling
- Advanced Analytical Tools
- Date and Time Functions
Category 1: Essential Querying Functions
1. SELECT
The SELECT statement retrieves data from one or more tables. For example:
SELECT customer_name, customer_email
FROM customers;
This query extracts specific columns (customer_name and customer_email) from the customers table.
2. WHERE
The WHERE clause filters records based on specified conditions.
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 3 AND salary > 50000;
Filters employees in department 3 earning more than $50,000.
3. ORDER BY
Sorts the result set by one or more columns.
SELECT * FROM employees
ORDER BY salary DESC, last_name ASC;
Sorts employees first by descending salary and then by last name in ascending order.
4. LIKE
Searches for patterns in strings using wildcards.
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
Finds all employees whose last name starts with ‘S’.
5. IN
Filters rows based on a set of specified values.
SELECT * FROM customers
WHERE customer_city IN ('New York', 'London', 'Paris');
Fetches customers from specified cities.
Category 2: Data Aggregation and Summarization
6. COUNT()
Counts the number of rows that meet a condition.
SELECT COUNT(order_id) AS order_count
FROM orders
WHERE customer_id = 100;
Counts orders placed by customer ID 100.
7. SUM()
Calculates the sum of a numeric column.
SELECT SUM(quantity * price) AS total_revenue
FROM sales;
Calculates total revenue from sales.
8. AVG()
Finds the average of a numeric column.
SELECT AVG(sale_amount) AS average_sale
FROM sales
WHERE product_id = 100;
Finds the average sale amount for product ID 100.
9. MIN() and MAX()
Retrieve the smallest or largest value in a column.
SELECT MIN(product_price) AS lowest_price
FROM products;
Finds the lowest product price.
10. GROUP BY
Groups rows for aggregation.
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id;
Calculates total spending by each customer.
11. HAVING
Filters aggregated results.
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;
Finds customers who spent over $1,000.
Category 3: Data Transformation and Type Handling
12. DISTINCT
Removes duplicate values from the result set.
SELECT DISTINCT customer_city
FROM customers;
Lists all unique customer cities.
13. CASE WHEN
Creates conditional logic in queries.
SELECT product_id,
CASE
WHEN price < 50 THEN 'Low'
WHEN price BETWEEN 50 AND 100 THEN 'Medium'
ELSE 'High'
END AS price_category
FROM products;
Classifies products into price categories (Low, Medium, High) based on their price.
14. ISNULL()
Replaces NULL with a specified value.
SELECT product_id, ISNULL(discount_price, 0) AS final_price
FROM products;
If discount_price is NULL, it replaces it with 0.
15. CAST
Converts a value from one data type to another.
SELECT CAST(price AS decimal(10,2)) AS numeric_price
FROM products;
Converts the price column from text to a numeric format with 2 decimal places.
16. COALESCE
Returns the first non-null value.
SELECT COALESCE(email, phone) AS contact
FROM employees;
Provides email as the primary contact and phone as a fallback.
17. CONCAT
Concatenates strings.
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;
Combines first and last names into full names.
Category 4: Advanced Analytical Tools
18. JOIN
Combines rows from two or more tables.
SELECT customers.customer_name, orders.order_total
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
Fetches customer names along with their order totals.
19. UNION
Combines the result sets of multiple SELECT statements.
SELECT name FROM customers
UNION
SELECT name FROM employees;
Lists unique names from both customers and employees.
20. PARTITION BY
Divides a result set into partitions for calculations.
SELECT region, SUM(sales) OVER (PARTITION BY region) AS total_sales
FROM sales;
Calculates total sales for each region.
21. RANK
Assigns a rank to rows based on a column’s values.
SELECT region, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM sales;
Ranks regions by sales amount.
22. LEAD and LAG
Access rows before or after the current row.
SELECT region, year, sales,
LAG(sales) OVER (PARTITION BY region ORDER BY year) AS previous_sales
FROM sales;
Finds the sales amount for the previous year.
23. ROUND
Rounds numeric values to a specified precision.
SELECT ROUND(sales_amount, 2) AS rounded_sales
FROM sales;
Rounds sales amounts to two decimal places.
Category 5: Date and Time Functions
24. DATEPART
Extracts parts of a date.
SELECT DATEPART(year, order_date) AS order_year
FROM orders;
Extracts the year from the order_date.
25. DATEADD
Adds or subtracts time intervals from a date.
SELECT DATEADD(week, 1, order_date) AS shipping_date
FROM orders;
Calculates shipping date as one week after the order date.
26. DATEDIFF
Calculates the difference between two dates.
SELECT DATEDIFF(day, order_date, ship_date) AS days_to_ship
FROM orders;
Finds the number of days between order_date and ship_date.
27. ISNULL()
Handles missing dates by replacing NULL with a default.
SELECT order_id, ISNULL(ship_date, GETDATE()) AS effective_ship_date
FROM orders;
Replaces a missing shipping date with the current date.
Conclusion
Mastering these 27 SQL functions will elevate your data analytics skills. From essential querying to advanced analytics and date handling, these tools ensure you can extract, analyze, and interpret data effectively. Save this list as your go-to reference for SQL success!