For a web development course in CodeGrade, I created an SQL assignment that was automatically graded. Using SQL in CodeGrade is easy and autograding SQL queries is rather intuitive. After some initial research, I was able to set up new SQL assignments in CodeGrade in a matter of minutes. In this blog, I will go over my considerations while creating and designing this assignment and explain how you can easily set up AutoTests for an SQL assignment.
Choosing SQL software
There are many relational database management systems available. From my experience of working with different teachers from many different universities and schools, the most popular relational database systems in education are MySQL, PostgreSQL and SQLite. Even though all of these could be excellent choices to teach SQL to students, we have to research which one is the best suited for instant automatic grading via CodeGrade AutoTest.
The best suited database system is one that has the shortest start up time, that is not error prone and allows for quick prototyping. As CodeGrade AutoTest instantly runs for each student submission, and builds a fresh sandboxed environment for this, a system with the quickest set up time is preferable. Furthermore, for educational purposes, we prefer a system that allows students with quick prototyping and is not that error-prone (as I want to teach students SQL and not a specific database system).
To research that, I found some great comparisons online, for instance one from DigitalOcean. Here we find that SQLite is our best option, as this is a ‘serverless’ database: instead of loading a database it directly reads from and writes to the database disk file. This simplifies the setup process and makes it quicker, since it eliminates the need to configure the server. This setup also allows for quick and easy prototyping that is not error prone, as students do not need to first configure the server and import the database on their local machine either, something that is necessary in MySQL and PostgreSQL.
Designing the SQL CodeGrade assignment
We can now design our SQL CodeGrade assignment. For this, we of course need to find an interesting sample database for our assignment. I want to find a database that simulates a real-world example, to engage the students more, and that is timeless enough so that I do not have to change my assignments in the near future.
I found the open source Chinook database a good choice for this assignment. The Chinook data model represents a media webshop, including tables for artists, albums, media tracks, invoices and customers. It can be found on GitHub here: https://github.com/lerocha/chinook-database. We can download and find the SQLite database we need in the ChinookDatabase/DataSources/Chinook_Sqlite.sqlite file.
Using this database, I will design my assignment to have the students make a seperate SQL query per task and save them in a separate file, e.g. customers_from_canada.sql. Some of the tasks I have designed are:
- Get a list of all customers who are from Canada, write your query in customers_from_canada.sql.
- Get a list of all tracks composed by Jimi Hendrix, return only the song names, write your query in songs_from_hendrix.sql.
- Find out the top 5 most sold songs, return the name of the song and the number of times sold sorted by number of times sold first and name of the song (ascending) second, write your query in top_5_songs.sql.
With this setup, CodeGrade can nicely instruct students to only hand in these files, using Hand In Requirements. As I want students to only hand in the files I specify, but I do allow them to hand in partial submissions (i.e. if they only have a couple tasks finished), I chose the policy to Deny All Files by default and allowed only these files to be handed in.
Finally, before moving on to the autograder configuration, I have turned on the option for GitHub/GitLab uploads as I encourage students to create this assignment using version control.
.jpg)
