Skip to main content

Understanding SQL Functions

SQL Functions

SQL functions are powerful tools that can simplify your SQL queries and make them easier to read and understand. These functions can perform operations on data, such as mathematical operations, string manipulations, date manipulations, and more.

What are SQL Functions?​

SQL functions are built-in methods that are used to perform specific operations. They can take in parameters and return a value. SQL functions are categorized into two types:

  1. Aggregate Functions: These functions operate on many records and return a summarized result. Examples include SUM(), AVG(), COUNT(), etc.

  2. Scalar Functions: These functions operate on a single value and return a single value. Examples include UCASE(), LCASE(), ROUND(), etc.

Aggregate Functions​

Aggregate functions perform a calculation on a set of values and return a single summarized value.

COUNT()​

The COUNT() function returns the number of rows that matches a specified criterion.

Example:

SELECT COUNT(ProductID)
FROM Products;

This will return the number of ProductIDs in the Products table.

SUM()​

The SUM() function returns the total sum of a numeric column.

Example:

SELECT SUM(Quantity)
FROM OrderDetails;

This will return the total quantity of all orders from the OrderDetails table.

Scalar Functions​

Scalar functions return a single value based on the input value.

UCASE() and LCASE()​

The UCASE() function converts the value of a field to uppercase, while the LCASE() function converts it to lowercase.

Example:

SELECT UCASE(FirstName) AS Upper, LCASE(FirstName) AS Lower
FROM Customers;

This will return the first names of all customers in uppercase and lowercase.

ROUND()​

The ROUND() function is used to round a numeric field to the number of decimals specified.

Example:

SELECT ROUND(Price, 2)
FROM Products;

This will return the price of all products rounded to 2 decimal places.

Date Functions​

SQL also provides a set of functions to work with date and time. Some of the most commonly used date functions include NOW(), CURDATE(), and CURTIME().

NOW()​

The NOW() function returns the current date and time.

Example:

SELECT NOW() AS CurrentDateTime;

This will return the current date and time.

By learning and understanding SQL functions, you can write more efficient and effective SQL queries. These functions can help you manipulate data in various ways, making SQL a powerful tool for managing and analyzing data.