Linux

Linux is a Unix-like computer operating system assembled under the model of free and open source software development and distribution. The defining component of Linux is the Linux kernel, an operating system kernel first released 5 October 1991 by Linus Torvalds.
Linux was originally developed as a free operating system for Intel x86-based personal computers.

It has since been ported to more computer hardware platforms than any other operating system. It is a leading operating system on servers and other big iron systems such as mainframe computers and supercomputers:

more than 90% of today’s 500 fastest supercomputers run some variant of Linux, including the 10 fastest. Linux also runs on embedded systems such as mobile phones, tablet computers, network routers, televisions  and video game consoles; the Android system in wide use on mobile devices is built on the Linux kernel.

The development of Linux is one of the most prominent examples of free and open source software collaboration: the underlying source code may be used, modified, and distributed—commercially or non-commercially—by anyone under licenses such as the GNU General Public License. Typically Linux is packaged in a format known as a Linux distribution for desktop and server use. Some popular mainstream Linux distributions include Debian (and its derivatives such as Ubuntu), Fedora and openSUSE. Linux distributions include the Linux kernel, supporting utilities and libraries and usually a large amount of application software to fulfill the distribution’s intended use.

A distribution oriented toward desktop use will typically include the X Window System and an accompanying desktop environment such as GNOME or KDE Plasma. Some such distributions may include a less resource intensive desktop such as LXDE or Xfce for use on older or less powerful computers. A distribution intended to run as a server may omit all graphical environments from the standard install and instead include other software such as the Apache HTTP Server and an SSH server such as OpenSSH. Because Linux is freely redistributable, anyone may create a distribution for any intended use. Applications commonly used with desktop Linux systems include the Mozilla Firefox web browser, the LibreOffice office application suite, and the GIMP image editor.

Since the main supporting user space system tools and libraries originated in the GNU Project, initiated in 1983 by Richard Stallman, the Free Software Foundation prefers the name GNU/Linux.

SQL ROUND()

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()

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()

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()

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

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

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()

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()

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)