SQL - Coding Exercise Docs

Coding Exercise Docs
Search…
Coding Exercise Docs
Introduction
SQL
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
Copied!
Setup file:
setup.sql
1
CREATE TABLE pantry_items (item_name string, weight_oz number, date_purchased date);
2
INSERT INTO pantry_items VALUES
3
('flour', 64, 20190506),
4
('sugar', 32, 20191218),
5
('chocolate chips', 24, 20200304);
Copied!
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
1
CREATE TABLE pantry_items (item_name string, weight_oz number, date_purchased date);
2
INSERT INTO pantry_items VALUES
3
('flour', 64, 20190506),
4
('sugar', 32, 20191218),
5
('chocolate chips', 24, 20200304);
Copied!
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
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 INNER JOIN stores AS s ON p.store_id = s.id ORDER BY closing_hour ASC;
Copied!
Setup file
setup.sql
1
CREATE TABLE stores (id number primary key, name string, address string, opening_hour number, closing_hour number, hours_normalized string, delivery_offered boolean);
2
INSERT INTO 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
CREATE TABLE pantry_items (id number primary key, name string, weight_oz number, store_id number foreign key references stores(id));
9
INSERT INTO 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 IS NOT NULL;
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.
1
SELECT * FROM USERS WHERE USERID IS NOT NULL;
Copied!
SQLite Resources
​SQLite Language​
​SQLite Core Functions​
​SQLite Aggregate Functions​
SciPy
Swift
Last modified 1yr ago
Copy link
Contents
Database setup
Solution
Testing SQL Exercises
Inserting comments
Examples of creating SQL exercises:
Example 1 (with data setup for student):
Example 2 (with no starting data):
Example 3 (with JOIN and ORDER BY):
Limitations
SQLite Resources