SQL-parser.md

SQL query parser

In SQL, the most syntactically complex and tricky query is probably the SELECT query. It has explicit and implicit joins, groupings, subqueries, sorting and truncation of selects - all this beauty can occur repeatedly even in one single select query.

For example, like this:

SELECT * FROM book

or like this:

SELECT author.name, count(book.id), sum(book.cost) 
FROM author 
LEFT JOIN book ON (author.id = book.author_id) 
GROUP BY author.name 
HAVING COUNT(*) > 1 AND SUM(book.cost) > 500
LIMIT 10;

Please write a parser for an arbitrary SELECT query, representing it as a class of approximately this structure:

class Query {
	private List<String> columns;
	private List<Source> fromSources;
	private List<Join> joins;
	private List<WhereClause> whereClauses;
	private List<String> groupByColumns;
	private List<Sort> sortColumns;
	private Integer limit;
	private Integer offset;
}

The structure of this class is not a requirement, but a guide to action :) If you have invented a class that reflects the SQL query structure better than the one given here - feel free to use your solution.

What constructs the parser must support in a mandatory way:

• Enumeration of sample fields explicitly (with aliases) or *
• Implicit join of several tables (select * from A,B,C)
• Explicit join of tables (inner, left, right, full join)
• Filter conditions (where a = 1 and b > 100)
• Subqueries (select * from (select * from A) a_alias)
• Grouping by one or several fields (group by)
• Sorting by one or more fields (order by)
• Selection truncation (limit, offset)

What can be ignored:

• Complementing selections (union and union all)
• CTE
• Window functions

Some details:

• The assignment should be done in Kotlin or Java (version 21 or higher).
• The assignment must have a working main() method, to demonstrate how it works
• If you have any questions, feel free to write to join-ecom@lightspeedhq.com.
• The completed assignment should be posted on GitHub