Loading a starting database (MySQL)

When working with a lab that teaches database or SQL skills, you'll often want to load an initial database for the user. The below guide covers how to do this for MySQL labs.

To start, you'll need a database setup script, such as the following:

CREATE DATABASE company_db;
USE company_db;
CREATE TABLE jobs (
job_code VARCHAR(3),
job_description VARCHAR(25),
PRIMARY KEY(job_code)
);
INSERT INTO jobs VALUES('1','Programmer');
INSERT INTO jobs VALUES('2','Systems Analyst');

This script simply creates a database, adds a table, and inserts two records into the table. You can save this file with any filename, but something like .setup.sql is recommended, as the . at the start of the filename will cause the file to be hidden from the end user (it will still be visible in the Creator).

Once you've created this file, be sure to click Save in the top right corner of the page to persist the file.

Now, create a startup script to load the file with the following contents:

sudo mysql < /home/nt-user/workspace/.setup.sql

Note that you need to provide the full file path, as startup scripts are run from the /tmp directory.

It's advisable that you test this command from the terminal first to ensure it works correctly. If you receive any errors, make changes to the startup script to correct them.

Once you're happy with the script, refresh the Creator page or launch a preview of the lab to run the startup script.

To ensure it ran successfully, you can type sudo mysql into the terminal to load a MySQL prompt. From there, if you're using the above example file, you can type the following to test the database:

USE company_db;
SELECT * FROM jobs;

This will return the following:

+----------+-----------------+
| job_code | job_description |
+----------+-----------------+
| 1 | Programmer |
| 2 | Systems Analyst |
+----------+-----------------+
2 rows in set (0.01 sec)

You can also set the name of your database for the SQL Results Viewer, if you'd like to use that interface element. In this case, you would set it to company_db. You can then create a file for the user to write their query in (e.g. query.sql) and query the database using the commands typed in there, as shown below:

Note that you do not need the USE statement when using the SQL Results Viewer, as the earlier configuration sets this automatically.