-
Notifications
You must be signed in to change notification settings - Fork 30
Querying Tables
Once a user has created a table, they can query the table using SQL via the SQL function.
The output of this function has the Table value type.
SQL(query, [param1], ...)
The query and the parameterss must be String values.
(We hope to support Number and other types of parameters in future releases.)
The query must be a single SQL SELECT statement, possibly containing ?.
When ? exists in the query, the query is treated like a SQL prepared statement,
where each ? is substituted by the values param1, ... in order.
The number of parameters must match the number of ? in the query.
Each parameter must evaluate to a single value; that is, it cannot refer to a range.
There can be at most 29 parameters.
Assume that the following table named EmployeeList has been created.
| Employee | Dept |
|---|---|
| Jesse | IT |
| Mike | Sales |
| Rhonda | Engr |
| Lee | Engr |
| Lanisha | Engr |
After entering the formula =SQL("SELECT * FROM EmployeeList WHERE Dept = 'Engr'") into cell A1, it will display the value "[3x2]".
This value indicates that the cell contains a Table value, of which there are
3 rows and 2 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | |
|---|---|---|
| 1 |
=SQL("SELECT * FROM EmployeeList WHERE Dept = 'Engr'") [3x2] |
|
| 2 |
=INDEX(A1,0,1) Employee |
=INDEX(A1,0,2) Dept |
| 3 |
=INDEX(A1,1,1) Rhonda |
=INDEX(A1,1,2) Engr |
| 4 |
=INDEX(A1,2,1) Lee |
=INDEX(A1,2,2) Engr |
| 5 |
=INDEX(A1,3,1) Lanisha |
=INDEX(A1,3,2) Engr |
A user can also call SQL on the result of these operations directly.
| A | |
|---|---|
| 6 |
=INDEX(SQL("SELECT * FROM EmployeeList WHERE Dept = 'Engr'"),2,1) Lee |
It is also possible to use String values stored in other cells.
| A | B | |
|---|---|---|
| 7 | SELECT * FROM EmployeeList WHERE Dept = ? | Engr |
| 8 |
=SQL(A6, B6) [3x2] |
We have basic support of the following relational operators:
UNION,
DIFFERENCE,
INTERSECTION,
CROSSPRODUCT,
SELECT,
PROJECT,
RENAME, and
JOIN.
The output of these functions has the Table value type.
The Union, Difference, Intersection, and Cross Product functions act like the set/relational operators of the same name.
UNION(T1, T2)
DIFFERENCE(T1, T2)
INTERSECTION(T1, T2)
CROSSPRODUCT(T1, T2)
Each of T1 and T2 must contain a Table value. Instead of a Table value, one can use a (contiguous) range of non-Table values instead; it would be treated as a Table. Each row in the range is treated as a tuple.
For UNION, DIFFERENCE, and INTERSECTION, T1 and T2 must have
the same schema; that is, their sets of attributes must be identical,
otherwise it returns a #VALUE! error.
For CROSSPRODUCT, however, T1 and T2 must have disjoint sets of
attributes, otherwise it returns a #VALUE! error.
Consider the following worksheet.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Dept | Employee | Dept |
| 2 | Jesse | IT | Mike | Sales |
| 3 | Mike | Sales | Lee | Engr |
| 4 | Rhonda | Engr | Lanisha | Engr |
After entering the formula =UNION(A1:B4,C1:D4) into cell A5, it will display the value [5 x 2].
The displayed value indicates that the cell contains a Table value, within which there are
5 rows and 2 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | |
|---|---|---|
| 5 |
=UNION(A1:B4,C1:D4) [5 x 2] |
|
| 6 |
=INDEX(A5,0,1) Employee |
=INDEX(A5,0,2) Dept |
| 7 |
=INDEX(A5,1,1) Jesse |
=INDEX(A5,1,2) IT |
| 8 |
=INDEX(A5,2,1) Mike |
=INDEX(A5,2,2) Sales |
| 9 |
=INDEX(A5,3,1) Rhonda |
=INDEX(A5,3,2) Engr |
| 10 |
=INDEX(A5,4,1) Lee |
=INDEX(A5,4,2) Engr |
| 11 |
=INDEX(A5,5,1) Lanisha |
=INDEX(A5,5,2) Engr |
One can also call INDEX on the result of these operations directly.
| A | |
|---|---|
| 11 |
=INDEX(UNION(A1:B4,C1:D4),4,1) Lee |
The Select function is used to create a copy of a Table that only contains a specified subset of tuples.
SELECT(T, filter)
T must contain a Table value.
Instead of a Table value, one can use a (contiguous)
range of non-Table values instead; it would be treated as a Table.
The filter must be a Boolean expression which may utilize
functions supported by DataSpread. The filter can refer to attributes
in table T, but the reference must be appended with the prefix ATTR_.
Consider the following worksheet.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Dept | Salary |
| 2 | Jesse | IT | 80000 |
| 3 | Mike | Sales | 100000 |
| 4 | Rhonda | Engr | 120000 |
After entering the formula =SELECT(A1:C4, ABS(ATTR_Salary-95000) > 6000) into cell A5, it will display the value [2 x 3].
The displayed value indicates that the cell contains a Table value, of which there are
2 rows and 3 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | C | |
|---|---|---|---|
| 5 |
=SELECT(A1:C4, ABS(ATTR_Salary-95000) > 6000) [2 x 3] |
||
| 6 |
=INDEX(A5,0,1) Employee |
=INDEX(A5,0,2) Dept |
=INDEX(A5,0,3) Salary |
| 7 |
=INDEX(A5,1,1) Jesse |
=INDEX(A5,1,2) IT |
=INDEX(A5,1,3) 80000 |
| 8 |
=INDEX(A5,2,1) Rhonda |
=INDEX(A5,2,2) Engr |
=INDEX(A5,2,3) 120000 |
Here are a few more examples.
-
=SELECT(A1:C4, true)all tuples -
=SELECT(A1:C4, ATTR_Salary < D1)tuples whose Salary attribute has value less than that in the cell D1. -
=SELECT(A1:C4, OR(RIGHT(ATTR_Employee, 1) = "a", LEFT(ATTR_Employee, 1) = "M"))tuples whose Employee attribute ends with "a" or begins with "M".
The Project function is used to create a copy of a Table that only contains a specified subset of attributes.
PROJECT(T, attribute1, [attribute2], ...)
T must contain a Table value. Instead of a Table value, one can use a (contiguous) range of non-Table values instead; it would be treated as a Table. Each attribute should be a String value.
Consider the following worksheet.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Dept | Supervisor |
| 2 | Jesse | IT | Paul |
| 3 | Mike | Sales | Naomi |
| 4 | Rhonda | Engr | Mel |
After entering the formula =PROJECT(A1:C4, "Supervisor", "Employee") into cell A5, it will display the value [3 x 2].
The displayed value indicates that the cell contains a Table value, of which there are
3 rows and 2 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | |
|---|---|---|
| 5 |
=PROJECT(A1:C4, "Supervisor", "Employee") [3 x 2] |
|
| 6 |
=INDEX(A5,0,1) Supervisor |
=INDEX(A5,0,2) Employee |
| 7 |
=INDEX(A5,1,1) Paul |
=INDEX(A5,1,2) Jesse |
| 8 |
=INDEX(A5,2,1) Naomi |
=INDEX(A5,2,2) Mike |
| 9 |
=INDEX(A5,3,1) Mel |
=INDEX(A5,3,2) Rhonda |
The Rename function is used to create a copy of a Table where one attribute has its name changed as specified.
RENAME(T, attributeOld, attributeNew)
T must contain a Table value. Instead of a Table value, one can use a (contiguous) range of non-Table values instead; it would be treated as a Table. Each attribute should be a String value.
Consider the following worksheet.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Dept | Supervisor |
| 2 | Jesse | IT | Paul |
| 3 | Mike | Sales | Naomi |
| 4 | Rhonda | Engr | Mel |
After entering the formula =RENAME(A1:C4, "Dept", "Team") into cell A5, it will display the value [3 x 3].
The displayed value indicates that the cell contains a Table value, of which there are
3 rows and 3 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | C | |
|---|---|---|---|
| 5 |
=RENAME(A1:C4, "Dept", "Team") [3 x 3] |
||
| 6 |
=INDEX(A5,0,1) Employee |
=INDEX(A5,0,2) Team |
=INDEX(A5,0,3) Supervisor |
| 7 |
=INDEX(A5,1,1) Jesse |
=INDEX(A5,1,2) IT |
=INDEX(A5,1,3) Paul |
| 8 |
=INDEX(A5,2,1) Mike |
=INDEX(A5,2,2) Sales |
=INDEX(A5,2,3) Naomi |
| 9 |
=INDEX(A5,3,1) Rhonda |
=INDEX(A5,3,2) Engr |
=INDEX(A5,3,3) Mel |
The Join function is used to create a join, a cross product of two tables filtered under given conditions, of two tables.
JOIN(T1, T2, [filter])
T must contain a Table value. Instead of a Table value, one can use a (contiguous) range of non-Table values instead; it would be treated as a Table.
If the filter is not used, this function performs a natural join of the two tables. That is, a tuple appears in the result if and only if for any attribute that is shared by T1 and T2, the corresponding values for that attribute in T1 and in T2 are the same.
If the filter is used, this function performs a theta join of
the two tables, with the specified filter as the "theta" condition.
T1 and T2 must have disjoint sets of attributes, otherwise it returns a #VALUE! error.
The filter must be a Boolean expression which may utilize
functions supported by DataSpread. The filter can refer to attributes
in table T, but the reference must be appended with the prefix ATTR_.
Consider the following worksheet.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Salary | Dept | Floor | Benefit | ReqSalary |
| 2 | Jesse | IT | 80000 | Engr | 1 | Massage | 90000 |
| 3 | Mike | Sales | 100000 | IT | 2 | Pension | 130000 |
| 4 | Rhonda | Engr | 120000 | ||||
| 5 | Lee | Engr | 140000 |
After entering the formula =JOIN(A1:B5, D1:E3) into cell A6, it will display the value [3 x 3].
The displayed value indicates that the cell contains a Table value, of which there are
3 rows and 3 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | C | |
|---|---|---|---|
| 6 |
=JOIN(A1:B5, D1:E3) [3 x 3] |
||
| 7 |
=INDEX(A6,0,1) Employee |
=INDEX(A6,0,2) Dept |
=INDEX(A6,0,3) Floor |
| 8 |
=INDEX(A6,1,1) Jesse |
=INDEX(A6,1,2) IT |
=INDEX(A6,1,3) 2 |
| 9 |
=INDEX(A6,2,1) Rhonda |
=INDEX(A6,2,2) Engr |
=INDEX(A6,2,3) 1 |
| 10 |
=INDEX(A6,3,1) Lee |
=INDEX(A6,3,2) Engr |
=INDEX(A6,3,3) 1 |
After entering the formula =JOIN(A1:C5, F1:G3, ATTR_Salary >= ATTR_ReqSalary) into cell A11, it will display the value [4 x 5].
The displayed value indicates that the cell contains a Table value, of which there are
4 rows and 5 columns/attributes. In order to view the entries inside the table value, one must
use the function INDEX, as in the example below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 11 |
=JOIN(A1:C5, F1:G3, ATTR_Salary >= ATTR_ReqSalary) [4 x 5] |
||||
| 12 |
=INDEX(A11,0,1) Employee |
=INDEX(A11,0,2) Dept |
=INDEX(A11,0,3) Salary |
=INDEX(A11,0,4) Benefit |
=INDEX(A11,0,5) ReqSalary |
| 13 |
=INDEX(A11,1,1) Mike |
=INDEX(A11,1,2) Sales |
=INDEX(A11,1,3) 100000 |
=INDEX(A11,1,4) Massage |
=INDEX(A11,1,5) 90000 |
| 14 |
=INDEX(A11,2,1) Rhonda |
=INDEX(A11,2,2) Engr |
=INDEX(A11,2,3) 120000 |
=INDEX(A11,2,4) Massage |
=INDEX(A11,2,5) 90000 |
| 15 |
=INDEX(A11,3,1) Lee |
=INDEX(A11,3,2) Engr |
=INDEX(A11,3,3) 140000 |
=INDEX(A11,3,4) Massage |
=INDEX(A11,3,5) 90000 |
| 16 |
=INDEX(A11,4,1) Lee |
=INDEX(A11,4,2) Engr |
=INDEX(A11,4,3) 140000 |
=INDEX(A11,4,4) Pension |
=INDEX(A11,4,5) 130000 |