Training Outcomes Within Your Budget!

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

Share this Resource

Table of Contents

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.
 

SQL Training Courses

 

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 

Laptop 

Electronics 

999.99 

50 

Smartphone 

Electronics 

599.99 

100 

Headphones 

Electronics 

49.99 

200 

T-shirt 

Clothing 

19.99 

300 

Jeans 

Clothing 

39.99 

150 

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 

1719.94 

200.0 

9.99 

500 

Laptop - Electronics 

Ele 

LAPTOP 

 

 

 

 

 

Smartphone - Electronics 

Ele 

SMARTPHONE 

10 

 

 

 

 

 

Headphones - Electronics 

Ele 

HEADPHONES 

10 

 

 

 

 

 

T-shirt - Clothing 

Clo 

T-SHIRT 

 

 

 

 

 

Jeans - Clothing 

Clo 

JEANS 

 

 

 

 

 

Book - Books 

Boo 

BOOK 

 
 

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 

Laptop 

Electronics 

999.99 

50 

2023-07-01 

2023-07-25 

Smartphone 

Electronics 

599.99 

100 

2023-07-02 

2023-07-25 

Headphones 

Electronics 

49.99 

200 

2023-07-03 

2023-07-25 

T-shirt 

Clothing 

19.99 

300 

2023-07-04 

2023-07-25 

Jeans 

Clothing 

39.99 

150 

2023-07-05 

2023-07-25 

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 

24 

Elect 

-1 

Ele 

1000.0 

167 

16 

 

23 

Elect 

Ele 

600.0 

 

 

 

22 

Elect 

-1 

Ele 

50.0 

 

 

 

21 

Clo 

-1 

Clo 

20.0 

 

 

 

20 

Clo 

-1 

Clo 

40.0 

 

 

 

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. 

Frequently Asked Questions

Upcoming Programming & DevOps Resources Batches & Dates

Date

building Introduction to SQL

Get A Quote

WHO WILL BE FUNDING THE COURSE?

cross

BIGGEST
BLACK FRIDAY SALE!

red-starWHO WILL BE FUNDING THE COURSE?

close

close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

close

close

Press esc to close

close close

Back to course information

Thank you for your enquiry!

One of our training experts will be in touch shortly to go overy your training requirements.

close close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.