DML: Functions

name: portada
layout: true
class: portada-slide, middle, right
---
# Data Manipulation Language (DML): Functions
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Functions
- Functions are a operation that take a certai number of parameters and return a result.
- Functions can be used in any SQL expression.
- Functions can be nested.

.center[[Build-in Functions MariaDB](https://mariadb.com/kb/en/built-in-functions/)]

---
# Numeric Functions

.center[[Numeric Functions MariaDB](https://mariadb.com/kb/en/numeric-functions/)]

```sql
POW(10, 2) --> 100 (Power of)
LOG(10, 100) --> 2 (Logarithm) If only one parameter, uses natural logarithm
PI() --> 3.1415... (π)
ABS(-10) --> 10 (Absolute value)
ROUND(10.25, 1) --> 10.3 (Rounds to the specified decimal point)
ROUND(10.25, 0) --> 10   (Rounds to the specified decimal point)
CEIL(10.25) --> 11 (Rounds up)
FLOOR(10.25) --> 10 (Rounds down)
```

---
# String Functions

.center[[String Functions MariaDB](https://mariadb.com/kb/en/string-functions/)]

```sql
ASCII("A") -- 65 (Return the numeric ascii value)
BINARY "A" = "a" -- FALSE (Returns de binary representation)
CONCAT("DAW", "1", ...) --> "DAW1" (Concatenates the provided strings)
LEFT("MariaDB", 2) --> "Ma" (Returns the leftmost characters)
RIGHT("MariaDB", 2) --> "DB" (Returns the rightmost characters)
UPPER("MariaDB") --> "MARIADB" (Returns a uppercase version of the string)
LOWER("MariaDB") --> "mariadb" (Returns a lowercase version of the string)
```

---
# DateTime Functions

.center[[DateTime Functions MariaDB](https://mariadb.com/kb/en/date-time-functions/)]

```sql
NOW() --> Returns current date and time
DATE_FORMAT("1707-04-25", "%d/%m/%Y")
--> "25/04/1707" (Formats the date to the specified format)
STR_TO_DATE("25/04/1707", "%d/%m/%Y")
--> "1707-04-25" (Returns a DATE object from a STR with the specified format)
DAY("1707-04-25") --> 25 (Returns the day of the month)
MONTH("1707-04-25") --> 4 (Returns the month)
YEAR("1707-04-25") --> 1707 (Returns the year)
HOUR("23:59:00") --> 23 (Returns the hour)
MINUTE("23:59:00") --> 59 (Returns the minute)
SECOND("23:59:00") --> 00 (Returns the second)
```

---
# Agreggate Functions
Agregate functions are used to compute a operations using a set of results.
This functions can be used with the GROUP BY clause.

```
SELECT COUNT(DNI) students --> Counts the number of rows
FROM student;
```

```text
+----------+
| students |
+----------+
|     1276 |
+----------+
```

---
# Agreggate Functions
```
COUNT() --> Counts the number of rows
COUNT(DISTINCT()) --> Counts the number of diferent rows
SUM() --> Sum the value in every row
MIN() --> Returns the minimum value
MAX() --> Returns the maximum value
AVG() --> Returns the average value
```

.center[[Aggregate Functions MariaDB](https://mariadb.com/kb/en/aggregate-functions/)]