SQL Functions

SQL functions are built-in operations that perform calculations, manipulate data, and return results in queries, making data handling simpler without writing complex code.

• Simplify calculations, formatting, and analysis.
• Enable conditional logic and aggregation.

Categories of SQL Functions

SQL functions are classified based on how they process data and the type of results they return.

1. Single Row Function

Single-row functions return one result per row of a query. They are widely used in SELECT lists, WHERE clauses, and conditional expressions to manipulate and format individual data items.

Types of Single-Row Functions:

• Numeric Functions: Perform arithmetic or numeric operations (e.g., ROUND(), ABS(), POWER()).
• Character Functions: Manipulate text values (e.g., UPPER(), LOWER(), INITCAP()).
• Date and Time Functions: Handle date, time, and interval calculations (e.g., SYSDATE, NOW()).
• Conversion Functions: Convert data from one type to another (e.g., TO_CHAR(), CAST()).
• Data Mining Functions: Used in predictive modeling queries (e.g., PREDICTION_PROBABILITY()).

Example: First, we will create a demo SQL database and table, on which we will use the Single Row Function.

Query:

SELECT
    -- Character Function to format the name
    INITCAP(first_name || ' ' || last_name) AS full_name,

    -- Numeric Function to round the income for readability
    ROUND(annual_income, -3) AS rounded_income,

    -- Data Mining Function to get the default probability
    PREDICTION_PROBABILITY(loan_default_model, 'YES' USING *) AS default_probability
FROM
    Loan_applicant
WHERE
    annual_income > 100000
ORDER BY
    default_probability DESC
FETCH FIRST 5 ROWS ONLY;

Output:

• Formats names and rounds incomes for readability.
• Uses PREDICTION_PROBABILITY() to estimate loan default risk.
• Returns top 5 applicants with highest default probability.

Note: Oracle’s PREDICTION_PROBABILITY function requires a pre-trained data mining model to return probabilities. Without a model, this function cannot be used and must be simulated or omitted.

2. Analytic Function

Analytic functions perform calculations over a window of rows and return a value for each row (unlike aggregate functions, which return one row per group). They are evaluated after all other clauses except the final ORDER BY.

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM
  employees;

Output:

• In this example, this query calculates a running average of salaries within each department, ordered by salary.
• It returns the average salary up to the current row without collapsing rows.

Note: Analytic functions use the analytic_clause, which includes PARTITION BY, ORDER BY, and optional windowing with ROWS or RANGE.

3. Model Function

Model functions are used inside the MODEL clause of a SELECT statement to perform spreadsheet-like calculations on rows and columns. They help reference current, previous, or conditional values dynamically during rule evaluation and iteration.

SELECT month, sales
FROM sales_data
MODEL
  DIMENSION BY (month)
  MEASURES (sales)
  RULES ITERATE(2) (
    sales['Mar'] = NVL(sales['Mar'], sales['Feb'] * 1.10),
    sales['Apr'] = NVL(sales['Apr'], sales['Mar'] * 1.10)
  );

Output:

• In this example, the query forecasts March and April sales using previous months.
• NVL() calculates only missing values.
• MODEL performs cell-based, Excel-like calculations.

Note: Key Model Functions includes CV(), Iteration_Number, PresentV, PresentNNV, Previous that enable dynamic, iterative rules in the MODEL clause.

4. User Defined Function

User Defined Functions (UDFs) are custom functions created using PL/SQL or Java to extend SQL's capabilities. They provide functionality not available through built-in SQL functions and can be used anywhere an expression is allowed. 

SELECT
  employee_id,
  salary,
  get_annual_salary(salary) AS annual_salary
FROM
  employees;

Output:

• In this example the user-defined function get_annual_salary multiplies the monthly salary by 12.
• It's used in the SELECT list just like a built-in SQL function.

Note: User Defined Functions can be used in SELECT list, WHERE, ORDER BY, GROUP BY, CONNECT BY, START WITH clauses, VALUES clause of INSERT, SET clause of UPDATE.

5. Scalar Function

Scalar functions return a single value based on the input. They operate on individual data items and can be used wherever expressions are allowed in SQL queries.

SELECT UPPER('geeksforgeeks') AS result FROM dual;

Output:

• The UPPER() scalar function converts the input string to uppercase.
• It processes a single value and returns a single result.

Commonly used scalar functions in SQL include:

• UCASE(): Converts text to uppercase.
• LCASE(): Converts text to lowercase.
• LEN(): Returns the length of a text field.
• ROUND(): Rounds a number to a specified decimal place.
• NOW(): Returns the current system date and time.

6. System and Utility Functions

System functions provide metadata and diagnostic details about the database, user sessions, and server state. These functions are particularly useful for administrative and monitoring tasks.

1. BIN(): Convert decimal to binary

SELECT BIN(18);

Output:

2. BINARY(): Convert to binary string

SELECT BINARY "GeeksforGeeks";

Output:

3. CONNECTION_ID(): Returns the unique connection ID for the current connection

SELECT CONNECTION_ID();

Output:

4. CURRENT_USER(): Returns the user name and hostname for the MySQL account used by the server.

SELECT CURRENT_USER();

Output:

5. DATABASE(): Returns the name of the default database.

SELECT DATABASE();

Output:

6. LAST_INSERT_ID(): Returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement

SELECT LAST_INSERT_ID();

Output:

7. SESSION_USER(): Returns the user name and host name for the current MySQL user

SELECT SESSION_USER();

Output:

8. SYSTEM_USER(): Returns the user name and host name for the current MySQL user.

SELECT SYSTEM_USER();

Output:

9. USER(): It returns the user name and host name for the current MySQL user

SELECT USER();

Output:

10. VERSION(): It returns the version of the MySQL database

SELECT VERSION();

Output:

Note: These functions can be used with clauses like SELECT, WHERE, ORDER BY, GROUP BY, HAVING, INSERT, and UPDATE wherever a value or expression is allowed.

7. Conditional Functions

Conditional functions allow SQL queries to apply logic and make decisions based on data values, similar to if-else statements in programming.

Example:

SELECT Name, 
       CASE WHEN Salary > 5000 THEN 'High' 
            ELSE 'Low' END AS Salary_Level
FROM Employees;

Output:

• Uses a CASE statement to categorize employees based on salary.
• Labels salaries above 5000 as ‘High’ and others as ‘Low’.

8. Mathematical Functions

Mathematical functions in SQL are used to perform numeric calculations on data, such as rounding, powers, absolute values, and square roots. They help simplify computations directly within queries.

Query:

SELECT num, SQRT(num) AS square_root
FROM numbers;

Output:

• The SQRT() function in SQL is used to calculate the square root of a numeric value directly within a query.
• Example: SELECT SQRT(49) AS square_root FROM dual; it returns 7, the square root of 49.

Suggested Quiz

Edit Quiz

5 Questions

Which type of SQL function returns one result per row?

• A

  Aggregate Function

• B

  Analytic Function

• C

  Single-Row Function

• D

  Model Function

Explanation:

Single-row functions process individual rows and return one output per row.

Which function category requires a pre-trained model in Oracle?

• A

  Conversion Functions

• B

  Data Mining Functions

• C

  Analytic Functions

• D

  Scalar Functions

Explanation:

Data mining functions like PREDICTION_PROBABILITY() depend on existing ML models.

Which SQL clause pair is used with Analytic Functions?

• A

  GROUP BY only

• B

  WHERE and HAVING

• C

  JOIN and UNION

• D

  PARTITION BY and ORDER BY

Explanation:

Analytic functions rely on the analytic clause, which includes PARTITION BY and ORDER BY.

What is the purpose of a User Defined Function (UDF)?

• A

  Replace built-in SQL operators

• B

  Perform custom calculations not supported natively

• C

  Modify table structure

• D

  Create triggers automatically

Explanation:

UDFs extend SQL by allowing custom logic that behaves like built-in functions.

Which SQL function returns metadata such as the current logged-in user?

• A

  CURRENT_USER()

• B

  ABS()

• C

  POWER()

• D

  INITCAP()

Explanation:

System functions like CURRENT_USER() supply database session details.

Quiz Completed Successfully

Your Score :   2/5

Accuracy :  0%

Login to View Explanation

1/5 1/5 < Previous Next >