An automatically graded SQL query assignment handed in by a student
Guides
January 6, 2021

Creating an automatically graded SQL assignment in CodeGrade

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.

Start giving your students instant feedback on their SQL assignments!

Setting up automatic grading

One of the best features of CodeGrade’s autograder is that it allows you to install and run any software that you like. Many packages are installed by default, but SQLite is not. So, the first thing I have to do is install SQLite to the AutoTest of my assignment. I can do this by writing the simple command `sudo apt install -y sqlite3` in the Setup Script of the AutoTest. While we chose SQLite because of its performance and ease of use, we could have simply installed MySQL or PostgreSQL in this step instead too.

Next we have to upload our SQLite database as a fixture to our AutoTest. In this case, I will upload the downloaded Chinook_Sqlite.sqlite file. This will now be available in our AutoTest. One of the benefits of SQLite, and one of the reasons I prefer to use it for educational purposes, is that I do not have to install or load the database file before I can start testing, this greatly improves the performance of our AutoTest configuration while reducing its complexity.

With CodeGrade’s IO (Input and Output) Tests and SQLite, it is now very easy to test the different queries that were handed in by the student. In our IO Test, we simply run `sqlite3 $FIXTURES/Chinook_Sqlite.sqlite` and in the different input and expected output pairs, we redirect the content of the handed in query files to SQLite, with for instance `< songs_from_hendrix.sql` as input. The expected output can now be simply written or copy and pasted as expected.

For my assignment, I turn on the Ignore All Whitespace option and turn off the Substring match option. As a SQL query with superfluous output means the query is incorrect.

Automatic SQL assignment test by CodeGrade autograder
An example I/O Test for an SQL query in CodeGrade AutoTest.


Job is done!

It now took me only a couple of minutes to create tests to check all of my 7 queries that students can hand in. After I’ve turned on my AutoTest, the students will see immediately if their queries got the correct results every time they hand in. Engaging them and motivating them to continuously improve their answers until they have as many as they can right.

Next to the automatic grading, I have also added a rubric category to manually assess the query style and readability. 

So that’s it, the job is done! We have created an automatically graded SQL assignment in CodeGrade. After some initial research (which you can skip now!) I was able to quickly set up automatic tests for many questions and assignments. Feel free to reach out to me via support@codegrade.com if you have any questions regarding autograding SQL assignments, CodeGrade in general or if you would like to receive a copy of the assignments and queries I have created for this blog.

Continue reading

Start using CodeGrade now to supercharge your feedback on code.