🐬MySQL

In this tutorial we will guide you through all the steps to create your very first MySQL assignment in CodeGrade. For this tutorial we will be considering the example below. The zip folder contains a completed solution and a template file. Please make sure to save this file somewhere accessible on your computer:

156KB

MySQL.zip

archive

A quick note about this example In this assignment, students have been asked to create 3 MySQL queries. Each query should make a selection from the database of a record store:

• customers_from_canada.sql - The customer ID, last name, first name, and company of each customer based in Canada

• songs_from_hendrix.sql - the name of every song sold by the store that was written by Jimi Hendrix

• top_5_tracks.sql - The name and number of purchases of the five most popular tracks sold by the store.

Step 1: Create an assignment

Firstly, we need an assignment to work with. Whether CodeGrade is integrated in your LMS or you are using CodeGrade Standalone, you can follow the steps in the guide below to get started:

➕Create assignment

Step 2: Configure submission settings

The first step in setting up an assignment is to ensure that students can submit their code correctly. Because this example is straightforward and only requires a single solution file, we will offer the students the option to code in the CodeGrade Editor alongside the default File Uploader option.

1. Navigate to the Assignment Management page by using the ⚙️ icon at the right-hand side of the page header on the submissions page.

1. Find the Submission settings in the corresponding container under the General tab.

Submission settings with the File uploader and Editor options enabled and a template file uploaded.

1. Enable the File uploader and Editor submission methods by clicking the respective check boxes.

2. Enabling the Editor will reveal the Template files option. Unzip MySQL.zip and upload all the files in the Template folder.

3. Finalize your settings by clicking the "Submit" button.

It is good practice to provide a template file when enabling the Editor so that your students don't have to create their own files. This also avoids the risk of students submitting a file with the wrong name.

For more information about submission methods, see Submission Settings.

Step 3: Create a rubric

Rubric categories

While you can always grade assignments directly by manually setting the Final grade, It's only possible to award points for your automatic tests with Rubrics. Rubrics also allow you to standardize the grading scheme for graders and they allow you to clarify the grading requirements to your students.

CodeGrade offers two types of rubric category:

• Discrete category - Specify points in discrete steps (eg. 0, 5 or 10 points)

• Continuous category - Specify points as a continuous scale (eg. 0 - 10)

1. Navigate to the Rubric tab on the Assignment Management page.

2. Select Create new rubric.

3. Create 3 new Continuous Categories. Set the parameters as follows:

Category name	Description	Min - Max points
Customers from Canada	Do you correctly find the CustomerId, FirstName, LastName and Company attributes for all customers from canada?	0 - 10
Songs from Hendrix	Do you correctly find the name of all songs composed by Jimi Hendrix?	0 - 10
Top 5 tracks	Did you correctly find the name and number of purchases of the top 5 most popular tracks?	0 - 10

Step 4: Create automatic tests

Automatic tests are really what gives your students the opportunity to learn in a unique way. The immediate feedback they get from the automatic tests allow them to learn through trial and error by applying the instant feedback and resubmitting their work.

For this tutorial we will be setting up IO tests.

Step 4.1: Setup

Before actually creating the tests, we need to make sure that we have MySQL installed correctly and that we have prepared the MySQL database and server.

1. Navigate to the Setup tab under the AutoTest settings.

2. Add an Upload Files block to your configuration and upload the chinook.sql file from MySQL.zip.

3. Add a Script block to your configuration and enter the following commands:

   sudo apt update
   sudo apt install mysql-server mysql-client
   
   sudo mysql < $UPLOADED_FILES/chinook.sql
   
   cat <<EOF | sudo mysql
       CREATE USER 'codegrade'@'localhost' IDENTIFIED BY 'password';
       GRANT ALL PRIVILEGES ON Chinook.* TO 'codegrade'@'localhost';
   EOF

Step 4.3: Input/Output tests

A graded IO test that is only run when a submission is made.

Input/Output tests, or "IO tests" for short, are a great way of checking a program's functionality by providing various input cases that should result in a different outputs. You can create IO test cases in AutoTest v2 using the IO Test wrapper block and its corresponding Full match, Substring match, and Regex match blocks.

1. Add three Connect rubric blocks to your AutoTest configuration.

2. Add three IO test blocks and nest one within each Connect rubric block.

3. In each IO test block, provide the following command to run the MySQL query, making sure to adjust the filename appropriately:

   MYSQL_PWD='password' mysql --database="Chinook" -t "$@" < customers_from_canada.sql

4. Add a Full match block to each IO Test block.

5. Copy and paste the text below into the Expected output field in their respective match block:

customers_from_canada	songs_from_hendrix	top_5_tracks
+------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+ | CustomerId | FirstName | LastName | Company | Address | City | State | Country | PostalCode | Phone | Fax | Email | SupportRepId | +------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+ | 3 | François | Tremblay | NULL | 1498 rue Bélanger | Montréal | QC | Canada | H2G 1A7 | [phone] | NULL | [e-mail] | 3 | | 14 | Mark | Philips | Telus | 8210 111 ST NW | Edmonton | AB | Canada | T6G 2C7 | [phone] | [phone] | [e-mail] | 5 | | 15 | Jennifer | Peterson | Rogers Canada | 700 W Pender Street | Vancouver | BC | Canada | V6C 1G8 | [phone] | [phone] | [e-mail] | 3 | | 29 | Robert | Brown | NULL | 796 Dundas Street West | Toronto | ON | Canada | M6J 1V1 | [phone] | NULL | [e-mail] | 3 | | 30 | Edward | Francis | NULL | 230 Elgin Street | Ottawa | ON | Canada | K2P 1L7 | [phone] | NULL | [e-mail] | 3 | | 31 | Martha | Silk | NULL | 194A Chain Lake Drive | Halifax | NS | Canada | B3S 1C5 | [phone] | NULL | [e-mail] | 5 | | 32 | Aaron | Mitchell | NULL | 696 Osborne Street | Winnipeg | MB | Canada | R3L 2B9 | [phone] | NULL | [e-mail] | 4 | | 33 | Ellie | Sullivan | NULL | 5112 48 Street | Yellowknife | NT | Canada | X1A 1N6 | [phone] | NULL | [e-mail] | 3 | +------------+-----------+----------+---------------+------------------------+-------------+-------+---------+------------+-------------------+-------------------+------------------------+--------------+	+--------------------------+ | Name | +--------------------------+ | Foxy Lady | | Manic Depression | | Red House | | Can You See Me | | Love Or Confusion | | I Don't Live Today | | May This Be Love | | Fire | | Third Stone From The Sun | | Remember | | Are You Experienced? | | Stone Free | | Purple Haze | | 51st Anniversary | | The Wind Cries Mary | | Highway Chile | | The Star Spangled Banner | +--------------------------+	+----------------------+-------+ | Name | Count | +----------------------+-------+ | Dazed and Confused | 5 | | The Trooper | 5 | | Eruption | 4 | | Hallowed Be Thy Name | 4 | | Sure Know Something | 4 | +----------------------+-------+

It's a good idea to give your tests descriptive names so that it's clear to both you and your students what is actually being checked (e.g. "Does customers_from_canada.sql produce the correct output?").

Step 5: Test and publish your AutoTests

It's important to test your AutoTest configuration before running it on students' submissions to make sure that your tests are running as expected and to check for edge cases. This is easy to do with Snapshots. When you're ready to check your tests, simply press the Build snapshot button at the bottom of the test block sidebar. This will make a test run of your AutoTest configuration on your Test submission.

The first time you build a snapshot, CodeGrade will prompt you to upload a Test submission. Use the "Click here to upload files" option or drag-and-drop all the files in the "Solution" folder from MySQL.zip and click "Submit".

If in the future you need to re-upload your test submission, you can do so by going to Upload Submission option in the General settings tab. Just upload your file and check the "Test Submission" option before clicking "Submit".

Once you're happy with your tests, you need to publish them to your students for them to run on their submissions. Publish your tests by building a snapshot and at the bottom of the pop-up modal click the Publish snapshot button.

Conclusion

You have just built your first Automatically graded assignment, ready for your students to begin submitting work!

This guide is designed to get you started with a completed assignment but doesn't go into explicit detail about all of the features CodeGrade has to offer. For more in-depth information about the product and the various workflows that you can achieve, see Learn more or reach out to our support team at [e-mail].