Search code examples
mysqldatabaserelationshipqueryinglibreoffice-base

Graded Assignment [Zaption] Database

Most specifically, I'm having trouble returning from LibreOffice Base [HSQLdb] a list of grades organized by (1) class, (2) assignment, (3) student's last name.

I want this output so I can run a script to copy the grades from the database to an online gradebook (which doesn't have an API [sadface])

I suspect several possible causes for this problem:

  1. My relational structure may need tweaking.

  2. I somehow need to implement a "student ID." On Zaption, Students make their submissions under whatever "ZaptionName" they choose to use. I then manually match ZaptionName to RosterFullName in the second table shown.

  3. Zaption allows multiple submissions by the same "student" for the same assignment. Because multiple submissions are allowed, I run a FilterLowMultiples query to select the highest grade for that assignment for that student.

FilterLowMultiples:

SELECT MAX( "Grade" ) "Grade", "RosterFullName",
"Assignment", MAX( "ZaptionName" ) "ZapName"
FROM "SelectAssignment"
GROUP BY "RosterFullName", "Assignment"

SelectAssignment is below for reference:

SELECT "GradedAssignments"."Assignment", "Roster"."RosterFullName",
"GradedAssignments"."Grade", "ZaptionNames"."ZaptionName"
FROM "Roster", "ClassIndex", "GradedAssignments", "ZaptionNames"
WHERE "Roster"."Class" = "ClassIndex"."Class"
AND "GradedAssignments"."ZaptionName" = "ZaptionNames"."ZaptionName"
AND "ZaptionNames"."RosterFullName" = "Roster"."RosterFullName"
AND ( "GradedAssignments"."Assignment" = 'YouKnowWhatever')

My query to PullAssignmentGrades is as follows, but sorting by assignment fails, as there is no assignment by default unless that student submitted one, so the row is blank and that student falls to the bottom of the sort, which is bad for the transfer-to-online script I run.

SELECT "Roster"."RosterFirstName", "ClassIndex"."Class",
"Roster"."RosterFullName", "ClassIndex"."ClassLevel",
"FilterLowMultiples"."Grade", "FilterLowMultiples"."ZapName",
"FilterLowMultiples"."Assignment", "FilterLowMultiples"."Grade",
"FilterLowMultiples"."Assignment", "ClassIndex"."ClassDisplayOrder",
"Roster"."RosterLastName"
FROM "ClassIndex", "FilterLowMultiples", "Roster"
ORDER BY "Roster"."RosterFirstName" ASC,
"FilterLowMultiples"."Grade" DESC,
"FilterLowMultiples"."Assignment" ASC,
"ClassIndex"."ClassDisplayOrder" ASC,
"Roster"."RosterLastName" ASC
Solution

  • Use a LEFT JOIN in your query for SelectAssignment so you don't drop students who didn't do a particular assignment. Optionally you can use COALESCE on the potentially NULL values from the "GradedAssignments" table to assign a grade of 0 or I. Like so:

    SELECT 'YouKnowWhatever' AS "Assignment", "Roster"."RosterFullName",
  COALESCE("GradedAssignments"."Grade",0), "ZaptionNames"."ZaptionName"
FROM "Roster"
  INNER JOIN "ClassIndex" ON "Roster"."Class" = "ClassIndex"."Class"
  INNER JOIN "ZaptionNames" ON "ZaptionNames"."RosterFullName" = "Roster"."RosterFullName"
  LEFT JOIN "GradedAssignments" ON ("GradedAssignments"."ZaptionName" = "ZaptionNames"."ZaptionName" 
    AND "GradedAssignments"."Assignment" = 'YouKnowWhatever')