create table students as
select 101 as "SID", "Knowles" as Last, "Jason" as First,
"F" as SemEnt, 2003 as YearEnt, "EECS" as Major union
select 102, "Chan", "Valerie", "S", 2003, "Math" union
select 103, "Xavier", "Jonathan", "S", 2004, "LSUnd" union
select 104, "Armstrong", "Thomas", "F", 2003, "EECS" union
select 105, "Brown", "Shana", "S", 2004, "EECS" union
select 106, "Chan", "Yangfan", "F", 2003, "LSUnd";
create table schedule as
select 21228 as CCN, "61A" as Num, "EECS" as Dept, "2-3MWF" as Time,
"1 Pimentel" as Room, "F" as Sem, 2003 as Year union
select 21231, "61A", "EECS", "1-2MWF", "1 Pimentel", "S", 2004 union
select 21229, "61B", "EECS", "11-12MWF", "155 Dwinelle", "F", 2003 union
select 21232, "61B", "EECS", "1-2MWF", "2050 VLSB", "S", 2004 union
select 21103, "54", "Math", "1-2MWF", "2050 VLSB", "F", 2003 union
select 21105, "54", "Math", "1-2MWF", "1 Pimentel", "S", 2004 union
select 21001, "1A", "English", "9-10MWF", "2301 Tolman", "F", 2003 union
select 21005, "1A", "English", "230-5TuTh", "130 Wheeler", "S", 2004;
create table grades as
select 101 as SID, 21228 as CCN, "B" as Grade union
select 101, 21105, "B+" union
select 101, 21232, "A-" union
select 101, 21001, "B" union
select 102, 21231, "A" union
select 102, 21105, "A-" union
select 102, 21229, "A" union
select 102, 21001, "B+" union
select 103, 21105, "B+" union
select 103, 21005, "B+" union
select 104, 21228, "A-" union
select 104, 21229, "B+" union
select 104, 21105, "A-" union
select 104, 21005, "A-" union
select 105, 21228, "A" union
select 105, 21001, "B+" union
select 106, 21103, "A" union
select 106, 21001, "B" union
select 106, 21231, "A";
create table grade_values as
select "A+" as Letter, 4 as GP union
select "A", 4 union
select "A-", 3.7 union
select "B+", 3.3 union
select "B", 3 union
select "B-", 2.7 union
select "C+", 2.3 union
select "C", 2 union
select "C-", 1.7 union
select "D+", 1.3 union
select "D", 1 union
select "D-", 0.7 union
select "F", 0;
select "
** Example 1 **";
select First || " " || Last from students;
select "
** Example 2: Aggregations **";
select "Average " || avg(GP) from grades, grade_values
where Letter=Grade and SID = 101;
select "Maximum " || max(GP) from grades, grade_values
where Letter=Grade and SID = 101;
select "Count " || count(GP) from grades, grade_values
where Letter=Grade and SID = 101;
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 3: Grandparents **";
select left.parent, right.child from people as left, people as right
where left.child = right.parent;
select "
** Example 4 **";
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 5 **";
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 6 **";
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 7 **";
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 8 **";
-- 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 9 **";
-- 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;