SQLRepair: Identifying and Repairing Mistakes in Student-Authored SQL Queries

Abstract

Computer science educators seek to understand the types of mistakes that students make when learning a new (programming) language so that they can help students avoid those mistakes in the future. While educators know what mistakes students regularly make in languages such as C and Python, students struggle with SQL and regularly make mistakes when working with it. We present an analysis of mistakes that students made when first working with SQL, classify the types of errors introduced, and provide suggestions on how to avoid them going forward. In addition, we present an automated tool, SQLRepair, that is capable of repairing errors introduced by undergraduate programmers when writing SQL queries. Our results show that students find repairs produced by our tool comparable in understandability to queries written by themselves or by other students, suggesting that SQL repair tools may be useful in an educational context. We also provide to the community a benchmark of SQL queries written by the students in our study that we used for evaluation of SQLRepair.

Expert Perspectives on Student Errors in SQL

“…It is a syntax error, as parsing will lead to an error. This error is mentioned by Presler-Marshall et al as a broken operator [32], and by Taipalus et al as SYN-6 Common syntax error: nonstandard operators (36) [46]. In later work, Taipalus et al highlight that errors of type SYN-6 are among the most persistent errors made by students [45].…”

“…The errors novices make when writing SQL queries have been researched through several quantitative studies and have been organized in various categories [4,7,32,[44][45][46]. Commonly reported categories are syntax errors [2,3,32] and semantic errors [3,4,7,32].…”

“…The errors novices make when writing SQL queries have been researched through several quantitative studies and have been organized in various categories [4,7,32,[44][45][46]. Commonly reported categories are syntax errors [2,3,32] and semantic errors [3,4,7,32]. In their discussion of semantic errors, Brass and Goldberg distinguish between two types of answers [7]: those queries for which we need to know the question to see whether they are correct, and those where the query is incorrect regardless of the question.…”

“…Although the missing second table for self-join was not mentioned specifically, Ahadi et al do indicate that students did not understand that they needed a self-join [4]. Presler-Marshall et al capture this error with their categories of incomplete query and table reference error [32]. In the categorization of Taipalus et al, this error is covered in SEM-3 Missing JOIN (48) and LOG-2 JOIN error (62).…”

“…Ahadi et al found that sometimes students would forget the ON in JOIN ON [4]. Presler-Marshall et al group this error under miscellaneous errors, with the example of a student writing BY instead of GROUP BY [32]. Taipalus classifies this error as SYN-6 Common syntax error (36) [46].…”