SQL Functions
- SQL Functions, which are built-in operations to calculate, manipulate data, or query values in a database, are the most common ways of using database data.
- SQL Functions are classified into two major types, Scalar and Aggregate SQL Functions.
- Scalar SQL Functions are ones that work on individual data and give out just one result, but aggregate functions, on the other hand, are computations across the set of values and give out one total value.
- It is vital to be familiar with the SQL Functions, as a result, with the help of them, the most optimal queries can be asked and the data analysis can be done more effectively.
- The following paragraphs take both types of SQL Functions with examples one by one.
1. SQL Date and Time Functions
- SQL Date and Time functions are functions that are used to handle date and time values, and they allow you to do various activities like extracting some specific dates and or calculating the difference between them.
Key Date and Time Functions:
- NOW(): This function gives back the present date and time of the server’s system clock. This method can be used to find out when the entry was created or last updated.
SELECT NOW();
Result: 2024-12-19 10:30:00
It’s useful for inserting timestamps in audit logs or logging events.
- CURDATE(): Brings forth the current date, only, without any time value.
SELECT CURDATE();
Result: 2024-12-19
Useful when you want to seize the date but not the moment, for example, in birthday or anniversary records.
- DATE_ADD(): It adds a specific interval of time (for example, days, months, or years) to a date. This feature is particularly convenient for the computation of future dates, i.e., delivery deadlines.
SELECT DATE_ADD('2024-12-19', INTERVAL5DAY);
Result: 2024-12-24
This could be used to compute expiry dates, deadlines, or any upcoming events.
- DATEDIFF(): Returns the difference between two dates in terms of days. It’s helpful for the determination of the duration between two events.
SELECT DATEDIFF('2024-12-19', '2024-12-10');
Result: 9
Useful for instance in a situation where you need to calculate how long a customer has been subscribed to a service.
- YEAR(), MONTH(), DAY(): Extracts specific components from a date.
SELECT YEAR('2024-12-19'), MONTH('2024-12-19'), DAY('2024-12-19');
Result: 2024, 12, 19
Can be used for filtering, grouping, or sorting data based on year, month, or day, such as grouping sales data by month.
2. SQL String Functions
- SQL String Functions are used to manipulate text or string data.
- These functions are beneficial for cleaning data, formatting text, or extracting specific parts of a string.
Key String Functions:
- UPPER(): Takes a string and convert it to uppercase.
SELECT UPPER('hello world');
Result: HELLO WORLD
This all the same forms ensures that the strings are uniform when using short sensitive databases.
- LOWER(): Converts a string to all lowercase letters. The same principle as the UP function, but it corrects the text normalization process.
SELECT LOWER('HELLO WORLD');
Result: hello world
It can be used in comparison operations or data cleaning up before the data is stored.
- CONCAT(): Joins two or more strings together. Convenient when you want to mix the first name and last name.
SELECT CONCAT('Hello', ' ', 'World');
Result: Hello World
These are usually for the full names or for dynamic content to be generated by records.
- SUBSTRING(): A substring is the portion of a string that is being cut from the first character at the beginning. This can be useful for eliminating specified parts of text, e.g. area codes from phone numbers.
SELECT SUBSTRING(“Hello World”,1,5);
Result: Hello
Earlier, this was smoothed out in writing reports by using string parsing or formatting in Excel.
3. SQL Numeric Functions
- SQL Numeric functions help you in performing mathematical operations on the numeric data types.
- These functions give you the possibility to perform calculations on numeric data as well as to round the numbers and manipulate them as needed.
Key Numeric Functions:
- ROUND(): A function that rounds off a number to a specified number of decimal places. This function is widely used in financial and scientific research.
SELECT ROUND(123.4567, 2);
Result: 123.46
This feature is perfect for the scenario where a user needs to display results with only a certain number of decimal places, such as those in accounting reports.
- CEIL() or CEILING(): This function returns the smallest integer that is greater than or equal to the number. It is generally used to “round up” to the next whole number.
SELECT CEIL(123.45);
Result: 124
Sometimes, we use it in the process of calculating the number of items needed for an order and also in the case of oversized objects.
- FLOOR(): This function returns/takes the nearest smallest integer or a quotient instead of a remainder, which is a portion remaining in a number. That is, it is actually returning the integer portion of the number with any division bits remainder being truncated.
SELECT FLOOR(123.45);
Result: 123
One useful technique where the FLOOR() function can be applied is in getting the round-down value of a number, such as in discount calculations or while dealing with integers.
4. SQL Statistical Functions
- SQL statistical functions summarize the datasets by giving you the essential necessary information.
- These aggregate functions enable you to compute statistics like sums, averages, and counts.
Key Statistical Functions:
- COUNT(): Counts the number of rows in a dataset or a non-null column.
SELECT COUNT(*) FROM employees;
Result: Returns the total number of employees.
- SUM(): Calculates the sum of values in a numerical column.
SELECT SUM(salary) FROM employees;
Result: Returns the total salary for all employees.
- AVG(): Calculates the average of values in a numerical column.
SELECT AVG(salary) FROM employees;
Result: The average salary of all employees.
- MIN(): Returns the minimum value in a column.
SELECT MIN(salary) FROM employees;
Result: The lowest salary in the employees table.
- MAX(): Returns the maximum value in a column.
SELECT MAX(salary) FROM employees;
Result: The highest salary in the employees table.
5. Working With JSON in SQL
- Modern relational databases such as MySQL and PostgreSQL have allowed support for JSON data types.
- SQL includes built-in functions aimed at working with JSON data.
Key JSON Functions:
- JSON_EXTRACT(): This method is the one used to extract parts of a JSON document.
SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name');
Result: "Alice"
It can also be used to take particular data from a JSON object that is stored in a database.
- JSON_OBJECT(): It helps the creation of JSON from key-value pairs.
SELECT JSON_OBJECT('name', 'Alice', 'age', 30);
Result: {"name": "Alice", "age": 30}
One use case could be of it locating or creating JSON data during runtime.
- JSON_ARRAY(): This function is used to construct a JSON array containing the list of all the arguments.
SELECT JSON_ARRAY('Alice', 'Bob', 'Carol');
Result: ["Alice", "Bob", "Carol"]
Very convenient to have JSON arrays built for use in various APIs or response ways.
- JSON_ARRAY_LENGTH(): With this function, a number of elements of a JSON array can be determined.
SELECT JSON_ARRAY_LENGTH('["Apple", "Banana", "Cherry"]');
Result: 3
Checking JSON arrays size is the main thing here.
6. Conversion Functions in SQL
- Conversion functions are vital for changing data from one data type to another, for example, converting strings into numbers or dates.
Key Conversion Functions:
- CAST(): Changes a value from one data type to another.
SELECT CAST('123'ASINT);
Result: 123 (converted to an integer)
This is helpful when you want to resize a column or rename a column or change your data type in order to avoid issues that may come up with other operations.
- CONVERT(): It is just a well as CAST(), but it is specific to particular SQL dialects linguistically.
SELECT CONVERT('2024-12-19', DATE);
Result: 2024-12-19 (converted to date)
This function is usually used for converting strings to date formats in SQL Server.
7. SQL Data Types
- SQL data types are a way of signifying the type of data to be stored by a column.
- There exists a different set of data types in each database system, but the following are the most common data types, which are found:
- INT: integers are integers that can be stored (not whole numbers without the addition of decimal points).
- VARCHAR: Stores variable length character strings.
- DATE: Stores date values (year, month, day of the month).
- DATETIME: Records both the date and time details.
- FLOAT: decimals are stored as (numbers with decimal).
- BOOLEAN: Is true or false (true or false) (1=TRUE,0=FALSE).
- JSON: JSON is a data type that is represented using JSON (there are so many databases that support it now). data is of this type (it is good to have JSON formatted data in many modern databases).
8. SQL LTRIM() Function
- LTRIM() Function eliminates leading spaces (spaces visible at the start of the statement) from a string.
- It’s more particularly helpful to refine and format input data for display.
SELECT LTRIM('Hello');
Result: Hello
9. SQL UPPER() Function
- The UPPER() function transforms all the letters of an input string to uppercase, and it is frequently employed to standardize the case in queries or make the string comparison case-insensitive.
SELECT UPPER('hello');
Result: HELLO
10. SQL RTRIM() Function
- The RTRIM() function is utilized to delete the spaces at the end of the string (spaces that come after the text).
- Companies typically employ it to erase extraneous spaces incorrectly put in.
SELECT RTRIM('Hello ')
Result: Hello