|
/*
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM bird AS p1, bird AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+-----------+------+----------+------+---------+
| name | sex | name | sex | species |
+-----------+------+----------+------+---------+
| BlueBird1 | f | RedBird1 | m | Bus |
| BlueBird1 | f | RedBird3 | m | Bus |
| BlueBird1 | f | RedBird4 | m | Bus |
+-----------+------+----------+------+---------+
3 rows in set (0.04 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ('BlueBird1','Joe','Bus','f','1999-03-30',NULL);
INSERT INTO Bird VALUES ('RedBird1','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO Bird VALUES ('BlueBird2','Joe','Car','f','1999-03-30',NULL);
INSERT INTO Bird VALUES ('RedBird3','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO Bird VALUES ('RedBird4','Yin','Bus','m','1998-01-30',NULL);
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM bird AS p1, bird AS p2
WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
|