SQL Functions

---

SQL functions are pre-written blocks of code that perform specific tasks within queries.

They save you time and effort by encapsulating common operations, making your code more readable and maintainable.

Categorization of popular SQL functions:

1. Scalar Functions:

• Operate on one or more input values and return a single scalar value.
• Examples:
  • Mathematical functions: ABS(), SQRT(), ROUND(), etc.
  • String manipulation functions: UPPER(), LOWER(), SUBSTRING(), etc.
  • Date/time functions: CURDATE(), DATEDIFF(), etc.

2. Aggregate Functions:

• Work on a set of values (often entire columns) and return a single summarized result.
• Examples:
  • COUNT(): Calculates the number of rows in a set.
  • SUM(): Computes the total of a numeric column.
  • AVG(): Returns the average of a numeric column.
  • MIN(), MAX(): Find the minimum and maximum values in a column.

3. Conditional Functions:

• Evaluate a condition and return a value based on the outcome (true/false).
• Example: CASE WHEN... THEN... ELSE... END

SQL functions are built-in operations that perform specific tasks and return a single value.

These functions can be used to manipulate data, perform calculations, format output, and more.

Some common SQL functions:

1. Aggregate Functions:

   • COUNT(): Returns the number of rows that match a specified condition.

     SELECT COUNT(*) FROM employees;
     

   • SUM(): Returns the sum of values in a numeric column.

     SELECT SUM(salary) FROM employees;
     

   • AVG(): Returns the average value of a numeric column.

     SELECT AVG(salary) FROM employees;
     

   • MIN(): Returns the minimum value in a column.

     SELECT MIN(salary) FROM employees;
     

   • MAX(): Returns the maximum value in a column.

     SELECT MAX(salary) FROM employees;
     

2. String Functions:

   • CONCAT(): Concatenates two or more strings together.

     SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
     

   • SUBSTRING(): Returns a substring from a string.

     SELECT SUBSTRING(description, 1, 10) AS short_description FROM products;
     

   • UPPER() / LOWER(): Converts a string to uppercase or lowercase.

     SELECT UPPER(last_name) AS last_name_upper FROM employees;
     

3. Date Functions:

   • CURRENT_DATE: Returns the current date.

     SELECT CURRENT_DATE;
     

   • DATE_FORMAT(): Formats a date according to a specified format.

     SELECT DATE_FORMAT(birth_date, '%Y-%m-%d') AS formatted_date FROM employees;
     

   • DATEDIFF(): Calculates the difference between two dates.

     SELECT DATEDIFF(end_date, start_date) AS days_difference FROM projects;
     

4. Math Functions:

   • ROUND(): Rounds a numeric value to a specified number of decimal places.

      

   • SELECT ROUND(salary, 2) FROM employees;

   • ABS(): Returns the absolute value of a numeric value.

     SELECT ABS(-10);
     

5. Conditional Functions:

   • CASE WHEN: Evaluates a series of conditions and returns a result.
     SELECT
         CASE 
             WHEN age < 18 THEN 'Minor'
             WHEN age >= 18 AND age < 65 THEN 'Adult'
             ELSE 'Senior'
         END AS age_group
     FROM customers;

These are just a few examples of SQL functions.

Each database system may have its own set of functions with additional features and variations.

Enroll Now

• SQL
• DBMS