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;

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 **";
select First || " " || Last from students;

select "
** 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;


select "
** Grandparents **";

select left.parent, right.child from people as left, people as right 
    where left.child = right.parent;
