Various SQL newbie syntax mistakes that could use clearer messages

[pamelafox]

I recently went through hundreds of reports for our first SQL challenge and identified common syntax errors that newbies make. I've updated the SQL output for the most common ones, but there are still quite a few that I didn't address. The messages are just based off of doing an indexOf/search() on the error message and statement right now, nothing fancy. We may want to get fancier in future if syntax-error-finding performance is significant.

See example commit ee78b46 for making changes.

Reported multiple times:

• Missing quotes around column values:
  INSERT INTO Booklist VALUES (1, mother, 5);
INSERT INTO favouriteBooks values ("Great Expectations", Dickens, 7);
INSERT INTO books VALUES (1, "The Diary of a Wimpy Kid", 3 out of 5);
INSERT INTO Good_Books VALUES ( 1, The_Hobbit, 10/10 );
• Missing comma in comma-separated lists:
  CREATE TABLE books(id INTEGER PRIMARY KEY name TEXT);
CREATE TABLE groceries (name TEXT quantity INTEGER );
INSERT into books VALUES (1 "The Outsiders", 10 );
INSERT INTO books values (1"Charlotte's Web",4);
INSERT INTO books VALUES (1, 'Percy Jackson'1);
• Space in table name in INSERT:
  INSERT INTO book list VALUES (1, "the hunt begins", 7);
INSERT INTO Books I Like VALUES (1, "Percy Jackson and Sequel Series", 10);
• Unnecessary semi-colon:
  SELECT * FROM movies; WHERE release_year > 2000
• Unnecessary comma:
  CREATE TABLE books (id, PRIMARY KEY, name TEXT, quantity INTEGER );
• Missing column name in CREATE:
  Create Table groceries (integer primary key,name text);
CREATE TABLE BOOKS(TEXT, NUMERIC, INTEGER, REAL, NONE);
• Misspelled column types:
  CREATE TABLE books (id INTEGER PRIMARY KEYS , name TEXT, rating INTEGER);
CREATE TABLE books (id INTIGER PRIMARY KEY, name TEXT, rating INTIGER);
create table favbooks (id integer primarykey);
• Missing values: (they should probably look at documentation harder)
  CREATE TABLE meep;
• Using a dot instead of a comma for comma-separated values:
  INSERT INTO book values (1,"HARY POTER THE BLOOD PRINCE". 1);
INSERT INTO todo_list VALUES (4. "plaY", 40);
• Unnecessary semi-colon mid-statement:
  CREATE TABLE Books; (id INTERGER primary KEY, name TEXT, quantity INTERGER );
CREATE TABLE favourite books; (id INTEGER PRIMARY KEY, name TEXT, ranking INTEGER );
SELECT * FROM movies; WHERE release_year > 2000
• Misplaced parenthesis:
  SELECT * FROM Books);
INSERT INTO Booklist VALUES)(1," To Kill A Mockingbird”);
INSERT into books VALUES (3, "TAX LAW CHINA"), 003);
insert INTO f_books VALUES (2,'i am awsome’,200
• Missing what to aggregate:
  SELECT SUM FROM todo_list;
SELECT (SUM) FROM todo_list;

Reported once:

• Missing table name:
  CREATE TABLE(id INTEGER PRIMARY KEY, name TEXT, rating INTEGER);
• Using fractions as numbers:
  INSERT INTO booklist VALUES(3,"Hunger Games",3 1/2);
• Put a period in table name:
  CREATE TABLE fav. books (id INTEGER PRIMARY KEY name TEXT,quantity INTEGER);
• Left out KEY:
  CREATE TABLE books(id PRIMARY );
• Left out INTEGER:
  CREATE TABLE f (id PRIMARY KEY,name TEXT, release year INTEGER);
• Misspelled CREATE:
  CRATE TABLE Books;
• Missing table name and values: (they should probably look at documentation harder)
  CREATE TABLE;
• Missing what to SELECT:
  SELECT FROM groceries;
• Missing commas:
  INSERT INTO books VALUES (1,"cloudette" 1);
• Missing values: (probably not done typing)
  INSERT INTO books VALUES ( );
• Unclosed quote:
  INSERT INTO MyBooks VALUES (2, History",4);