mysql>
mysql>
mysql>
mysql> CREATE TABLE COMMITTEE_MEMBERS
-> (EmployeeNO INTEGER NOT NULL,
-> BEGIN_DATE DATE NOT NULL,
-> END_DATE DATE ,
-> POSITION CHAR(20) ,
-> PRIMARY KEY (EmployeeNO, BEGIN_DATE));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1990-01-01', '1990-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1991-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1992-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1993-01-01', NULL, 'Chairman');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1990-01-01', '1992-12-31', 'Chairman');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1994-01-01', NULL, 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01', NULL, 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1990-01-01', '1990-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1991-01-01', '1991-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1993-01-01', '1993-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1994-01-01', NULL, 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01', NULL, 'Treasurer');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT *
-> FROM COMMITTEE_MEMBERS
-> WHERE (BEGIN_DATE, END_DATE) IN
-> (SELECT BEGIN_DATE, END_DATE
-> FROM COMMITTEE_MEMBERS
-> WHERE POSITION = 'Secretary');
+------------+------------+------------+-----------+
| EmployeeNO | BEGIN_DATE | END_DATE | POSITION |
+------------+------------+------------+-----------+
| 6 | 1990-01-01 | 1990-12-31 | Secretary |
| 112 | 1992-01-01 | 1992-12-31 | Member |
| 8 | 1990-01-01 | 1990-12-31 | Treasurer |
| 8 | 1991-01-01 | 1991-12-31 | Secretary |
| 57 | 1992-01-01 | 1992-12-31 | Secretary |
| 27 | 1990-01-01 | 1990-12-31 | Member |
| 27 | 1991-01-01 | 1991-12-31 | Treasurer |
+------------+------------+------------+-----------+
7 rows in set (0.00 sec)
mysql>
mysql> drop table COMMITTEE_MEMBERS;
Query OK, 0 rows affected (0.00 sec)