An automatically graded SQL query assignment handed in by a student
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: 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 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.

Devin Hillenius

Devin Hillenius

Co-founder, Product Expert
Devin is co-founder and Product Expert at CodeGrade. During his studies Computer Science and work as a TA at the University of Amsterdam, he developed CodeGrade together with his co-founders to make their life easier. Devin supports instructors with their programming courses, focusing on both their pedagogical needs and innovative technical possibilities. He also hosts CodeGrade's monthly webinar.

Continue reading

Watch our ChatGPT and Coding Education webinar!

Watch CodeGrade's webinar on using ChatGPT in coding courses to help students work with this new tool and even use it in your grading worklfow yourself!

Join our webinar on ChatGPT in Coding Education!

Join CodeGrade’s CEO, Youri Voet, for a webinar on the impact of ChatGPT on computer science education. Learn how to make ChatGPT-proof coding assignments, teach AI literacy and how to use ChatGPT to set up automatic testing for your coding assignments.

New features: Assignment Schedules and Asynchronous Assignments

On May 8th and May 22nd, respectively, CodeGrade will launch two exciting new features. These features are Assignment Schedules and Asynchronous Assignments.

5 ways CS instructors can benefit from ChatGPT in their workflow

Learn about 5 ways computer science teachers can use OpenAI's ChatGPT or other AI assistants for their code grading and teaching workflows.

Sign up to our newsletter

Book a quick 30-minute demo and see how it works for you!