PostgreSQL and SQL Server each have a different set of steps necessary to ensure your databases are properly loaded and available to students in each exercise.
Below are the steps to ensure your database is properly loaded into the DataCamp teach editor:
1. Set up your CSV files.
Each table in the database(s) you use will need to be exported into a separate .csv file and uploaded to the course editor in the course-specs branch. Make sure there are no commas in the data contained in these files before you upload them to your course.
2. Creating your SQL file.
In order to set up your CSV files as a relational database, you need to write a .sql file and upload it to the course editor in the course-specs branch. The .sql file should include all of the information in this example file.
To properly initialize your database, the .sql file should:
Drop + create a table for each .csv file in your database.
Create the table with one column corresponding to each column in the CSV file that specifies the data type.
Replace the table names and links in “COPY table FROM …” section, with the table links generated when you uploaded your .csv files to the teach editor.
Upload the .sql file to the course-specs branch of your course in the teach editor.
In general, we recommend you use only one .sql file per course. Your tables are generally not set up with primary/foreign keys so you can have unrelated tables in the same .sql file. When setting up your exercises, you will specify which tables you want students to see (more below).
3. Update your requirements.sh file.
After setting up your .sql file, you will need to edit the requirements.sh file in the course-specs branch of your GitHub repository. This file will initialize the database and allow you to load the exercises and make tables available for your students to view in the exercises.
Here is an example of file before any edits:
Follow these steps to initialize your database:
Uncomment lines 21 through 24, under “Section 1” for a PostgreSQL database. You can do this by removing the “#” sign.
Copy the link to your .sql file in the course editor. Paste it in line 18, replacing the link currently there. Uncomment this line by removing the “#” sign. (Make sure you don’t delete wget before your .sql file link!)
On lines 22 and 23, replace **DATABASENAME** in the three places this occurs with the name of your database from the .sql file. Do not include the “ * ”s.
Here is an example after edits, for the Intermediate SQL course:
4. Set up your pre-exercise code.
In every exercise you create on the Teach Editor, you will need to load the database using the pre-exercise code. Paste the following code into each exercise’s pre-exercise code, replacing DATABASENAME and TABLE, keeping them in single quotations:
connect('postgresql', 'DATABASENAME')
set_options(visible_tables = ['TABLE', 'TABLE'])
You may choose to load one or more tables for students to see under set_options. This will be visible to students in their console as they work on your exercises.
Other considerations:
Preview your exercises. You won’t know for sure that the database has loaded properly until you get a blank query result with the visible tables listed as tabs on the bottom of your screen. See below:
You will need to set up a complete query in the solution code that accurately interacts with your database before your exercises load properly. If your solution code is blank or your query is inaccurate (i.e., missing a comma), your preview session will break.
You will need to carefully consider what queries you include in your exercises. Correlated subqueries, for example, will take a long time to initialize in the exercise.