-- Cities
create table cities as
  select 38 as latitude, 122 as longitude, "Berkeley" as name union
  select 42,             71,               "Cambridge"        union
  select 45,             93,               "Minneapolis";

-- Parents
create table parents as
  select "abraham" as parent, "barack" as child union
  select "abraham"          , "clinton"         union
  select "delano"           , "herbert"         union
  select "fillmore"         , "abraham"         union
  select "fillmore"         , "delano"          union
  select "fillmore"         , "grover"          union
  select "eisenhower"       , "fillmore";

-- Children of Abraham
select child from parents where parent = "abraham";

-- Fillmores
select parent from parents where parent > child;

-- Operations
create table homes as
  select 1 as home, 1 as small, 1 as big union
  select 2        , 3         , 1        union
  select 3        , 8         , 0;

select home, 1.5 * small + 3 * big as food from homes;

-- Ints
create table ints as
  select "zero" as word, 0 as one, 0 as two, 0 as four, 0 as eight union
  select "one"         , 1       , 0       , 0        , 0          union
  select "two"         , 0       , 2       , 0        , 0          union
  select "three"       , 1       , 2       , 0        , 0          union
  select "four"        , 0       , 0       , 4        , 0          union
  select "five"        , 1       , 0       , 4        , 0          union
  select "six"         , 0       , 2       , 4        , 0          union
  select "seven"       , 1       , 2       , 4        , 0          union
  select "eight"       , 0       , 0       , 0        , 8          union
  select "nine"        , 1       , 0       , 0        , 8;

select word, one + two + four + eight as value from ints;
select word from ints where one + two/2 + four/4 + eight/8 = 1;

-- Fur
create table dogs as
  select "abraham" as name, "long" as fur union
  select "barack"         , "short"       union
  select "clinton"        , "long"        union
  select "delano"         , "long"        union
  select "eisenhower"     , "short"       union
  select "fillmore"       , "curly"       union
  select "grover"         , "short"       union
  select "herbert"        , "curly";

-- Parents of curly dogs
select parent from parents, dogs where child = name and fur = "curly";

-- Siblings
select a.child as first, b.child as second
  from parents as a, parents as b
  where a.parent = b.parent and a.child < b.child;

-- Grandparents
create table grandparents as
  select a.parent as grandog, b.child as granpup
    from parents as a, parents as b
    where b.parent = a.child;

-- Grandogs with the same fur as their granpups
select grandog, granpup, c.fur from grandparents, dogs as c, dogs as d
  where grandog = c.name  and
        granpup = d.name and
        c.fur = d.fur;

-- Sentences
create table nouns as
  select "the dog" as phrase union
  select "the cat"           union
  select "the bird";

select subject.phrase || " chased " || object.phrase
       from nouns as subject, nouns as object
       where subject.phrase != object.phrase;

create table ands as
  select phrase from nouns union
  select first.phrase || " and " || second.phrase
         from nouns as first, nouns as second
         where first.phrase != second.phrase;

select subject.phrase || " chased " || object.phrase
       from ands as subject, ands as object
       where subject.phrase != object.phrase;
