The NULL column type is special in many ways. To insert a NULL value, just leave the column name from the INSERT statement. Columns have NULL as default unless specified by NOT NULL. You can have null values for integers as well as text or binary data.
NULL cannot be compared using arithemetic operators. Comparisions for NULL take place with IS NULL or IS NOT NULL.
select e_id, children
from employee_per
where children IS NOT NULL;
+——+———-+
| e_id | children |
+——+———-+
| 2 | 3 |
| 3 | 2 |
| 7 | 3 |
| 9 | 1 |
| 11 | 4 |
| 12 | 3 |
| 13 | 2 |
| 15 | 3 |
| 16 | 2 |
| 17 | 1 |
| 21 | 2 |
+——+———-+
11 rows in set (0.00 sec)
The above lists ids and no. of children of all employees who have children.
Assignment:
1.Find and list the ids and spouse names of all employees who are married.
2.Change the above so that the display is sorted on spouse names.
3.How many employees do we have under each sex (male and female)?
4.How many of our employees are married and unmarried?
5.Find the total number of children.
6.Make unique groups of children and find the number in each group. Sort the display with the group having maximum children, at the top.
Possible Answers
1.mysql> select e_id, s_name
-> from employee_per
-> where m_status = 'Y';
+——+—————–+
| e_id | s_name |
+——+—————–+
| 1 | Anamika Sharma |
| 2 | Jane Donner |
| 3 | Sandhya Pillai |
| 4 | Manish Sharma |
| 7 | Mary Shelly |
| 9 | Manika Nanda |
| 11 | Muriel Lovelace |
| 12 | Rina Brighton |
| 13 | Matt Shikari |
| 15 | Betty Cudly |
| 16 | Stella Stevens |
| 17 | Edgar Alan |
| 21 | Ruby Richer |
+——+—————–+
13 rows in set (0.01 sec)
~~~~ OR ~~~~
mysql> select e_id, s_name
-> from employee_per
-> where s_name IS NOT NULL;
+——+—————–+
| e_id | s_name |
+——+—————–+
| 1 | Anamika Sharma |
| 2 | Jane Donner |
| 3 | Sandhya Pillai |
| 4 | Manish Sharma |
| 7 | Mary Shelly |
| 9 | Manika Nanda |
| 11 | Muriel Lovelace |
| 12 | Rina Brighton |
| 13 | Matt Shikari |
| 15 | Betty Cudly |
| 16 | Stella Stevens |
| 17 | Edgar Alan |
| 21 | Ruby Richer |
+——+—————–+
13 rows in set (0.00 sec)
2.mysql> select e_id, s_name
-> from employee_per
-> where m_status = 'Y'
-> ORDER BY s_name;
mysql> select e_id, s_name
-> from employee_per
-> where s_name IS NOT NULL
-> ORDER BY s_name;
3.mysql> select sex, count(*)
-> from employee_per
-> GROUP BY sex;
+——+———-+
| sex | count(*) |
+——+———-+
| M | 16 |
| F | 5 |
+——+———-+
2 rows in set (0.01 sec)
4.mysql> select m_status, count(*)
-> from employee_per
-> GROUP BY m_status;
+———-+———-+
| m_status | count(*) |
+———-+———-+
| Y | 13 |
| N | 8 |
+———-+———-+
2 rows in set (0.01 sec)
5.mysql> select sum(children) from employee_per;
+—————+
| sum(children) |
+—————+
| 26 |
+—————+
1 row in set (0.01 sec)
6.mysql> select children, count(*) AS
-> number from employee_per
-> GROUP BY children
-> ORDER BY number DESC;
+———-+——–+
| children | number |
+———-+——–+
| NULL | 10 |
| 2 | 4 |
| 3 | 4 |
| 1 | 2 |
| 4 | 1 |
+———-+——–+
5 rows in set (0.01 sec)