mysql>
mysql>
mysql> CREATE TABLE expt
-> (
-> subject VARCHAR(10),
-> test VARCHAR(5),
-> score INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','A',47);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','B',50);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','C',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','D',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','A',52);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','B',45);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','C',53);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','D',NULL);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM expt;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+---------+------+-------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> IFNULL(AVG(score),0) AS average,
-> IFNULL(MIN(score),'Unknown') AS lowest,
-> IFNULL(MAX(score),'Unknown') AS highest
-> FROM expt WHERE score IS NULL GROUP BY subject;
+---------+---+-------+---------+---------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+---------+---------+
| Jane | 0 | NULL | 0.0000 | Unknown | Unknown |
| Marvin | 0 | NULL | 0.0000 | Unknown | Unknown |
+---------+---+-------+---------+---------+---------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table expt;
Query OK, 0 rows affected (0.00 sec)