Coverage of Course Topics in Learnersourced SQL Exercises

Nea Pirttinen, Arto Hellas, Juho LeinonenarXiv 2023 · arXiv

TL;DR

Learnersourcing can successfully generate a massive, diverse pool of SQL exercises that comprehensively cover introductory database course topics. By analyzing over 11,000 student-created exercises, this work-in-progress study demonstrates that students naturally align their contributions with instructor-defined learning objectives, offering a scalable way to scale up educational content.

What Problem Does This Paper Address?

Creating high-quality practice exercises for database courses is a notorious time sink for instructors. While "learnersourcing"—crowdsourcing the creation of educational resources from students themselves—has proven effective for multiple-choice questions and standard programming assignments, its viability for SQL exercises remains largely unexplored.

The core challenge is ensuring quality and curriculum alignment: if students are left to create their own SQL problems, will they actually cover the full breadth of the course? Or will they leave critical gaps, focusing only on easy concepts while ignoring complex topics like joins, subqueries, or aggregations? This paper evaluates whether student-generated SQL exercises provide sufficient topic coverage to complement or potentially replace instructor-created work.

Methods

The authors evaluated SQL Trainer, a web-based learnersourcing platform where students can either practice existing problems or author new ones. When creating an exercise, a student selects a topic, chooses a pre-defined database provided by the instructor, writes a problem description, and submits a working sample solution.

To measure topic coverage, the authors analyzed the SQL keywords (e.g., SELECT, JOIN, GROUP BY) used within the students' sample solutions. They mapped these keywords against 11 instructor-defined course topics to see if the student-generated content actually matched the intended curriculum goals.

Datasets

The study utilizes a real-world dataset collected from three introductory database courses at the University of Helsinki.

  • Participants: 1,569 students interacted with the system, with 1,187 students creating at least one exercise.
  • Volume: A total of 11,247 unique student-created SQL exercises were generated across 11 distinct topics.

Key Insights and Ideas

  • Accurate Conceptual Mapping: Students are remarkably good at identifying which SQL commands belong to which topic. For example, exercises created for the "Adding data to a database" topic heavily featured INSERT INTO commands without requiring explicit keyword constraints from the system.
  • The Late-Semester Decline: There is a clear downward trend in exercise creation as the course progresses. Earlier topics (like basic SELECT statements) received significantly more submissions than later, more complex topics (like grouping and advanced functions).
  • Inherent Student Biases: When given creative freedom, students exhibit distinct preferences. In topic 5 ("Other types of joins"), students overwhelmingly preferred writing exercises around LEFT JOIN rather than RIGHT JOIN or INNER JOIN. Similarly, for table management, they preferred creating tables (CREATE TABLE) over deleting them (DROP).

Key Results

  • High Participation: Roughly 75% of students who logged into the system successfully authored at least one exercise.
  • Substantial Content Generation: The system yielded an average of over 1,000 exercises per topic. Topic 1 (Basic Select) saw the highest volume with 1,301 exercises (11.6%), while Topic 5 (Other Joins) was a notable low outlier with only 666 exercises (5.9%).
  • Strong Keyword Alignment: As shown in the keyword distribution matrix, the syntactic features of the student solutions tightly correlate with the weekly topic definitions, validating that learnersourced exercises can effectively mirror an instructor's syllabus.
Figure 1: The number of SQL concepts used per topic. Color legend: concept used in over 80 % of the created exercises in dark blue, over 40 % light blue, over 10 % yellow, under 10 % gray, and 0 occurrences white.

Limitations and Future Work

While the keyword analysis proves that students cover the right topics, it does not measure the quality, correctness, or difficulty of the exercises. A student could theoretically write a highly repetitive or trivial query that still contains the correct keyword.

As this is a work-in-progress paper, the authors outline several next steps:

  1. Evaluating the pedagogical effectiveness of these student-generated exercises when other students solve them.
  2. Analyzing the difficulty levels of the learnersourced pool.
  3. Investigating whether demographic differences influence how students engage with learnersourcing activities.

Summary updated

Abstract

Learnersourcing is a common task in modern computing classrooms, where it is used, for example, for the creation of educational resources such as multiple-choice questions and programming exercises. One less studied type of learnersourced artefact is SQL exercises. In this work, we explore how well different SQL topics are covered by learnersourced SQL exercises. Covering most course topics would allow students to practice the full content of the course by completing learnersourced exercises. Our results suggest that learnersourcing can be used to create a large pool of SQL exercises that cover most of the topics of the course.
BibTeX
@misc{pirttinen2023coverage,
  author = {Nea Pirttinen and Arto Hellas and Juho Leinonen},
  title = {{Coverage of Course Topics in Learnersourced SQL Exercises}},
  year = {2023},
  eprint = {2301.07509},
  archivePrefix = {arXiv},
  url = {https://arxiv.org/abs/2301.07509},
}
Figure