-
mysql> select count(*) from employee_data
-> where yos >= 4;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.01 sec)
-
mysql> select age, count(*)
-> from employee_data
-> GROUP BY age;
+------+----------+
| age | count(*) |
+------+----------+
| 25 | 1 |
| 26 | 1 |
| 27 | 3 |
| 28 | 1 |
| 30 | 1 |
| 31 | 1 |
| 32 | 6 |
| 33 | 1 |
| 34 | 2 |
| 35 | 1 |
| 36 | 2 |
| 43 | 1 |
+------+----------+
12 rows in set (0.00 sec)
-
mysql> select age, count(*)
-> from employee_data
-> GROUP BY age
-> ORDER by age DESC;
+------+----------+
| age | count(*) |
+------+----------+
| 43 | 1 |
| 36 | 2 |
| 35 | 1 |
| 34 | 2 |
| 33 | 1 |
| 32 | 6 |
| 31 | 1 |
| 30 | 1 |
| 28 | 1 |
| 27 | 3 |
| 26 | 1 |
| 25 | 1 |
+------+----------+
12 rows in set (0.00 sec)
-
mysql> select title, AVG(age)
-> from employee_data
-> GROUP BY title;
+----------------------------+----------+
| title | AVG(age) |
+----------------------------+----------+
| CEO | 28.0000 |
| Customer Service Manager | 32.0000 |
| Finance Manager | 36.0000 |
| Marketing Executive | 28.0000 |
| Multimedia Programmer | 36.0000 |
| Programmer | 30.5000 |
| Senior Marketing Executive | 36.0000 |
| Senior Programmer | 32.0000 |
| Senior Web Designer | 32.0000 |
| System Administrator | 34.5000 |
| Web Designer | 26.5000 |
+----------------------------+----------+
11 rows in set (0.00 sec)
-
mysql> select title, AVG(age)
-> AS 'Average Age'
-> from employee_data
-> GROUP BY title
-> ORDER BY 'Average Age' DESC;
+----------------------------+-------------+
| title | Average Age |
+----------------------------+-------------+
| Finance Manager | 36.0000 |
| Multimedia Programmer | 36.0000 |
| Senior Marketing Executive | 36.0000 |
| System Administrator | 34.5000 |
| Customer Service Manager | 32.0000 |
| Senior Programmer | 32.0000 |
| Senior Web Designer | 32.0000 |
| Programmer | 30.5000 |
| CEO | 28.0000 |
| Marketing Executive | 28.0000 |
| Web Designer | 26.5000 |
+----------------------------+-------------+
11 rows in set (0.01 sec)
Note: We need to give a pseudoname to the column that contains the average ages so that we can sort it.
-
mysql> select f_name, l_name,
-> ((perks/salary) * 100) as '% Perk'
-> from employee_data
-> ORDER BY '% Perk' DESC;
+---------+------------+--------+
| f_name | l_name | % Perk |
+---------+------------+--------+
| Monica | Sehgal | 27.78 |
| Hal | Simlai | 25.71 |
| Manish | Sharma | 25.00 |
| Joseph | Irvine | 25.00 |
| Mike | Harper | 23.33 |
| John | Hagan | 20.83 |
| Peter | Champion | 20.83 |
| Fred | Kruger | 20.00 |
| John | MacFarland | 20.00 |
| Arthur | Hoopla | 20.00 |
| Edward | Sakamuro | 18.67 |
| Ganesh | Pillai | 18.18 |
| Kim | Hunter | 18.18 |
| Mary | Anchor | 17.65 |
| Anamika | Pandit | 16.67 |
| Hassan | Rajabi | 16.67 |
| Alok | Nanda | 14.29 |
| Paul | Simon | 14.12 |
| Danny | Gibson | 13.33 |
| Roger | Lewis | 13.00 |
| Shahida | Ali | 12.86 |
+---------+------------+--------+
21 rows in set (0.00 sec)