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 (Parent, Daughter) relationship with AS keyword.
*/
CREATE TABLE Parents AS
SELECT Parent, Child AS Daughter
FROM Person, Birth
WHERE Child = Name AND Sex = 'F';

/*
The (Father, Daughter) relationship with AS keyword.
*/
/*
CREATE TABLE Fathers AS
SELECT Person.Name AS Father, Person1.Name AS Daughter 
FROM Person, Birth, Person AS Person1 
WHERE Child = Person1.Name 
AND Parent = Person.Name
AND Person.Sex = 'M' AND Person1.Sex = 'F';
SELECT * FROM FATHERS;
*/

/* 
(Grandparent, Grandchild)
*/
SELECT Birth.Parent AS Grandparent, Birth1.Child AS Grandchild 
FROM Birth, Birth AS Birth1
WHERE Birth.Child = Birth1.Parent;