SQL ROUND()  is used to round a numeric field to the number of decimals specified.

Syntaxt

SELECT ROUND(column_name,decimals) FROM table_name

Table – Users

+—–+—–+———–+
| oid | uid | price     |
+—–+—–+———–+
|   1 |   1 | 60.345645 |
|   2 |   2 | 30.45564  |
|   3 |   2 | 60.78565  |
|   4 |   1 | 70.96565  |
|   5 |   2 | 80.565645 |
|   6 |   1 | 50.57565  |
+—–+—–+———–+

Example

mysql> SELECT ROUND(price,2) as price  FROM orders;
+——-+
| price |
+——-+
| 60.35 |
| 30.46 |
| 60.79 |
| 70.97 |
| 80.57 |
| 50.58 |
+——-+
6 rows in set (0.00 sec)

SQL MID()  is used to extract characters from a text field.

Syntaxt

SELECT MID(column_name,start,length) FROM table_name

Table – Users

+—–+——-+
| uid | name  |
+—–+——-+
|   1 | name1 |
|   2 | name2 |
|   3 | name3 |
|   4 | name4 |
+—–+——-+

Example

mysql> SELECT MID(name,1,3) as name  FROM users;
+——+
| name |
+——+
| nam  |
| nam  |
| nam  |
| nam  |
+——+
4 rows in set (0.00 sec)

mysql> SELECT MID(name,2,4) as name  FROM users;
+——+
| name |
+——+
| ame1 |
| ame2 |
| ame3 |
| ame4 |
+——+
4 rows in set (0.00 sec)

SQL LCASE()  converts the value of a field to lowercase.

Syntaxt

SELECT LCASE(column_name) FROM table_name

Table – Users

+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+

Example

mysql> SELECT LCASE(name) FROM users;
+————-+
| LCASE(name) |
+————-+
| aaaa        |
| bbbb        |
| cccc        |
| dddd        |
| eeee        |
+————-+
5 rows in set (0.00 sec)

SQL UCASE()  converts the value of a field to uppercase.

Syntaxt

SELECT UCASE(column_name) FROM table_name

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | aaaa |
|   2 | bbbb |
|   3 | cccc |
|   4 | dddd |
|   5 | eeee |
+—–+——+

Example

mysql> SELECT UCASE(name) AS name  FROM users;
+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+
5 rows in set (0.00 sec)

SQL HAVING clause is used with aggregate functions.

Syntaxt

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | AAAA |
|   2 | BBBB |
|   3 | CCCC |
|   4 | DDDD |
|   5 | EEEE |
+—–+——+

Table – Orders

+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
|   1 |   1 | 60    |
|   2 |   2 | 30    |
|   3 |   2 | 60    |
|   4 |   1 | 70    |
|   5 |   2 | 80    |
|   6 |   1 | 50    |
+—–+—–+——-+

Example

mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid HAVING total >175;
+——-+——+
| total | name |
+——-+——+
|   180 | AAAA |
+——-+——+
1 row in set (0.00 sec)

SQL GROUP BY should be added with all aggregate functions and it s used in conjunction with the aggregate functions to group the result-set by one or more columns.

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | AAAA |
|   2 | BBBB |
|   3 | CCCC |
|   4 | DDDD |
|   5 | EEEE |
+—–+——+

Table – Orders

+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
|   1 |   1 | 60    |
|   2 |   2 | 30    |
|   3 |   2 | 60    |
|   4 |   1 | 70    |
|   5 |   2 | 80    |
|   6 |   1 | 50    |
+—–+—–+——-+

Example

mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid;;
+——-+——+
| total | name |
+——-+——+
| 180 | AAAA |
| 170 | BBBB |
+——-+——+
2 rows in set (0.00 sec)

SQL SUM()  returns the total sum of a numeric column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT SUM(mark1) FROM students ;
+————+
| SUM(mark1) |
+————+
|        151 |
+————+
1 row in set (0.00 sec)

SQL MIN()  returns the smallest value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT MIN(mark1) FROM students ;
+————+
| MIN(mark1) |
+————+
|         30 |
+————+
1 row in set (0.00 sec)

SQL MAX()  returns the largest value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT MAX(mark1) FROM students ;
+————+
| MAX(mark1) |
+————+
|         43 |
+————+
1 row in set (0.00 sec)