Partial grading SQL

Partial grading SQL

by Philipp W -
Number of replies: 5

Hi @all,

im searching for the opportunity, to give partial points for partial korrekt test-cases in SQL-Statements. The Info-box in moodle says, that you can use templates for partial-markings. 

Has anyone solved cases with partial-points in SQL-Statements before? 

F.e.: 

SELECT name

FROM workers

where name = "Miller"

Order by name desc. 

The student forgets the Order by statement. So I want to give him partial points.

Thanks in advance for your help with the template. 

Philipp

In reply to Philipp W

Re: Partial grading SQL

by Richard Lobb -

You should be able to simply turn off All-or-nothing grading (ringed in red in image below), then set per-test-case marks.

Screenclip of all or nothing checkbox

In reply to Richard Lobb

Re: Partial grading SQL

by Philipp W -
Hi Richard,
thanks for your reply. But I don't understand, where to set per-test-case-marks?

-----
I tried to set up points for different test-cases
f.e.
1st testcase with the correct SQL-Statement: 5 Points 

2nd testcase with the missing  "order by statement": 4 Points

etc...

If the student passes only the 4 Point-Testcase, he gets 4 of 9 Points, because it sums up all possible Points of all Testcases. But he should get 4 of 5 Points. And I have to set up all possible test-cases, a student can have. But is it possible to have f.e. one test-case with f.e. 5 Points and if the student forgets the Order-By-Statement, he gets automatically f.e. 4 Points for this test case

Thx for your help!
Philipp

In reply to Philipp W

Re: Partial grading SQL

by Philipp W -
Hi Richard,
I now found out, where I can switch to template-grader. Are there any examples for templates with partial grades in an SQL-Statement?

Thx
Philipp
In reply to Philipp W

Re: Partial grading SQL

by Richard Lobb -
Firstly, are you sure awarding partial marks is the appropriate thing to do here? Although that's traditionally how we marked code by hand, in a CodeRunner context I generally favour all-or-nothing grading, at least for formative assessment. I think it gives better learning outcomes, as it gives the student much more incentive to get their answer exactly right.

However, if you're sure it's what you want to do, you have at least a couple of options in the example you quote above.

  1. You can just turn off all-or-nothing grading and then set up two test cases. One is a test case with the original database and the other modifies the database so that the entries are all present in the expected order. Then, if a student omits the ORDER BY clause altogether they will pass the second test but not the first. You can award part marks however you choose (e.g. 0.5 for each will result in 50% for an answer without an ORDER BY). The database can be modified by using the Extra field of the testcase; I attach an example.
  2. You can switch to using a template grader and write your own code to grade the submission. This still requires turning off all-or-nothing grading. A full combinator template grader gives you ultimate flexibility to display any feedback you like, but a per-test template grader is probably sufficient in this case and is much easier to write. I attach an example. The example gives half marks if the correct rows are present in the wrong order, which includes the case of an ORDER BY clause being present but ordering by a different column. This may or may not be what you want. [That's one of the problematic aspects of awarding part marks - there are an infinite number of wrong answers and how are you to decide which ones to award marks to? Is a near-perfect answer with a missing quote mark really worth nothing while an answer that completely lacks an ORDER BY clause is worth, say, 50%?]
(2) also shows how you can provide extra feedback by adding an extra column to the table in the event of an error. It also displays the fractional mark awarded. The Result columns field in the form has had to be defined to display those two extra columns, although the Comment column is displayed only if there is at least one non-empty value in it.
Output when partial marks awarded

(2) is much more flexible but also much harder to write and more prone to error. 

In reply to Richard Lobb

Re: Partial grading SQL

by Philipp W -
Hi Richard,
thank you so much for your detailed reply.
In option No. 1 I still have the issue, that when I´m adding an third, forth,... testcase (f.e. the city Paris is missing but the order is correct), I also would like to give 50% of the points for that. But it sums up all of the points of all testcases, so the student would get 50 of 150 Points (3 testcases with 50 Points = 33%) instead of 50%. What can I do?

Thx fin advance!
Philipp