-
mysql> select e_id, birth_date, p_email
-> from employee_per
-> where month(birth_date) = 4;
+------+------------+----------------------+
| e_id | birth_date | p_email |
+------+------------+----------------------+
| 2 | 1968-04-02 | johnny4@hotmail.com |
| 14 | 1965-04-28 | tinkertone@email.com |
| 17 | 1970-04-18 | mona@darling.com |
+------+------------+----------------------+
3 rows in set (0.01 sec)
~~~~ OR ~~~~
mysql> select e_id, birth_date, p_email
-> from employee_per
-> where MONTHNAME(birth_date) = 'April';
+------+------------+----------------------+
| e_id | birth_date | p_email |
+------+------------+----------------------+
| 2 | 1968-04-02 | johnny4@hotmail.com |
| 14 | 1965-04-28 | tinkertone@email.com |
| 17 | 1970-04-18 | mona@darling.com |
+------+------------+----------------------+
3 rows in set (0.00 sec)
-
mysql> select e_id, birth_date, s_name
-> from employee_per where
-> YEAR(birth_date) = 1968
-> ORDER BY s_name;
+------+------------+----------------+
| e_id | birth_date | s_name |
+------+------------+----------------+
| 20 | 1968-01-25 | NULL |
| 2 | 1968-04-02 | Jane Donner |
| 9 | 1968-05-19 | Manika Nanda |
| 13 | 1968-09-03 | Matt Shikari |
| 12 | 1968-02-15 | Rina Brighton |
| 3 | 1968-09-22 | Sandhya Pillai |
+------+------------+----------------+
6 rows in set (0.01 sec)
Note: The NULL value is listed at the top. We'll talk more about NULL in the next session.
-
mysql> select e_id from employee_per
-> where month(birth_date) = month(current_date);
+------+
| e_id |
+------+
| 8 |
| 19 |
| 20 |
+------+
3 rows in set (0.00 sec)
-
mysql> select distinct year(birth_date) from employee_per;
+------------------+
| year(birth_date) |
+------------------+
| 1957 |
| 1964 |
| 1965 |
| 1966 |
| 1967 |
| 1968 |
| 1969 |
| 1970 |
| 1972 |
| 1973 |
| 1974 |
| 1975 |
+------------------+
12 rows in set (0.00 sec)
-
mysql> select year(birth_date) as Year,
-> count(*) from employee_per
-> GROUP BY Year;
+------+----------+
| Year | count(*) |
+------+----------+
| 1957 | 1 |
| 1964 | 2 |
| 1965 | 1 |
| 1966 | 2 |
| 1967 | 1 |
| 1968 | 6 |
| 1969 | 1 |
| 1970 | 1 |
| 1972 | 2 |
| 1973 | 2 |
| 1974 | 1 |
| 1975 | 1 |
+------+----------+
12 rows in set (0.00 sec)
-
mysql> select MONTHNAME(birth_date) AS Month,
-> count(*) AS Number
-> from employee_per
-> GROUP BY Month
-> ORDER BY Number DESC;
+-----------+--------+
| Month | Number |
+-----------+--------+
| April | 3 |
| January | 3 |
| August | 2 |
| June | 2 |
| March | 2 |
| October | 2 |
| September | 2 |
| December | 1 |
| February | 1 |
| July | 1 |
| May | 1 |
| November | 1 |
+-----------+--------+
12 rows in set (0.00 sec)