Difference between revisions of "Napier Students Progress"

From Progzoo
Jump to: navigation, search
Line 2: Line 2:
 
<barChart>
 
<barChart>
 
SELECT tut_seq.tut,quest
 
SELECT tut_seq.tut,quest
       ,COUNT(DISTINCT (CASE WHEN score=100 THEN wgUserName END)) AS Successful
+
       ,COUNT(DISTINCT (CASE WHEN score>97 THEN wgUserName END)) AS Successful
 
       ,COUNT(DISTINCT wgUserName) AS Num_Students
 
       ,COUNT(DISTINCT wgUserName) AS Num_Students
 
       ,COUNT(1) AS Attempts
 
       ,COUNT(1) AS Attempts

Revision as of 15:33, 1 October 2010

Bar Chart:

DROP TABLE tut_seq;
CREATE TABLE tut_seq
(ord INT PRIMARY KEY
,tut VARCHAR(255)
);
INSERT INTO tut_seq VALUES (0,'Flags_with_Rectangles_Tutorial');
INSERT INTO tut_seq SELECT COUNT(*),'Flags_with_Lines_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Flags_with_Polygons_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Flags_with_Stars_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Flags_with_Repeats_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Harder_Flags_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Drawing_Dice_Tutorial' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'Simple_Printing_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Filtering_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Printing_Several_Items_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Multiple_Conditions_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Composing_Functions_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Formatting_Numbers_Tutorial' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'Letters_for_Numbers' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Street_Repairs' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Ticket_Prices' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Temperature' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Pass_Fail' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Grades' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Christmas_Trees' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Mileage_Claim' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Maximum' FROM tut_seq;	
INSERT INTO tut_seq SELECT COUNT(*),'Three_for_Two' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'Strings_Tutorial%2C_Simple_Filtering' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Strings_Tutorial%2C_Complex_Filters' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Strings_Tutorial%2C_Pattern_Matching' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'Testing_Two_Numbers_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Modulus_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Testing_Ranges_Tutorial' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'For_Tutorial_1' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'For_Tutorial_2' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'For_Tutorial_3' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Looping over Structures' FROM tut_seq;

INSERT INTO tut_seq SELECT COUNT(*),'Accumulate_Adding_and_Counting' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulate_with_Various_Operations' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulate_over_a_File' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulate_over_a_File_with_Multiple_Variables' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulator_Counting_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulator_to_Add_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulators_For_Searching_Tutorial' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Accumulate_over_a_File_Harder_Examples' FROM tut_seq;
INSERT INTO tut_seq SELECT COUNT(*),'Reprise' FROM tut_seq;

--Find unlisted tutorials
SELECT prg.tut,quest,COUNT(1),ord
  FROM prg LEFT JOIN tut_seq on prg.tut=tut_seq.tut
 WHERE wgUserName BETWEEN '00000000' AND '99999999' 
  AND whn > '2010-09-06'
GROUP BY prg.tut,quest

--Show progress
SELECT ord, tut_seq.tut, quest, COUNT(*),COUNT(DISTINCT wgUserName) AS Num_Students
      ,COUNT(DISTINCT (CASE WHEN score=100 THEN wgUserName END)) AS Successful
  FROM tut_seq JOIN prg ON tut_seq.tut=prg.tut
WHERE wgUserName BETWEEN '00000000' AND '99999999' 
  AND whn > '2010-09-06'
GROUP BY ord,tut_seq.tut,quest
ORDER BY ord,quest;