Informatics Practices · Ch 1 — Querying and SQL Functions
Functions in SQL
Functions in SQL
Functions are a core part of SQL, just as they are in programming languages. A function takes some input, performs a specific task, and returns a result. In SQL, functions operate on the data stored in a table. The key idea is that a function can work on a single row of data at a time, or it can work on a group of rows all at once. This difference in how they are applied is the basis for the main classification of SQL functions.
SQL functions are divided into two broad categories based on whether they act on one row or multiple rows:
- Single row functions — These work on one row at a time and return one result per row. For example, if you have a table with 100 rows, a single row function will produce 100 separate results, one for each row.
- Aggregate functions — These work on a group (or all) of the rows in a table and return a single result for the entire group. For example, finding the total salary of all employees is an aggregate function — it takes many rows and gives back just one number.
The textbook introduces this classification at the very start of the section. The actual names and working of specific single row and aggregate functions are covered in the subsections that follow. The purpose here is to understand the why behind the classification: it depends on whether the function processes one record or multiple records at a time. …