Complete sql grammar rules for the SQL Compiler and Engine KBA series

complete_sql.bnf

<sql_statement> ::= <select> | <insert> | <update> | <delete> // the full sql statement
<select> ::= "SELECT" <select_list> "FROM" <table_reference> ["WHERE <condition>"] ["GROUP BY" <column_list>] ["ORDER BY" <order_list> [<order_by_option>]] ["LIMIT" <number>] // select * from users where age > 12 or select name,age from users order by name ASC
<insert> ::= "INSERT INTO" <identifier> "("<column_list>")" "VALUES" "(" <value_list> ")" // insert into users (name,age) values ('John', 12)
<update> ::= "UPDATE" <identifier> "SET" <column_assignment> ["WHERE" <condition>] // update users set name='Alice' where id=1
<delete> ::= "DELETE FROM" <identifier> ["WHERE" <condition>] // delete from users where age=12
<table_reference> ::= <identifier> ["JOIN" <identifier> "ON" <condition>] // items or items JOIN orders ON users.id=orders.user_id
<select_list> ::= "*"| <column_list> // * name or * name,age,gender
<column_list> ::= <identifier>{","<identifier>} // name or name,age or name,age,gender
<order_by_option>::= "ASC"|"DESC"
<column_assignment> ::= <identifier> "=" <value> {","<identifier> "=" <value>} // username = 'Bob'
<condition> ::= <expression> <comparison_operator> <expression> | <condition> <logical_operator> <condition>
| "(" <condition> ")" // age> 18 or age>18 AND name='Alice' or (name='Alice' OR name='BOB')
<value_list> ::= <value> {","<value>} // Bob, 12
<value> ::= <sql_literal> | <expression> // Bob or Age+3
<expression> ::= <identifier> | <sql_literal> | <function_call>
| <expression> <operators> <expression> //  age -identifier or 'John' - literal string or age + 10 -- arithmetic expression
<function_call> ::= <identifier> "(" [<expression_list>] ")" // SUM(age)
<expression_list> ::= <expression> {","<expression>} // age, height. salary
<sql_literal> ::= <string> | <number> | <null> // 'John' or 12 or null
<number> ::= <integer_part> [<decimal_part>] // 122.12
<integer_part> ::= <digit>{<digit>} // 122
<decimal_part> ::= "."<digit>{<digit>} // .12
<string> ::= '"'<characters>'"' | "'" <character> "'" // "john"  or 'john'
<characters> ::= <character> |<character><characters> // 111 or aa
<character> ::= <digit> | <letter> // 1 or a
<operators> ::= <comparison_operator> | <logical_operator> |<arithmetic_operator>
<comparison_operator> ::= "=" | "!=" | ">" | "<" | ">=" | "<="
<logical_operator> ::= "AND" | "OR" | "NOT"
<arithmetic_operator> ::= "+"| "*" | "/"
<order_list> ::= <identifier> {","<identifier>} // Order by.... age, user_name (these are the order list)
<identifier> ::= <letter> | <digit> {<digit>| <letter>| "_"} // for column or table names
<null> ::= "NULL"
<digit> ::= '0' | '1' | '2' | '3' | '4' | '5' | '6' | '7' | '8' | '9'
<letter> ::= <upper_case> | <lower_case> // a or A
<upper_case> ::= 'A'|'B'|'C'|'D'|'E'|'F'|'G'|'H'|'I'|'J'|'K'|'L'|'M'|'N'|'O'|'P'|'R'|'S'|'T'|'U'|'V'|'W'|'X'|'Y'|'Z'
<lower_case> ::= 'a'|'b'|'c'|'d'|'e'|'f'|'g'|'h'|'i'|'j'|'k'|'l'|'m'|'n'|'o'|'p'|'r'|'s'|'t'|'u'|'v'|'w'|'x'|'y'|'z'