We teach SQL on Khan Academy by embedding SQLite.js inside our real-time editor. In our courses, students first learn how a concept works in an interactive talk-through and then they work through a step-by-step SQL-writing challenge.
We've now had thousands of learners attempt the SQL challenges in our SQL course. Many of those figure out how to write correct SQL and pass the challenges. But a big fraction of them struggle to write SQL correctly the first time and bang their head over syntax errors. When that happens, I get a JIRA report with their code and any other colorful language they'd like to express :) A few weeks ago, I triaged nearly 2,000 reports from our first 3 challenges, and I was fascinated to see the huge variety of syntax errors.
Some of the errors kept popping up - here were the top 3:
- Students putting a whitespace in their table name.
- Students missing semi-colons after
SELECTorINSERTstatements. - Students spelling
INTEGERasINTERGERand not noticing that extra 'R'.
For most syntax errors, students used to see "syntax error near [token]" and nothing else - so they knew something was wrong near a line of their SQL but had no other clues about why their SQL was wrong. To help those new SQLers out, I added more specific messages using simple regex-based checks. For example, the students from before will now see these messages:
- You can't have a space in your table name.
- Do you have a semi-colon after each statement?
- Is
INTEGERspelled correctly?
As I saw more repeat errors across learners, I just kept adding more messages. Here's a table of all the messages I added:
| example SQL | message |
|---|---|
CREATE TABLE books (id PRIMARY KEY INTEGER,name TEXT,rating INTEGER );
| Did you mean to put PRIMARY KEY after INTEGER? |
CREATE TABLE books (id INTEGER PRIMARY KEY, name TEXT, rating INTEGER;
| Are you missing a parenthesis?"]); |
CREATE books(id INTEGER PRIMARY KEY name TEXT);
| You may be missing what to create. For example, CREATE TABLE... |
CREATE TABLE (name TEXT);
| Are you missing the table name?"]); |
CREATE TABLE books (id INTEGER PRIMARY KEY,name,TEXT,rating INTEGER);
| Do you have an extra comma between the name and type? |
CREATE TABLE booklist (id, INTEGER PRIMARY KEY, name TEXT, rating INTEGER);
| Do you have an extra comma between the name and type? |
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, rating out of ten INTEGER);
| You can't have a space in your column name. |
INSERT INTO FavBooks VALUES (1, Beautiful Creatures, 10);
| Are you missing quotes around text values? |
CREATE TABLE books (name TEXT)
INSERT INTO books VALUES (1, 'book a', 100)
INSERT INTO books VALUES (2, 'book b', 110)
INSERT INTO books VALUES (3, 'book c', 1)
| Do you have a semi-colon after each statement? |
INSERT, INTO books VALUES (1, \"gone with the wind\", 1);
| There shouldn't be a comma after INSERT. |
CREATE TABLE customers (id INTEGER PRIMARY KEY);
INSERT INTO customers VALUES (1);
INSERT INTO customers VALUES (1);
| Are you specifying a different value for each row? |
CREATE TABLE customers (id INTEGER PRIMARY KEY, id TEXT);
| You have multiple columns named `id` - column names must be unique. |
I stopped there, but there's still much more work to do: I put 23 more syntax error situations in a Github issue on our live-editor open source repo. If you're interested in making SQL easier to learn, I encourage you to submit a PR for that issue or any other SQL-related issues. Your message could prevent thousands of future SQLers from pounding their head against a keyboard!
