SQL Day 8:
1. How to Create a Function in SQL?
A function in SQL is a set of SQL statements that perform a specific task and return a value. Functions are used for calculations, data manipulations, and complex queries.
Syntax for Creating a Function
DELIMITER //
CREATE FUNCTION function_name(param1 DATATYPE, param2 DATATYPE)
RETURNS return_type
DETERMINISTIC
BEGIN
-- Function logic
RETURN value;
END //
DELIMITER ;
Example:
DELIMITER //
CREATE FUNCTION get_total_salary(emp_id INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE total_salary DECIMAL(10,2);
SELECT SUM(salary) INTO total_salary FROM employees WHERE id = emp_id;
RETURN total_salary;
END //
DELIMITER ;
2. Why and Where Do We Need Functions?
- Used for reusable calculations like total salary, tax deductions, etc.
- Helps maintain modular and structured code.
- Used in SELECT queries for dynamic computations.
- Can be used in WHERE, GROUP BY, and ORDER BY clauses.
3. When to Use Functions?
- When you need a reusable computation.
- When a query needs a computed column.
- When performing repeated calculations in queries.
4. Function Naming Rules
- Must be unique within the schema.
- Cannot use SQL reserved keywords.
- Must start with a letter.
- Can contain letters, numbers, and underscores (_).
5. What is a Delimiter?
A delimiter is a character or sequence that marks the beginning or end of a statement block. It is used to differentiate between multiple SQL statements in a script.
Delimiters Used in Functions
-
//
(Double Slash) -
$$
(Double Dollar Sign) -
;
(Semicolon, default SQL delimiter)
Why is a Delimiter Used in Functions?
Functions contain multiple SQL statements. Since ;
is used by default in SQL, we change the delimiter to prevent errors.
6. What is a Stored Procedure?
A stored procedure is a group of SQL statements stored in the database that performs a task but does not return a value like functions.
Syntax for Creating a Procedure
DELIMITER $$
CREATE PROCEDURE procedure_name(IN param1 DATATYPE, OUT param2 DATATYPE)
BEGIN
-- Procedure logic
SELECT value INTO param2 FROM table_name WHERE column = param1;
END $$
DELIMITER ;
Why and Where Do We Use Procedures?
- Used for executing multiple SQL queries as a batch.
- Best for operations like inserting, updating, or deleting records.
- Helps improve performance by reducing network traffic.
- Used in applications to execute business logic at the database level.
When to Use Procedures?
- When performing multiple operations in a single execution.
- When improving performance by reducing redundant queries.
7. Why Declare Variables in Functions but Not in Procedures?
- Functions must return a value and operate within a single query.
- Variables in functions are used for computations before returning a result.
- Procedures do not return values but execute statements directly, so variables are declared differently.
8. Functions vs. Procedures
Feature | Functions | Procedures |
---|---|---|
Returns a value | Yes | No |
Can be used in SELECT | Yes | No |
Supports Transactions | No | Yes |
Used for computations | Yes | No |
Used for data manipulation | No | Yes |
9. How to Call Functions and Procedures?
Calling a Function
SELECT get_total_salary(101);
Calling a Procedure
CALL procedure_name(param1, @output);
SELECT @output;
10. Windows Functions in SQL
Window functions operate over a set of rows related to the current row.
Available Window Functions
- ROW_NUMBER() – Assigns a unique row number to each record.
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;
- RANK() – Assigns a rank to each row based on ordering.
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
- DENSE_RANK() – Similar to RANK() but without gaps in ranking.
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
Conclusion
- Functions are best for computations and return values.
- Procedures are best for executing multiple operations.
- Delimiters help in writing multi-line SQL code.
- Window functions help in ranking and row numbering.