Friday, December 18, 2015

The most common syntax mistakes from SQL newbies

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 SELECT or INSERT statements.
  • Students spelling INTEGER as INTERGER and 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 INTEGER spelled 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!

Monday, December 7, 2015

Try an Hour of Code at Khan Academy

At Khan Academy, we know how important computer science and computer programming are - in fact, the Khan Academy website wouldn’t exist if Sal didn’t know how to code! That’s why we participate in’s Hour of Code campaign every year during Computer Science Education Week - we want to make sure every student gets to learn the basics of coding.

From December 7 to 13, millions of students around the world will be learning to code - in classrooms, in after-school clubs, and with parents at home. We invite you to join the #HourOfCode movement by learning to code here on Khan Academy, picking from one of our three tutorials:


Hour of Drawing with Code: Learn to program using JavaScript, one of the world’s most popular programming languages, via two great options:
- Drag-and-drop: block-based coding for those with less typing experience or on tablet devices (ages 8+).
- Typing: keyboard-based coding (ages 10+).

Hour of Webpages: Learn to make your own webpages using the basics of HTML and CSS (ages 10+).

Hour of Databases: Learn the fundamentals of databases using SQL to create tables with data and query the data (ages 12+).

We hope you’ll join us in celebrating Computer Science Education Week and spend an hour on Khan Academy learning to code!

- Pamela, Coder and Content Creator at Khan Academy