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

  1. ROW_NUMBER() – Assigns a unique row number to each record.
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;
  1. RANK() – Assigns a rank to each row based on ordering.
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
  1. 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.