We may not have the course you’re looking for. If you enquire or give us a call on + 1-866 272 8822 and speak to our training experts, we may still be able to help with your training requirements.
Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
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.
Frequently Asked Questions
Upcoming Programming & DevOps Resources Batches & Dates
Date
Mon 2nd Dec 2024
Fri 17th Jan 2025
Fri 21st Feb 2025
Fri 4th Apr 2025
Fri 6th Jun 2025
Fri 25th Jul 2025
Fri 7th Nov 2025
Fri 26th Dec 2025