SQL Functions

Introduction

In the realm of database management, SQL (Structured Query Language) is a powerful tool used to manage and manipulate data stored in relational databases. One of the key features of SQL is its ability to perform complex operations through built-in functions. These functions can be categorized into various types such as string functions, date and time functions, mathematical functions, and more. In this tutorial, we will explore some of these functions and how they can be used for data manipulation and calculation.

Concept

SQL provides a wide range of functions that allow you to perform operations directly within your queries. These functions are essential for tasks such as formatting data, performing calculations, and manipulating strings or dates. Understanding and effectively using these functions can greatly enhance the efficiency and effectiveness of your SQL queries.

Types of Functions

1. String Functions: Used for manipulating string data.
2. Date and Time Functions: Used for manipulating date and time values.
3. Mathematical Functions: Used for performing mathematical calculations.
4. Aggregate Functions: Used to perform calculations on a set of values and return a single value.

Examples

String Functions

String functions are used to manipulate string data. Here are some common string functions:

CONCAT

The CONCAT function is used to concatenate two or more strings.

1SELECT CONCAT('Hello', ' ', 'World') AS greeting;

$ SELECT CONCAT('Hello', ' ', 'World') AS greeting;

greeting
----------
Hello World

UPPER and LOWER

The UPPER function converts a string to uppercase, while the LOWER function converts it to lowercase.

1SELECT UPPER('hello world'), LOWER('HELLO WORLD');

$ SELECT UPPER('hello world'), LOWER('HELLO WORLD');

UPPER('hello world') | LOWER('HELLO WORLD')
-------------------|--------------------
HELLO WORLD       | hello world

Date and Time Functions

Date and time functions are used to manipulate date and time values. Here are some common date and time functions:

CURRENT_DATE and CURRENT_TIME

The CURRENT_DATE function returns the current date, while the CURRENT_TIME function returns the current time.

1SELECT CURRENT_DATE, CURRENT_TIME;

$ SELECT CURRENT_DATE, CURRENT_TIME;

current_date | current_time
------------|------------
2023-10-05   | 14:30:00

DATE_ADD and DATE_SUB

The DATE_ADD function adds a specified time interval to a date, while the DATE_SUB function subtracts a specified time interval from a date.

1SELECT DATE_ADD('2023-10-05', INTERVAL 7 DAY), DATE_SUB('2023-10-05', INTERVAL 3 MONTH);

$ SELECT DATE_ADD('2023-10-05', INTERVAL 7 DAY), DATE_SUB('2023-10-05', INTERVAL 3 MONTH);

date_add     | date_sub
------------|------------
2023-10-12   | 2023-07-05

Mathematical Functions

Mathematical functions are used to perform mathematical calculations. Here are some common mathematical functions:

ROUND

The ROUND function rounds a number to a specified number of decimal places.

1SELECT ROUND(123.456, 2);

$ SELECT ROUND(123.456, 2);

round
-----
123.46

ABS

The ABS function returns the absolute value of a number.

1SELECT ABS(-10), ABS(10);

$ SELECT ABS(-10), ABS(10);

abs | abs
----|----
10  | 10

What's Next?

In the next section, we will delve into Aggregate Functions. These functions are used to perform calculations on a set of values and return a single value, such as SUM, AVG, MAX, MIN, and COUNT. Understanding these functions is crucial for data analysis and reporting.

By mastering SQL functions, you can significantly enhance your ability to manipulate and analyze data stored in relational databases. Keep practicing with different queries and functions to build your skills further.