create table people as
select "Martin" as parent, "George" as child union
select "Christina", "George" union
select "George", "Martin F" union
select "Johanna", "Martin F" union
select "George N", "Paul" union
select "George N", "Ann" union
select "George N", "John" union
select "Martin F", "George N" union
select "Martin F", "Robert" union
select "Martin F", "Donald" union
select "Donald", "Peter";
select "**Example 1**";
with foreigner(person) as (
select "Martin" union
select "Christina" union
select "Johanna"
)
select distinct child from people, foreigner
where people.parent = foreigner.person;
select "**Example 2**";
with kin(first, second) as (
select a.child, b.child
from people as a, people as b
where a.parent = b.parent
and a.child != b.child )
select distinct kin.second, child
from people, kin
where kin.first = parent;
select "**Example 3**";
create table ints as
with ints(n) as (
select 1 union
select n+1 from ints where n<30
)
select n from ints;
select n from ints;
select "**Example 4**";
with
related(ancestor, descendant) as (
select parent, child from people union
select ancestor, child from related, people
where descendant = parent
)
select ancestor from related where descendant = "Paul";
select "**Example 5**";
-- Pythagorean Triples
select a.n, b.n, c.n from ints as a, ints as b, ints as c
where a.n < b.n and a.n * a.n + b.n * b.n = c.n * c.n;
select "**Example 6**";
-- Taxicab numbers.
with cubes(x, y, cube) as (
select a.n, b.n, a.n*a.n*a.n + b.n*b.n*b.n
from ints as a, ints as b
where a.n <= b.n
) select first.x, first.y, second.x, second.y, first.cube
from cubes as first, cubes as second
where first.cube = second.cube and first.x < second.x;