Functions in Programming

Functions in Programming

• A function is a block of code that executes only when called.
• Data passed into a function is referred to as parameters.
• Functions promote code reuse: define once, use many times.

Predefined Functions

• Common functions include main() for execution and printf() for output.
• Example of using printf() to display "Hello World!" in main() function.

Creating a Function

• Define a function by specifying its name, followed by parentheses and curly brackets.
• Example syntax: void myFunction() { // code to be executed }.
• void indicates the function does not return a value.

Calling a Function

• Functions are executed when called, not immediately upon declaration.
• To call a function, write its name followed by parentheses and a semicolon.
• Example: myFunction(); will execute the code defined in myFunction().

Multiple Calls and Nested Functions

• Functions can be called multiple times within the same program.
• Functions can also call other functions, demonstrating hierarchical execution.

Example of Function Use

• Function calculateSum() computes the sum of two variables x and y.
• The output of calculateSum() displays "The sum of x + y is: 15".
• The true potential of functions is realized by allowing dynamic inputs (parameters) for calculations.

Importance of Functions

• Functions help organize code and reduce redundancy by encapsulating behavior.
• The next chapter will cover how parameters enhance the functionality of functions.

In-built Functions

• In-built functions are expressions in SQL that execute operations using keywords or special operators.
• They are case-insensitive and identified as SQL92Identifiers.
• Utilized in SQL SELECT expressions for calculating values and manipulating data.

Types of In-built Functions

• Numeric Functions: Allow manipulation of numeric values, also known as mathematical functions.
• String Functions: Designed for string manipulation and returning output strings.

String Functions

• CHAR_LENGTH(): Returns length of a word; use LEN() in SQL Server.
  • Example: SELECT char_length('Hello!'); Output: 6
• ASCII(): Finds ASCII value of a character.
  • Example: SELECT ascii('t'); Output: 116
• CONCAT(): Concatenates two or more strings.
  • Example: SELECT CONCAT('My', 'S', 'QL'); Output: ‘MySQL’
• LEFT(): Retrieves leftmost characters from a string; returns NULL if arguments are NULL.
  • Example: SELECT LEFT('foobarbar', 5); Output: fooba
• INSTR(): Returns position of the first occurrence of a substring in a string.
• REVERSE(): Reverses the order of characters in a string.
• STRCMP(): Compares two strings and returns 0 if equal, -1 if the first is smaller, or 1 if larger.

Date and Time Functions

• Utilizes ISO-8601 date and time formats.
• date(): Returns date in YYYY-MM-DD format.
• time(): Returns time in HH:MM:SS format.
• datetime(): Returns combined format "YYYY-MM-DD HH:MM:SS".

Aggregate Functions

• Performs calculations on a set of values returning a single value.
• Generally ignores null values (except for COUNT(*)).
• Functions are deterministic, yielding the same result for the same input values.
• Groups values according to specified criteria.

Key Aggregate Functions

• COUNT(): Count of rows in a table, applicable to both numeric and non-numeric data.
  • COUNT(*) returns total count, including duplicates and NULLs.
• SUM(): Calculates the sum of selected columns (numeric data only).
• AVG(): Computes average of numeric values, ignoring nulls.
• MAX(): Finds maximum value in a specified column.
• MIN(): Identifies minimum value in a specified column.

Additional Notes on Aggregate Functions

• MySQL supports standard aggregate functions: COUNT, SUM, AVG, MIN, MAX.
• DISTINCT excludes duplicates; ALL includes all data (duplicate inclusion is default).
• Aggregate functions are compatible with clauses like GROUP BY.

GROUP BY Clause

• Used for summarizing or aggregating data instead of retrieving individual records.
• Organizes data into groups and allows functions (like min(), max(), avg(), count(), sum()) to apply on grouped data.
• Employs a split-apply-combine strategy for effective data analysis.