Basic and Advanced SQL Functions

The Knowledge Academy 18 December 2025

SQL functions are essential tools for data manipulation and analysis. They allow you to perform a wide range of tasks, from simple calculations to complex data transformations. Learn the basics of SQL functions and take your skills to the next level with advanced functions like window and user-defined functions.

Home

Resources

Programming & DevOps

Basic and Advanced SQL Functions

Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

Table of Contents

Related Courses

Basic and Advanced SQL Functions

In the realm of database management, Structured Query Language (SQL) and its functions are instrumental tools for extracting, manipulating, and analysing data. These SQL Functions bolster the potential of SQL queries, empowering developers to accomplish intricate tasks effortlessly.

According to Statista, Oracle was the most popular Database Management System (DBMS) in the world, with a ranking score of 1247.52. If you wish to understand the core principles of functions in SQL and gain insights into their applications, this blog is the right choice for you. In this blog, you will learn about the core principles of Basic and Advanced SQL Functions, which are extremely effective database tools.

Table of Contents

1) What are SQL Functions?

2) Basic SQL Functions

3) Advanced SQL Functions

4) How to use a SQL Function in queries?

5) Implementing SQL Functions

6) Conclusion

What are SQL Functions?

SQL based functions are essential components of the Structured Query Language, commonly known as SQL. These functions play a crucial role in database management systems, allowing users to perform various operations on the data stored in the databases. These functions enable users to manipulate, analyse, and extract data efficiently.

At its core, functions in SQL are predefined algorithms that accept input parameters, process the data, and return a result. When creating a database in SQL, these functions come in two main types: aggregate functions and scalar functions. Aggregate functions perform calculations on multiple rows and return a single value, while scalar functions work on individual data items and provide a single value as output.

These functions are incredibly versatile and enable developers, data analysts, and administrators to perform a wide range of tasks, including data summarization, data transformation, and data manipulation. Mastering these functions is particularly valuable for database professionals, as their expertise directly influences the Average Salary of an Oracle Database Administrator Salary. Understanding functions in SQL, such as the BETWEEN Operator in SQL, is crucial for effectively managing and leveraging the power of relational databases.

Basic SQL Functions

Basic functions refer to two types of functions: SQL Aggregate Functions and SQL Scalar Functions. Alongside these, SQL Operators play a crucial role in managing and manipulating data effectively. These functions play a fundamental role in database management, enabling efficient data summarization, analysis, and manipulation, making them valuable assets for anyone working with databases. To better understand the broader context, explore the Difference Between Oracle and SQL Database, which highlights distinctions between these popular database systems.

SQL aggregate functions

Aggregate functions process multiple rows of data and return a single value. They are crucial for generating insightful data summaries.

1) COUNT(): This function is used to count the number of rows in a specified column or the entire table. It is valuable for obtaining the total number of records, identifying the size of result sets, and aggregating data for statistical analysis.

SELECT COUNT(CustomerID) AS TotalCustomers

FROM Customers;

2) SUM(): It calculates the total sum of a numeric column. It is commonly used for financial data, sales records, or any other scenario where the sum of numeric values needs to be determined. When comparing SQL vs MySQL, both support this calculation, but there may be slight variations in performance or handling of large datasets depending on the system being used.

SELECT SUM(UnitPrice * Quantity) AS TotalRevenue

FROM OrderDetails;

3) AVG(): It computes the average of values in a numeric column, which is a key function within the Components of SQL Server. It is beneficial for calculating average scores, ratings, or any other metric that requires averaging numerical data.

SELECT AVG(UnitPrice) AS AveragePrice

FROM Products;

4) MIN(): It retrieves the smallest value from a column. It is useful for finding the minimum value in a set, such as the lowest price of products or the earliest date in a dataset.

SELECT MIN(UnitPrice) AS MinPrice

FROM Products;

5) MAX(): It retrieves the largest value from a column. It is employed to find the maximum value in a set, such as the highest temperature recorded or the latest date in a dataset.

SELECT MAX(Quantity) AS MaxQuantity

FROM OrderDetails;

SQL scalar functions

Scalar functions operate on individual data items and return a single value. They are used for data transformation and manipulation.

1) CONCAT(): CONCAT() combines two or more strings into a single string. It is helpful for creating new text values by joining multiple strings, such as creating full names from first and last names.

SELECT MAX(Quantity) AS MaxQuantity

FROM OrderDetails;

2) SUBSTRING(): SUBSTRING() extracts a part of the string of objects based on the specified starting position and length. It is used to extract substrings from larger text data, like extracting area codes from phone numbers.

SELECT SUBSTRING(ProductName, 1, 3) AS ProductCode

FROM Products;

3) UPPER() and LOWER(): UPPER() converts a string to uppercase, and LOWER() converts it to lowercase. These functions are used to standardise the casing of text data, making it easier to search and compare.

SELECT UPPER(ProductName) AS UppercaseProductName

FROM Products;

4) LENGTH(): LENGTH() returns the number of characters in a string. It is employed to determine the length of text data, useful for data validation or formatting purposes.

SELECT ProductName, LENGTH(ProductName) AS NameLength

FROM Products;

Unlock the power of data with our Introduction to MySQL Course – join now and become an SQL expert!

Advanced SQL Functions

The Advanced SQL Guide covers advanced functions, which are divided into three categories: SQL Date Functions, SQL String Functions, and SQL Numeric Functions. These functions are valuable additions to a database professional's toolkit, simplifying complex operations and enhancing data manipulation capabilities, including optimisation of SQL Index.

SQL date functions

Date functions streamline handling date and time data, providing valuable insights and precision.

NOW(): This function retrieves the current date and time from the system clock. It is commonly used for timestamping records or capturing the current time for real-time data tracking.

SELECT NOW() AS CurrentDateTime;

DATEPART(): DATEPART() extracts a specific part (e.g., day, month, year) from a date. It enables data grouping and analysis based on time intervals, such as counting the number of orders per month.

SELECT DATEPART(YEAR, OrderDate) AS OrderYear

FROM Orders;

DATEDIFF(): It calculates the total difference between two dates, providing the duration between two events. It is used for calculating age, time intervals, or tracking time elapsed between events.

SELECT DATEPART(YEAR, OrderDate) AS OrderYear

FROM Orders;

SQL string functions

String functions facilitate efficient manipulation and analysis of text data.

REPLACE(): REPLACE() allows you to replace occurrences of a specified substring within a string with another string. It's useful for data cleansing, data transformations, and correcting typographical errors.

SELECT REPLACE(Description, 'Old', 'New') AS UpdatedDescription

FROM Products;

CHARINDEX(): CHARINDEX() helps find the starting position of a substring within a string. It's commonly used to search for specific patterns or keywords within text data.

SELECT REPLACE(Description, 'Old', 'New') AS UpdatedDescription

FROM Products;

LEFT() and RIGHT(): LEFT() extracts a specified amount of characters from the start of a string, while RIGHT() retrieves characters from the end. These functions are handy when you need to extract prefixes or suffixes from strings, such as area codes from phone numbers.

SELECT LEFT(PhoneNumber, 3) AS AreaCode

FROM Customers;

SQL numeric functions

Numeric functions operate on numerical data, enabling precise computations.

ROUND(): ROUND() is used to round up a numeric value to a specified decimal place. It is useful for formatting data or simplifying large numeric values.

SELECT ROUND(UnitPrice, 2) AS RoundedPrice

FROM Products;

CEILING(): CEILING() rounds up to the nearest integer. It is commonly used for rounding up quantities or values to ensure accurate calculations.

SELECT CEILING(TotalAmount) AS RoundedAmount

FROM Invoices;

FLOOR(): FLOOR() rounds down to the nearest integer. It is often used for calculating integer values or ensuring appropriate values for storage or display.

SELECT FLOOR(QuantityInStock) AS RoundedStock

FROM Inventory;

Master the advanced art of SQL with our expert-led Advanced SQL Training – join now!

How to use a SQL Function in queries?

Utilising functions in queries can substantially improve data output and analysis, offering valuable insights and simplifying complex operations. Some examples of using functions in queries are as follows:

SELECT statement with SQL based functions

Incorporate functions within the SELECT statement to compute values dynamically. Example:

SELECT ProductName, UnitPrice * (1 - Discount) AS DiscountedPrice

FROM Products;

WHERE Clause with SQL based functions

Filter data based on specific conditions using functions in the WHERE clause. Example:

SELECT ProductName, UnitPrice

FROM Products

WHERE UnitPrice > AVG(UnitPrice);

GROUP BY Clause with SQL aggregate functions

Combine GROUP BY with aggregate functions to group and summarise data. Example:

SELECT CategoryID, COUNT(ProductID) AS TotalProducts

FROM Products

GROUP BY CategoryID;

ORDER BY Clause with SQL based functions

Sort query results using SQL based functions in the ORDER BY clause. Example:

SELECT ProductName, UnitsInStock

FROM Products

ORDER BY UnitsInStock DESC;

Unlock the power of data with our comprehensive Introduction to SQL Training – sign up now!

Implementing SQL Functions

Here is an example of implementing SQL-based functions in a query within a database. In this example, we created a table named "Products" with columns. The query will return the desired results, showing the relevant information for the products in the specified category. Proper Normalisation in SQL ensures that this table is structured efficiently, avoiding redundancy.

Learn more by exploring our top SQL Projects. Your next challenge awaits!

Implementing Basic SQL based functions

Tables are the structured representations of data, organised with columns such as ProductID, ProductName, Category, UnitPrice, and UnitsInStock, each defining the type of information stored.

Rows in the table contain specific product data, such as laptop, smartphone, headphones, and their respective attributes. The "Products" table serves as the foundation for storing product information in an online store database, ensuring data integrity, easy retrieval, and efficient data management.

ProductID	ProductName	Category	UnitPrice	UnitsInStock
1	Laptop	Electronics	999.99	50
2	Smartphone	Electronics	599.99	100
3	Headphones	Electronics	49.99	200
4	T-shirt	Clothing	19.99	300
5	Jeans	Clothing	39.99	150
6	Book	Books	9.99	500

SQL Query:

SELECT

COUNT(*) AS TotalProducts,

SUM(UnitPrice) AS TotalPrice,

AVG(UnitsInStock) AS AvgStock,

MIN(UnitPrice) AS MinPrice,

MAX(UnitsInStock) AS MaxStock,

CONCAT(ProductName, ' - ', Category) AS ProductDetails,

SUBSTRING(Category, 1, 3) AS CategoryCode,

UPPER(ProductName) AS UppercaseName,

LENGTH(ProductName) AS NameLength

FROM Products;

Total Products	Total Price	Avg Stock	Min Price	Max Stock	Product Details	Category Code	UppercaseName	NameLength
6	1719.94	200.0	9.99	500	Laptop - Electronics	Ele	LAPTOP	6
					Smartphone - Electronics	Ele	SMARTPHONE	10
					Headphones - Electronics	Ele	HEADPHONES	10
					T-shirt - Clothing	Clo	T-SHIRT	7
					Jeans - Clothing	Clo	JEANS	5
					Book - Books	Boo	BOOK	4

Implementing Advanced SQL based functions

Advanced queries utilise a combination of basic and advanced SQL-based functions to perform intricate tasks. These queries retrieve data, calculate statistics like total units in stock or average unit price, filter products based on specific conditions, sort them by unit price, and perform string manipulations. To excel in these areas, reviewing SQL Interview Questions will help you prepare for real-world scenarios and showcase your SQL expertise during job interviews.

Advanced queries provide valuable insights into the product inventory, sales, and trends, enabling informed decision-making and comprehensive data analysis in the online store business.

ProductID	ProductName	Category	UnitPrice	Units In Stock	Entry Date	LastStock UpdateDate
1	Laptop	Electronics	999.99	50	2023-07-01	2023-07-25
2	Smartphone	Electronics	599.99	100	2023-07-02	2023-07-25
3	Headphones	Electronics	49.99	200	2023-07-03	2023-07-25
4	T-shirt	Clothing	19.99	300	2023-07-04	2023-07-25
5	Jeans	Clothing	39.99	150	2023-07-05	2023-07-25
6	Book	Books	9.99	500	2023-07-06	2023-07-25

SELECT

NOW() AS CurrentDateTime,

DATEPART(DAY, EntryDate) AS EntryDay,

DATEDIFF(DAY, EntryDate, NOW()) AS DaysSinceEntry,

REPLACE(Category, 'Electronics', 'Elect') AS ModifiedCategory,

CHARINDEX('phone', ProductName) AS PhonePosition,

LEFT(Category, 3) AS CategoryAbbreviation,

ROUND(UnitPrice, 1) AS RoundedPrice,

CEILING(AVG(UnitsInStock)) AS RoundedAvgStock,

FLOOR(AVG(UnitPrice)) AS RoundedAvgPrice

FROM Products;

Resulting Table:

Current Date Time	Entry Day	Days Since Entry	Modified Category	Phone Position	Category Abbreviation	Rounded Price	Rounded Avg Stock	Rounded Avg Price
2023-07-25 12:34:56.789	1	24	Elect	-1	Ele	1000.0	167	16
	2	23	Elect	1	Ele	600.0
	3	22	Elect	-1	Ele	50.0
	4	21	Clo	-1	Clo	20.0
	5	20	Clo	-1	Clo	40.0
	6	19	Boo	-1	Boo	10.0

Optimize Your Database Management – Compare SQL Truncate vs Delete Now!

Conclusion

SQL Functions play a crucial role in database management, offering powerful tools for data manipulation, analysis, and extraction. Inserting data in SQL is a fundamental step before utilising functions like COUNT() and MIN() for essential aggregations, while advanced functions like NOW() and DATEDIFF() enable more intricate data processing. Utilising these functions empowers businesses to derive valuable insights, allowing them to make informed decisions with the help of the collected data in their databases.

Ready to Enhance Your SQL Queries? Discover How to Create Temp Tables!

Frequently Asked Questions

The Knowledge Academy

Global Training Provider

The Knowledge Academy is a world-leading provider of professional training courses, offering globally recognised qualifications across a wide range of subjects. With expert trainers, up-to-date course material, and flexible learning options, we aim to empower professionals and organisations to achieve their goals through continuous learning.

View Detail