📚 MySQL Functions

## 🏗️ **What are Functions?** In MySQL, a function is a pre-defined piece of code that accepts input (parameters), performs a specific action, and returns a result. ### **1. Single Row Functions (Scalar Functions)** These functions operate on a **single row** at a time and return **one result per row**. * *Example:* If you use `UPPER(Name)` on a table with 5 rows, you get 5 capitalized names back. * *Types:* String, Numeric, Date/Time functions. ### **2. Multiple Row Functions (Aggregate Functions)** These functions operate on a **group of rows** and return a **single result** for the whole group. * *Example:* `SUM(Salary)` adds up salaries from all rows and gives just one total number. * *(Note: This guide focuses on Single Row functions as requested).* --- ## 🛠️ **The Setup: Sample Table** To make these examples easy to understand, we will use a single hypothetical table named **`EMPLOYEES`**. | EmpID | Name | Salary | Bonus | Rating | JoinDate | | --- | --- | --- | --- | --- | --- | | 101 | **Alice** | 50000.50 | 1200.75 | 4.5 | 2023-05-15 | | 102 | **Bob** | 65000.00 | 500.00 | -2.0 | 2021-11-20 | | 103 | **Cathy** | 45000.25 | 0.00 | 3.0 | 2024-01-10 | | 104 | **David** | 72000.90 | 2100.50 | 5.0 | 2020-12-25 | --- ## 🔤 **I. Text / String Functions** These functions manipulate text data. ### 1. `CHAR()` * **Purpose:** Returns the character for each integer passed (based on ASCII values). * **Syntax:** `CHAR(N, ...)` * **Example:** ```sql SELECT CHAR(65, 66, 67) AS Result; ``` * **Output:** `ABC` (65=A, 66=B, 67=C) ### 2. `CONCAT()` * **Purpose:** Joins two or more strings together. * **Syntax:** `CONCAT(str1, str2, ...)` * **Scenario:** Joining Name and "is an employee". ```sql SELECT CONCAT(Name, ' works here') AS Statement FROM EMPLOYEES WHERE EmpID=101; ``` * **Output:** `Alice works here` * *Note:* If any argument is `NULL`, the result is `NULL`. ### 3. `LOWER()` / `LCASE()` * **Purpose:** Converts string to lowercase. * **Syntax:** `LOWER(str)` * **Example:** ```sql SELECT LOWER(Name) FROM EMPLOYEES WHERE EmpID=102; ``` * **Output:** `bob` ### 4. `UPPER()` / `UCASE()` * **Purpose:** Converts string to uppercase. * **Syntax:** `UPPER(str)` * **Example:** ```sql SELECT UPPER(Name) FROM EMPLOYEES WHERE EmpID=101; ``` * **Output:** `ALICE` ### 5. `SUBSTR()` / `MID()` / `SUBSTRING()` * **Purpose:** Extracts a portion of a string. * **Syntax:** 1. `SUBSTR(str, pos, len)` -> Start at `pos`, take `len` characters. 2. `SUBSTR(str, pos)` -> Start at `pos`, take everything until the end. * **Scenario A (With Length):** ```sql SELECT SUBSTR(Name, 2, 3) FROM EMPLOYEES WHERE Name='Alice'; ``` * **Output:** `lic` (Starts at 2nd char 'l', takes 3 chars). * **Scenario B (No Length):** ```sql SELECT MID(Name, 3) FROM EMPLOYEES WHERE Name='David'; ``` * **Output:** `vid` (Starts at 3rd char 'v' to end). ### 6. `LTRIM()`, `RTRIM()`, `TRIM()` * **Purpose:** Removes whitespace. * `LTRIM`: Removes from Left (leading). * `RTRIM`: Removes from Right (trailing). * `TRIM`: Removes from Both sides. * **Scenario:** Imagine a messy input `' Data '`. ```sql SELECT LTRIM(' Data '), RTRIM(' Data '), TRIM(' Data '); ``` * **Output:** `'Data '`, `' Data'`, `'Data'` ### 7. `INSTR()` * **Purpose:** Returns the position of the *first occurrence* of a substring in a string. Returns 0 if not found. * **Syntax:** `INSTR(string, substring)` * **Scenario:** Find 'a' in 'David'. ```sql SELECT INSTR(Name, 'a') FROM EMPLOYEES WHERE Name='David'; ``` * **Output:** `2` (Because 'a' is the 2nd letter). ### 8. `LENGTH()` * **Purpose:** Returns the length of the string in bytes. * **Syntax:** `LENGTH(str)` * **Example:** ```sql SELECT LENGTH(Name) FROM EMPLOYEES WHERE Name='Bob'; ``` * **Output:** `3` ### 9. `LEFT()` / `RIGHT()` * **Purpose:** Extracts a specified number of characters from the left or right side. * **Syntax:** `LEFT(str, N)` or `RIGHT(str, N)` * **Example:** ```sql SELECT LEFT(Name, 2), RIGHT(Name, 1) FROM EMPLOYEES WHERE Name='Alice'; ``` * **Output:** `Al` (First 2 chars), `e` (Last 1 char). --- ## 🧮 **II. Numeric Functions** These functions perform mathematical operations on numbers. ### 1. `MOD()` * **Purpose:** Returns the remainder of a division. * **Syntax:** `MOD(N, M)` or `N % M` * **Scenario:** Checking if Salary is divisible by 1000. ```sql SELECT MOD(10, 3); ``` * **Output:** `1` (10 divided by 3 leaves a remainder of 1). ### 2. `POWER()` / `POW()` * **Purpose:** Returns the argument raised to the specified power. * **Syntax:** `POWER(X, Y)` (X to the power of Y). * **Example:** ```sql SELECT POWER(Rating, 2) FROM EMPLOYEES WHERE EmpID=103; ``` * **Output:** `9.0` (3.0 squared). ### 3. `SQRT()` * **Purpose:** Returns the square root of a non-negative number. * **Syntax:** `SQRT(N)` * **Example:** ```sql SELECT SQRT(25); ``` * **Output:** `5` ### 4. `SIGN()` * **Purpose:** Returns the sign of a number. * Returns `1` if number > 0. * Returns `-1` if number < 0. * Returns `0` if number = 0. * **Example:** ```sql SELECT SIGN(Rating) FROM EMPLOYEES WHERE EmpID=102; ``` * **Output:** `-1` (Since rating is -2.0). ### 5. `ROUND()` (Very Important for Exams!) * **Purpose:** Rounds a number to a specified number of decimal places. * **Syntax:** `ROUND(Number, Decimals)` * **Scenario A (Positive Decimals):** Rounds to nearest decimal. ```sql SELECT ROUND(50000.567, 2); ``` * **Output:** `50000.57` (6 rounds up to 7). * **Scenario B (Zero Decimals):** Rounds to nearest integer. ```sql SELECT ROUND(50000.50, 0); ``` * **Output:** `50001` * **Scenario C (Negative Decimals):** Rounds to the left of the decimal point (tens, hundreds). ```sql SELECT ROUND(125.50, -1); ``` * **Output:** `130` (Rounds to nearest 10). ### 6. `TRUNCATE()` * **Purpose:** Truncates (cuts off) a number to a specified number of decimals *without rounding*. * **Syntax:** `TRUNCATE(Number, Decimals)` * **Scenario A (Standard):** ```sql SELECT TRUNCATE(50000.567, 2); ``` * **Output:** `50000.56` (Simply drops the 7, no rounding). * **Scenario B (Negative Decimals):** ```sql SELECT TRUNCATE(125.50, -1); ``` * **Output:** `120` (Makes the ones place 0). --- ## 📅 **III. Date & Time Functions** These functions work with date values. Assume today is **2023-12-25** (Monday) for "Current" examples. ### 1. `NOW()` / `SYSDATE()` * **Purpose:** Returns current date and time. * **Syntax:** `NOW()` * **Example:** ```sql SELECT NOW(); ``` * **Output:** `2023-12-25 10:30:45` (Format: YYYY-MM-DD HH:MM:SS) ### 2. `CURDATE()` / `CURRENT_DATE()` * **Purpose:** Returns only the current date. * **Syntax:** `CURDATE()` * **Example:** ```sql SELECT CURDATE(); ``` * **Output:** `2023-12-25` ### 3. `DATE()` * **Purpose:** Extracts the date part from a datetime expression. * **Syntax:** `DATE(expression)` * **Example:** ```sql SELECT DATE('2023-12-25 10:30:45'); ``` * **Output:** `2023-12-25` ### 4. `DAY()` / `DAYOFMONTH()` * **Purpose:** Returns the day of the month (1-31). * **Syntax:** `DAY(date)` * **Example:** ```sql SELECT DAY(JoinDate) FROM EMPLOYEES WHERE EmpID=101; -- Date: 2023-05-15 ``` * **Output:** `15` ### 5. `MONTH()` * **Purpose:** Returns the month number (1-12). * **Syntax:** `MONTH(date)` * **Example:** ```sql SELECT MONTH(JoinDate) FROM EMPLOYEES WHERE EmpID=101; -- Date: 2023-05-15 ``` * **Output:** `5` ### 6. `YEAR()` * **Purpose:** Returns the year (YYYY). * **Syntax:** `YEAR(date)` * **Example:** ```sql SELECT YEAR(JoinDate) FROM EMPLOYEES WHERE EmpID=101; ``` * **Output:** `2023` ### 7. `DAYNAME()` * **Purpose:** Returns the name of the weekday (Sunday, Monday...). * **Syntax:** `DAYNAME(date)` * **Example:** ```sql SELECT DAYNAME('2023-12-25'); ``` * **Output:** `Monday` ### 8. `MONTHNAME()` * **Purpose:** Returns the name of the month (January, February...). * **Syntax:** `MONTHNAME(date)` * **Example:** ```sql SELECT MONTHNAME(JoinDate) FROM EMPLOYEES WHERE EmpID=101; ``` * **Output:** `May` ### 9. `DAYOFWEEK()` * **Purpose:** Returns the weekday index (1 = Sunday, 2 = Monday, ..., 7 = Saturday). *Note: This is the ODBC standard.* * **Syntax:** `DAYOFWEEK(date)` * **Example:** ```sql SELECT DAYOFWEEK('2023-12-25'); -- It is a Monday ``` * **Output:** `2` ### 10. `DAYOFYEAR()` * **Purpose:** Returns the day of the year (1-366). * **Syntax:** `DAYOFYEAR(date)` * **Example:** ```sql SELECT DAYOFYEAR('2023-02-01'); ``` * **Output:** `32` (31 days in Jan + 1st day of Feb). --- ## ⚡ **Cheat Sheet: ROUND vs TRUNCATE** This is the most common pitfall in Class 12 exams. | Value | Function | Result | Reason | | --- | --- | --- | --- | | **15.79** | `ROUND(15.79, 1)` | **15.8** | 9 is >= 5, so 7 becomes 8. | | **15.79** | `TRUNCATE(15.79, 1)` | **15.7** | Simply cuts off after 7. | | **15.29** | `ROUND(15.29, 1)` | **15.3** | 9 is >= 5, so 2 becomes 3. | | **15.29** | `TRUNCATE(15.29, 1)` | **15.2** | Simply cuts off after 2. |