mysql>
mysql> CREATE TABLE Classes
-> (
-> ClassID SMALLINT NOT NULL PRIMARY KEY,
-> Dept CHAR(4) NOT NULL,
-> Level ENUM('Upper', 'Lower') NOT NULL,
-> TotalStudents TINYINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO Classes VALUES
-> (1001, 'ANTH', 'Upper', 25),
-> (1002, 'ANTH', 'Upper', 25),
-> (1003, 'MATH', 'Upper', 18),
-> (1004, 'ANTH', 'Lower', 19),
-> (1005, 'ENGL', 'Upper', 28),
-> (1006, 'MATH', 'Lower', 23),
-> (1007, 'ENGL', 'Upper', 25),
-> (1008, 'MATH', 'Lower', 29),
-> (1009, 'ANTH', 'Upper', 25),
-> (1010, 'ANTH', 'Lower', 30),
-> (1011, 'ENGL', 'Lower', 26),
-> (1012, 'MATH', 'Lower', 22),
-> (1013, 'ANTH', 'Upper', 27),
-> (1014, 'ANTH', 'Upper', 21),
-> (1015, 'ENGL', 'Lower', 25),
-> (1016, 'ENGL', 'Upper', 32);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT Dept, Level, MAX(TotalStudents) AS Maximum
-> FROM Classes
-> GROUP BY Dept, Level WITH ROLLUP;
+------+-------+---------+
| Dept | Level | Maximum |
+------+-------+---------+
| ANTH | Upper | 27 |
| ANTH | Lower | 30 |
| ANTH | NULL | 30 |
| ENGL | Upper | 32 |
| ENGL | Lower | 26 |
| ENGL | NULL | 32 |
| MATH | Upper | 18 |
| MATH | Lower | 29 |
| MATH | NULL | 29 |
| NULL | NULL | 32 |
+------+-------+---------+
10 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)
|