Using Date to sort data
select e_id, birth_date
from employee_per
ORDER BY birth_date;
+——+————+
| e_id | birth_date |
+——+————+
| 11 | 1957-11-04 |
| 16 | 1964-03-06 |
| 21 | 1964-06-13 |
| 14 | 1965-04-28 |
| 15 | 1966-06-23 |
| 7 | 1966-08-20 |
| 10 | 1967-07-06 |
| 20 | 1968-01-25 |
| 12 | 1968-02-15 |
| 2 | 1968-04-02 |
| 9 | 1968-05-19 |
| 13 | 1968-09-03 |
| 3 | 1968-09-22 |
| 6 | 1969-12-31 |
| 17 | 1970-04-18 |
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 19 | 1973-01-20 |
| 18 | 1973-10-09 |
| 5 | 1974-10-13 |
| 8 | 1975-01-12 |
+——+————+
Selecting data using Dates
Here is how we can select employees born in March.
select e_id, birth_date
from employee_per
where MONTH(birth_date) = 3;
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 16 | 1964-03-06 |
+——+————+
2 rows in set (0.00 sec)
Alternatively, we can use month names instead of numbers.
select e_id, birth_date
from employee_per
where MONTHNAME(birth_date) = 'January';
+——+————+
| e_id | birth_date |
+——+————+
| 8 | 1975-01-12 |
| 19 | 1973-01-20 |
| 20 | 1968-01-25 |
+——+————+
3 rows in set (0.00 sec)
Be careful when using month names as they are case sensitive. Thus, January will work but JANUARY will not!
Similarly, you can select employees born in a specific year or under specific dates.
select e_id, birth_date
from employee_per
where year(birth_date) = 1972;
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
+——+————+
2 rows in set (0.00 sec)
select e_id, birth_date
from employee_per
where DAYOFMONTH(birth_date) = 20;
+——+————+
| e_id | birth_date |
+——+————+
| 7 | 1966-08-20 |
| 19 | 1973-01-20 |
+——+————+
2 rows in set (0.00 sec)
Current dates
We had seen in the session on SELECT statement (A little more on the SELECT statement) that current date, month and year can be displayed with CURRENT_DATE argument to DAYOFMONTH(), MONTH() and YEAR() clauses, respectively. The same can be used to select data from tables.
select e_id, birth_date
from employee_per where
MONTH(birth_date) = MONTH(CURRENT_DATE);
+——+————+
| e_id | birth_date |
+——+————+
| 8 | 1975-01-12 |
| 19 | 1973-01-20 |
| 20 | 1968-01-25 |
+——+————+
3 rows in set (0.00 sec)
Assignment:
1.List ids, birth dates and emails of employes born in April.
2.Display Ids, birth dates and spouse names of employees born in 1969 and sort the entires on the basis of their spouse names.
3.List the emploee ids for employees born under the current month.
4.How many unique birth years do we have?
5.Display a list of unique birth years and the number of employees born under each.
6.How many employees were born under each month? The display should have month names (NOT numbers) and the entries should be sorted with the month having the largest number listed first.
Possible Answers:
1.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)
2.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.
3.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)
4.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)
5.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)
6.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)