Automated Grading of SQL Queries

Abstract

Grading student SQL queries manually is a tedious and errorprone process. Earlier work on testing correctness of student SQL queries, such as the XData system, can be used to test correctness of a student query. However, in case a student query is found to be incorrect there is currently no way to automatically assign partial marks. Partial marking is important so that small errors are penalized less than large errors. Manually awarding partial marks is not scalable for classes with large number of students, especially MOOCs, and is also prone to human errors.In this paper, we discuss techniques to find a minimum cost set of edits to a student query that would make it correct, which can help assign partial marks, and to help students understand exactly where they went wrong. Given the limitations of current formal methods for checking equivalence, our approach is based on finding nearest query, from a set of instructor provided correct queries, that is found to be equivalent based on query canonicalization. We show that exhaustive techniques are expensive, and propose a greedy heuristic approach that works well both in terms of runtime and accuracy on queries in real-world datasets. Our system can also be used in a learning mode where query edits can be suggested as feedback to students to guide them towards a correct query. Our partial marking system has been successfully used in courses at IIT Bombay and IIT Dharwad.

Towards a Generic Model for Classifying Software into Correctness Levels and its Application to SQL

“…Automated grading tools having a minimum of two correctness levels, correct and incorrect have already been proposed [7], [12]. Other tools capable of using more than two correctness levels are also available [8]- [11].The greater the number of correctness levels a tool possesses, the more profound its understanding. As an illustration, [6] uses 8 correctness levels to distinguish among syntax, schema, and semantics errors.…”

“…Result -Analyzing the result is another way of testing the equivalence of SQL queries [8]. SQL is a language that allows statements with different query formulations, but which accomplish the same thing.…”

Efficient Partial Credit Grading of Proof Blocks Problems

“…Chandra et al [10] use edit distance to assign partial credit to incorrect SQL queries submitted by students, using reference solutions provided by the instructor. Edit distance based methods, often backed by a database of known correct solutions, have also been used to give feedback to students learning to program in general purpose programming languages [24,13] One difference between these and our methods is that in programming contexts, the solution space is very large, and so the methods work based on edit distance to some known correct solution (manually provided by the instructor or other students).…”

Erfahrungen mit kombinierten digitalen Lernhilfen bei Datenbank-Vorlesungen

“…Eine besondere Schwierigkeit besteht darin, auch für nicht korrekte SQL-Anweisungen automatisch Bewertungen zu generieren, die auch partielle Korrektheit angemessen honorieren. Auch dazu gibt es bereits seit einiger Zeit Vorschläge [ 10 ] und auch aktuelle Forschungsansätze [ 1 ]. Zhengjie Miao et al stellen mit I‑Rex ein System vor, das eine interaktive Analyse der Auswertungsschritte komplexer SQL-Anfragen ermöglicht, was nicht nur in der Lehre sinnvolle Anwendungen finden dürfte [ 13 ].…”