SQL Functions Cheat Sheet
If you’re looking for information on SQL functions, then you’re in the right place. SQL functions are powerful tools that can be used to manipulate data within a database.
This cheat sheet will provide you with all the information you need to use SQL functions effectively, including text and numeric functions, CASE WHEN, NULLs, Date and Time Types, INTERVALs, and Aggregate Functions.
Text and Numeric Functions
SQL has a wide range of text and numeric functions that can be used to manipulate data within a database. Some of the most commonly used text functions include CONCAT, LEFT, RIGHT, and LENGTH.
CONCAT is used to join two or more strings together, while LEFT and RIGHT are used to return a specified number of characters from the start or end of a string respectively. LENGTH is used to return the length of a string.
Numeric functions, on the other hand, are used to perform mathematical operations on data within a database. Some of the most commonly used numeric functions include ROUND, CEIL, FLOOR, and TRUNC.
ROUND is used to round a number to a specified number of decimal places, while CEIL and FLOOR are used to round a number up or down respectively. TRUNC is used to truncate a number to a specified number of decimal places.
CASE WHEN
CASE WHEN is an SQL clause that can be used to perform conditional logic within a query. It’s often used to perform calculations based on certain conditions.
For example, if you have a table that contains data on sales, you could use CASE WHEN to calculate the total sales for each region.
CASE WHEN requires a condition to be specified, followed by the result if the condition is true. You can specify multiple conditions by using the ELSE keyword.
For example, if you wanted to calculate the total sales for each region, but also wanted to include a row for the total sales for all regions, you could use the following query:
SELECT
region,
SUM(sales) AS total_sales
FROM
sales_table
GROUP BY
region
WITH ROLLUP;
NULLs, Date and Time Types, INTERVALs, Aggregate Functions
SQL also has a number of other useful functions, including functions for handling NULL values, Date and Time Types, INTERVALs, and Aggregate Functions. NULLs are often used to represent missing or unknown data within a database.
SQL has a number of functions that can be used to handle NULL values, including IS NULL and COALESCE. IS NULL is used to test whether a value is NULL, while COALESCE is used to return the first non-NULL value in a list.
Date and Time Types are important data types in SQL, and can be used to store and manipulate date and time data. SQL has a number of functions that can be used to manipulate date and time data, including DATEADD, DATEDIFF, and DATEPART.
INTERVALs are used to represent a period of time in SQL. SQL has a number of functions that can be used to manipulate INTERVALs, including EXTRACT, which is used to extract a specific part of an INTERVAL value, and DATE_TRUNC, which is used to truncate an INTERVAL to a specific unit of time.
Aggregate Functions are used to perform calculations on a set of values within a database. Some of the most commonly used Aggregate Functions include SUM, AVG, MIN, MAX, and COUNT.
SUM is used to calculate the total of a set of values, while AVG is used to calculate the average. MIN and MAX are used to calculate the minimum and maximum values in a set, while COUNT is used to count the number of rows in a table.
Changing Number Types with CAST()
In addition to the functions covered in the SQL Functions Cheat Sheet, it’s also helpful to know how to change number types using CAST(). CAST() is an SQL function that can be used to change the data type of a column in a query.
For example, if you have a column that contains string values that represent numbers, you can use CAST() to convert them to actual numeric values.
Useful Functions
In addition to CAST(), SQL has a number of other useful functions that can be used to manipulate numeric data. Some of the most commonly used functions include MOD, which is used to return the remainder of a division operation, and ABS, which is used to return the absolute value of a number.
SQRT is used to return the square root of a number. By using the functions covered in the SQL Functions Cheat Sheet, as well as CAST() and other useful functions, you’ll be able to manipulate data within a database in a powerful and efficient way.
So whether you’re a seasoned developer or just starting out with SQL, these functions are an essential part of your toolkit.
CASE WHEN
CASE WHEN is an SQL clause that is used to apply conditional logic within a query. It allows us to check for a condition and return different values based on whether the condition is true or false.
Basic Version
The basic version of CASE WHEN syntax is as follows:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
WHEN valueN THEN resultN
ELSE else_result
END
The `expression` is typically a column that you want to evaluate. The `value` is the condition that you want to check for, and the `result` is the value that you want to return when the condition is true.
The `ELSE` clause specifies what value to return when none of the conditions are true. Here’s an example that shows how to use the basic version of CASE WHEN to return different values based on the gender column:
SELECT
first_name,
last_name,
gender,
CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Unknown'
END AS gender_full
FROM
employees;
Searched Version
The searched version of CASE WHEN allows us to use boolean expressions to evaluate conditions. The syntax looks like this:
CASE
WHEN boolean_expression1 THEN result1
WHEN boolean_expression2 THEN result2
WHEN boolean_expressionN THEN resultN
ELSE else_result
END
Here’s an example that shows how to use the searched version of CASE WHEN to determine whether an employee’s salary is above or below the average salary:
SELECT
first_name,
last_name,
salary,
CASE
WHEN salary > AVG(salary) OVER () THEN 'Above Average'
ELSE 'Below Average'
END AS salary_comparison
FROM
employees;
Troubleshooting
When using CASE WHEN, there are a few common issues that you may run into. One of the issues that you may encounter is decimal places.
You may need to round a value to a certain number of decimal places to ensure that it matches the value in the WHEN clause. In cases where you are doing a calculation to determine a round number, you may also run into problems when dividing by zero or dealing with inexact calculations.
In these cases, you may need to use a different function or consider alternative options for handling the calculation.
COUNT() and GROUP BY
The COUNT() and GROUP BY clauses are used to aggregate data in SQL. COUNT() allows us to count the number of rows in a table or the number of unique values in a column.
GROUP BY is used to group the data based on one or more columns.
Counting Rows
To count the number of rows in a table, you can use the COUNT() function with the * wildcard:
SELECT COUNT(*)
FROM employees;
To count the number of rows that meet a certain condition, you can use the COUNT() function with a specific column:
SELECT COUNT(employee_id)
FROM employees WHERE department_id = 10;
Counting Unique Values
To count the number of unique values in a column, you can use the COUNT() function with the DISTINCT keyword:
SELECT COUNT(DISTINCT department_id)
FROM employees;
This will return the number of unique department IDs in the employees table.
GROUP BY
GROUP BY is used to group the data based on one or more columns. Here’s an example that groups the employee data by department ID:
SELECT
department_id,
COUNT(*) AS num_employees
FROM
employees
GROUP BY
department_id;
This will return the number of employees in each department.
Common Mistakes
One common mistake when using GROUP BY is forgetting to include all non-aggregate columns in the GROUP BY clause. Here’s an example that will result in an error because the first_name column is missing from the GROUP BY clause:
SELECT
department_id,
first_name,
COUNT(*) AS num_employees
FROM
employees
GROUP BY
department_id;
To fix this error, we need to include the first_name column in the GROUP BY clause:
SELECT
department_id,
first_name,
COUNT(*) AS num_employees
FROM
employees
GROUP BY
department_id,
first_name;
By using the COUNT() and GROUP BY clauses correctly, you can aggregate data in SQL to get meaningful insights into your data.
Date and Time Types
Date and time data is an essential part of any database, and SQL has a range of data types and functions for manipulating these types of data. In this article, we will cover the basic date and time types in SQL, how to create values, and how to handle time zones.
Basic Types
SQL has three basic date and time types: DATE, TIME, and TIMESTAMP. The DATE type is used to store date values.
Date values are stored as year, month, and day values. The format of the date value is typically ‘YYYY-MM-DD’.
The TIME type is used to store time values. Time values are stored as hour, minute, and second values, with optional fractions of a second.
The format of the time value is typically ‘HH:MM:SS’. The TIMESTAMP type is used to store date and time values.
Timestamp values are stored with time zone information, and are typically represented in the format ‘YYYY-MM-DD HH:MM:SS.mmm’.
Creating Values
To create date and time values in SQL, we can use either string input or casting. String input involves specifying the date and time value in a specific format.
Here’s an example of a SQL query that uses string input to create a date value:
SELECT '2022-02-14'::DATE;
This will return a date value that represents February 14th, 2022. Casting involves converting a value from one data type to another.
Here’s an example of a SQL query that uses casting to create a timestamp value:
SELECT TIMESTAMP '2022-02-14 10:30:00';
This will return a timestamp value that represents February 14th, 2022 at 10:30 AM.
Time zones
In SQL, timestamps can be stored with time zone information. This is useful when dealing with data that spans multiple time zones, or when you need to handle daylight savings time changes.
To specify a time zone in a timestamp value, we can use the timestamptz data type. Here’s an example of a SQL query that uses the timestamptz data type:
SELECT TIMESTAMP WITH TIME ZONE '2022-02-14 10:30:00-05';
This will return a timestamp value that represents February 14th, 2022 at 10:30 AM EST. To convert a timestamp to a different time zone, we can use the AT TIME ZONE operator.
Here’s an example of a SQL query that uses AT TIME ZONE to convert a timestamp from one time zone to another:
SELECT TIMESTAMP '2022-02-14 10:30:00-05' AT TIME ZONE 'UTC';
This will return a timestamp value that represents February 14th, 2022 at 3:30 PM UTC. SQL also has a number of time zone shortcuts that can be used when working with time zones.
For example, ‘EST’ can be used to represent Eastern Standard Time, and ‘UTC’ can be used to represent Coordinated Universal Time. In summary, date and time data is an essential part of any database, and SQL provides a range of data types and functions for manipulating this type of data.
By understanding the basic date and time types, how to create values, and how to handle time zones in SQL, you can effectively manage date and time data in your database. In this article, we covered a range of topics related to SQL functions, counting and grouping data, and date and time data.
We started by discussing the syntax and troubleshooting tips for using SQL functions like CASE WHEN, COUNT(), and GROUP BY, which are powerful tools for manipulating and aggregating data within a database. We then explored the basics of date and time types, including how to create values using string input and casting, and how to handle time zones using the timestamptz type and the AT TIME ZONE operator.
These topics are crucial for anyone working with SQL, and by applying these concepts, you can more effectively work with and analyze data in your database. Remember, proper data analysis and manipulation is key to making informed decisions and taking actions that will benefit your organization.