A Quantitative Study of the Relative Difficulty for Novices of Writing Seven Different Types of SQL Queries

Abstract

This paper presents a quantitative analysis of data collected by an online testing system for SQL "select" queries. The data was collected from almost one thousand students, over eight years. We examine which types of queries our students found harder to write. The seven types of SQL queries studied are: simple queries on one table; grouping, both with and without "having"; natural joins; simple and correlated sub-queries; and self-joins. The order of queries in the preceding sentence reflects the order of student difficulty we see in our data.

Errors and Complications in SQL Query Formulation

“…We encountered similar syntax errors as Ahadi et al [1,2], even though they used a different DBMS than us. In terms of syntax errors, we expected and encountered many errors regarding grouping.…”

“…Previous research [1,2,4,6,34,39] categorizes SQL errors under two classes: syntax and semantic errors. The division is intuitive because DBMSs detect syntax but not semantic errors.…”

“…The exercises within a set could be completed in whatever order, and the correct result table was presented for each of the exercises during the whole process so that the students could compare it with the result table produced by their query. Although existing literature on this approach is limited, Ahadi et al [2] note that, compared to work environments, providing the correct result table constitutes in making the environment unnatural, as nobody knows the correct result table when writing a query. Prior [30], however, utilizes an approach similar to ours and suggests that providing the correct result table facilitates query formulation skills.…”

SQL tester: an online SQL assessment tool and its impact

“…Since our test is worth 10% of the unit we opted to have ten questions and to include more questions of simpler types. We did not include correlated subqueries or self-joins because these are too complex (see [2]). For each test, there is a bank of questions for each category and questions are chosen at random from the bank.…”

Early Identification of Novice Programmers' Challenges in Coding Using Machine Learning Techniques

“…Accomplishing the final output of this research project involves fulfilling the objectives of three main phases. The first phase is dedicated to analysis of snapshot data in different contexts [5]- [9] to understand the data and find possible links to what previously has been reported [10]- [16]. The second phase is devoted to designing machine learning derived techniques for snapshot data analysis [6] .…”