# Functional SQL¶

The functional forms of `delete`

, `exec`

, `select`

and `update`

are particularly useful for programmatically generated queries, such as when column names are dynamically produced. (The q interpreter parses `select`

, `exec`

, `update`

and `delete`

into their equivalent functional forms, so there is no performance difference.) The functional forms are

```
![t;c;b;a] /update and delete
?[t;c;b;a] /select and exec
?[t;i;p] /simple exec
```

`t`

- is a table
`c`

- is a list of
**where**specifications (constraints) - Each item in
`c`

is a parse tree of a boolean- or int- valued binary function together with its arguments; each argument an expression containing column names and other variables. The function is applied to the two arguments, producing a boolean vector. The resulting boolean vector selects the rows that yield non-zero results. The selection is performed in the order of the items in`c`

, from left to right. `b`

- is a dictionary of
**group-by**specifications (by phrase) - The domain of
`b`

is a list of symbols: the key names for the grouping. The range of`b`

is a list of column expressions whose results are used to construct the groups. The grouping is ordered by the keys, from major to minor. `a`

- is a dictionary of
**select**specifications (aggregations) - The domain of
`a`

is a list of symbols: the names of the produced columns. Each item of the range of`a`

is a parse tree consisting of a function and its argument/s, each of which is a column name or another parse tree. For each parse tree, the function is applied to the specified value/s for each row and the result is returned. When nested, the parse trees are evaluated recursively. `i`

- is a list of indexes
`p`

- is a parse tree

Use my name

All q entities in `a`

, `b`

and `c`

must be referenced by name, meaning they appear as symbols containing the entity names.

Enlist me

Note throughout the use of `enlist`

to create singletons to ensure that appropriate entities are lists.

[q-ist] Functional Query Functions.

`?`

(select)¶

Syntax: `?[t;c;b;a]`

```
q)show t:([]n:`x`y`x`z`z`y;p:0 15 12 20 25 14)
n p
----
x 0
y 15
x 12
z 20
z 25
y 14
q)select m:max p,s:sum p by name:n from t where p>0,n in `x`y
name| m s
----| -----
x | 12 12
y | 15 29
```

Following is the equivalent functional form. Note the use of `enlist`

to create singletons, ensuring that appropriate entities are lists.

```
q)c: ((>;`p;0);(in;`n;enlist `x`y))
q)b: (enlist `name)!enlist `n
q)a: `m`s!((max;`p);(sum;`p))
q)?[t;c;b;a]
name| m s
----| -----
x | 12 12
y | 15 29
```

Degenerate cases

- For no constraints, make
`c`

the empty list - For no grouping make
`b`

a boolean`0b`

- To produce all columns of
`t`

in the result, make`a`

the empty list`()`

`select from t`

is equivalent to functional form `?[t;();0b;()]`

.

`?`

(exec)¶

Syntax: `?[t;c;b;a]`

The functional form of `exec`

is a simplified form of *select*. Since the constraint parameter is the same as in *select*, we omit it in the following.
In the simplest example of a single result column, the group-by parameter is the empty list and the aggregate parameter is a symbol atom.

```
q)exec n from t
`x`y`x`z`z`y
q)?[t;();();`n] / same as previous exec
`x`y`x`z`z`y
```

In the same query with multiple columns, the group-by parameter is the empty list and the aggregate parameter is a dictionary as it would be in a *select*. Remember that the result is a dictionary rather than a table.

```
q)exec n,p from t
n| x y x z z y
p| 0 15 12 20 25 14
q)?[t;();();`n`p!`n`p] / same as previous exec
n| x y x z z y
p| 0 15 12 20 25 14
```

If you wish to group by a single column, specify it as a symbol atom.

```
q)exec p by n from t
x| 0 12
y| 15 14
z| 20 25
q)?[t;();`n;`p] / same as previous exec
x| 0 12
y| 15 14
z| 20 25
```

More complex examples of *exec* seem to reduce to the equivalent *select*.

`?`

(simple exec)¶

Syntax: `?[t;i;p]`

Where `t`

is a (non-partitioned) table, `i`

is a list of indices, and `p`

is a parse tree, is another form of *exec*.

```
q)show t:([]a:1 2 3;b:4 5 6;c:7 9 0)
a b c
-----
1 4 7
2 5 9
3 6 0
q)?[t;0 1 2;`a]
1 2 3
q)?[t;0 1 2;`b]
4 5 6
q)?[t;0 1 2;(last;`a)]
3
q)?[t;0 1;(last;`a)]
2
q)?[t;0 1 2;(*;(min;`a);(avg;`c))]
5.333333
```

`!`

(update)¶

Syntax: `![t;c;b;a]`

```
q)show t:([]n:`x`y`x`z`z`y;p:0 15 12 20 25 14)
n p
----
x 0
y 15
x 12
z 20
z 25
y 14
q)select m:max p,s:sum p by name:n from t where p>0,n in `x`y
name| m s
----| -----
x | 12 12
y | 15 29
q)update p:max p by n from t where p>0
n p
----
x 0
y 15
x 12
z 25
z 25
y 15
q)c: enlist (>;`p;0)
q)b: (enlist `n)!enlist `n
q)a: (enlist `p)!enlist (max;`p)
q)![t;c;b;a]
n p
----
x 0
y 15
x 12
z 25
z 25
y 15
```

Tip

The degenerate cases are the same as in *select*.

`!`

(delete)¶

The functional form of delete is a simplified form of *update*.

```
![t;c;0b;a]
```

One of `c`

or `a`

must be empty, the other not. `c`

selects which rows will be removed. `a`

is a symbol vector with the names of columns to be removed.

```
q)t:([]c1:`a`b`c;c2:`x`y`z)
q)/following is: delete c2 from t
q)![t;();0b;enlist `c2]
c1
--
a
b
c
q)/following is: delete from t where c2 = `y
q)![t;enlist (=;`c2; enlist `y);0b;`symbol$()]
c1 c2
-----
a x
c z
```