MySQL autograding for databases education
Guides
June 16, 2021

Automatically grading MySQL assignments

Some months ago, I created a web development course in CodeGrade and created an SQL assignment for that. Back then, before we had AutoTest caching, my choice of SQL software was SQLite, which was the quickest and worked the best in CodeGrade AutoTest. You can click here to read my previous blog and learn more about setting up a SQLite assignment in CodeGrade. 

Now that we have AutoTest caching however, it is more efficient to install new software like MySQL. Moreover, we do not have to worry about the server set up time (short recap: we chose SQLite back then for its “serverless” approach, which meant that there was very little configuration time in comparison to MySQL), as this can all be cached. As MySQL is the most popular SQL software in education, I will explain how to easily set this up in CodeGrade to create your autograded SQL assignments. 

Want to read more about the comparison between SQL software or how to set up an autograded SQLite instead? Read our previous blog here!

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 SQL database we need in the `ChinookDatabase/DataSources/Chinook_MySql_AutoIncrementPKs.sql` 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.

Setting up MySQL

First we have to upload our MySQL database as a fixture to our AutoTest. In this case, I will upload the downloaded `Chinook_MySql_AutoIncrementPKs.sql` file. This will now be available in our AutoTest. 

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 MySQL is not. So, the first thing I have to do is install MySQL to the AutoTest of my assignment. After installing, we also have to set up our database and create a new user for it. We do this with a very simple setup script called `setup.sh` which we created:

-!- CODE language-sh -!-#!/bin/sh
# Quit if any command returns an error code (!= 0)
set -e

# Install mysql
sudo apt install -y mysql-server mysql-client

# Set up the database with data from the example data set.
sudo mysql <$FIXTURES/Chinook_MySql_AutoIncrementPKs.sql

# Create a new database user named codegrade (we use the name
# codegrade for the user so that we do not have to specify the
# user when we are running mysql queries later on, because mysql
# uses the current user by default).
# Then give the new codegrade user _all_ permissions on the
# Chinook database.
cat <<EOF | sudo mysql
    CREATE USER 'codegrade'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON Chinook.* TO 'codegrade'@'localhost';
EOF

In this script, we first make sure to quit if any of the lines return an error code. Aftwards, we simply install mysql using the package manager apt.  Then set up the database with the data set we have downloaded and uploaded as a fixture. Finally, we create a new database user with all permissions which we can use to run the queries in our AutoTest steps.

Start autograding your MySQL assignments too with the most flexible autograder available!

Autograding MySQL queries

With CodeGrade’s IO (Input and Output) Tests and the installed MySQL, it is now very easy to test the different queries that were handed in by the student. In our IO Test, we simply run MySQL with a query and check for the correct output. As MySQL needs a little bit more setup, we need a small script to run it too, let’s call that `run-mysql.sh`:

-!- CODE language-sh -!-#!/bin/sh
# Quit if any command returns an error code (!= 0)
set -e

# Wait for the mysql service to start up
while ! mysqladmin ping --password=password >/dev/null 2>&1; do
    sleep 1
done

# Then we run mysql with the password we have set up in setup.sh
# with the sql file redirected to its stdin.
mysql --database=Chinook --password='password' "$@"

In this script, we first wait for the MySQL service to start up. After it has started up, we can actually run MySQL by specifying the database (in this case Chinook, but different depending on the database you set up) and the password we have set up.

We can now run this script in our IO Test by running: `bash $FIXTURES/run-mysql.sh`. We can specify the query file in the Input arguments, by redirecting it to mysql’s stdin, in our case with `< songs_from_hendrix.sql`.  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.


MySQL IO Test in CodeGrade


Please note: as you can see in our expected output, we start with the name of the column (“Name”). You can disable the output of column names by passing the -N flag, in that case your Input arguments will become: `< songs_from_hendrix.sql -N`.


Job is done!

After the initial setup, 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 MySQL assignment in CodeGrade. After some initial research and scripting (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

Grow your coding classroom without compromising on quality.