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.
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.
The solution file is where you would provide the query or queries that you are expecting from the student for this exercise.
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.
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_itemsColumns: item_name, weight_oz, date_purchasedPlease write your query below.*/
Here are a few examples of how your exercise could be set up:
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.
query.sql
SELECT item_name, weight_oz FROM pantry_items WHERE weight_oz > 30
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);
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
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.sql
Leave blank, as there is no setup needed for this problem.
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.
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.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);
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.
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.
GOSELECT * 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;