create table grades as
   select "John Brown" as name, "hw1" as assign, 2 as score union
   select "Walt Green", "hw1", 3 union
   select "Valerie Blue", "hw1", 1 union
   select "Simon Red", "hw2", 3 union
   select "John Brown", "test1", 20  union
   select "Walt Green", "test1", 14 union
   select "John Brown", "test2", 19  union
   select "Valerie Blue", "test1", 14 union
   select "Simon Red", "test1", 17 union
   select "Walt Green", "test2", 12 union
   select "Valerie Blue", "test2", 15 union
   select "Sarah Tan", "test2", 19 union
   select "Sarah Tan", "test1", 18;

create table categories as
   select "hw1" as assign, "hw" as type union
   select "hw2", "hw" union
   select "test1", "test" union
   select "test2", "test";

-- To experiment, you might want to extract the portion of this file above
-- this comment into a file (say grading.sql) and then start an sqlite3 session
-- with

--    $ sqlite3
--    sqlite> .read grading.sql
--    your commands...

-- Here, .read is a sqlite command for reading in SQL code from a file.


select "Average Score";
select avg(score) from grades;

select "Max Score";
select max(score) from grades;

select "Average HW1";
select avg(score) from grades where assign="hw1";

select "Average HW2";
select avg(score) from grades where assign="hw2";

select "Average TEST1";
select avg(score) from grades where assign="test1";

select "Average TEST2";
select avg(score) from grades where assign="test2";

select "All Averages (Hard Way)";
select assign, avg(score) from grades where assign="hw1" union
   select assign, avg(score) from grades where assign="hw2" union
   select assign, avg(score) from grades where assign="test1" union
   select assign, avg(score) from grades where assign="test2";

select "All Averages";
select assign, count(score), avg(score) from grades group by assign;

select "All Averages for assignments with at least 2";
select assign, count(score), avg(score) from grades 
     group by assign having count(*) >= 2;

select "Average Test Score";
select avg(score) from grades, categories
     where type = "test" and grades.assign = categories.assign group by type;

select "Score spreads"
select max(abs(score1 - score2)) 


