MySQL quary part 4(ORDER BY, ARITHMETIC, NESTING OF QUERIES, EXISTS, UNION)

visit MySQL quary part 1
visit MySQL quary part 2
visit MySQL quary part 3

• ORDER BY
• AGGREGATE FUNCTIONS
• ARITHMETIC OPERATIONS
• SUBSTRING COMPARISON
• NESTING OF QUERIES
• THE EXISTS FUNCTION
• SET OPERATION (UNION)

ORDER BY

SELECT * FROM powerplant.turbine ORDER BY weight;

+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
|  5 | man2         | yes     |         1 |   NULL |
|  1 | man1         | yes     |         1 |     10 |
|  2 | man2         | no      |         1 |     20 |
|  4 | man2         | no      |         2 |     20 |
|  3 | man3         | no      |         2 |     50 |
+----+--------------+---------+-----------+--------+

AGGREGATE FUNCTIONS

/*********** COUNT, SUM, MAX, MIN, and AVG ***************/

SELECT COUNT(outputpower),SUM(outputpower),MAX(outputpower),MIN(outputpower), AVG(outputpower) FROM genarator;

+--------------------+------------------+------------------+------------------+------------------+
| COUNT(outputpower) | SUM(outputpower) | MAX(outputpower) | MIN(outputpower) | AVG(outputpower) |
+--------------------+------------------+------------------+------------------+------------------+
|                  2 |               30 |               20 |               10 |               15 |
+--------------------+------------------+------------------+------------------+------------------+

ARITHMETIC OPERATIONS

/* The standard arithmetic operators '+', '-'. '*', and '/' (for addition,subtraction, multiplication, and division, respectively) can be applied to numeric values in an SQL query result*/

SELECT weight,weight+1,weight*2,weight-5,weight/2 FROM powerplant.turbine;

+--------+----------+----------+----------+----------+
| weight | weight+1 | weight*2 | weight-5 | weight/2 |
+--------+----------+----------+----------+----------+
|     10 |       11 |       20 |        5 |        5 |
|     20 |       21 |       40 |       15 |       10 |
|     50 |       51 |      100 |       45 |       25 |
|     20 |       21 |       40 |       15 |       10 |
|   NULL |     NULL |     NULL |     NULL |     NULL |
+--------+----------+----------+----------+----------+

SUBSTRING COMPARISON

/* Two reserved characters are used: '%' (or '*' in some implementations) replaces an arbitrary number of characters, and '_' replaces a single arbitrary character*/

SELECT * FROM powerplant.turbine WHERE details LIKE '_e%';

+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
|  1 | man1         | yes     |         1 |     10 |
|  5 | man2         | yes     |         1 |   NULL |
+----+--------------+---------+-----------+--------+

NESTING OF QUERIES

SELECT * FROM turbine WHERE turbine.genarator IN (SELECT id from genarator WHERE outputpower=20 );

+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
|  3 | man3         | no      |         2 |     50 |
|  4 | man2         | no      |         2 |     20 |
+----+--------------+---------+-----------+--------+

THE EXISTS FUNCTION

/* EXISTS is used to check whether the result of a correlated nested query is empty*/

SELECT * FROM turbine WHERE EXISTS (SELECT id from genarator WHERE id=turbine.genarator AND outputpower=20 );

+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
|  3 | man3         | no      |         2 |     50 |
|  4 | man2         | no      |         2 |     20 |
+----+--------------+---------+-----------+--------+

SET OPERATION (UNION)

SELECT * from turbine,genarator 
WHERE turbine.genarator=genarator.id AND turbine.id=1
 UNION SELECT * from turbine,genarator 
 WHERE turbine.genarator=genarator.id AND genarator.id=2;

+----+--------------+---------+-----------+--------+----+-------------+
| id | manufacturer | details | genarator | weight | id | outputpower |
+----+--------------+---------+-----------+--------+----+-------------+
|  1 | man1         | yes     |         1 |     10 |  1 |          10 |
|  3 | man3         | no      |         2 |     50 |  2 |          20 |
|  4 | man2         | no      |         2 |     20 |  2 |          20 |
+----+--------------+---------+-----------+--------+----+-------------+