/*
mysql> SELECT bird.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM bird, event
-> WHERE bird.name = event.name AND event.type = 'sell';
+---------+------+----------------+
| name | age | remark |
+---------+------+----------------+
| RedBird | 16 | sell just sell |
+---------+------+----------------+
1 row in set (0.00 sec)
*/
/* Create table */
Drop table event;
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
CREATE TABLE event (
name VARCHAR(20),
date DATE,
type VARCHAR(15),
remark VARCHAR(255)
);
INSERT INTO Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO Bird VALUES ('RedBird1','Yin','Bus','m','1998-01-30',NULL);
insert into event values ('BlueBird','1995-05-15','buy','Buy just Buy');
insert into event values ('RedBird','1995-05-15','sell','sell just sell');
insert into event values ('RedBird2','1995-05-15','sell','sell and buy');
SELECT bird.name,
(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
remark
FROM bird, event
WHERE bird.name = event.name AND event.type = 'sell';
|