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)

SQL LAST() returns the last 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

SELECT LAST(*) AS total FROM students ;

SQL FIRST() returns the first 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

SELECT FIRST(*) AS total FROM students ;

SQL COUNT() returns the number of rows

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 COUNT(*) AS total FROM students ;
+——-+
| total |
+——-+
|     4 |
+——-+
1 row in set (0.01 sec)

SQL AVG() function returns the average value 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 AVG(mark1) FROM students ;
+————+
| AVG(mark1) |
+————+
|    37.7500 |
+————+
1 row in set (0.02 sec)