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.
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.
