4
\$\begingroup\$

I've invented an SQL-like language. Let's call it SelectQL. You can't INSERT anything, you can only SELECT, and I don't have the time for an interpreter. How about you help?

The Challenge

Write a full program or a function that takes in a database of tables and a valid SelectQL SELECT string, interprets the string, and outputs the resulting SELECTion.

SELECT Specification

A minimum valid SELECT SelectQL statement is as follows:

SELECT [column] FROM [table]
  • [column] can be ALL or * for selecting all columns, or a valid column name in the [table].
  • [table] is the name of a table.
  • An optional WHERE ([conditions]) can be appended to the end of ths string.
  • [conditions] is a list of column=value pairs separated by AND or OR.
  • The precedence of AND and OR is left to right.
  • [conditions] can be preceded with NOT to select the complement of the condition.
  • Conditions involving arrays/lists ([980, "abc"] in data below) will look for the existence of a requested value in the array, and return the whole array.
  • key must be a column in [table].
  • SelectQL query strings are case insensitive.

Table Format

Your program should take in an SelectQL database (array of arrays, list of lists, delimited string, JSON object, etc) in the following format:

[
    [table name, [column names], [[row], [row], ...]],
    [table name, [column names], [[row], [row], ...]]
]
  • table name is the name of the table.
  • [column names] is a list-like of unique strings which are column names.
  • Each column's values all must have the same type.
  • [row] is a list-like of values. Each row must be the same length as [column names].
  • There can be no [row]s.
  • Elements in [row] cannot be empty. Empty strings or arrays are not considered empty.

Input

  • A valid SelectQL database, and
  • A valid SelectQL SELECT query. It will always SELECT a valid table in the database.

Output

  • The result of the query. The result must have the columns as well as the resulting rows.

Examples

The examples will use the following database:

[
    [data, [d1, d2, 3, ~!@#], [[123, "", [980, "abc"], "false"], [456, "this isn't empty!", [], "-123.4444"], [1, "2", [3, 4], "five"]]],
    [no, [yes, a, 1.5, what], []
]
Format:
string --> result
-----------------
"SELECT ALL FROM data"
        --> [[d1, D2, 3, ~!@#], [[123, "", [980, "aBc"], "false"], [456, "this isn't empty!", [], "-123.4444"], [1, "2", [3, 4], "five"]]]

"SELECT D1 FROM DATA"
        --> [[d1], [[123],[456], [1]]]

"SELECT d1, 3 FROM data"
        --> [[d1, 3], [[123, [980, "aBc"]], [456, []], [1, [3,4]]]]

"SELECT ALL FROM DATA WHERE (NOT d1=123)"
        --> [[d1, D2, 3, ~!@#], [[456, "this isn't empty!", [], "-123.4444"], [1, "2", [3, 4], "five"]]]

"SELECT * FROM data WHERE (d1=123)"
        --> [[d1, D2, 3, ~!@#], [[123, "", [980, "aBc"], "false"]]

"SELECT ~!@# FROM DATA WHERE (3=[] OR d2="")"
        --> [[~!@#], [["false"], ["-123.4444"]]

"SELECT * FROM DATA WHERE (d1=2)"
        --> [[d1, d2, 3, ~!@#], []]

"select all from no"
        --> [[yes, a, 1.5, what], []]

"select 3 from data where (3=980)"
        --> [[3],[[[980, "aBc"]]]

Additional Rules/Clarifications

Sandbox Link

\$\endgroup\$
  • 4
    \$\begingroup\$ May AND and OR be mixed together? If so, what is their precedence? \$\endgroup\$ – Arnauld May 18 at 15:06
  • 1
    \$\begingroup\$ I guess column1 = column2 comparisons are not supported by the language. \$\endgroup\$ – manatwork May 18 at 15:11
  • \$\begingroup\$ Left to right, so A=1 AND B=2 OR C=3 AND D=4 is ((A==1 and B==2) or C==3) and D==4 \$\endgroup\$ – bigyihsuan May 18 at 15:12
  • 1
    \$\begingroup\$ Can we assume there won't be any spaces between the column = value checks? \$\endgroup\$ – Kevin Cruijssen May 18 at 15:39
  • 1
    \$\begingroup\$ What does a row like [123, "", [980, "abc"], "false"], mean, in particular, the third value. How does that work with the = operator? \$\endgroup\$ – Abigail May 18 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.