User-defined functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a user-defined function (UDF) in SQL?

A user-defined function is a custom function created by the user to perform specific tasks or calculations that can be reused in SQL queries.

beginner

How do you call a user-defined function in a SQL query?

You call a user-defined function by using its name followed by parentheses, optionally passing arguments inside the parentheses, like: SELECT function_name(arguments);

intermediate

What are the two main types of user-defined functions in SQL?

The two main types are scalar functions, which return a single value, and table-valued functions, which return a table (multiple rows and columns).

beginner

Why use user-defined functions instead of repeating code in SQL?

User-defined functions help avoid repeating code, make queries easier to read, and allow reusing logic in many places, saving time and reducing errors.

intermediate

Can user-defined functions modify data in SQL tables?

No, user-defined functions cannot change data in tables. They only return values and do not perform insert, update, or delete operations.

What does a scalar user-defined function return?

A No value

B A table with multiple rows

C A list of tables

D A single value

Scalar functions return a single value, such as a number or text.

Which SQL statement is used to create a user-defined function?

A CREATE FUNCTION

B CREATE PROCEDURE

C CREATE TABLE

D CREATE VIEW

User-defined functions are created using the CREATE FUNCTION statement.

Can a user-defined function perform INSERT operations on tables?

A Yes, always

B No, functions cannot modify data

C Only if specified

D Only in some SQL dialects

User-defined functions cannot modify data; they only return values.

What is a benefit of using user-defined functions?

A They simplify complex calculations

B They make queries longer

C They reduce code reuse

D They slow down queries

User-defined functions simplify complex calculations by encapsulating logic.

How do you pass input to a user-defined function?

A You cannot pass input

B By using global variables

C By specifying parameters inside parentheses

D By writing inside the function body

Input is passed to functions as parameters inside parentheses.

Explain what a user-defined function is and why it is useful in SQL.

Think about how you might reuse a calculation many times.

Describe the difference between scalar and table-valued user-defined functions.

One returns one value, the other returns many rows.