WARNING: SQL support is in a beta. Please contact support if you see a problem or if you have an improvement request.
We use a JavaScript SQLite library on our coding platform to evaluate SQL queries. Queries are run against an in-memory database that does not get persisted after each evaluation. The documentation link shows examples of how the queries are run.
Database setup
Depending on the scope of the problem, creating a SQL Exercise will likely require you to provide some setup beforehand, such as creating table, adding values to tables, etc. Please use the setup.sql file provided to add your setup queries. This file is hidden from the student.
Solution
The solution file is where you would provide the query or queries that you are expecting from the student for this exercise.
Testing SQL Exercises
SQL exercises do not require an evaluation file; they are automatically tested on our platform by comparing the output of the student's query with that of your solution.
Note: Please exclude any fields that have timestamps that depend on 'now()', e.g. a 'created' or 'modified' date field. The student's query may not compare equally with yours.
Inserting comments
Due to some limitations on our SQL evaluator functionality, comments should only be inserted at the beginning of a SQL file. If including comments in the student file could be helpful to the student, feel free to do so, with a note to indicate that the student code should go below this comment to avoid any confusion.
Example setup for student file:
query.sql
1
/*
2
Table name: pantry_items
3
Columns: item_name, weight_oz, date_purchased
4
Please write your query below.
5
*/
Copied!
Examples of creating SQL exercises:
Here are a few examples of how your exercise could be set up:
Example 1 (with data setup for student):
Problem statement:
A table named pantry_items has been created for you with the columns item_name (string), weight_oz (number), and date_purchased (date). Write a select statement that retrieves the item name and weight of items whose weight is above 30 oz.
Solution file:
query.sql
1
SELECT item_name, weight_oz FROM pantry_items WHERE weight_oz >30
Create a table named pantry_items with the following columns: item_name (string), weight_oz (number), and date_purchased (date). Insert the following values into your table (written as "item_name, weight_oz, date_purchased", left to right)
Leave blank, as there is no setup needed for this problem.
Example 3 (with JOIN and ORDER BY):
Problem statement:
Two tables pantry_items and stores have been created for you with the following columns:
stores
id (number)
name (string)
address (string)
opening_hour (number)
closing_hour (number)
hours_normalized (string)
delivery_offered (boolean)
pantry_items
id (number)
name (string)
weight_oz (number)
store_id (number, foreign key that references stores)
Write a query that joins the table stores with the table pantry_items to show each pantry item's name, the name of the store it came from, the store address, the normalized store hours, and whether the store offers delivery. Order the results by store closing hour, earliest to latest. Please rename the pantry item's name to item_name and the store's name to store_name to make it easier to differentiate between the two names in the output.
Solution file
query.sql
1
SELECT p.name AS item_name, s.name AS store_name, s.address, s.hours_normalized, s.delivery_offered FROM pantry_items AS p INNERJOIN stores AS s ON p.store_id = s.id ORDERBY closing_hour ASC;
Copied!
Setup file
setup.sql
1
CREATETABLE stores (id number primarykey, name string, address string, opening_hour number, closing_hour number, hours_normalized string, delivery_offered boolean);
2
INSERTINTO stores VALUES
3
(1,"Garden Fresh","123 Main Street City, ST 12345",7,20,"7 AM to 8 PM",true),
4
(2,"Organics","456 Main Street Town, ST 54321",8,22,"8 AM to 10 PM",true),
5
(3,"Quality Grocers","123 Broadway Street City, ST 12345",9,21,"9 AM to 9 PM",false),
6
(4,"Food 'n' Stuff","456 Broadway Street Town, ST 54321",7,22,"7 AM to 10 PM",true);
7
8
CREATETABLE pantry_items (id number primarykey, name string, weight_oz number, store_id number foreignkeyreferences stores(id));
9
INSERTINTO pantry_items VALUES
10
(1,'flour',64,4),
11
(2,'white sugar',32,2),
12
(3,'chocolate chips',24,4),
13
(4,'baking soda',16,3),
14
(5,'brown sugar',40,2);
Copied!
Limitations
The SQL evaluator does not provide 100% compatibility with all SQL commands. Some features may evaluate correctly although they may not function as expected. Display issues or other functionality issues may occur with SQL statements that are not fully supported. Please be sure to use only syntax supported by SQLite for best results.
Batch Limitation Example
Batching with the GO statement will evaluate without errors, however, the output will not be displayed as expected and the result may not be accurate. Removing the batching statement will allow proper display.
1
GO
2
SELECT*FROM USERS WHERE USERID ISNOTNULL;
Copied!
The above will evaluate, however the output may not appear as expected. In order to avoid issues, remove the GO statement as seen below. The query will now display results correctly.