Recall our optimized tables:

Id	Bird Name	Birthday	Male
1	Peter	2004-11-05	T
2	John	2004-12-21	T
3	Mary	2003-05-02	F
4	Jane	2005-07-12	F
5	Bob	2003-06-10	T
6	Ellen	2002-11-27	F

Bird_Id	Parent_Id
1	3
1	5
2	3
3	6
4	3
4	2
5	6

Sample queries to create and operate on these tables

select text with mouse and middle-click in MySQL window to enter queries quickly

1. Start up the MySQL daemon, start a MySQL user session and select a database (from UNIX terminal)

   /etc/init.d/mysqld start

   mysql

   use test;

2. create and fill and display birds table (various fill methods shown)
   Note how a NULL id value will use the next available integer

   create table birds ( id smallint unsigned auto_increment not null primary key, name char(63) binary not null, birthday date not null, male bool not null, index (name), index (male) ); 

   insert into birds (id,name,birthday,male) values (1,"Peter","2004-11-05",1);

   insert into birds (id,name,birthday,male) values (NULL,"John","2004-12-21",1);

   insert into birds set id=NULL,name="Mary",birthday="2003-05-02",male=0;

   insert into birds set id=4,name="Jane",birthday="2005-07-12",male=0;

   insert into birds (id,name,birthday,male) values (NULL,"Bob","2003-06-10",1);

   insert into birds (id,name,birthday,male) values (6,"Ellen","2002-11-27",0);

   select * from birds;

3. create and fill and display parents table (various fill methods shown)

   create table parents ( bird_id smallint unsigned not null, parent_id smallint unsigned not null, primary key (bird_id,parent_id) ); 

   insert low_priority into parents (bird_id,parent_id) values (1,3);

   insert into parents (bird_id,parent_id) values (1,5);

   insert ignore into parents set bird_id=2, parent_id=3;

   insert low_priority ignore into parents set bird_id=3, parent_id=6;

   insert into parents (bird_id,parent_id) values (4,3);

   insert into parents (bird_id,parent_id) values (4,2);

   insert into parents (bird_id,parent_id) values (5,6);

   select * from parents;

4. Remind yourself what the table structure is

   describe birds;

   describe parents;

5. count how many birds

   select count(*) from birds;

6. count how many male birds

   select count(*) from birds where male=1;

7. list female birds

   select * from birds where male=0;

8. create a new table of just male birds selected from birds table
   

   create table malebirds ( id smallint unsigned auto_increment not null primary key, name char(63) binary not null, birthday date not null, male bool not null, index (name), index (male) ); 

   insert into malebirds select * from birds where male=1;

   select * from malebirds;

   alter table malebirds drop column male;

   select * from malebirds;

9. count number of parents for each bird with a parent

   select name, count(*) from birds, parents where id=bird_id group by id;

10. list parents by bird name and parent_id

    select name, parent_id from birds, parents where id=bird_id;

11. list parents by name (note line continuation)

    select Bird.name as bird, Parent.name as parent from birds as Bird, birds as Parent, parents \
       where Bird.id=bird_id and Parent.id=parent_id;

12. insert a new parent: John's dad is Bob (genetic testing was apparently done) and retry previous query

    insert into parents set bird_id=2, parent_id=5;

    select Bird.name as bird, Parent.name as parent from birds as Bird, birds as Parent, parents \
       where Bird.id=bird_id and Parent.id=parent_id;

13. Peter and Jane make beautiful music together, and George is born

    insert into birds (id,name,birthday,male) values (7,"George","2005-12-25",0);

    insert into parents (bird_id,parent_id) values (7,1);

    insert into parents (bird_id,parent_id) values (7,4);

14. list all info for birds with a known mother

    select Bird.id,Bird.name as bird,Bird.birthday,Bird.male, Parent.name as mother \
       from birds as Bird, birds as Parent, parents \
       where Bird.id=parents.bird_id and parents.parent_id=Parent.id and Parent.male=0;
    

15. the age of all birds in fractional years (note "age" alias to simplify returned column heading)

    select name, year(curdate()) + dayofyear(curdate())/365.25 - year(birthday) - dayofyear(birthday)/365.25 as age from birds;

16. the average age of all birds in fractional years (note "age" alias to simplify returned column heading)

    select avg( year(curdate()) + dayofyear(curdate())/365.25 - year(birthday) - dayofyear(birthday)/365.25 ) as average_age from birds;

17. determine the most reproductive bird

    select name, count(*) as offspring from birds, parents where id=parent_id group by id order by offspring desc limit 1;

18. show all tables in database

    show tables;

19. show the status of all the tables in detail

    show table status;

20. find the grandparents by name of all birds, if known

    figure it out yourself!  
    You will need to use the birds table three times and the parents table twice

21. delete the tables you have created

    drop table birds;

    drop table parents;