
create table Person(Name TEXT, 
                    Sex TEXT NOT NULL, 
                    Age INTEGER, 
                    PRIMARY KEY(Name));
insert into Person values('Albert', 'M', 20);
insert into Person values('Dennis', 'M', 40);
insert into Person values('Evalyn', 'F', 20);
insert into Person values('John', 'M', 60);
insert into Person values('Mary', 'F', 40);
insert into Person values('Robert', 'M', 60);
insert into Person values('Susan', 'F', 40);

create table Birth(Parent TEXT, 
                   Child TEXT,
                   PRIMARY KEY(Parent, Child), 
                   FOREIGN KEY(Parent) REFERENCES Person(Name),
                   FOREIGN KEY(Child) REFERENCES Person(Name)); 
insert into Birth values('Dennis', 'Albert');
insert into Birth values('John', 'Mary');
insert into Birth values('Mary', 'Albert');
insert into Birth values('Robert', 'Evalyn');
insert into Birth values('Susan', 'Evalyn');
insert into Birth values('Susan', 'Richard');

create table Marriage(Wife TEXT,
                      Husband TEXT NOT NULL,
                      Age INTEGER,
                      PRIMARY KEY(Wife), 
                      FOREIGN KEY(Wife) REFERENCES Parent(Name),
                      FOREIGN KEY(Husband) REFERENCES Parent(Name));
insert into Marriage values('Dennis', 'Mary', 20);
insert into Marriage values('Robert', 'Susan', 30);

/*
 All women who are less that 32 years old
*/
SELECT Name
FROM Person
WHERE Age <= 32 AND Sex ='F';

/* 
The Daughter relationship with AS keyword.
*/
SELECT Parent, Child AS Daughter
FROM Person, Birth
WHERE Child = Name AND Sex = 'F';

