Ultimate SQL Functions Cheat Sheet For Quick Reference
Table of Contents :
SQL (Structured Query Language) is an essential tool for managing and manipulating databases. Whether you're a beginner or an experienced developer, having a cheat sheet handy can enhance your productivity and streamline your workflow. In this blog post, we will provide a comprehensive SQL functions cheat sheet that covers a variety of SQL functions, their syntax, and practical examples. 📊
What are SQL Functions?
SQL functions are predefined routines in SQL that perform operations on data. They can return a single value, a table, or modify data within a database. SQL functions are categorized into several types, such as aggregate functions, string functions, date functions, and numeric functions.
Categories of SQL Functions
To make the cheat sheet easy to understand, we can categorize SQL functions as follows:
- Aggregate Functions: Operate on a set of values and return a single value.
- String Functions: Operate on string data types to perform operations like concatenation or substring.
- Date Functions: Manipulate date and time values.
- Numeric Functions: Perform mathematical operations.
SQL Aggregate Functions
Aggregate functions perform calculations on multiple rows of a table's column and return a single value. Here’s a table summarizing the most commonly used aggregate functions:
<table> <tr> <th>Function</th> <th>Description</th> <th>Syntax</th> <th>Example</th> </tr> <tr> <td>COUNT()</td> <td>Counts the number of rows in a set</td> <td>COUNT(column_name)</td> <td>SELECT COUNT(*) FROM employees;</td> </tr> <tr> <td>SUM()</td> <td>Returns the sum of a set</td> <td>SUM(column_name)</td> <td>SELECT SUM(salary) FROM employees;</td> </tr> <tr> <td>AVG()</td> <td>Returns the average value of a set</td> <td>AVG(column_name)</td> <td>SELECT AVG(salary) FROM employees;</td> </tr> <tr> <td>MAX()</td> <td>Returns the maximum value in a set</td> <td>MAX(column_name)</td> <td>SELECT MAX(salary) FROM employees;</td> </tr> <tr> <td>MIN()</td> <td>Returns the minimum value in a set</td> <td>MIN(column_name)</td> <td>SELECT MIN(salary) FROM employees;</td> </tr> </table>
Important Note: Aggregate functions ignore null values unless specified otherwise.
SQL String Functions
String functions help manipulate string data types, enabling you to perform operations like changing case, trimming whitespace, or concatenating strings. Below is a summary of commonly used string functions:
<table> <tr> <th>Function</th> <th>Description</th> <th>Syntax</th> <th>Example</th> </tr> <tr> <td>CONCAT()</td> <td>Concatenates two or more strings</td> <td>CONCAT(string1, string2, ...)</td> <td>SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;</td> </tr> <tr> <td>LENGTH()</td> <td>Returns the length of a string</td> <td>LENGTH(string)</td> <td>SELECT LENGTH(first_name) FROM employees;</td> </tr> <tr> <td>UPPER()</td> <td>Converts a string to uppercase</td> <td>UPPER(string)</td> <td>SELECT UPPER(first_name) FROM employees;</td> </tr> <tr> <td>LOWER()</td> <td>Converts a string to lowercase</td> <td>LOWER(string)</td> <td>SELECT LOWER(first_name) FROM employees;</td> </tr> <tr> <td>SUBSTRING()</td> <td>Extracts a substring from a string</td> <td>SUBSTRING(string, start_position, length)</td> <td>SELECT SUBSTRING(first_name, 1, 3) FROM employees;</td> </tr> <tr> <td>TRIM()</td> <td>Removes leading and trailing spaces from a string</td> <td>TRIM(string)</td> <td>SELECT TRIM(first_name) FROM employees;</td> </tr> </table>
SQL Date Functions
Date functions allow you to manipulate and format date and time values. These functions are particularly useful for reporting and querying time-sensitive data. Here's a summary of key date functions:
<table> <tr> <th>Function</th> <th>Description</th> <th>Syntax</th> <th>Example</th> </tr> <tr> <td>NOW()</td> <td>Returns the current date and time</td> <td>NOW()</td> <td>SELECT NOW();</td> </tr> <tr> <td>CURDATE()</td> <td>Returns the current date</td> <td>CURDATE()</td> <td>SELECT CURDATE();</td> </tr> <tr> <td>DATEDIFF()</td> <td>Returns the difference between two dates</td> <td>DATEDIFF(date1, date2)</td> <td>SELECT DATEDIFF(NOW(), '2021-01-01');</td> </tr> <tr> <td>DATE_FORMAT()</td> <td>Formats a date based on a specified format</td> <td>DATE_FORMAT(date, format)</td> <td>SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');</td> </tr> <tr> <td>ADDDATE()</td> <td>Adds a time interval to a date</td> <td>ADDDATE(date, interval)</td> <td>SELECT ADDDATE(NOW(), INTERVAL 1 DAY);</td> </tr> <tr> <td>SUBDATE()</td> <td>Subtracts a time interval from a date</td> <td>SUBDATE(date, interval)</td> <td>SELECT SUBDATE(NOW(), INTERVAL 1 MONTH);</td> </tr> </table>
Important Note: Date formatting can vary between SQL database systems. Be sure to check the documentation for the specific SQL dialect you are using.
SQL Numeric Functions
Numeric functions are used to perform mathematical calculations and operations. They are essential for applications that require numerical data processing. Here’s a brief overview of common numeric functions:
<table> <tr> <th>Function</th> <th>Description</th> <th>Syntax</th> <th>Example</th> </tr> <tr> <td>ABS()</td> <td>Returns the absolute value of a number</td> <td>ABS(number)</td> <td>SELECT ABS(-5);</td> </tr> <tr> <td>ROUND()</td> <td>Rounds a number to a specified number of decimal places</td> <td>ROUND(number, decimals)</td> <td>SELECT ROUND(123.456, 2);</td> </tr> <tr> <td>FLOOR()</td> <td>Returns the largest integer less than or equal to a number</td> <td>FLOOR(number)</td> <td>SELECT FLOOR(4.9);</td> </tr> <tr> <td>CEIL()</td> <td>Returns the smallest integer greater than or equal to a number</td> <td>CEIL(number)</td> <td>SELECT CEIL(4.1);</td> </tr> <tr> <td>MOD()</td> <td>Returns the remainder of a division operation</td> <td>MOD(dividend, divisor)</td> <td>SELECT MOD(10, 3);</td> </tr> </table>
Conclusion
A solid understanding of SQL functions can significantly enhance your database management and manipulation skills. This cheat sheet serves as a quick reference to commonly used SQL functions, allowing you to work more efficiently and effectively. Remember to familiarize yourself with different SQL dialects, as syntax and function availability may vary across platforms. 📈
Use this ultimate SQL functions cheat sheet to strengthen your SQL skills and speed up your workflow. Happy querying! 🥳