MySQL quary part 3(ALIASES, NULLS, EXPLICIT SETS, GROUP BY, HAVING)

visit MySQL quary part 1
visit MySQL quary part 2
visit MySQL quary part 4

• ALIASES
• USE of DISTINCT
• NULLS IN SQL QUERIES
• EXPLICIT SETS
• GROUP BY
• HAVING

ALIASES

SELECT `manufacturer` AS 'MAN', `details` AS 'Det' FROM `powerplant`.`turbine`;

+------+-----+
| MAN  | Det |
+------+-----+
| man1 | yes |
| man2 | no  |
| man3 | no  |
| man2 | no  |
| man2 | yes |
+------+-----+

SELECT G.id, T.details, T.weight
FROM genarator AS G, turbine AS T
WHERE G.id=T.genarator AND T.details='no';

+----+---------+--------+
| id | details | weight |
+----+---------+--------+
|  1 | no      |     20 |
|  2 | no      |     50 |
|  2 | no      |     20 |
+----+---------+--------+

SELECT genarator.id, turbine.details, turbine.weight
FROM genarator, turbine
WHERE genarator.id=turbine.genarator AND turbine.details='no';

+----+---------+--------+
| id | details | weight |
+----+---------+--------+
|  1 | no      |     20 |
|  2 | no      |     50 |
|  2 | no      |     20 |
+----+---------+--------+

USE of DISTINCT

/* The SELECT DISTINCT statement is used to return only distinct (different) values.*/

SELECT manufacturer FROM turbine;

+--------------+
| manufacturer |
+--------------+
| man1         |
| man2         |
| man3         |
| man2         |
| man2         |
+--------------+

SELECT DISTINCT manufacturer FROM turbine;

+--------------+
| manufacturer |
+--------------+
| man1         |
| man2         |
| man3         |
+--------------+

NULLS IN SQL QUERIES

SELECT * FROM turbine WHERE weight IS NOT NULL;

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

SELECT * FROM turbine WHERE weight IS  NULL;

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

EXPLICIT SETS

/*It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested query*/

SELECT * FROM powerplant.turbine WHERE manufacturer in('man1','man2');

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

GROUP BY

/* SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause*/

SELECT details,COUNT(details) FROM powerplant.turbine GROUP BY details;

+---------+----------------+
| details | COUNT(details) |
+---------+----------------+
| no      |              3 |
| yes     |              2 |
+---------+----------------+

HAVING

/* The HAVING-clause is used for specifying a selection condition on groups (rather than on individual tuples)*/

SELECT details,COUNT(details) FROM powerplant.turbine GROUP BY details HAVING COUNT(details)>2;

+---------+----------------+
| details | COUNT(details) |
+---------+----------------+
| no      |              3 |
+---------+----------------+