SQL Functions Cheat Sheet

SQL functions are powerful tools for performing operations on data in a relational database. Here’s a cheat sheet for some commonly used SQL functions:

Aggregate Functions

SUM():

SELECT SUM(column) FROM table;

AVG():

SELECT AVG(column) FROM table;

COUNT():

SELECT COUNT(column) FROM table;

MIN():

SELECT MIN(column) FROM table;

MAX():

SELECT MAX(column) FROM table;

String Functions

CONCAT():

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

SUBSTRING():

SELECT SUBSTRING(column, start_position, length) FROM table;

UPPER():

SELECT UPPER(column) FROM table;

LOWER():

SELECT LOWER(column) FROM table;

LENGTH():

SELECT LENGTH(column) FROM table;

Date and Time Functions

NOW():

SELECT NOW() AS current_time;

DATE():

SELECT DATE(column) FROM table;

CURDATE():

SELECT CURDATE() AS current_date;

DATEDIFF():

SELECT DATEDIFF(end_date, start_date) FROM table;

Mathematical Functions

ROUND():

SELECT ROUND(column, decimals) FROM table;

CEIL() or CEILING():

SELECT CEIL(column) FROM table;

FLOOR():

SELECT FLOOR(column) FROM table;

ABS():

SELECT ABS(column) FROM table;

Conditional Functions

CASE WHEN:

SELECT 
    column,
    CASE 
        WHEN condition THEN 'Result1'
        WHEN another_condition THEN 'Result2'
        ELSE 'DefaultResult'
    END AS result_column
FROM table;

COALESCE():

SELECT COALESCE(column1, column2, 'Default') FROM table;

Conversion Functions

CAST():

SELECT CAST(column AS data_type) FROM table;

CONVERT():

SELECT CONVERT(column, data_type) FROM table;

Window Functions

ROW_NUMBER():

SELECT column, ROW_NUMBER() OVER (ORDER BY column) AS row_num FROM table;

RANK():

SELECT column, RANK() OVER (ORDER BY column) AS rank FROM table;

DENSE_RANK():

SELECT column, DENSE_RANK() OVER (ORDER BY column) AS dense_rank FROM table;

Depending on your database system (e.g., MySQL, PostgreSQL, SQL Server), some functions may have variations in syntax or additional functionalities. Always refer to the documentation of your specific database system for the most accurate and detailed information.