MySQL autograding for databases education
Guides
June 16, 2021

Automatically grading MySQL assignments

In 30 seconds...

  • MySQL is the most popular SQL software used in education;
  • MySQL requires you to set up a database before use, with CodeGrade's AutoTest caching, that is now super fast;
  • With CodeGrade’s IO (Input and Output) Tests and the installed MySQL, it’s easy to test the different queries that were handed in by the student;
  • After turning on AutoTest, the students will see immediately if their queries got the correct results every time they hand in.

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.

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!