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) 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. They come in two main types: aggregate functions and scalar functions. Aggregate functions perform calculations on multiple rows and return a single value. On the other hand, scalar functions work on individual data items and provide a single value as output. 

These functions are incredibly versatile and allow developers, data analysts, and administrators to perform a wide range of tasks, including data summarisation, data transformation, and data manipulation. Understanding functions 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. These functions play a fundamental role in database management, enabling efficient data summarisation, analysis, and manipulation, making them valuable assets for anyone working with databases.   

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.

 

SELECT SUM(UnitPrice * Quantity) AS TotalRevenue  FROM OrderDetails;

 

3) AVG(): It computes the average of values in a numeric column. 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 

Advanced functions encompass 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. 

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 in 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. 

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.  

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

 

Conclusion 

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