How many types function in sql with example

1. Aggregate Functions: These operate on a set of values and return a single value.

   • COUNT(): Counts the number o

     SELECT COUNT(*) FROM employees;
     

   • SUM(): Adds up the values of a column

     SELECT SUM(salary) FRO employees;
     

   • AVG(): Calculates the average value.

     
     SELECT AVG(salary) FROM employees;
     

   • MAX(): Finds the maximum value.

     sql
     SELECT MAX(salary) FROM employees;
     

   • MIN(): Finds the minimum value.

     sql 
     SELECT MIN(salary) FROM employees;
     

2. Scalar Functions: These operate on a single value and return a single value.

   • UPPER(): Converts a string to uppercase.

     sql
     SELECT UPPER(name) FROM employees;
     

   • LOWER(): Converts a string to lowercase.

     sql
     SELECT LOWER(name) FROM employees;
     

   • ROUND(): Rounds a number to a specified decimal place.

     sql
     SELECT ROUND(salary, 2) FROM employees;
     

   • LENGTH(): Returns the length of a string.

     sql
     SELECT LENGTH(name) FROM employees;
     

   • NOW(): Returns the current date and time.

     sql
     SELECT NOW();
     

3. Date and Time Functions: These handle date and time operations.

   • DATEADD(): Adds a specified time interval to a date.

     sql
     SELECT DATEADD(day, 10, GETDATE());
     

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

     sql
     SELECT DATEDIFF(day, '2024-01-01', GETDATE());
     

   • FORMAT(): Formats a date or time value.

     sql
     SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
     

4. String Functions: These are used to manipulate and analyze string data.

   • CONCAT(): Concatenates two or more strings.

     sql
     SELECT CONCAT(first_name, ' ', last_name) FROM employees;
     

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

     sql
     SELECT SUBSTRING(name, 1, 3) FROM employees;
     

   • REPLACE(): Replaces occurrences of a substring within a string.

     sql
     SELECT REPLACE(description, 'old', 'new') FROM products;
     

5. Conversion Functions: Convert data from one type to another.

   • CAST(): Converts a value to a specified data type.

     sql
     SELECT CAST(price AS DECIMAL(10, 2)) FROM products;
     

   • CONVERT(): Similar to CAST(), but with more options.

     sql
     SELECT CONVERT(VARCHAR, hire_date, 103) FROM employees;
     

These functions help in performing various operations on data stored in SQL databases, making it easier to retrieve, analyze, and manipulate information.