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

/*
    Table name: pantry_items
    Columns: item_name, weight_oz, date_purchased
    Please write your query below.
*/

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

SELECT item_name, weight_oz FROM pantry_items WHERE weight_oz > 30

Setup file:

setup.sql

CREATE TABLE pantry_items (item_name string, weight_oz number, date_purchased date);
INSERT INTO pantry_items VALUES
('flour', 64, 20190506),
('sugar', 32, 20191218),
('chocolate chips', 24, 20200304);

Example 2 (with no starting data):

Problem statement:

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)

• flour, 64, 20190506

• sugar, 32, 20191218

• chocolate chips, 24, 20200304

Solution file:

query.sql

CREATE TABLE pantry_items (item_name string, weight_oz number, date_purchased date);
INSERT INTO pantry_items VALUES
('flour', 64, 20190506),
('sugar', 32, 20191218),
('chocolate chips', 24, 20200304);

Setup file

setup.sql

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

SELECT p.name AS item_name, s.name AS store_name, s.address, s.hours_normalized, s.delivery_offered FROM pantry_items AS p INNER JOIN stores AS s ON p.store_id = s.id ORDER BY closing_hour ASC;

Setup file

setup.sql

CREATE TABLE stores (id number primary key, name string, address string, opening_hour number, closing_hour number, hours_normalized string, delivery_offered boolean);
INSERT INTO stores VALUES
(1, "Garden Fresh", "123 Main Street City, ST 12345", 7, 20, "7 AM to 8 PM", true),
(2, "Organics", "456 Main Street Town, ST 54321", 8, 22, "8 AM to 10 PM", true),
(3, "Quality Grocers", "123 Broadway Street City, ST 12345", 9, 21, "9 AM to 9 PM", false),
(4, "Food 'n' Stuff", "456 Broadway Street Town, ST 54321", 7, 22, "7 AM to 10 PM", true);
​
CREATE TABLE pantry_items (id number primary key, name string, weight_oz number, store_id number foreign key references stores(id));
INSERT INTO pantry_items VALUES
(1, 'flour', 64, 4),
(2, 'white sugar', 32, 2),
(3, 'chocolate chips', 24, 4),
(4, 'baking soda', 16, 3),
(5, 'brown sugar', 40, 2);

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.

GO
SELECT * FROM USERS WHERE USERID IS NOT NULL;

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.

SELECT * FROM USERS WHERE USERID IS NOT NULL;

SQLite Resources

• ​SQLite Language​

• ​SQLite Core Functions​

• ​SQLite Aggregate Functions​