Built-in Database Functions

 

1. Aggregate Functions: These functions operate on a collection of values, such as all the values in a column, and return a single value or null. Examples include:

   • SUM(): Adds up all the values in a column.
   • MIN(): Returns the lowest value.
   • MAX(): Returns the highest value.
   • AVG(): Returns the average or mean value.

2. Using Aggregate Functions:

   • Aggregate functions are applied to columns by specifying the column name within parentheses after the function name.
   • It's possible to explicitly name the resulting column using the AS keyword.

3. Scalar Functions: These functions operate on individual values and can perform operations like rounding, string manipulation, etc. Examples include:

   • ROUND(): Rounds values to the nearest integer.
   • LENGTH(): Returns the length of a string.
   • UPPERCASE(), LOWERCASE(): Convert strings to uppercase or lowercase.

4. Using Scalar Functions:

   • Scalar functions are applied to individual values or columns in a similar manner to aggregate functions.

5. String Functions:

   • A subset of scalar functions specifically designed for string manipulation.
   • Useful for working with character and varchar values.

6. Using String Functions:

   • Examples include functions like LENGTH(), UPPERCASE(), LOWERCASE().
   • These functions can be used in the SELECT statement to manipulate string values.

7. Combining Functions:

   • Functions can be nested, allowing one function to operate on the output of another function.
   • For example, UPPERCASE(ANIMAL) can be nested within DISTINCT to get unique uppercase values from the ANIMAL column.

Understanding and leveraging these built-in functions can greatly enhance the efficiency and effectiveness of SQL queries, allowing for complex data manipulation directly within the database. Thank you for watching!